Changing the SID of an Oracle Database keeping the DBID unchanged

When I once needed to change a DB SID, I didn't notice that it is possible to perform this, without changing the DBID. As consequence of my mishap, I had to perform an "DATABASE OPEN RESETLOGS" with all its consequences.

So, If you are only changing the DB SID  (leaving the DBID unchanged)  you should use the parameter SETNAME=YES.

For example:

OS> nid TARGET=SYS/<pwd>@<DB_Alias> DBNAME=NewDbSID SETNAME=YES

If you forget to use it, like me in the following example, the DBID will also be changed:

OS> nid TARGET=SYS/<pwd>@<DB_Alias> DBNAME=NewDbSID

. . .

Connected to database OldDbSID (DBID=1075560178)

. . .

Change database ID and database name OldBbSID to NewDbSID? (Y/[N]) => y

Proceeding with operation

Changing database ID from 1075560178 to 1518037673

Changing database name from OldBbSID to NewDbSID

    Control File ... - modified

    Control File ... - modified

...

    Datafile ... - dbid changed, wrote new name

    Datafile ... - dbid changed, wrote new name

...

    Control File ... - dbid changed, wrote new name

    Control File ... - dbid changed, wrote new name

    Instance shut down

Database name changed to NewDbSID.

Modify parameter file and generate a new password file before restarting.

Database ID for database NewDbSID changed to 1518037673.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

... you will need to perform an "OPEN RESETLOGS".

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

If you are using the RMAN catalog, you will then need to register this DB in the RMAN catalog again. In short:

OS> rman target / catalog /@<RmanCatalogAlias>

RMAN> register database;

Perhaps you also want to remove the DB entry with the old DBID from the catalog.

The previous backups and archived logs of the database become unusable.
You should also perform a full backup of the DB as soon as possible.

Tags: SID, DBID, nid
June 27, 2016 at 05:13PM

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.