Pages

Wednesday 7 March 2012

Oracle DBNEWID Utility


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




2 comments: