Change DB ID in oracle

There are two ways to change the DB ID.

The DB ID is the unique identifier of a DB. This is a randomly generated number that is assigned to the database at the time of creation. typically this would be a 10 digit number.

If there are scenarios to change the DB ID then please follow any of the two ways discussed below.

Case 1:
-------

Usage of nid utility which is in $ORACLE_HOME/bin location
-----------------------------------------------------------
DB Name - TEST

Login to sqlplus as sysdba


$> sqlplus / as sysdba

SQL>select dbid form v$database; -- to check the current DB ID of the database.

SQL>shutdown immediate

start the database in mount state so that the control file is read and all the other datafiles are read.

SQL> startup mount;

SQL> exit

on the OS command prompt execute the following command.

$>nid target=TEST

It will prompt for password. password is the sys account's password.

password - sys password

Once the password is accepted it will ask whether to change the DB ID or not. Press 'Y'

change database ID of database DBNAMR (Y/[N]) => Y

proceeding with operation
..........
.........
.
.

DBNEWID- completed successfully.



Here you will see that the DB ID is modified in control files and changed changed in datafiles.

Also, all the previous backups and archivelog backups will be unusable.


connect to sqlplus

sqlplus / as sysdba

open the database using reset logs as the DBID is changed the DB must be opened using resetlogs

SQL> alter database open resetlogs;

SQL> select dbid form v$database; -- now a new ID is assigned to the database.

SQL> shutdown immediate;

SQL> startup;

The DB ID is changed.

Take a full backup of the database and archivelogs if any.
-------------------------
Case 2:
------------------------

Using dbms_backup_restore pacakge.

Login to sqlplus

$> sqlplus / as sysdba

SQL> select dbid from v$database; -- To note the current DB ID

Take the control file to trace backup

sql> alrer database backup controlfile to trace as '/home/oracle/trace.sql' reuse; -- reuse will be used to replace the existing trace files if any on the location.

SQL> shutdown immediate;

SQL> exit

open the trace file and remove all the commented lines and -------------------------------------------------------

$> remove reuse instead put set also remove noresetlogs put resetlogs in create controlfile statement



sqlplus / as sysdba

SQL> startup mount;

SQL> select name,dbid form v$database;

SQL> execute dbms_backup_restore.zeroDbid(0);

PL/SQL procedure successfully completed.

SQL> select name,dbid form v$database; - output comes as is becuase its still in dynamic performance views

SQL> shutdown immediate;

SQL> exit

$> delete all the control files

sqlplus / as sysdba

SQL> @/home/oracle/trace.sql

controlfile created,

SQL> alter database open resetlogs;

database altered

SQL> select name,dbid from v$database;

DB is will be changed.

Take a full backup of the database and archivelogs if any.