Oracle Utilities – dbNewID

In this post let us see how to rename the database –

Basically a database is identified with DBNAME and DBID – a unique identifier number which is internal to Oracle.

Usually for renaming a database, have the copy of your database and re-create the control file with the new database name. But using this method you can only change the DBNAME but you can’t change the DBID. This means, your source database and the copied database will have same DBID’s.

So, is there any problem in having same DBID’s for different databases?

Hmm.. it depends on your environment. But truly say, it’s a bad idea!! In certain cases, DBID is used to distinguish the databases. For example: In RMAN – you cannot register different databases that have the same DBID, in its repository.

Oracle comes up with a solution for this problem – i.e. dbNewID utility – with which you can change not only the DBNAME but also DBID.

Now let us see how can we rename the databse using the utility ‘dbNewID’ –

But before that, let’s see how this been doing before this utility was introduced –

Here im going to change - MGRORCL1 to NEWORCL - manually

SQL> select name, dbid,log_mode,open_mode from v$database;

NAME            DBID LOG_MODE     OPEN_MODE
--------- ---------- ------------ --------------------
MGRORCL1  1823939660 ARCHIVELOG   READ WRITE

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

created a parameter file that reflects the new DBNAME 'NEWORCL'

SQL> startup nomount pfile='c:\app\mgrvinod\product\11.2.0\
                            dbhome_1\database\INITneworcl.ora';
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size                  2176448 bytes
Variable Size            1023412800 bytes
Database Buffers          687865856 bytes
Redo Buffers                6873088 bytes
SQL>

Now create the CONTROL FILE with new dbname 'NEWORCL' - 

SQL>CREATE CONTROLFILE REUSE SET DATABASE NEWORCL 
    RESETLOGS ARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
    LOGFILE
     GROUP 1 (
      'C:\APP\MGRVINOD\ORADATA\NEW_DB_LOC\MGRORCL1\
        ONLINELOG\O1_MF_1_6XLXNGL9_.LOG',
      'C:\APP\MGRVINOD\FLASH_RECOVERY_AREA\NEW_DB_LOC\MGRORCL1\
        ONLINELOG\O1_MF_1_6XLXNH39_.LOG'
             ) SIZE 50M BLOCKSIZE 512,
     GROUP 2 (
      'C:\APP\MGRVINOD\ORADATA\NEW_DB_LOC\MGRORCL1\
         ONLINELOG\O1_MF_2_6XLXNHQX_.LOG',
      'C:\APP\MGRVINOD\FLASH_RECOVERY_AREA\NEW_DB_LOC\MGRORCL1\
         ONLINELOG\O1_MF_2_6XLXNJDZ_.LOG'
             ) SIZE 50M BLOCKSIZE 512,
     GROUP 3 (
      'C:\APP\MGRVINOD\ORADATA\NEW_DB_LOC\MGRORCL1\
         ONLINELOG\O1_MF_3_6XLXNJY5_.LOG',
      'C:\APP\MGRVINOD\FLASH_RECOVERY_AREA\NEW_DB_LOC\MGRORCL1\
         ONLINELOG\O1_MF_3_6XLXNKQX_.LOG'
           ) SIZE 50M BLOCKSIZE 512
     -- STANDBY LOGFILE
    DATAFILE
      'C:\APP\MGRVINOD\ORADATA\NEW_DB_LOC\MGRORCL1\
        DATAFILE\O1_MF_SYSTEM_6XLXLKOK_.DBF',
      'C:\APP\MGRVINOD\ORADATA\NEW_DB_LOC\MGRORCL1\
        DATAFILE\O1_MF_SYSAUX_6XLXLKS9_.DBF',
      'C:\APP\MGRVINOD\ORADATA\NEW_DB_LOC\MGRORCL1\
        DATAFILE\O1_MF_UNDOTBS1_6XLXLKSX_.DBF',
      'C:\APP\MGRVINOD\ORADATA\NEW_DB_LOC\MGRORCL1\
        DATAFILE\O1_MF_USERS_6XLXLKTK_.DBF'
      CHARACTER SET WE8MSWIN1252
     ;
