DBNewID is an internal identifier which is unique for a database.
Prior to DBNewID , we have to manually create a copy of database and give it a new name by recreating a control file, but we cannot change the DBID of the database. As RMAN identifies databases by DBID , we cannot register a seed database and manually copied database together in same RMAN but with DBNewID it is possible.
Ramification after change of DBID using DBNewID utility :-
1. All previous backups and archivelogs become unusable as they have old DBID.2. Database has to be open with resetlogs and onlinelogs are recreated and reset sequence with 1.
3. Backup of database necessary.
Ramification after change of DBNAME using DBNewID utility :-
1. Backups and archivelogs are not invalidated and doesnot require the database to open with resetlogs.2. After changing DBNAME we must change the DBNAME initialization parameter in PFILE or SPFILE.
3. Oracle Password file has to be recreated.
#########DBNEWID utility doesnot change global database name of a database#########
To change global database name use :-
sql > ALTER DATABASE rename GLOBAL_NAME to <new_name>;
eg :- name.domain
Changing DBID and DBNAME using DBNEWID utility or To change only DBID . PFB steps :-
1. One whole Database Backup.
2.Shut down your database.
sql > shu immediate
3. Start your database in mount mode.
sql > startup mount
sql > exit
4. From oracle user( OS user having all rights of database) prompt use DBNewID utility
$ nid target = sys DBNAME = newname --------- press enter it will prompt for sys passsword
Password = (sys password)
OR
$ nid target = / DBNAME = newname --------- press enter it won't prompt for password
########## above nid target command will change both DBID and DBNAME###########
4. From oracle user( OS user having all rights of database) prompt use DBNewID utility
$ nid target = sys -------- press enter it will prompt for sys passsword
Password = (sys password)
OR
$ nid target = / -------- press enter it won't prompt for password
############## above nid target command will change only DBID #################
5. Database become shutdown after DBNewID command in both cases.
6. Start the database in mount mode.
sql > Startup mount
7. Open the database with reset logs.
sql > ALTER DATABASE open RESETLOGS;
Output after change of both DBID and DBNAME of database.
C:\Users\amit>nid target = / DBNAME=orclDBNEWID: Release 11.1.0.6.0 - Production on Mon Sep 24 20:06:26 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database AMIT (DBID=1310829817)
Connected to server version 11.1.0
Control Files in database:
C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL
C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL
C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL
Change database ID and database name AMIT to ORCL? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1310829817 to 1322635970
Changing database name from AMIT to ORCL
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - modified
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - modified
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - modified
Datafile C:\APP\AMIT\ORADATA\ORCL\SYSTEM01.DBF - dbid changed, wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\SYSAUX01.DBF - dbid changed, wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\USERS01.DBF - dbid changed, wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\EXAMPLE01.DBF - dbid changed, wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\TEMP01.DBF - dbid changed, wrote new name
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - dbid changed, wrote new name
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - dbid changed, wrote new name
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - dbid changed, wrote new name
Instance shut down
Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1322635970.
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 name and ID.
DBNEWID - Completed succesfully.
Output after change of DBID of database.
C:\Users\amit>nid target = /
DBNEWID: Release 11.1.0.6.0 - Production on Mon Sep 24 20:18:12 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database ORCL (DBID=1322635970)
Connected to server version 11.1.0
Control Files in database:
C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL
C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL
C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL
Change database ID of database ORCL? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1322635970 to 1322641285
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - modified
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - modified
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - modified
Datafile C:\APP\AMIT\ORADATA\ORCL\SYSTEM01.DBF - dbid changed
Datafile C:\APP\AMIT\ORADATA\ORCL\SYSAUX01.DBF - dbid changed
Datafile C:\APP\AMIT\ORADATA\ORCL\UNDOTBS01.DBF - dbid changed
Datafile C:\APP\AMIT\ORADATA\ORCL\USERS01.DBF - dbid changed
Datafile C:\APP\AMIT\ORADATA\ORCL\EXAMPLE01.DBF - dbid changed
Datafile C:\APP\AMIT\ORADATA\ORCL\TEMP01.DBF - dbid changed
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - dbid changed
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - dbid changed
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - dbid changed
Instance shut down
Database ID for database ORCL changed to 1322641285.
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.
Changing DBNAME using DBNEWID utility. PFB steps :-
1.Shut down your database.
sql > shu immediate
2. Start your database in mount mode.
sql > startup mount
sql > exit
4. From oracle user( OS user having all rights of database) prompt use DBNewID utility
$ nid target = sys DBNAME = newname setname=yes ----- press enter, prompt for passsword
Password = (sys password)
OR
$ nid target = / DBNAME = newname setname=yes ----- press enter,won't prompt for password
############## above nid target command will change only DBNAME#################
5. Database become shutdown after DBNewID comman.
6. set db_name initialization parameter in pfile
6. Start the database in mount mode by using pfile
sql > Startup mount pfile='<location>'
7. Open the database.
sql > ALTER DATABASE OPEN;
Output after change of DBNAME of database.
C:\Users\amit>nid target = / DBNAME=AMIT setname=yesDBNEWID: Release 11.1.0.6.0 - Production on Mon Sep 24 19:53:50 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database ORCL (DBID=1310829817)
Connected to server version 11.1.0
Control Files in database:
C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL
C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL
C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL
Change database name of database ORCL to AMIT? (Y/[N]) => Y
Proceeding with operation
Changing database name from ORCL to AMIT
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - modified
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - modified
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - modified
Datafile C:\APP\AMIT\ORADATA\ORCL\SYSTEM01.DBF - wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\SYSAUX01.DBF - wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\UNDOTBS01.DBF - wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\USERS01.DBF - wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\EXAMPLE01.DBF - wrote new name
Datafile C:\APP\AMIT\ORADATA\ORCL\TEMP01.DBF - wrote new name
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - wrote new name
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - wrote new name
Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - wrote new name
Instance shut down
Database name changed to AMIT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
################some things to remember regarding DBNewID utility####################
If using DBNewID for changing DBNAME or both create a pfile of the database before using DBNewID utility because after using this DB become shutdown and we have to change the db_name initialization parameter to the changed name and start the database with that pfile.
I hope this article helped you.
Regards,
Amit Rath
