Objective:- Change DBID in oracle database .
For example, we will be changing existing DBID LOGCDB to a
new DBID UATCDB
Step 1. Shutdown database instance
|
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. |
Step 2. Start database in mount mode
|
SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 4294964000 bytes Fixed Size
9143072 bytes Variable Size 805306368 bytes Database Buffers
3472883712 bytes Redo Buffers
7630848 bytes Database mounted. SQL> select name,open_mode,instance_name from v$database,v$instance; NAME OPEN_MODE INSTANCE_NAME --------- -------------------- ---------------- LOGCDB MOUNTED LOGCDB |
Step 3. Change DBNAME using nid utility
|
Syntax for changing DBNAME nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME [logminedb@ccuine1126 dbs]$ nid
target=sys/manager@LOGCDB DBNAME=UATCDB DBNEWID: Release 19.0.0.0.0 - Production on Tue Apr 19
16:47:33 2022 Copyright (c) 1982, 2019, Oracle and/or its
affiliates. All rights reserved. Connected to database LOGCDB (DBID=3597672493) Connected to server version 19.9.0 Control Files in database:
/db/app/logminedb/oradata/LOGCDB/control01.ctl
/db/app/logminedb/oradata/LOGCDB/control02.ctl Change database ID and database name LOGCDB to UATCDB? (Y/[N])
=> Y Proceeding with operation Changing database ID from 3597672493 to 205605669 Changing database name from LOGCDB to UATCDB Control File
/db/app/logminedb/oradata/LOGCDB/control01.ctl - modified Control File
/db/app/logminedb/oradata/LOGCDB/control02.ctl - modified Datafile
/db/app/logminedb/oradata/LOGCDB/system01.db - dbid changed, wrote new name Datafile
/db/app/logminedb/oradata/LOGCDB/pdbseed/system01.db - dbid changed, wrote
new name Datafile
/db/app/logminedb/oradata/LOGCDB/sysaux01.db - dbid changed, wrote new name Datafile
/db/app/logminedb/oradata/LOGCDB/pdbseed/sysaux01.db - dbid changed, wrote
new name Datafile
/db/app/logminedb/oradata/LOGCDB/undotbs01.db - dbid changed, wrote new name Datafile
/db/app/logminedb/oradata/LOGCDB/pdbseed/undotbs01.db - dbid changed, wrote
new name Datafile
/db/app/logminedb/oradata/LOGCDB/users01.db - dbid changed, wrote new name Datafile
/db/app/logminedb/oradata/LOGCDB/UAT/system01.db - dbid changed, wrote new
name Datafile
/db/app/logminedb/oradata/LOGCDB/UAT/sysaux01.db - dbid changed, wrote new
name Datafile
/db/app/logminedb/oradata/LOGCDB/UAT/undotbs01.db - dbid changed, wrote new
name Datafile
/db/app/logminedb/oradata/LOGCDB/UAT/users01.db - dbid changed, wrote new
name Datafile
/db/app/logminedb/oradata/LOGCDB/temp01.db - dbid changed, wrote new name Datafile
/db/app/logminedb/oradata/LOGCDB/pdbseed/temp01.db - dbid changed, wrote new
name Datafile
/db/app/logminedb/oradata/LOGCDB/UAT/temp01.db - dbid changed, wrote new name Control File
/db/app/logminedb/oradata/LOGCDB/control01.ctl - dbid changed, wrote new name Control File
/db/app/logminedb/oradata/LOGCDB/control02.ctl - dbid changed, wrote new name Instance shut
down Database name changed to UATCDB. Modify parameter file and generate a new password file
before restarting. Database ID for database UATCDB changed to 205605669. 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 4. Change dbname in parameter file
|
SQL> startup nomount ORACLE instance started. Total System Global Area 4294964000 bytes Fixed Size
9143072 bytes Variable Size
805306368 bytes Database Buffers
3472883712 bytes Redo Buffers
7630848 bytes SQL> alter system set db_name=UATCDB scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Fixed Size
9143072 bytes Variable Size
805306368 bytes Database Buffers
3472883712 bytes Redo Buffers
7630848 bytes SQL> show parameter db_name NAME
TYPE VALUE ------------------------------------ -----------
------------------------------ db_name
string UATCDB SQL> shu immediate ORA-01507: database not mounted ORACLE instance shut down. |
Step 5. Create a new parameter file from old parameter file
with a new db name
|
[logminedb@ccuine1126 ~]$ cd $ORACLE_HOME/dbs SQL> create pfile from spfile; [logminedb@ccuine1126 dbs]$ mv initLOGCDB.ora initUATCDB.ora [logminedb@ccuine1126 dbs]$ export ORACLE_SID=UATCDB SQL> startup nomount pfile='initUATCDB.ora' ORACLE instance started. Total System Global Area 4294964000 bytes Fixed Size
9143072 bytes Variable Size
805306368 bytes Database Buffers
3472883712 bytes Redo Buffers
7630848 bytes SQL> ! ps -ef | grep pmon logmine+ 4671 1
0 17:01 ? 00:00:00
ora_pmon_UATCDB logmine+ 4752 4654
0 17:01 pts/5 00:00:00
/bin/bash -c ps -ef | grep pmon logmine+ 4754 4752
0 17:01 pts/5 00:00:00 grep
pmon SQL> create spfile from pfile='initUATCDB.ora';
SQL> alter database mount; Database altered. SQL> alter database open resetlogs; Database altered. SQL> select name,open_mode,instance_name from v$database,v$instance; NAME OPEN_MODE INSTANCE_NAME --------- -------------------- ---------------- UATCDB READ WRITE UATCDB |