Control file created.

So, once the controlfile is created - We need to recover the 
database using the backup controlfile - as it knows up to what 
SCN should your database be recovered -

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 3021933 generated at 06/29/2011 10:26:12 needed 
for thread 1
ORA-00289: suggestion :
C:\APP\MGRVINOD\FLASH_RECOVERY_AREA\NEWORCL\ARCHIVELOG\2011_06_29\
     O1_MF_1_84_%U_.ARC
ORA-00280: change 3021933 for thread 1 is in sequence #84

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> select name,dbid,log_mode,open_mode from v$database;

NAME            DBID LOG_MODE     OPEN_MODE
--------- ---------- ------------ --------------------
NEWORCL   1823939660 ARCHIVELOG   READ WRITE

SQL>

So, here DBNAME has been changed, but the DBID remains the same.

Since DBID not changed here, you can even open the database with
'NORESETLOGS' option - so that you can still use your old archives 
and backups.

Starting from 9i, Oracle introduced an utility ‘dbNewID’ that is used for renaming the database. With this utility you can not only change the DBNAME but also DBID even.

So, using NID one can change (i)only DBNAME (ii) only DBID (iii) Both.

Let’s see how NID can perform those actions – Here initally we change DBNAME and then continue with DBID –

a) Changing only DBNAME - 

I'm going to change 'MGRORCL1' to 'NIDORCL' - 

Mount the database(MGRORCL1) in exclusive mode-

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2176448 bytes
Variable Size            1073744448 bytes
Database Buffers          637534208 bytes
Redo Buffers                6873088 bytes
Database mounted.
SQL>

In another session run the following command - 

C:\>nid target=system/mgrorcl1 dbname=NIDORCL setname=Y;

DBNEWID: Release 11.2.0.1.0-Production on Wed Jun 29 12:11:49 2011

Copyright 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database MGRORCL1 (DBID=1823939660)

Connected to server version 11.2.0

Control Files in database:
 C:\APP\MGRVINOD\ORADATA\MGRORCL1\CONTROLFILE\O1_MF_6XLXND6Q_.CTL
 C:\APP\MGRVINOD\FLASH_RECOVERY_AREA\MGRORCL1\CONTROLFILE\
                                              O1_MF_6XLXND8X_.CTL

Change database name of database MGRORCL1 to NIDORCL? (Y/[N]) => y

Proceeding with operation
Changing database name from MGRORCL1 to NIDORCL
  Control File C:\APP\..\O1_MF_6XLXND6Q_.CTL - modified
  Control File C:\APP\..\O1_MF_6XLXND8X_.CTL - modified
  Datafile C:\APP\..\O1_MF_SYSTEM_6XLXLKOK_.DB - wrote new name
  Datafile C:\APP\..\O1_MF_SYSAUX_6XLXLKS9_.DB - wrote new name
  Datafile C:\APP\..\O1_MF_UNDOTBS1_6XLXLKSX_.DB - wrote new name
  Datafile C:\APP\..\O1_MF_USERS_6XLXLKTK_.DB - wrote new name
  Datafile C:\..\O1_MF_TEMP_6XLXNRH5_.TM - wrote new name
  Control File C:\APP\..\O1_MF_6XLXND6Q_.CTL - wrote new name
  Control File C:\APP\..\O1_MF_6XLXND8X_.CTL - wrote new name
Instance shut down

Database name changed to NIDORCL.
Modify par file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

As per the suggestion, make the necessary changes to the par file  
- to reflect the new database name -  DBNAME to 'NIDORCL' and create 
the new password file using ORAPWD utility -

C:\>orapwd file=c:\app\mgrvinod\product\11.2.0\dbhome_1\
                   database\oranidorcl password=nidorcl entries=5;
C:\>

