How to change the DBID (and DB_NAME)
Step 1. The database should be backed up.
Step 2. Ensure to perform clean shut down of a database and startup in mount stage (but not open).
SQL> SHUDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Step 3. Invoke the DBNEWID utility (nid) specifying the new DBNAME (if also changing the DB_NAME) from the command line using a user with SYSDBA privilege:
| Note: To change the database ID without changing the database name, in Step 3 do not specify the optional database name (DBNAME). |
$ nid TARGET=SYS/XXXX@test DBNAME=test_db
DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.
DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
..
Connected to database PROD (DBID=86997811) Connected to server version 10.1.0 Control Files in database: /oracle/TEST_DB/data/cf1.f /oracle/TEST_DB/data/cf2.f The following datafiles are offline clean: /oracle/TEST_DB/data/tbs_61.f (23) /oracle/TEST_DB/data/tbs_62.f (24) /oracle/TEST_DB/data/temp3.f (3) These files must be writable by this utility. The following datafiles are read-only: /oracle/TEST_DB/data/tbs_51.f (15) /oracle/TEST_DB/data/tbs_52.f (16) /oracle/TEST_DB/data/tbs_53.f (22) These files must be writable by this utility. Changing database ID from 86997811 to 1250654267 Changing database name from PROD to TEST_DB Control File /oracle/TEST_DB/data/cf1.f - modified Control File /oracle/TEST_DB/data/cf2.f - modified Datafile /oracle/TEST_DB/data/tbs_01.f - dbid changed, wrote new name Datafile /oracle/TEST_DB/data/tbs_ax1.f - dbid changed, wrote new name Datafile /oracle/TEST_DB/data/tbs_02.f - dbid changed, wrote new name Datafile /oracle/TEST_DB/data/tbs_11.f - dbid changed, wrote new name Datafile /oracle/TEST_DB/data/tbs_12.f - dbid changed, wrote new name Datafile /oracle/TEST_DB/data/temp1.f - dbid changed, wrote new name Control File /oracle/TEST_DB/data/cf1.f - dbid changed, wrote new name Control File /oracle/TEST_DB/data/cf2.f - dbid changed, wrote new name Instance shut down Database name changed to TEST_DB. Modify parameter file and generate a new password file before restarting. Database ID for database TEST_DB changed to 1250654267. All previous backups and archived redo logs for this database are unusable. Database has been shutdown, open database with RESETLOGS option. Successfully changed database name and ID. DBNEWID - Completed successfully. | Note: If validation is not successful, then DBNEWID terminates and leaves the target database intact, as shown in the following sample output. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID. |
Step 4. Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_NAME=test_db SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE;
Step 5. Create a new password file
On Unix:
$ orapwd file=/oracle/product/10.2.0/db_1/dbs/pwdtest_db.ora password=xxxxxxx entries=10
Step 6. Open the database in RESETLOGS mode and resume normal use. For example:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;
| Note: To change only the DBNAME, use the SETNAME parameter in Step 3 which tells the DBNEWID utility to only alter the database name and not the database ID, it is then not necessary to use the RESETLOGS option when opening the database. |
Step 7. Backup the whole database again.
