Home

Saturday, September 20, 2014

How To Change DBID and DB NAME

How To Change DBID and DB NAME


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

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)