Changing the SID of an Oracle DB

In order to perform some tests with legacy applications, we needed to make an already exiting DB "simulate" being another DB.

Besides creating the needed DB services, it was necessary to change the DBs SID (Site IDentifier), because some of the legacy applications could only connect to the DB using its SID (instead of a DB-Service Name).

The Oracle Dokument "DBNEWID Utility" describes how to do it by using Oracle's nid utility in detail.

Here is an example how to change only the DB SID to NEW_SID:

nid TARGET=SYS DBNAME=NEW_SID SETNAME=YES

Attention

If SETNAME=YES is missing, the Database ID will also be changed.

Then you will need to open the DB with: ALTER DATABASE OPEN RESETLOGS;

If the RMAN catalog is used, this DB must be registered into the RMAN catalog with the new ID.

It would be good to remove the old database ID entry from RMAN catalog).

Otherwise the RMAN Backup will output the error :

    RMAN-20001: target database not found in recovery catalog

(I will describe this in one of my next blog posts.)

After testing, I needed to change the SID back, but I got the following error from nid and also when trying to connect with sqlplus using the explicit tns-alias
(sqlplus "user"/"pwd"@"tns-alias"):

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

I the listener log I've found the following error messages, that gave me a hint to the problem:

TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adater error
TNS-00512: Address already in use
Linux Error: 98: Address already in use

There were several instances of the listener running. (I suppose that the listener could not stop correctly after I've changed the SID.) So performed the following steps:

• Killed all listeners
  Got the process ids of the listeners: ps -ef | grep -i listen
  Killed each listener process: kill -9 "MyListenerId"
  .
• "Correct" SID in listerner.ora
  Checked that the following "correct" (new temporary) NEW_SID is used in the file $ORACLE_HOME/network/admin/listener.ora :
  
  SID_LIST_"ListenerName" =
   (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = "DbGlobalName")
         (SID_NAME = "NEW_SID")
         (ORACLE_HOME = /u00/app/oracle/product/11.2.0.4.0.P0_EE)
       )
   )
  .
• Start the listener again
  srvctl start listener -l "ListenerName"
  or
  lsnrctl start "ListenerName"
.

After doing this, the SID could be restored using nid.