About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, September 24, 2012

How to change DBID and DBNAME of Oracle Database using DBNEWID utility


DBNewID is an internal identifier which is unique for a database.

Prior to DBNewID , we have to manually create a copy of database and give it a new name by recreating a control file, but we cannot change the DBID of the database. As RMAN identifies databases by DBID , we cannot register a seed database and manually copied database together in same RMAN but with DBNewID it is possible.

Ramification after change of DBID using DBNewID utility :-
1. All previous backups and archivelogs become unusable as they have old DBID.
2. Database has to be open with resetlogs and onlinelogs are recreated and reset sequence with 1.
3. Backup of database necessary.

Ramification after change of DBNAME using DBNewID utility :-
1. Backups and archivelogs are not invalidated and doesnot require the database to open with resetlogs.
2. After changing DBNAME we must change the DBNAME initialization parameter in PFILE or SPFILE.
3. Oracle Password file has to be recreated.

#########DBNEWID utility doesnot change global database name of a database#########

To change global database name use :-
sql >  ALTER DATABASE rename GLOBAL_NAME to <new_name>;
eg :- name.domain

Changing DBID and DBNAME using DBNEWID utility or To change only DBID . PFB steps :-

1. One whole Database Backup.

2.Shut down your database.
    sql > shu immediate

3. Start your database in mount mode.
   sql >  startup mount
   sql >  exit

4. From oracle user( OS user having all rights of database) prompt use DBNewID utility
   $ nid target = sys DBNAME = newname       --------- press enter it will prompt for sys passsword
      Password = (sys password)
                  OR
   $ nid target = /  DBNAME = newname          --------- press enter it won't prompt for password

########## above nid target command will change both DBID and DBNAME###########

 4. From oracle user( OS user having all rights of database) prompt use DBNewID utility
    $ nid target = sys                                        -------- press enter it will prompt for sys passsword
      Password = (sys password)
                  OR
    $ nid target = /                                            -------- press enter it won't prompt for password


############## above nid target command will change only DBID #################
 
5. Database become shutdown after DBNewID command in both cases.
6. Start the database in mount mode.
   sql > Startup mount
7. Open the database with reset logs.
   sql > ALTER DATABASE open RESETLOGS;

Output after change of both DBID and DBNAME of database.
C:\Users\amit>nid target = / DBNAME=orcl

DBNEWID: Release 11.1.0.6.0 - Production on Mon Sep 24 20:06:26 2012

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

Connected to database AMIT (DBID=1310829817)

Connected to server version 11.1.0

Control Files in database:
    C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL
    C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL
    C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL

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

Proceeding with operation
Changing database ID from 1310829817 to 1322635970
Changing database name from AMIT to ORCL
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - modified
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - modified
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - modified
    Datafile C:\APP\AMIT\ORADATA\ORCL\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\SYSAUX01.DBF - dbid changed, wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\USERS01.DBF - dbid changed, wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\EXAMPLE01.DBF - dbid changed, wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\TEMP01.DBF - dbid changed, wrote new name
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - dbid changed, wrote new name
    Instance shut down

Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1322635970.
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.
  
 Output after change of DBID of database.
C:\Users\amit>nid target = /

DBNEWID: Release 11.1.0.6.0 - Production on Mon Sep 24 20:18:12 2012

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

Connected to database ORCL (DBID=1322635970)

Connected to server version 11.1.0

Control Files in database:
    C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL
    C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL
    C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL

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

Proceeding with operation
Changing database ID from 1322635970 to 1322641285
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - modified
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - modified
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - modified
    Datafile C:\APP\AMIT\ORADATA\ORCL\SYSTEM01.DBF - dbid changed
    Datafile C:\APP\AMIT\ORADATA\ORCL\SYSAUX01.DBF - dbid changed
    Datafile C:\APP\AMIT\ORADATA\ORCL\UNDOTBS01.DBF - dbid changed
    Datafile C:\APP\AMIT\ORADATA\ORCL\USERS01.DBF - dbid changed
    Datafile C:\APP\AMIT\ORADATA\ORCL\EXAMPLE01.DBF - dbid changed
    Datafile C:\APP\AMIT\ORADATA\ORCL\TEMP01.DBF - dbid changed
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - dbid changed
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - dbid changed
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - dbid changed
    Instance shut down

Database ID for database ORCL changed to 1322641285.
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.

 Changing DBNAME using DBNEWID utility. PFB steps :-

1.Shut down your database.
    sql > shu immediate

2. Start your database in mount mode.
   sql >  startup mount
   sql >  exit

4. From oracle user( OS user having all rights of database) prompt use DBNewID utility
   $ nid target = sys DBNAME = newname setname=yes    ----- press enter, prompt for passsword
      Password = (sys password)
                  OR
   $ nid target = /  DBNAME = newname setname=yes       ----- press enter,won't prompt for password

############## above nid target command will change only DBNAME#################

5. Database become shutdown after DBNewID comman.
6. set db_name initialization parameter in pfile
6. Start the database in mount mode by using pfile
   sql > Startup mount pfile='<location>'
7. Open the database.
   sql > ALTER DATABASE OPEN;

Output after change of DBNAME of database.
C:\Users\amit>nid target = / DBNAME=AMIT setname=yes

DBNEWID: Release 11.1.0.6.0 - Production on Mon Sep 24 19:53:50 2012

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

Connected to database ORCL (DBID=1310829817)

Connected to server version 11.1.0

Control Files in database:
    C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL
    C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL
    C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL

Change database name of database ORCL to AMIT? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to AMIT
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - modified
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - modified
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - modified
    Datafile C:\APP\AMIT\ORADATA\ORCL\SYSTEM01.DBF - wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\SYSAUX01.DBF - wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\UNDOTBS01.DBF - wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\USERS01.DBF - wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\EXAMPLE01.DBF - wrote new name
    Datafile C:\APP\AMIT\ORADATA\ORCL\TEMP01.DBF - wrote new name
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL01.CTL - wrote new name
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL02.CTL - wrote new name
    Control File C:\APP\AMIT\ORADATA\ORCL\CONTROL03.CTL - wrote new name
    Instance shut down

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

################some things to remember regarding DBNewID utility####################
If using DBNewID for changing DBNAME or both create a pfile of the database before using DBNewID utility because after using this DB become shutdown and we have to change the db_name initialization parameter to the changed name and start the database with that pfile.

I hope this article helped you.

Regards,
Amit Rath
 
Posted by at
Labels:

4 comments:

  1. UnknownOctober 9, 2012 at 1:07 AM

    WOW!! It is really amazing. Thanks a lot amit..

    ReplyDelete
  2. Amit RathOctober 9, 2012 at 5:34 PM

    Hey Yatin,

    Thanx......

    ReplyDelete
  3. AnonymousDecember 6, 2013 at 5:42 PM

    thanks
    very nice documented.


    ...vinaykumar pathak
    Email : vinaykumark.pathak@gmail.com

    ReplyDelete
  4. MUKTIKANTA RANAAugust 31, 2015 at 3:45 PM

    Thanks Amit. Awesome Document.

    ReplyDelete

Subscribe to: Post Comments (Atom)