Use the nid command to modify the Database name and dbid

When you use the nid command to modify the Database name and dbid, you will inevitably encounter the situation where you need to modify the dbname and dbid, for example, restoring the database to the same machine is one of them. However, dbname and dbid are one of the important symbols used to identify the database, especially dbid, which is unique. Therefore, the modification should be handled with caution. This document describes how to modify dbname and dbid and provides an example. 1. Modifying dbid and dbname affects a. Modifying dbid is equivalent to creating a new database. The difference is that data already exists in a data file. This is determined by the uniqueness of dbid. After the modification, all the previous backup and archive logs will be unavailable, because dbid will be detected during recovery. Because of the mismatch, all backups will be invalid. After modification, you need to use open resetlogs to open the database. A new incarnation will be created and the sequence will be set to 1. If the database is successfully modified and open, we recommend that you disable the database consistently, restart the database, and perform a complete backup. B. After modifying the dbname, you do not need to use open resetlogs to open the database. (Note that dbname is not unique, and global name is unique ). After modification, all the backups are archived logs still available. After modification, You need to modify the db_name parameter in pfile/spfile and recreate the corresponding Oracle password file. If you need to use the old control file to restore the database, you should use the pfile/spfile before modification and the password file to start the database and then restore it. C. Modify the dbid and dbname simultaneously. If both are modified, the preceding two cases are considered as a combination. After the modification, open resetlogs and modify pfile/spifle, password file, and full backup database. 2. nid command robin @ SZDB: ~> Nid DBNEWID: Release 10.2.0.3.0-Production on Thu Apr 24 16:34:28 2013 Copyright (c) 1982,200 5, Oracle. all rights reserved. keyword Description (Default) -------------------------------------------------- TARGET Username/Password (NONE) DBNAME New database name (NONE) LOGFILE Output Log (NONE) REVERT Revert failed change no setname Set a new database name only no append Append to output log No help Displays these messages NO when executing the nid command, this program will verify all the data files in the current database and the header information of the control file. After the verification is successful, it will prompt whether to modify the information. If the logfile output to the log file is used, no modification prompt is displayed. Next, nid will use the new dbid (or dbname) to change the header of the control file one by one, data files (including read-only files and normal offline files. After successful modification, the database is automatically closed and exited. Note: For read-only files, normal offline files are not tested in this document. Second, we should ensure that the database is in the archive state, and the database can be normally archived and there are no data files to be recovered. 3. Modify step a. Full backup database. For Hot Backup (rman or OS) ensure that all archived logs and online logs are available. B. Delete dbconsole ([ID 863800.1] has this requirement. If dbconsole is used, perform this operation) c. Start the database to the mount state (startup mount) d. If you use the spfile file to start the database, back up the spfile file to pfile to modify db_name e and release the nid command nid target = sys/password # This method only modifies dbid nid target =/dbname = new_dbname [setname = yes] # /indicates the sid connecting to the current environment, use the operating system to authenticate nid target = sys/password dbname = new_dbname [setname = yes] # setname = yes to modify only the database name. If this parameter is omitted At the same time, modify nid target = sys/pwdd @ conn_string dbname = new_dbname [setname = yes] # connect to the remote host using a connection string, modify f, and modify the Oracle parameter file pfile (use the previous backup from spfile) if only dbid is modified, skip this step g and start to mount with the new parameter file pfile (if dbname is modified, if only dbid is modified, use the original pfile or spfile to start the database. h. Use open resetlogs to open the database. (modify the non-dbid and open it directly) i. Recreate the Oralce password file of the current database, convert the pfile file to the spfile file j, and modify the configuration value of the listener, including the listener. ora and tnsnames. ora k. Modify the global dbname if it is useful. Alter database rename GLOBAL_NAME TO <newname>. <domain>; l re-create dbconsole $ emca-config dbcontrol db-repos recreate m, full backup database 4. Demonstrate modifying dbname [SQL] $ export ORACLE_SID = CLBO $ sqlplus/as sysdba --> Current Database dbid, name and status. the following example uses the pfile file to start the database. SQL> select dbid, name, open_mode from v $ database; DBID NAME OPEN_MODE ------------- ---------- 1924111546 CLBO MOUNTED $ nid target = sys/oracle dbname = MMBO set Name = yes DBNEWID: Release 10.2.0.3.0-Production on Wed Apr 24 18:16:54 2013 Copyright (c) 1982,200 5, Oracle. all rights reserved. connected to database CLBO (DBID = 1924111546) Connected to server version 10.2.0 Control Files in database:/u02/database/SYBO/controlf/cntl1SYBO. ctl/u02/database/SYBO/controlf/cntl2SYBO. ctl/u02/database/SYBO/controlf/cntl3SYBO. ctl Change database name of database C LBO to MMBO? (Y/[N]) => Y Proceeding with operation Changing database name from CLBO to MMBO Control File/u02/database/SYBO/controlf/cntl1SYBO. ctl-modified Control File/u02/database/SYBO/controlf/cntl2SYBO. ctl-modified Control File/u02/database/SYBO/controlf/cntl3SYBO. ctl-modified ................. omitted ................. database name changed to MMBO. modify parameter file and generate a new password file Before restarting. succesfully changed database name. DBNEWID-Completed succesfully. -- modify the db_name parameter in the pfile file and restart the database (Omitted) -- check the status after the repair, dbid does not change, however, dbname and $ export ORACLE_SID = MMBO $ sqlplus/as sysdba SQL> select dbid, name, open_mode from v $ database; dbid name OPEN_MODE ---------- --------- ---------- 1924111546 mmbo read write 5, modify dbname and dbid [SQL] $ export ORACLE_SID = mmbo SQL> startup mount Pfile =/u02/database/SYBO/initMMBO. ora $ nid target =/dbname = sybo dbnewid: Release 10.2.0.3.0-Production on Wed Apr 24 19:12:57 2013 Copyright (c) 1982,200 5, Oracle. all rights reserved. connected to database MMBO (DBID = 1924111546) Connected to server version 10.2.0 Control Files in database:/u02/database/SYBO/controlf/cntl1SYBO. ctl/u02/database/SYBO/controlf/cntl2SYBO. ctl/u02/database/SYBO/c Ontrolf/cntl3SYBO. ctl Change database ID and database name MMBO to SYBO? (Y/[N]) => Y Proceeding with operation Changing database ID from 1924111546 to 4235963258 Changing database name from MMBO to SYBO Control File/u02/database/SYBO/controlf/cntl1SYBO. ctl-modified Control File/u02/database/SYBO/controlf/cntl2SYBO. ctl-modified Control File/u02/database/SYBO/controlf/cntl3SYBO. ctl-modified Datafile/u02/database/SYBO/oradata/sysSYBO. dbf-dbid changed, wrote new name ................ datafile/u02/database/SYBO/temp/tmp. dbf-dbid changed, wrote new name Control File/u02/database/SYBO/controlf/cntl1SYBO. ctl-dbid changed, wrote new name Control File/u02/database/SYBO/controlf/cntl2SYBO. ctl-dbid changed, wrote new name Control File/u02/database/SYBO/controlf/cntl3SYBO. ctl-dbid changed, wrote new name Instance shut down Database name changed to SYBO. modify parameter file and generate a new password file before restarting. database ID for database SYBO changed to 4235963258. 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. -- modify the db_name of the pfile and rename the pfile to initSYBO. ora SQL> startup pfile =/u02/database/SYBO/initSYBO. ora ORACLE instance started. database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; Database altered. SQL> select dbid, name, open_mode from v $ database; DBID NAME OPEN_MODE ------------- ---------- 2315014338 SYBO READ WRITE -- re-create the spfile and password file, configure value listening, tnsnames. ora, modify global name, recreate dbconsole, and perform full backup, which is omitted here. -- do not demonstrate the modification of dbid only. run nid target = sys/pwd in the mount status.