How to change the SID using DBNEWID utility

/ December 6, 2016

There are several requests to a DBA for changing the Database Name & DBID of a database, suppose user wants to clone one his env. (say PROD01) to make it a dev or test instance, so he clones the VM but this would give a database with the same Database Name as that of the original instance. Since the aim was to create a similar env. to that of PROD01 but to name as it DEV01 there comes a request to change the database name of the PROD01 instance.

It sounds very simple, right? but for anyone with database knowledge they could think of several places where DB Name & DBID is used so can understand what nightmare it would be to change the Database Name from all those places.

Indeed it was a challenging task until Oracle came up with DBNEWID tool with 9i, this post is a Step by Step instruction to change the DBID & DB Name of an Oracle instance with DBNEWID utility.

Here we are going to change the DB Name from PROD01 to DEV01.

STEP 1:- DBNEWID (NID) utility requires that the database has an updated pfile.

STEP 1a: – Check if SPFILE is being used.

Use the following query to check if SPFILE is being used:-

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File" FROM v$parameter WHERE name = 'spfile';

If pfile is being used, please make a copy with initDEV01.ora and please proceed to STEP 2. Else if spfile is being used please create pfile from spfile as described in Step 1b.

STEP 1b: – Creating PFILE.

Use following command to create pfile in $ORACLE_HOME/dbs location with init.ora filename:-

create pfile from spfile;

In our case this would generate a file named initPROD01.ora. Next step is to rename the file as per the new DB name, in our case initDEV01.ora.

STEP 2: – Shut down the database & listener.

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 6 09:12:16 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit

lsnrctl stop <LISTENER_NAME>

STEP 3: – Start the DB in MOUNT mode.

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 6 09:12:16 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             721423600 bytes
Database Buffers          838860800 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> exit

STEP 4: – Invoke the DBNEWID utility to change the DB Name.

Invoke the DBNEWID utility with a user with SYSDBA privilege with target clause and NEW DB NAME with DBNAME clause, as:-

nid target=SYS DBNAME=DEV01

DBNEWID: Release 11.2.0.4.0 - Production on Tue Dec 6 09:27:36 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:
Connected to database PROD01 (DBID=1057319719)

Connected to server version 11.2.0

Control Files in database:
    /apps/oracle/data01/PROD01/control01.ctl
    /apps/oracle/data01/PROD01/control02.ctl
    /apps/oracle/admin/PROD01/diag/rdbms/PROD01/PROD01/control/control03.ctl

Change database ID and database name PROD01 to DEV01? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1057319719 to 3663966145
Changing database name from PROD01 to DEV01
    Control File /apps/oracle/data01/PROD01/control01.ctl - modified
    Control File /apps/oracle/data01/PROD01/control02.ctl - modified
    Control File /apps/oracle/admin/PROD01/diag/rdbms/PROD01/PROD01/control/control03.ctl - modified
    Datafile /apps/oracle/data01/PROD01/system01.db - dbid changed, wrote new name
    Datafile /apps/oracle/data01/PROD01/sysaux01.db - dbid changed, wrote new name
    Datafile /apps/oracle/data01/PROD01/undotbs01.db - dbid changed, wrote new name
    Datafile /apps/oracle/data01/PROD01/users01.db - dbid changed, wrote new name
    Datafile /apps/oracle/data01/PROD01/tools01.db - dbid changed, wrote new name
    Datafile /apps/oracle/data01/PROD01/dba_audit01.db - dbid changed, wrote new name
    Datafile /apps/oracle/data01/PROD01/temp01.db - dbid changed, wrote new name
    Control File /apps/oracle/data01/PROD01/control01.ctl - dbid changed, wrote new name
    Control File /apps/oracle/data01/PROD01/control02.ctl - dbid changed, wrote new name
    Control File /apps/oracle/admin/PROD01/diag/rdbms/PROD01/PROD01/control/control03.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to DEV01.
Modify parameter file and generate a new password file before restarting.
Database ID for database DEV01 changed to 3663966145.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Step 5: – Update the init file for the new instance.

In Step 1, we took a copy of init file and renamed it as initDEV01.ora, now we would open this file and rename all the occurrences of old SID i.e PROD01 with new SID DEV01.

NOTE:- If there is any directory with the name of OLD SID please DO NOT rename that occurrence.

Step 6: – Update other config files.

Now update the following configuration files with the new SID.

  • oratab
  • listener.ora
  • tnsnames.ora
  • .profile

Step 7: – Start the listener.

As we have updated the listener.ora file with the SID change we just need to start the listener.

lsnrctl start <LISTENER_NAME>

Step 8: – Start the database in mount mode and open with resetlogs.

As mentioned in the final output of nid logs, we need to start the database with resetlogs option.

Ensure that ORACLE_SID is set to the new SID.

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 6 09:12:16 2016

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

Connected to an idle instance.

SQL>startup mount;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size             721423600 bytes
Database Buffers          838860800 bytes
Redo Buffers                7471104 bytes
Database mounted.

SQL>alter database open resetlogs;

Database altered.

SQL>alter system register;

SQL>

That’s it…!!!! You have successfully changed the database name of your instance.
Hope you find this post useful, in case you get into any error do comment below and we’ll try to work with you to resolve the issue.

To see all the available clauses with nid, invoke nid with help clause:-

nid help

DBNEWID: Release 11.2.0.4.0 - Production on Tue Dec 6 09:27:36 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

Related posts

3 thoughts on “How to change the SID using DBNEWID utility”

  1. I was doing ok. It said it changed everything successfully. Then I tried to connect and depending how I attempt I get tns errors which. I tried to see if rebooting the server would help. Nana.

    One question I have is Do you need to recreate the service?

    Like

    Reply

    • Hi Tom,

      There are changes that needs to be made network configuration files (tnsnames.ora & listener.ora) as mentioned in STEP 6 in the above article, the changes completely depends on your Error & your tnsnames.ora/listener.ora.

      Thus if you could mention 1) The tns Error you are referring to. & 2) Share your tnsnames.ora & listener.ora entries. And we can work on it accordingly.

      Regards
      Nitish

      Like

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.