Tales From A Lazy Fat DBA

We should be thankful to that almighty that we are living an age where we have Oracle Database 10g and further releases available, If we’re not then changing some of the internal identifiers like DBNAME & DBID would be a real pain in the ass. Before Oracle 10g assigning a new DBNAME was only possible by manually re-creating the Control File and with no alternate or method to change DBID.

* DBNAME: Name of your database and can be comapred with your name e.g Prashant …
* DBID: Unique number (Identifier) assigned to your database in order to identify it among other DB’s and can be easily compared it with your house address. e.g 9/90-

DBNEWID (NID is same) is an oracle Database utlity/program which sits in $ORACLE_HOME/bin directory and can change the internal database identifier number or DBID and Database Name.

Alright, after that basic idea about DBNAME & DBID let’s jump to Oracle’s NID Utlity (Introduced in 10g).

DBID is a unique identifier for a database. During the years oracle released several versions of their database packages along with newer utilities and with many advancenments and re-imaged so many existing tools. RMAN (Recovery Manager) is one of the tool which along with time turned out to be a very powerful weapon in any DBA’a armoury for Backup and Recovery activities.
Up to 9i you could not register a Primary Database and a Clone Database in the same RMAN Repository or RMAN Catalog. DBID resolved this problem by changing DBID or DBNAME.

With NID Utility you can change:
– Only DBNAME
– Only DBID
– DBNAME and DBID Both

Points to be keep in mind when altering DBID of a database:
* After changing the DBID of database you must have to open your DB with RESETLOG option.
* After changing DBID of a database all old backups and archive logs becomes unsuable.
* Full Backup of your database soon after changing the DBID.

Points to be keep in mind when altering DBNAME of a database:
* This does not require to open database with RESETLOG option (No re-creation of online redo logs will happen & no sequence reset)
* Must change DB_NAME entry in your Parameter File to reflect new name.
* May have to create a new Password File.

Options available with NID Utility:

[oracle@localhost ~]$ nid help=y

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 17:39:20 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
—————————————————-
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

CHANGING DBNAME & DBID Both

SQL> select name, dbid from v$database;

NAME            DBID
——— ———-
ORCL      1310899424

* Always recommended that you should take a full backup of your database before you go for DBID & DBNAME change.
– Mount your database after a graceful SHUTDOWN
– Change DBNAME & DBID
– SHUTDOWN IMMEDIATE
– STARTUP MOUNT (Could receive error like this: ORA-01103: database name ‘ANT’ in control file is not ‘ORCL’) ** Ignore
– Alter DB_NAME parameter in your pfile/spfile.
– SHUTDOWN IMMEDIATE
– Start your database with RESETLOGS option (SQL> alter database open resetlogs;)
– Verify both DBID & DBNAME.

[oracle@prashant ~]$ nid TARGET=sys/oracle90 DBNAME=ant
DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 13:04:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database ORCL (DBID=1310899424)
Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

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

Proceeding with operation
Changing database ID from 1310899424 to 124308932
Changing database name from ORCL to ANT
Control File /u01/app/oracle/oradata/orcl/control01.ctl – modified
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – modified
Datafile /u01/app/oracle/oradata/orcl/system01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/sysaux01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/undotbs01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/users01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/example01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/rmanrep/rman01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/temp01.db – dbid changed, wrote new name
Control File /u01/app/oracle/oradata/orcl/control01.ctl – dbid changed, wrote new name
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – dbid changed, wrote new name
Instance shut down

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

SQL> select name, dbid from v$database;

NAME            DBID
——— ———-
ANT        124308932

– Only DBNAME
To change DBNAME only you have to use both DBNAME and SETNAME parameters in nid string. DBNEWID performs validations in controlfiles headers only not datafiles. Change DB_NAME parameter soon after you modify DBNAME using nid utility in pfile/spfile.

Change DBNAME
SHUTDOWN IMMEDIATE
STARTUP MOUNT (Could recieve error like this: ORA-01103: database name ‘REDANT’ in control file is not ‘ANT’) ** Ignore
Alter DB_NAME parameter in your pfile/spfile.
SHUTDOWN IMMEDIATE
Verify both DBNAME.

[oracle@prashant ~]$ nid TARGET=sys/oracle90 DBNAME=redant SETNAME=YES

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 13:18:51 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database ANT (DBID=124308932)
Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database name of database ANT to REDANT? (Y/[N]) => Y

Proceeding with operation
Changing database name from ANT to REDANT
Control File /u01/app/oracle/oradata/orcl/control01.ctl – modified
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – modified
Datafile /u01/app/oracle/oradata/orcl/system01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/sysaux01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/undotbs01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/users01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/example01.db – wrote new name
Datafile /u01/app/oracle/oradata/rmanrep/rman01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/temp01.db – wrote new name
Control File /u01/app/oracle/oradata/orcl/control01.ctl – wrote new name
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – wrote new name
Instance shut down

Database name changed to REDANT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.

– Only DBID
If want to change DBID only, do not give DBNAME parameter in nid sting. This requires Opening of database with RESETLOG option.

[oracle@prashant ~]$ nid TARGET=SYS/oracle90
Connected to database REDANT (DBID=124308932)

Database ID for database REDANT changed to 629811920.
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.

To revert back changes use REVERT keyword.
[oracle@prashant ~]$ nid TARGET=SYS/oracle90 REVERT=YES