Tuesday, June 5, 2012

Oracle 10g/11g:Change DBID and Name of Database by DBNEWID Utility

Use of DBNEWID Utility.

1.Shut Down the Database.Take Cold Backup of All Datafiles,Control Files and Log files.

2.Start Database in Mount Mode.Check Current DBID.

SQL>SQLPLUS /NOLOG

SQL>CONN / AS SYSDBA 

CONNECTED.

SQL>STARTUP MOUNT

SQL> select dbid from v$database;

      DBID
----------
13350****

3.Execute this Command.

SQL> host nid target=sys/blogdb dbname=NEWBLOG

DBNEWID: Release 11.1.0.6.0 - Production on Tue Jun 5 11:32:23 2012

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

Connected to database BLOGDB (DBID=1335025255)

Connected to server version 11.1.0

Control Files in database:
    E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL01.CTL
    E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL02.CTL
    E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL03.CTL

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

Proceeding with operation
Changing database ID from 1335025255 to 3538211719
Changing database name from BLOGDB to NEWBLOG
    Control File E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL01.CTL - modified
    Control File E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL02.CTL - modified
    Control File E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL03.CTL - modified
    Datafile E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\SYSTEM01.DBF - dbid changed, wr
ote new name
    Datafile E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\SYSAUX01.DBF - dbid changed, wr
ote new name
    Datafile E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\UNDOTBS01.DBF - dbid changed, w
rote new name
    Datafile E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\USERS01.DBF - dbid changed, wro
te new name
    Datafile E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\TEMP01.DBF - dbid changed, wrot
e new name
    Control File E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL01.CTL - dbid change
d, wrote new name
    Control File E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL02.CTL - dbid change
d, wrote new name
    Control File E:\APP\ADMINISTRATOR\ORADATA\BLOGDB\CONTROL03.CTL - dbid change
d, wrote new name
    Instance shut down

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


4.Start Database in NOMOUNT.Execute command to change database name in parameter files.

SQL> startup nomount pfile=E:\app\Administrator\admin\blogdb\pfile\init.ora
SQL> alter system set db_name=newblog scope=spfile;

System altered.

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


System altered.

SQL> create pfile from spfile;

File created.

SQL>

SQL>alter database mount;

SQL> alter database open resetlogs;



SQL> select dbid from v$database;

      DBID
----------
35382*****

5.Change Global_name from SQL.

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO NEWBLOG;