What Is the DBNEWID Utility
Prior to the introduction of the DBNEWID utility, you could manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID). The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem by allowing you to change any of the following:
Both the DBNAME and DBID of a database
Only the DBNAME of a database
Only the DBID of a database
Consequences of Changing the DBID and DBNAME
When the DBID of a database is changed, all previous backups and archived logs of the database become unusable.You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1
Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB_NAME initialization parameter after a database name change to reflect the new name.
Changing the DBID and Database Name
The following steps describe how to change the DBID and database name.
1.Take the Database backup
2.Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. For example:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
#> . oraenv
ORACLE_SID = [TESTDB1] ?
The Oracle base remains unchanged with value /orasw/app/oracle
oracle@slc00uzk.us.oracle.com /orasw/app/oracle/product
#> sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:21:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 402656720 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7344128 bytes
Database mounted.
SQL>
3.Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege
#>
nid TARGET=SYS DBNAME=new_db_name
#> nid TARGET=SYS DBNAME=TESTDB2
DBNEWID: Release 11.2.0.3.0 - Production on Wed Mar 7 22:24:18 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database TESTDB1 (
DBID=1076930572)
Connected to server version 11.2.0
Control Files in database:
/test_oradata_01/TESTDB1/control01.ctl
/test_oradata_01/TESTDB1/control02.ctl
/test_oradata_01/TESTDB1/control03.ctl
Change database ID and database name TESTDB1 to TESTDB2? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1076930572 to 1999354907
Changing database name from TESTDB1 to TESTDB2
Control File /test_oradata_01/TESTDB1/control01.ctl - modified
Control File /test_oradata_01/TESTDB1/control02.ctl - modified
Control File /test_oradata_01/TESTDB1/control03.ctl - modified
Datafile /test_oradata_01/TESTDB1/system01.db - dbid changed, wrote new name
Datafile /test_oradata_01/TESTDB1/SYSAUX.db - dbid changed, wrote new name
Datafile /test_oradata_01/TESTDB1/UNDOTBS1.db - dbid changed, wrote new name
Datafile /test_oradata_01/TESTDB1/tools01.db - dbid changed, wrote new name
Datafile /test_oradata_01/TESTDB1/cists01.db - dbid changed, wrote new name
Datafile /test_oradata_01/TESTDB1/user01.db - dbid changed, wrote new name
Datafile /test_oradata_01/TESTDB1/xmldbts01.db - dbid changed, wrote new name
Datafile /test_oradata_01/TESTDB1/temp01.db - dbid changed, wrote new name
Control File /test_oradata_01/TESTDB1/control01.ctl - dbid changed, wrote new name
Control File /test_oradata_01/TESTDB1/control02.ctl - dbid changed, wrote new name
Control File /test_oradata_01/TESTDB1/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to TESTDB2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTDB2 changed to 1999354907.
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.
Note: If validation is not successful, then DBNEWID terminates and leaves the target database intact, as shown below. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID
NID-00122: Database should have no offline immediate datafiles
Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.
4. Modify the parameter file and also password file if you are using password file.
Modify the below parameters
db_name = TESTDB2
instance_name = TESTDB2
service_names = TESTDB2
Rename init.ora file from initTESTDB1.ora to initTESTDB2.ora
Note: If you are using spfile, create spfile from pfile.
Reset the ORACLE_SID environment variable.
Note: If you don't modify the parameter file, you will get error as below
#> sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:28:26 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 402656720 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7344128 bytes
ORA-01103: database name 'TESTDB2' in control file is not 'TESTDB1'
SQL> exit
#> . oraenv
ORACLE_SID = [TESTDB1] ? TESTDB2
ORACLE_HOME = [/home/oracle] ? /orasw/app/oracle/product/11.2.0.3
The Oracle base remains unchanged with value /orasw/app/oracle
4.Mount the database
#> . oraenv
ORACLE_SID = [TESTDB1] ? TESTDB2
ORACLE_HOME = [/home/oracle] ? /orasw/app/oracle/product/11.2.0.3
The Oracle base remains unchanged with value /orasw/app/oracle
oracle@slc00uzk.us.oracle.com /orasw/app/oracle/product/11.2.0.3/dbs
#> sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:42:29 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 402656720 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7344128 bytes
Database mounted.
5.Open the database in RESETLOGS mode and resume normal use.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,status from v$database,v$instance;
NAME STATUS
--------- ------------
TESTDB2 OPEN
Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
To change DBNAME Only
Repeat the process as before except use the following command to start the DBNEWID utility.
nid TARGET=sys/password@old_db_name DBNAME=new_db_name SETNAME=YES
To change DBID Only
1.Backup the database.
2.Mount the database after a clean shutdown
SHUTDOWN IMMEDIATE
STARTUP MOUNT
#> sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 22:53:36 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 402656720 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7344128 bytes
Database mounted.
SQL> exit
3. Invoke the DBNEWID utility (nid) using a user with SYSDBA privilege. Do not specify a new DBNAME.
nid TARGET=sys/password@db_name
#> nid TARGET=SYS
DBNEWID: Release 11.2.0.3.0 - Production on Wed Mar 7 22:54:09 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database TESTDB2 (
DBID=1999354907)
Connected to server version 11.2.0
Control Files in database:
/test_oradata_01/TESTDB1/control01.ctl
/test_oradata_01/TESTDB1/control02.ctl
/test_oradata_01/TESTDB1/control03.ctl
Change database ID of database TESTDB2? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1999354907 to 1999396119
Control File /test_oradata_01/TESTDB1/control01.ctl - modified
Control File /test_oradata_01/TESTDB1/control02.ctl - modified
Control File /test_oradata_01/TESTDB1/control03.ctl - modified
Datafile /test_oradata_01/TESTDB1/system01.db - dbid changed
Datafile /test_oradata_01/TESTDB1/SYSAUX.db - dbid changed
Datafile /test_oradata_01/TESTDB1/UNDOTBS1.db - dbid changed
Datafile /test_oradata_01/TESTDB1/tools01.db - dbid changed
Datafile /test_oradata_01/TESTDB1/cists01.db - dbid changed
Datafile /test_oradata_01/TESTDB1/user01.db - dbid changed
Datafile /test_oradata_01/TESTDB1/xmldbts01.db - dbid changed
Datafile /test_oradata_01/TESTDB1/temp01.db - dbid changed
Control File /test_oradata_01/TESTDB1/control01.ctl - dbid changed
Control File /test_oradata_01/TESTDB1/control02.ctl - dbid changed
Control File /test_oradata_01/TESTDB1/control03.ctl - dbid changed
Instance shut down
Database ID for database TESTDB2 changed to
1999396119.
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.
4.Mount the database
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 402656720 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7344128 bytes
Database mounted.
5.Open the database with resetlogs.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,status from v$database,v$instance;
NAME STATUS
--------- ------------
TESTDB2 OPEN
