Changing the DBID in Oracle Databases
============================================
Afer clone the database, the DB ID remains same as like the source database.
if we need to change it to the different DB ID, then use change the dbid using NID Utility .
once DBID chnaged of the database all previous backups will become unusable and we must open the database with RESETLOGS option.
Once you change the DBID make sure you take database backup immediatly
Step 1 : Find out current dbid
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
2332864271 TEST
SQL>
Step 2 : Shutdown database
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Step 3 : start database in mount stage.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
SQL>
Step 4 : Now change the dbid using NID Utility.
[oracle@localhost ~]$ which nid
/u01/app/oracle/product/11.2.0/dbhome_1/bin/nid
[oracle@localhost ~]$
[oracle@localhost admin]$ sqlplus sys/sys@test as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:01:18 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
2332864271 TEST
SQL>
nid target=sys/sys@test
[oracle@localhost admin]$ nid target=sys/sys@test
DBNEWID: Release 11.2.0.4.0 - Production on Tue Mar 10 22:02:50 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database TEST (DBID=2332864271)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/test/control01.ctl
/u01/app/oracle/fast_recovery_area/test/control02.ctl
Change database ID of database TEST? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2332864271 to 2332867595
Control File /u01/app/oracle/oradata/test/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - modified
Datafile /u01/app/oracle/oradata/test/system01.db - dbid changed
Datafile /u01/app/oracle/oradata/test/sysaux01.db - dbid changed
Datafile /u01/app/oracle/oradata/test/undotbs01.db - dbid changed
Datafile /u01/app/oracle/oradata/test/users01.db - dbid changed
Datafile /u01/app/oracle/oradata/test/example01.db - dbid changed
Datafile /u01/app/oracle/oradata/test/temp01.db - dbid changed
Control File /u01/app/oracle/oradata/test/control01.ctl - dbid changed
Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - dbid changed
Instance shut down
Database ID for database TEST changed to 2332867595.
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 ID.
DBNEWID - Completed succesfully.
[oracle@localhost admin]$
Step 5 : Start the database in mount stage
[oracle@localhost admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:04:10 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
SQL>
step 6 : open database using resetlogs option
SQL> alter database open resetlogs;
Database altered.
*alter database open command will not work .
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Step 7 : New DBID
SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- --------------------
2332867595 TEST READ WRITE
SQL>
