8 Replies Latest reply: Mar 11, 2012 4:11 PM by orafad RSS

    ORA-12514 after change SID of XE 11.2

    cardel
      Hello,
      I have XE 11.2 installed on Windows x64. I would like to change SID from XE to another name.
      I tried this tutorial:
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:963124000346653522

      I changed XE SID to HOCHBPS, also changed ORACLE_SID in windows registry and environment variable.

      I can start listener and also DB after change, I am able to call tnsping to new sid HOCHBPS, I am able to connect to DB "sqlplus / as sysdba"

      BUT

      I am not able to connect as normal user. I am getting ORA-12514 for the first time, but when I type user and password for the second time I am able to connect to DB.

      Do you have some ideas?

      -----
      C:\Users\PC>sqlplus system/password@hochbps

      SQL*Plus: Release 11.2.0.2.0 Production on So Be 10 19:47:36 2012

      Copyright (c) 1982, 2010, Oracle. All rights reserved.

      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


      Enter user-name: system
      Enter password:

      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

      SQL>
      -----

      tnsnames.ora
      -----
      HOCHBPS =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HOCHBPS)
      )
      )

      EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )

      ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
      )
      (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
      )
      )

      listener.ora
      -----
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      )
      )
      lsnrctl status
      -----
      C:\Users\PC>lsnrctl status

      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 10-BE-2012 20:02:19

      Copyright (c) 1991, 2010, Oracle. All rights reserved.

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
      Start Date 10-BE-2012 19:45:16
      Uptime 0 days 0 hr. 17 min. 2 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
      Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\PC-VAIO\listener\alert\log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC2ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PC-VAIO)(PORT=8080))(Presentation=HTTP)(Session=RAW))
      Services Summary...
      Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "XE" has 1 instance(s).
      Instance "hochbps", status READY, has 1 handler(s) for this service...
      Service "XEXDB" has 1 instance(s).
      Instance "hochbps", status READY, has 1 handler(s) for this service...
      The command completed successfully
        • 1. Re: ORA-12514 after change SID of XE 11.2
          Udo
          Hello Karel,

          what's the output of
          select instance_name from v$instance;
          I guess you still get "XE", right?
          Was the removal of the old service successful? Possibly you have to remove it first before you are able to create the new one with oradim.
          For other troubleshooting information, see {thread:id=2343827}.
          If this all doesn't help, could you please provide the exact steps you've performed to do the renaming?

          Just a side note:
          I have XE 11.2 installed on Windows x64.
          This is (currently) not a supported OS for XE 11.2. Did you receive any errors during installation or at some point of your renaming procedure?

          -Udo
          • 2. Re: ORA-12514 after change SID of XE 11.2
            cardel
            what's the output of
            select instance_name from v$instance;
            I guess you still get "XE", right?
            no I saw HOCHBPS as output

            Was the removal of the old service successful? Possibly you have to remove it first before you are able to create the new one with oradim.
            Yes, old service was removed successful and new one created
            This is (currently) not a supported OS for XE 11.2. Did you receive any errors during installation or at some point of your renaming procedure?
            No errors during installation or renaming procedure

            My steps (exactly same as here: [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:963124000346653522] ):

            1) shutdown database C:\oraclexe\app\oracle\product\11.2.0\server\bin\StopDB.bat
            2) stop the oracle services using control panel (oracleserviceXE and the listener)
            3) copy C:\oraclexe\app\oracle\product\11.2.0\server\dbs\spfileXE.ora to C:\oraclexe\app\oracle\product\11.2.0\server\dbs\spfileHOCHBPS.ora
            4) create C:\oraclexe\app\oracle\product\11.2.0\server\database\initHOCHBPS.ora by copying C:\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora and modify the spfile line to point to the new spfile
            5) create the new service to start database and remove the old one
            oradim -new -sid HOCHBPS -startmode auto -pfile C:\oraclexe\app\oracle\product\11.2.0\server\database\initHOCHBPS.ora
            oradim -delete -sid XE
            6) start listener
            lsnrctl start
            7) set ORACLE_SID=SOMETHIN
            8) sqlplus / as sysdba
            alter system register;
            9) edit tnsnames.ora (rename XE to HOCHBPS)
            • 3. Re: ORA-12514 after change SID of XE 11.2
              orafad
              cardel wrote:
              I changed XE SID to HOCHBPS, also changed ORACLE_SID in windows registry and environment variable.
              ERROR:
              ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
              Services Summary...
              Service "XE" has 1 instance(s).
              Instance "hochbps", status READY, has 1 handler(s) for this service...
              Yes you did, but service is still named XE. Thus listener responds as expected since you asked for the service hochbps, not known to the listener (as verified by the services listing).

              Did you change SID (also used as default value for instance_name) when you actually wanted to change service name?
              • 4. Re: ORA-12514 after change SID of XE 11.2
                Lubiez Jean-Valentin
                Hello,

                Service "XE" has 1 instance(s).
                You may check the SERVICE_NAMES defined in the Database by quering the View DBA_SERVICES and change it:

                http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams217.htm

                http://docs.oracle.com/cd/B28359_01/server.111/b28310/create007.htm


                Hope this help.
                Best regards,
                Jean-Valentin
                • 5. Re: ORA-12514 after change SID of XE 11.2
                  cardel
                  Did you change SID (also used as default value for instance_name) when you actually wanted to change service name?
                  Where/how should I change SID?
                  • 6. Re: ORA-12514 after change SID of XE 11.2
                    orafad
                    cardel wrote:
                    Where/how should I change SID?
                    SID is the internal or local identifier for the Oracle instance, including the memory and background processes allocated at instance start/creation. Over Oracle Net, clients do not connect directly with a specific SID instead there is a "mapping" from Service to what instance in which Oracle Home that provides a named service. This is different from connecting like 'sqlplus scott/tiger', where connection is made within a certain Home and using ORACLE_SID directly.

                    In general you "set" SID by:

                    export ORACLE_SID=<sid string> (or equivalent way of setting OS environment variable)
                    sqlplus / as sysdba
                    startup nomount

                    <-- in the last step an instance (i.e. memory and background processes) is created with SID as the identifier. (Note: on Windows platform, threads are used to implement Oracle processes.)

                    But, as you know, on Windows platform, the OracleService<SID> Windows service is also required.

                    If you followed the Asktom steps, you've changed SID already.

                    But perhaps the intention was not to change SID? Maybe you wanted to change some other object name, like database name, instance name or service name?

                    If you could explain the reasons behind wanting to change SID, it'll be possible to suggest a solution.

                    Edit:
                    Adding two potential suggestions:

                    - if you were just experimenting and only thing you want now is to be able to connect, then
                    change your HOCHBPS tnsnames.ora entry and change Service parameter to SERVICE_NAME=XE.

                    - if you want to connect by a different connect string, it is enough to only change, or add another entry to, the tnsnames.ora is as you may name the entry as you please. E.g.

                    MYDB =
                    (DESCRIPTION = ( ... (SERVICE_NAME= XE) ... )

                    Edited by: orafad on Mar 11, 2012 12:44 PM

                    Edited by: orafad on Mar 11, 2012 12:57 PM
                    • 7. Re: ORA-12514 after change SID of XE 11.2
                      cardel
                      I wanted to change default SID from XE to another name, because I wanted to increase security.
                      I found interesting document here http://www.red-database-security.com/wp/hacking_and_hardening_oracle_XE.pdf|

                      So I have made another step recomended by "Jean-Valentin":
                      alter system set service_names='HOCHBPS';
                      alter system register;
                      
                      SQL> select name from dba_services;
                      
                      NAME
                      ----------------------------------------------------------------
                      SYS$BACKGROUND
                      SYS$USERS
                      XEXDB
                      XE
                      HOCHBPS
                      Status of listener is bellow. I can now connect to HOCHBPS without any problem. But my last question is... Can I now delete XE service with DBMS_SERVICE.DELETE_SERVICE('XE') ?
                      C:\Users\PC>lsnrctl status
                      
                      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 11-BěE-2012 13:13
                      
                      Copyright (c) 1991, 2010, Oracle.  All rights reserved.
                      
                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
                      STATUS of the LISTENER
                      ------------------------
                      Alias                     LISTENER
                      Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
                      Start Date                11-BěE-2012 13:12:39
                      Uptime                    0 days 0 hr. 0 min. 29 sec
                      Trace Level               off
                      Security                  ON: Local OS Authentication
                      SNMP                      OFF
                      Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\a
                      Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\PC-VAIO\listener\a
                      Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PC-VAIO)(PORT=8080))(Presentation=HT
                      Services Summary...
                      Service "CLRExtProc" has 1 instance(s).
                        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                      Service "PLSExtProc" has 1 instance(s).
                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                      Service "XE" has 1 instance(s).
                        Instance "hochbps", status READY, has 1 handler(s) for this service...
                      Service "XEXDB" has 1 instance(s).
                        Instance "hochbps", status READY, has 1 handler(s) for this service...
                      Service "hochbps" has 1 instance(s).
                        Instance "hochbps", status READY, has 1 handler(s) for this service...
                      The command completed successfully
                      • 8. Re: ORA-12514 after change SID of XE 11.2
                        orafad
                        That puts things in a different light, so to speak.

                        Related to hardening, minimizing surface area, etc., there's a lot you could (and should) do besides changing the well-known name.
                        For example block the listener port(s), with exception maybe for a few known IP addresses.

                        Back to the topic of this thread, what you want to change is the service name not SID, since the former is used in connections and the latter is "internal".

                        The chain of default values is: service name <= db_unique_name <= db_name. I.e. if you change the name of the database in XE you'll also see and expose a different service name.

                        I don't have the time now for testing and writing up a case, but it would involve at least something like below (in a kind of pseudo notation):
                        C:\>nid target=/ ... setname ...
                        
                        C:\>type c:\oracle\init_somename.ora | findstr "db_name"
                        *.db_name='SOMENAME'
                        
                        C:\> create spfile ...
                        
                        SQL> show parameters db_name
                        
                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- --------
                        db_name                              string      SOMENAME
                        SQL> show parameters uniq
                        
                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- --------
                        db_unique_name                       string      SOMENAME
                        SQL> show parameters service
                        
                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- --------
                        service_names                        string      SOMENAME
                        Since the name of the database was different from creation, other traces, such as services, may need to be removed or altered.

                        Hope this helps.