DBNEWID is a database utility that can change the internal database Identifier(DBID) and database name (DBNAME). The DBNEWID utility solves this problem by allowing us to change any of the following:
1.) Only the DBID of a database
2.) Only the DBNAME of a database
3.) Both the DBNAME and DBID of a database
1.) Only the DBID of a database : The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, we could not register a seed database and a manually copied database together in the same RMAN repository.
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 we 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. we must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 .
Below are steps to change the dbid of the database.
C:\>set ORACLE_SID=test
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:16:48 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 205523188 bytes
Database Buffers 50331648 bytes
Redo Buffers 6410240 bytes
Database mounted.
Database opened.
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
TEST 2050645904
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 205523188 bytes
Database Buffers 50331648 bytes
Redo Buffers 6410240 bytes
Database mounted.
SQL> host nid target=sys/test@test
DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 14:25:13 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database TEST (DBID=2050645904)
Connected to server version 11.2.0
Control Files in database:
D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL
D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL
Change database ID of database TEST? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2050645904 to 2050636234
Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified
Control FileD:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - modified
Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - dbid changed
Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - dbid changed
Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB - dbid changed
Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - dbid changed
Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - dbid changed
Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - dbid changed
Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - dbid changed
Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL
dbid changed
Instance shut down
Database ID for database TEST changed to 2050636234. 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.
C:\>sqlplus sys/test@test as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:26:13 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 205523188 bytes
Database Buffers 50331648 bytes
Redo Buffers 6410240 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
TEST 2050636234
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
2.) Only the DBNAME of a database : Changing the DBNAME without changing the DBID does not require us to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. We must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, we may have to re-create the Oracle password file. If we restore an old backup of the control file (before the name change), then we should use the initialization parameter file and password file from before the database name change.
Below are steps to change the database name of the database :
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 209717492 bytes
Database Buffers 46137344 bytes
Redo Buffers 6410240 bytes
Database mounted.
SQL> host nid TARGET=SYS/test@test DBNAME=newtest SETNAME=YES
DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 14:35:45 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database TEST (DBID=2050636234)
Connected to server version 11.2.0
Control Files in database:
D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL
D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL
Change database name of database TEST to NEWTEST? (Y/[N]) => Y
Proceeding with operation
Changing database name from TEST to NEWTEST
Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified
Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - modified
Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB - wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - wrote new name
Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - wrote new name
Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - wrote new name
Instance shut down
Database name changed to NEWTEST. Modify parameter file and generate a new password file before restarting.Succesfully changed database name.
DBNEWID - Completed succesfully.
SQL> exit
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:36:39 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile='c:\inittest.ora' from spfile;
File created.
Now open the pfile inittest.ora and replace db_name parameter value "test" to "newtest"
SQL> create spfile from pfile='c:\inittest.ora' ;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 209717492 bytes
Database Buffers 46137344 bytes
Redo Buffers 6410240 bytes
Database mounted.
Database opened.
SQL> select open_mode ,name from v$database;
OPEN_MODE NAME
-------------------- ---------
READ WRITE NEWTEST
3.) Both the DBNAME and DBID of a database
C:\>sqlplus sys/test@newtest as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 15:54:45 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select dbid, name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- -----------------
2050636234 NEWTEST READ WRITE
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 16:01:03 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 213911796 bytes
Database Buffers 41943040 bytes
Redo Buffers 6410240 bytes
Database mounted.
SQL> host nid target=sys/test@newtest dbname=prod
DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 16:11:17 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database NEWTEST (DBID=2050636234)
Connected to server version 11.2.0
Control Files in database:
D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL
D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL
Change database ID and database name NEWTEST to PROD? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2050636234 to 164330150
Changing database name from NEWTEST to PROD
Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified
Control FileD:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL modifd
Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - dbid changed, wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - dbid changed, wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB-dbid changed,wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - dbid changed, wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - dbid changed, wrote new name
Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - dbid changed, wrote new name
Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTLdbid changd,wrote new name Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - dbid changed, wrote new name
Instance shut down
Database name changed to PROD. Modify parameter file and generate a new password file before restarting. Database ID for database PROD changed to 164330150. 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> exit
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 16:43:18 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 209717492 bytes
Database Buffers 46137344 bytes
Redo Buffers 6410240 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,dbid,open_mode from v$database;
NAME DBID OPEN_MODE
--------- ---------- ----------------
PROD 164330150 READ WRITE
Enjoy :-)
1 comment:
very nice post.
In my blog i posted regarding the change of DBID :
http://chandu208.blogspot.com/2011/04/changing-database-id-dbid.html
Post a Comment