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
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?
LikeLike
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
LikeLike
you must change password file SID from $ORACLE_HOME:dbs
orapwprod01 to orapwdev
LikeLike