When we manually copy the entire database and specify a new dbname for the database by recreating the control file, we cannot assign a new dbid to the database. for the above problems, we can use the nid command to allocate
When we manually copy the entire database and specify a new dbname for the database by recreating the control file, we cannot assign a new dbid to the database. for the above problems, we can use the nid command to allocate
When we manually copy the entire database and specify a new dbname for the database by recreating the control file, we cannot assign a new dbid to the database. we can use the nid command to assign a new dbid to the database for the above issues. At the same time, it should be noted that rman also uses dbid to differentiate databases.
1. Command explanation
[Oracle @ source ~] $ Nid help = yes
DBNEWID: Release 11.2.0.2.0-Production on Thu Dec 5 00:09:50 2013
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE) specifies the Username and Password used to connect to the database.
DBNAME New database name (NONE) DBNAME = new_db_name change the database name
LOGFILE Output Log (NONE) LOGFILE = logfile specifies to Output messages to the specified Log file. By default, nid overwrites the previous date file.
REVERT Revert failed change NO specifies yes, indicating that the dbid can be restored when it fails to be changed.
SETNAME Set a new database name only NO specified yes indicates that only the database db_name is changed
APPEND Append to output log NO specifies yes to indicate that the output is appended to an existing log file.
HELP Displays these messages NO specifies yes to display HELP information
Note: You can change the dbid and db_name of the database at the same time, change only the db_name of the database, or change only the dbid of the database. The syntax is as follows:
Change dbid and db_name: nid target = sys/dhhzdhhz dbname = crm_test (or target = /)
Only change db_name: nid target = sys/dhhzdhhz dbname = crm_test setname = yes (or target = /)
Only change dbid: nid target = sys/dhhzdhhz (or target = /)
2. Precautions for using nid
1. Ensure that a backup can be completely restored to the database.
2. Make sure that the database is in the mounted state when you perform the change dbid operation, and the database is shut down by shutdown immediate before mounted.
3. After nid is used to change the dbid of a database, the database needs to be started by alter database open resetlogs. After the database is started, a full backup is required, because the previous backup and archiving cannot be used any more.
4. After using nid to change the database dbname, you must change the DB_NAME parameter in the initialization parameter file and recreate the password file.
5. You cannot change the global database name by using nid.
6. Make sure that all data files are online and do not need to be recovered.
7. Try to ensure that oracle does not have offline data files and read-only tablespaces. If so, normalize them.
Here are two examples.
Eg1: Only Change Database dbid
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 922749032 bytes
Database Buffers 318767104 bytes
Redo Buffers 8921088 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[Oracle @ source ~] $ Nid target = sys
DBNEWID: Release 11.2.0.2.0-Production on Wed Dec 4 23:39:11 2013
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database CRM (DBID = 3599153036)
Connected to server version 11.2.0
Control Files in database:
/Oracle/CRM/control03.ctl
Change database ID of database CRM? (Y/[N]) => y
Proceeding with operation
Changing database ID from 3599153036 to 3641774948
Control File/oracle/CRM/control03.ctl-modified
Datafile/oracle/CRM/system01.db-dbid changed
Datafile/oracle/CRM/sysaux01.db-dbid changed
Datafile/oracle/CRM/zx. db-dbid changed
Datafile/oracle/CRM/users01.db-dbid changed
Datafile/oracle/CRM/pos. db-dbid changed
Datafile/oracle/CRM/erp. db-dbid changed
Datafile/oracle/CRM/user01.db-dbid changed
Datafile/oracle/CRM/undotbs03.db-dbid changed
Datafile/oracle/CRM/crm. db-dbid changed
Datafile/oracle/CRM/jxc. db-dbid changed
Datafile/oracle/CRM/temp01.db-dbid changed
Control File/oracle/CRM/control03.ctl-dbid changed
Instance shut down
Database ID for database CRM changed to 3641774948.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID-Completed succesfully.
[Oracle @ source ~] $ Sqlplus/as sysdba
SQL * Plus: Release 11.2.0.2.0 Production on Wed Dec 4 23:47:21 2013
Copyright (c) 1982,201 0, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 922749032 bytes
Database Buffers 318767104 bytes
Redo Buffers 8921088 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid, name from v $ database;
DBID NAME
-------------------
3641774948 CRM
Eg2: Only Change Database db_name
Oracle @ source ~] $ Sqlplus/as sysdba
SQL * Plus: Release 11.2.0.2.0 Production on Thu Dec 5 00:11:03 2013
Copyright (c) 1982,201 0, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select open_mode from v $ database;
OPEN_MODE
--------------------
READ WRITE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 905971816 bytes
Database Buffers 335544320 bytes
Redo Buffers 8921088 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Oracle @ source ~] $ Nid target = sys dbname = CRM_TEST setname = YES
DBNEWID: Release 11.2.0.2.0-Production on Thu Dec 5 00:24:58 2013
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database CRM (DBID = 3641774948)
Connected to server version 11.2.0
Control Files in database:
/Oracle/CRM/control03.ctl
Change database name of database CRM to CRM_TEST? (Y/[N]) => y
Proceeding with operation
Changing database name from CRM to CRM_TEST
Control File/oracle/CRM/control03.ctl-modified
Datafile/oracle/CRM/system01.db-wrote new name
Datafile/oracle/CRM/sysaux01.db-wrote new name
Datafile/oracle/CRM/zx. db-wrote new name
Datafile/oracle/CRM/users01.db-wrote new name
Datafile/oracle/CRM/pos. db-wrote new name
Datafile/oracle/CRM/erp. db-wrote new name
Datafile/oracle/CRM/user01.db-wrote new name
Datafile/oracle/CRM/undotbs03.db-wrote new name
Datafile/oracle/CRM/crm. db-wrote new name
Datafile/oracle/CRM/jxc. db-wrote new name
Datafile/oracle/CRM/temp01.db-wrote new name
Control File/oracle/CRM/control03.ctl-wrote new name
Instance shut down
Database name changed to CRM_TEST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID-Completed succesfully.
[Oracle @ source ~] $ Sqlplus/as sysdba
SQL * Plus: Release 11.2.0.2.0 Production on Thu Dec 5 00:25:33 2013
Copyright (c) 1982,201 0, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 905971816 bytes
Database Buffers 335544320 bytes
Redo Buffers 8921088 bytes
SQL> alter system set db_name = CRM_TEST scope = spfile;
System altered.
[Oracle @ source ~] $ Orapwd file = "$ ORACLE_HOME/dbs/orapw $ ORACLE_SID" password = dhhzdhhz force = y
[Oracle @ source dbs] $ sqlplus/as sysdba
SQL * Plus: Release 11.2.0.2.0 Production on Thu Dec 5 00:34:40 2013
Copyright (c) 1982,201 0, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup force open;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 905971816 bytes
Database Buffers 335544320 bytes
Redo Buffers 8921088 bytes
Database mounted.
Database opened.
SQL> select dbid, name from v $ database;
DBID NAME
-------------------
3641774948 CRM_TEST
This article is from the "myblog" blog. Be sure to keep this source
,