How to change DBID

DBID change

If the new database was created by copying files from an existing database, it will have the same DBID as the original database and cannot be registered in the same recovery catalog. Hence, rman will give the following error:

RMAN-20002: target database already registered in recovery catalog

Steps to change DBID:

1. Source into target database and check the DBID before change

   $sqlplus "/as sysdba"

  

   SQL> select dbid, name, open_mode, activation#, created from v$database;

         DBID NAME      OPEN_MODE  ACTIVATION# CREATED

   ---------- --------- ---------- ----------- ---------

   3220625138 ORCL   READ WRITE  3925125663 28-SEP-11

2. SQL> shutdown immediate;

   Database closed.

   Database dismounted.

   ORACLE instance shut down.

Note: If you have to do a shutdown abort, then start it up and shut it down again. Do not implement  this change against a database which has used the abort option to shutdown

3. SQL> startup mount

   ORACLE instance started.

   Total System Global Area  135338868 bytes

   Fixed Size                   453492 bytes

   Variable Size             109051904 bytes

   Database Buffers           25165824 bytes

   Redo Buffers                 667648 bytes

   Database mounted.

   SQL>exit

4. Now execute NID $ prompt

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 – Production

omrdb<ORCL>$ nid target=/

DBNEWID: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database ORCL (DBID=3220625138)

Control Files in database:

    /dev/oracle/oradata/Orcl/ctrl01

    /dev/oracle/oradata/Orcl/ctrl02

Change database ID of database ORCL? (Y/[N]) => Y

Proceeding with operation

Changing database ID from  3220625138  to 3220625138

    Control File /dev/oracle/oradata/Orcl/ctrl01 - modified

    Control File /dev/oracle/oradata/Orcl/ctrl02 - modified

    Datafile /dev/oracle/oradata/Orcl/sys01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/rb10101 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/spare01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tools01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/users01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/idx01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab02 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/idx02 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab03 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab04 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/undo01 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab05 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/idx03 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab06 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/tab07 - dbid changed

    Datafile /dev/oracle/oradata/Orcl/users02 - dbid changed

    Control File /dev/oracle/oradata/Orcl/ctrl01 - dbid changed

    Control File /dev/oracle/oradata/Orcl/ctrl02 - dbid changed

Database ID for database ORCL changed to 3120624238.

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

Shut down database and open with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

5. SQL> shutdown immediate;

   ORA-01109: database not open

   Database dismounted.

   ORACLE instance shut down.

6. Create the new passwordfile in $ORACLE_HOME/dbs directory using this command

$orapwd file=orapw$ORACLE_SID entries=12 password=SYS_PASSWORD

7. SQL> startup mount

   ORACLE instance started.

   Total System Global Area  135338868 bytes

   Fixed Size                   453492 bytes

   Variable Size             109051904 bytes

   Database Buffers           25165824 bytes

   Redo Buffers                 667648 bytes

   Database mounted.

8. SQL> alter database open resetlogs;

   Database altered.

9. Verify new DBID

    SQL> select dbid,name,open_mode,activation#,created from v$database;

DBID              NAME   OPEN_MODE  ACTIVATION# CREATED

----------------- ----------- ------------------ --------------------  -------------

3220625138   ORCL READ WRITE  3220625138         28-SEP-11

10. If you have Standby database for this database, you need to rebuild it  after DBID change.

Note:

REVERT option allows us to go back on the steps of executing.  If its YES then a failed change of DBID should be reverted but a successfully completed change of DBID cannot be reverted.  REVERT=YES is only valid when a DBID change failed.  Default value is NO.

     

Steps to change DBNAME only

  1. Source in to target database

  2. Shutdown target database

      SQL>shutdown immediate;

      SQL>startup mount;

  3. Open one session and run NID with sysdba privileges

      $nid TARGET=/ DBNAME=<new dbname> SETNAME=Y

    

     - the value of DBNAME is the new dbname of the database

     - SETNAME must be set to Y. The default is N and causes the

       DBID to be changed also.

 

5. Shutdown the database

    Shutdown immediate;

6. Set the DB_NAME initialization parameter in the initialization parameter

     file to the new database name

7. Create a new password file

    $cd $ORACLE_HOME/dbs

    $orapwd file=orapw$ORACLE_SID entries=12 password=SYS_PASSWORD

  8. Startup of the database (without resetlogs)