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.
Purpose
=======
It is useful to troubleshoot from the situation when duplicate database ID's
cause errors such as the following.
RMAN-20002: target database already registered in recovery catalog
Cause: target database is already registered in the recovery catalog
Action: If the target database is really registered, there is no need to
register it again. Note that the recovery catalog enforces that
all databases have a unique DBID. If the new database was created
by copying files from an existing database, it will have the same
DBID as the original database and cannot be registered in the
same recovery catalog.
RMAN-20011, 1, "target database incarnation is not current in recovery catalog"
RMAN-20009, 1, "database incarnation already registered"
-or-
SQLPLUS> alter database mount standby database;
alter database mount standby database
*
ORA-01102: cannot mount database in EXCLUSIVE mode
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. SHUTDOWN IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
% nid TARGET=SYS/password@test_db
5. Shutdown IMMEDIATE of the database
6. Set the DB_NAME initialization parameter in the initialization parameter
file to the new database name
7. Create a new password file
8. Startup of the database with open resetlogs
Example:
========
1. C:\>set ORACLE_SID=TEST1BY
C:\>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 24 11:16:52 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
2. check the DBID before change
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- ---------- ----------- ---------
1395399949 TEST1BY READ WRITE 1395404134 10-SEP-02
3. SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL>exit
5. execute NID
C:\>nid target=sys/oracle@TEST1BY
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database TEST1BY (DBID=1395399949)
Control Files in database:
D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL
Change database ID of database TEST1BY? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1395399949 to 1397190693
Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - modified
Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF - dbid changed
Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - dbid changed
Database ID for database TEST1BY changed to 1397190693.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
6. SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
7. create the new passwordfile
8. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
9. SQL> alter database open resetlogs;
Database altered.
10. check the new DBID
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- ---------- ----------- ---------
1397190693 TEST1BY READ WRITE 1397188261 10-SEP-02
NOTE: The NID change the OLD DBID 1395399949 to the NEW DBID 1397190693
Change Only the DBNAME
======================
1. Backup the database
2. SHUTDOWN IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
% nid TARGET=SYS/password@test_db DBNAME=test_db2 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.
5. shutdown IMMEDIATE of the database
6. Set the DB_NAME initialization parameter in the initialization parameter
file to the new database name
7. Create a new password file
8. Startup of the database(without resetlogs)
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/password@test_db DBNAME=test_db2
- 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. Create a new password file.
8. Startup of the database with open resetlogs
Example:
========
1. Shutdown the databse
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2. Mount the database
SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
startup mount
3. Run NID utility
C:\>nid target=sys/oracle@test1by dbname=test1by logfile=d:\oracle\base_test\nid.log
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database TEST1 (DBID=849281895)
Control Files in database:
D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL
Changing database ID from 849281895 to 1395399949
Changing database name from TEST1 to TEST1BY
Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - modified
Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF - dbid changed, wrote new name
Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF - dbid changed, wrote new name
Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - dbid changed, wrote new name
Database name changed to TEST1BY.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST1BY changed to 1395399949.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
4. Shutdown immediate of the database
5. Modfiy the file init.ora of the database
change the parameter BD_NAME=TEST1 TO DBNAME=TEST1BY
6. create a new password file
Usage: orapwd file=password= entries=
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and OPERs (opt),
7. mount the database
SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
8. Open the databse with RESETLOGS
SQL> alter database open resetlogs;
Database altered.
9. check the dictionary view for the new DBID and DBNAME
select dbid, name, open_mode, activation#, created from v$database;
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.
Documentation
=============
Oracle9i Database Utilities Release 2 (9.2)
Tuesday, February 19, 2008
How To Rename a Oracle Database Using DBNEWID Tool (nid)
Steps to Manually Clone a Oracle Database
Steps to clone the database
The following steps can be used to clone a
database on the same server or on another server,
We will clone a database with name proddb
the cloned database will have the name clonedb,
and clonedb will reside on the same server as proddb,
The cloning process mainly has 2 steps,
1. Take a cold backup of the production database
2. Restore the backup on to the clone destination and startup the clone database
Optionally you can rename the clone database, before starting it up.
the steps below describe the process of cloning a database and also renaming the
database.
Perform Cold Backup of the Production database
1. Note down the datafile, controlfile and redologfile locations on proddb
OS> sqlplus / as sysdba
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> show parameter control_files
2. Shutdown the production database proddb
OS> sqlplus / as sysdba
SQL> shutdown immediate
Note : Ensure clean shutdown.
3. Copy the database files (datafiles, logfiles, controlfiles and parameter file) into the clone destination.
Using the information gathered in step 1, change directory to the production database directories
OS> cd /u01/
Note : for simplicity sake we'll assume all files are in the same directory
OS> cp /u01/
..
.. repeat the same for all datafiles, controlfiles and redologfiles
..
OS> cp $ORACLE_HOME/dbs/initproddb.ora $ORACLE_HOME/dbs/initclonedb.ora
4. Modify the parameter CONTROL_FILES in the pfile for clonedb to reflect
the new path of the control files.
Using your favorite text editor edit the pfile to reflect the new path
of the control files
vi initclonedb.ora
..
..
CONTROL_FILES = '/u01/clonedb/control/control01.ctl','/u01/clonedb/control/control02.ctl'
..
...
Rename the database as per requirements and start the clone database
We rename the database only if needed, if you plan to retain the
same name, the whole process simplifies to taking a cold backup
and restoring it in the target location and starting up the database,
however renaming becomes mandatory if the cloning happens on the same
server and in the same Oracle Home.
If the database version is less than version 9.2,
please go to section "Renaming database with version lower than 9.2"
Note :
Ensure DB_NAME is still proddb in initclonedb.ora, clonedb pfile,
Please note that the production database should be shutdown throughout the
clone process, this is because we are cloning the database on the same server
and using the same Oracle Home, if either one is different, database can be started
immediately after copying the files.
1. Mount the database and rename the datafiles and redolog files.
export ORACLE_SID=clonedb
OS> sqlplus / as sysdba
OS> startup mount
OS> alter database rename file '/u01/
2. Rename the database with the dbnewid (nid) utility
For information on how to use the DBNEWID utility, please refer this note
Metalink Note 224266.1 Title: How to Change the DBID and the DBNAME by using NID
Note : If you are using a database of version lesser than 9.2 then the
DBNEWID (NID) utility cannot be used as it was introduced only from 9.2
3. Edit the pfile and update the new database name
vi initclonedb.ora
---> Modify parameter DB_NAME to reflect the new database name.
also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.
4. Open the clone database.
5. Startup and open the production database.
Note : These steps may not be applicable to databases using OMF files as there are restrictions on renaming the datafiles, controlfiles and logfiles when they are Oracle Managed, it is Likely that you will receive a ORA-1276 when you rename a OMF file.
Renaming database with version lower than 9.2
Note : In this case it is not required to copy controlfiles or redologfiles
as they will be created newly, also note that when using this method to rename the database, the production database can be started immediately following the copying of the datafiles to the clone location.
1. Generate a controlfile backup trace file with the command
SQL> alter database backup controlfile to trace as '/path/filename.trc';
2. Edit the pfile and update the new database name
vi initclonedb.ora
---> Modify parameter DB_NAME to reflect the new database name.
also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.
3. Make a script to create a new controlfile
Editing the trace generated in
step 1 of this section, ensure to use the SET option to set the new database name.
Example
CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 '/home/oracle/databases/clonedb/redo1.ora' SIZE 100M,
GROUP 2 '/home/oracle/databases/clonedb/redo2.ora' SIZE 100M,
GROUP 3 '/home/oracle/databases/clonedb/redo3.ora' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/databases/clonedb/system.dbf',
'/home/oracle/databases/clonedb/undo.dbf',
'/home/oracle/databases/clonedb/data.dbf'
CHARACTER SET WE8ISO8859P1
;
File saved as createcontrol.sql
4. Connect to the clone instance and start in the NOMOUNT stage.
export ORACLE_SID=clonedb
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup nomount
SQL> @createcontrol.sql
Control File Created.
SQL> alter database open resetlogs ;
Please note you may have to add tempfiles to temporary tablespaces
as needed.
These steps complete the cloning process.
Subscribe to:
Comments (Atom)