Now start the database using the current pfile -

SQL> startup pfile='c:\app\mgrvinod\product\11.2.0\
                    dbhome_1\database\INITmgrorcl1.ora'
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size                  2176448 bytes
Variable Size            1023412800 bytes
Database Buffers          687865856 bytes
Redo Buffers                6873088 bytes
Database mounted.
Database opened.

SQL> select name,dbid,log_mode,open_mode from v$database;

NAME            DBID LOG_MODE     OPEN_MODE
--------- ---------- ------------ --------------------
NIDORCL   1823939660 ARCHIVELOG   READ WRITE

So, here DBNAME has been changed keeping DBID the same - 

b) Changing DBID - 

I'm going to change DBID for 'NIDORCL' - 

Mount the database 'NIDORCL' in Exclusive mode - 

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2176448 bytes
Variable Size            1073744448 bytes
Database Buffers          637534208 bytes
Redo Buffers                6873088 bytes
Database mounted.
SQL>

In another session run the following command - 

C:\>nid target=sys/nidorcl

DBNEWID: Release 11.2.0.1.0-Production on Wed Jun 29 14:02:19 2011

Copyrigh  2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database NIDORCL (DBID=1823939660)

Connected to server version 11.2.0

Control Files in database:
    C:\APP\MGRVINOD\ORADATA\MGRORCL1\CONTROLFILE\
                                        O1_MF_6XLXND6Q_.CTL
    C:\APP\MGRVINOD\FLASH_RECOVERY_AREA\MGRORCL1\CONTROLFILE\
                                         O1_MF_6XLXND8X_.CTL

Change database ID of database NIDORCL? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1823939660 to 2583480939
    Control File C:\APP\..\O1_MF_6XLXND6Q_.CTL - modified
    Control File C:\APP\..\O1_MF_6XLXND8X_.CTL - modified
    Datafile C:\APP\..\O1_MF_SYSTEM_6XLXLKOK_.DB - dbid changed
    Datafile C:\APP\..\O1_MF_SYSAUX_6XLXLKS9_.DB - dbid changed
    Datafile C:\APP\..\O1_MF_UNDOTBS1_6XLXLKSX_.DB - dbid changed
    Datafile C:\APP\..\O1_MF_USERS_6XLXLKTK_.DB - dbid changed
    Datafile C:\APP\..\O1_MF_TEMP_6XLXNRH5_.TM - dbid changed
    Control File C:\APP\..\O1_MF_6XLXND6Q_.CTL - dbid changed
    Control File C:\APP\..\O1_MF_6XLXND8X_.CTL - dbid changed
    Instance shut down

Database ID for database NIDORCL changed to 2583480939.
All previous backups and archived logs for this db are unusable.
Db not aware of previous backups and archivedlogs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

Once DBID is changed, Shutdown the database - 

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

Now start the database and keep it in 'MOUNT ' mode - 

SQL> startup mount pfile='c:\app\mgrvinod\product\11.2.0\
                          dbhome_1\database\INITmgrorcl1.ora'
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2176448 bytes
Variable Size            1023412800 bytes
Database Buffers          687865856 bytes
Redo Buffers                6873088 bytes
Database mounted.

Open the database with 'RESETLOGS' option -

SQL> alter database open resetlogs;
Database altered.

SQL> select name,dbid,log_mode,open_mode from v$database;
NAME            DBID LOG_MODE     OPEN_MODE
--------- ---------- ------------ --------------------
NIDORCL   2583480939 ARCHIVELOG   READ WRITE

Now we can see the new DBID - 

You can even revert your NID operation – if it was messed up in middle – if the reversion succeeds, then NID reverts all performed changes and leaves the database in a mounted state.

To perform this operation mention the REVERT keyword on the command line
as shown –

c:\>nid target=sys/nidorcl REVERT=YES 

No doubt – it’s a great tool and been made easy in renaming the database –

Advertisements
This entry was posted in General, Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s