28 | 03 | 2024
Latest Articles
Popular Articles

Backup & Recovery

NID : change DBID and DB_NAME together

User Rating:  / 1
PoorBest 

Change the DBID and DB_NAME together with NID.

 

The DBID is an unique identifier for a database. You should only change the DBID of a database when really needed since a new DBID,

1. invalidates previously taken backups and backup information

2. invalidates previously generated archivelogs

3. resets the log sequence to 1

The DBID is used in RMAN'  s recovery catalog. Also you cannot recover archived redo logs when there is a DBID mismatch. Changing the database DB_NAME has less consequences with respect to database backup and recovery.

With the below procedure I show how easy it is to use NID to change both DBID and DB_NAME together ( but TAKE CARE ).



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2096568 bytes
Variable Size            1023410760 bytes
Database Buffers          536870912 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
[oracle@myhost oradata]$ $ORACLE_HOME/bin/nid target=sys/aciripme DBNAME=GOLD

DBNEWID: Release 10.2.0.5.0 - Production on Thu Dec 22 16:50:51 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database SILVER (DBID=3601868207)

Connected to server version 10.2.0

Control Files in database:
/u01/oradata/SILVER/control01.ctl
/u01/oradata/SILVER/control02.ctl
/u01/oradata/SILVER/control03.ctl

Change database ID and database name SILVER to GOLD? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3601868207 to 1648546860
Changing database name from SILVER to GOLD
Control File /u01/oradata/SILVER/control01.ctl - modified
Control File /u01/oradata/SILVER/control02.ctl - modified
Control File /u01/oradata/SILVER/control03.ctl - modified
Datafile /u01/oradata/SILVER/system01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/undotbs01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/sysaux01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/users01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/data01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/indx01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/meta_data_i1.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/meta_data_d1.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/meta_data_i2.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/data03.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/meta_data_d2.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/data02.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/data04.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/temp01.dbf - dbid changed, wrote new name
Datafile /u01/oradata/SILVER/temp02.dbf - dbid changed, wrote new name
Control File /u01/oradata/SILVER/control01.ctl - dbid changed, wrote new name
Control File /u01/oradata/SILVER/control02.ctl - dbid changed, wrote new name
Control File /u01/oradata/SILVER/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to GOLD.
Modify parameter file and generate a new password file before restarting.
Database ID for database GOLD changed to 1648546860.
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.


[oracle@myhost oradata]$ cd $ORACLE_HOME/dbs
[oracle@myhost:/opt/oracle/ORA_HOME/dbs] $ cp spfileSILVER.ora spfileGOLD.ora
[oracle@myhost:/opt/oracle/ORA_HOME/dbs] $ cp orapwSILVER orapwGOLD
[oracle@myhost:/opt/oracle/ORA_HOME/dbs] $ export ORACLE_SID=GOLD
[oracle@myhost:/opt/oracle/ORA_HOME/dbs] $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 18 15:53:39 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2050856 bytes
Variable Size             239076568 bytes
Database Buffers          826277888 bytes
Redo Buffers                6336512 bytes
SQL> alter system set db_name=GOLD scope=spfile;

System altered.

SQL> alter system set db_unique_name=GOLD scope=spfile;

System altered.


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

 

[oracle@myhost oradata]$ $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 18 15:33:16 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2050856 bytes
Variable Size             251659480 bytes
Database Buffers          813694976 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.





That' s it, changing the DBID and DBNAME together goes fast and is easy, but again be aware what kind of impact it has with respect to your backup and recovery situation. A part from changing the DBID we can
use nid to change the db_name only.
use nid to change the dbid only.