How to Change the DBID and the DBNAME by using NID
Introduction
============
The NID (New Database ID)is a new utility introduced with Oracle 9.2. The NID
utility allows you to change only the DBNAME, or only the DBID or both
DBNAME and DBID in the same command.
1. If you change the DBID you must open the database with the RESETLOGS option,
which re-creates the online redo logs and resets their sequence to 1.
2. If you change the DBNAME without changing the DBID then this does not require you to open with the RESETLOGS option,
so database backups and archived logs are not invalidated.
You must change the DB_NAME initialization parameter after a database name change to reflect the new name.
Also, you may have to re-create the Oracle password file.
If you restore an old backup of the control file (before the name change,then you should use the initialization parameter file and password file from before the database name change.
Restrictions and Usage Notes
============================
The DBNEWID utility has the following restrictions:
– The utility is available only on the UNIX and Windows NT operating systems.
– The NID executable file should be owned and run by the Oracle owner because it needs direct access to the datafiles and control files.
– If another user runs the utility, then set the user ID to the owner of the datafiles and control files.
– The DBNEWID utility must access the datafiles of the database directly through a local connection.
Although DBNEWID can accept a net service name, it cannot change the DBID of a nonlocal database.
– To change the DBID of a database, the database must be mounted and must have been shut down consistently
prior to mounting. In the case of an Oracle Real Application Clusters database, the database must be
mounted in NOPARALLEL mode. i.e set the init parameter CLUSTER_DATABASE=FALSE and then mount the database
– You must open the database with the RESETLOGS option after changing the DBID.
– Note that you do not have to open with the RESETLOGS option after changing only the database name.
– No other process should be running against the database when DBNEWID is executing.
If another session shuts down and starts the database, then DBNEWID aborts.
– All online datafiles should be consistent without needing recovery.
– Normal offline datafiles should be accessible and writable.
If this is not the case, you must drop these files before invoking the DBNEWID utility.
– All read-only tablespaces must be accessible and made writable at the operating system level prior
to invoking DBNEWID. If these tablespaces cannot be made writable (for example, they are on a CD-ROM),
then you must unplug the tablespaces using the transportable tablespace feature and then plug them back
in the database before invoking the DBNEWID utility (see the Oracle9i Database Administrator's Guide).
– You can only specify REVERT when changing only the DBID.
一.Change Only the DBID
====================
1. Backup the database
2. check the DBID before change
3. SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT
4. Open one session and run NID with sysdba privileges % nid target=sys/trsenzhang09
5. Shutdown IMMEDIATE of the database
6. Startup of the database with open resetlogs
Example:
========
1.backup database
run {allocate channel c1 device type disk; backup incremental level 0 format '/u02/rman/db_full_%U.bkp' tag 'level0' database plus archivelog; backup current controlfile format '/u02/rman/ctl .bak'; backup spfile format '/u02/rman/spfile.bak'; release channel c1; }
2. check the DBID before change
SQL> select dbid,name,open_mode,activation#,created from v$database; DBID NAME OPEN_MODE ACTIVATION# CREATED ---------- --------- -------------------- ----------- --------- 701555330 trsendb READ WRITE 747358942 11-JAN-16
3.SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 704645016 bytes Database Buffers 1426063360 bytes Redo Buffers 4923392 bytes Database mounted.
4. execute NID
[oracle@scmadmin dbs]$ nid target=sys/trsenzhang09 DBNEWID: Release 11.2.0.4.0 - Production on Wed Jun 14 10:15:03 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to database trsendb (DBID=701555330) Connected to server version 11.2.0 Control Files in database: /u02/oradata/trsendb/control01.ctl /u02/oradata/trsendb/control02.ctl /u02/oradata/trsendb/control03.ctl Change database ID of database trsendb? (Y/[N]) => Y Proceeding with operation Changing database ID from 701555330 to 747316135 Control File /u02/oradata/trsendb/control01.ctl - modified Control File /u02/oradata/trsendb/control02.ctl - modified Control File /u02/oradata/trsendb/control03.ctl - modified Datafile /u02/oradata/trsendb/system01.db - dbid changed Datafile /u02/oradata/trsendb/sysaux01.db - dbid changed .... Control File /u02/oradata/trsendb/control02.ctl - dbid changed Control File /u02/oradata/trsendb/control03.ctl - dbid changed Instance shut down Database ID for database trsendb changed to 747316135. 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.
5.Shutdown IMMEDIATE of the database
[oracle@scmadmin dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 14 10:17:37 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> shutdown immediate; ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory
6.Startup of the database with open resetlogs
SQL> startup mount; ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 704645016 bytes Database Buffers 1426063360 bytes Redo Buffers 4923392 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; Database altered.
7. check the new DBID
SQL> select dbid,name,open_mode,activation#,created from v$database; DBID NAME OPEN_MODE ACTIVATION# CREATED ---------- --------- -------------------- ----------- --------- 747316135 trsendb READ WRITE 747295334 11-JAN-16
NOTE:
1.The NID change the OLD DBID 701555330 to the NEW DBID 747316135
2.The database backups and archived logs are invalidated.
二.Change Only the DBNAME
======================
1. Backup the database
2. SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT
3. Open one session and run NID with sysdba privileges
% nid TARGET=sys/trsenzhang09 DBNAME=trsendb2 SETNAME=Y
– the value of DBNAME is the new dbname of the database
– SETNAME must be set to Y. The default is N and causes the
DBID to be changed also.
4. shutdown IMMEDIATE of the database
5. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name
6. Startup of the database(without resetlogs)
1.backup the database.
run {allocate channel c1 device type disk; backup incremental level 0 format '/u02/rman/db_full_%U.bkp' tag 'level0' database plus archivelog; backup current controlfile format '/u02/rman/ctl .bak'; backup spfile format '/u02/rman/spfile.bak'; release channel c1; }
2.SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 704645016 bytes Database Buffers 1426063360 bytes Redo Buffers 4923392 bytes Database mounted.
3.Open one session and run NID with sysdba privileges
[oracle@scmadmin rman]$ nid TARGET=sys/trsenzhang09 DBNAME=trsendb2 SETNAME=Y DBNEWID: Release 11.2.0.4.0 - Production on Wed Jun 14 10:39:52 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to database trsendb (DBID=747316135) Connected to server version 11.2.0 Control Files in database: /u02/oradata/trsendb/control01.ctl /u02/oradata/trsendb/control02.ctl /u02/oradata/trsendb/control03.ctl Change database name of database trsendb to trsendb2? (Y/[N]) => Y Proceeding with operation Changing database name from trsendb to trsendb2 Control File /u02/oradata/trsendb/control01.ctl - modified Control File /u02/oradata/trsendb/control02.ctl - modified Control File /u02/oradata/trsendb/control03.ctl - modified Datafile /u02/oradata/trsendb/system01.db - wrote new name Datafile /u02/oradata/trsendb/sysaux01.db - wrote new name ....... Datafile /u02/oradata/trsendb/temp01.db - wrote new name Control File /u02/oradata/trsendb/control01.ctl - wrote new name Control File /u02/oradata/trsendb/control02.ctl - wrote new name Control File /u02/oradata/trsendb/control03.ctl - wrote new name Instance shut down Database name changed to trsendb2. Modify parameter file and generate a new password file before restarting. Succesfully changed database name. DBNEWID - Completed succesfully.
4.shutdown IMMEDIATE of the database
SQL> shutdown immediate; ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory
5.Set the DB_NAME initialization parameter in the initialization parameter file to the new database name
SQL> create pfile from spfile; File created. [oracle@scmadmin dbs]$ vi inittrsendb.ora [oracle@scmadmin dbs]$ mv inittrsendb.ora inittrsendb2.ora [oracle@scmadmin dbs]$ export ORACLE_SID=trsendb2 SQL> create spfile from pfile; File created.
6.Startup of the database(without resetlogs)
SQL> startup mount; ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 704645016 bytes Database Buffers 1426063360 bytes Redo Buffers 4923392 bytes Database mounted. SQL> alter database open; Database altered. SQL> select dbid,name,open_mode,activation#,created from v$database; DBID NAME OPEN_MODE ACTIVATION# CREATED ---------- --------- -------------------- ----------- --------- 747316135 trsendb2 READ WRITE 747295334 11-JAN-16
三.Change Both DBID and DBNAME
===========================
1. Backup of the database.
2. Shutdown IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
% nid TARGET=SYS/xxxx DBNAME=new_dbname
– the value of DBNAME is the new dbname of the database
5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the database
6. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.
7. Startup of the database with open resetlogs
Other Useful Information
========================
Except the parameters like LOGFILE=, APPEND, HELP, there is a specific option
REVERT which allows us to go back on the steps of executing. If the value of
REVERT is YES that means that a failed change of DBID should be reverted but a
successfully completed change of DBID cannot be reverted. REVERT=YES is only
valid when a DBID change failed. Default value is NO.
In 10.2 and higher the database may automatically shut down after a change.
Do not be alarmed if this occurs.
Note if SPFILE is used
———————————
If you use a SPFILE initially, do the changes documented above in the SPFILE using ALTER SYSTEM SET … ; command
If you using SPFILE, you can create PFILE from SPFILE, then do the changes to PFILE as documented in this note.
However keep in mind, after PFILE used / changes recreate the SPFILE from PFILE so you have changes in SPFILE. .