How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards


Applies to: Database Management Software Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the datafiles. After you change the DBID, backups and archive logs that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 (see the Oracle Database Administrator's Guide). Consequently, you should make a backup of the whole database immediately after changing the DBID.

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. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.

Steps to change DBID and DBNAME:

Source database name is fms01 and we will convert it to fms02.

[oracle@netmindtwcint ~]$ export ORACLE_SID=fms01
[oracle@netmindtwcint ~]$ sqlplus sys as sysdba


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             360712352 bytes
Database Buffers          167772160 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> exit;

[oracle@netmindtwcint ~]$ nid target=sys/*** dbname=fms02

DBNEWID: Release 11.2.0.2.0 - Production on Fri Apr 27 14:40:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database FMS01 (DBID=3355521024)

Connected to server version 11.2.0

Control Files in database:
    /fms01/dbs/control01.ctl
    /fms01/dbs/control02.ctl
    /fms01/dbs/control03.ctl

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

Proceeding with operation
Changing database ID from 3355521024 to 3803284043
Changing database name from FMS01 to FMS02
    Control File /fms01/dbs/control01.ctl - modified
    Control File /fms01/dbs/control02.ctl - modified
    Control File /fms01/dbs/control03.ctl - modified
    Datafile /fms01/dbs/system01.db - dbid changed, wrote new name
    Datafile /fms01/dbs/undotbs01.db - dbid changed, wrote new name
    Datafile /fms01/dbs/sysaux01.db - dbid changed, wrote new name
    Datafile /fms01/dbs/users01.db - dbid changed, wrote new name
    Datafile /fms01/dbs/indx01.db - dbid changed, wrote new name
    Datafile /fms01/dbs/tools01.db - dbid changed, wrote new name
    Datafile /fms01/dbs/data/ora_data.db - dbid changed, wrote new name
    Datafile /fms01/dbs/temp01.db - dbid changed, wrote new name
    Control File /fms01/dbs/control01.ctl - dbid changed, wrote new name
    Control File /fms01/dbs/control02.ctl - dbid changed, wrote new name
    Control File /fms01/dbs/control03.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to FMS02.

Modify parameter file and generate a new password file before restarting.

Database ID for database FMS02 changed to 3803284043.
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@netmindtwcint ~]$ cd /data1/oracle11g/product/11.2.0/dbhome_2/dbs/

Now, create new password file for new name:

[oracle@netmindtwcint dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwfms02 password=sys force=y

Make new init.ora file and from existing init.ora file and change db_name parameter from fms01 to fms02

[oracle@netmindtwcint dbs]$ cp initfms01.ora initfms02.ora

change *.db_name='fms01' to *.db_name='fms02' in init file.

Change the $ORACLE_HOME/network/admin/tnsnames.ora file wherever it has the old db name.

If there is a static registration of the database in the listener.ora file then change the database name in the following file $ORACLE_HOME/network/admin/listener.ora.

Open the database with Resetlogs option:

[oracle@netmindtwcint dbs]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 27 15:00:03 2012

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

Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             167774368 bytes
Database Buffers          360710144 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
fms02

No comments:

Post a Comment