手动在同机创建clone database，小心原库redo损坏

 

PURPOSE

The following document deals with creating a copy of a database on the same unix machine. A database might need to be copied in order to duplicate the production system, for example for testing purposes. It is recommended that the reader refer also to the following documentation:

Oracle 7, 8, 8i, 9i, 10g, 11g Server Administrators Guide Performing full backups: Chapter 18 page 18-7 (note: chapters may vary between versions)

NOTE:  If you intend to use RMAN with a recovery catalog for the newly copied database, please use caution. If RMAN already has a database registered in the recovery catalog with the same DBID as the copied database, you can introduce a recovery catalog inconsistency. If you plan to register the copied database and you are not sure how to proceed, please open a new service request for the Recovery Manager support team and mention in the new service request this comment in this document.

SCOPE

For DBAs.

DETAILS

Overview

Before copying the database to a new location, it is necessary to perform a full cold backup of the database, whilst the database is shutdown. This will ensure that no data will be lost if the copying of the database is unsuccessful.

Creating a copy of a database involves usage of the CREATE CONTROLFILE command (explained below). If this command is not executed correctly it could corrupt the production database. If this happens, and if the files in question are important, this will mean that the original database will need to be restored from a backup.

Note: If you are using Oracle 7.1.6 on Sun Solaris 2.X there is a serious bug with CREATE CONTROLFILE. Call Oracle Worldwide Support asking for details of Bug 274054 before proceeding.

1. Obtain Database Information from ControlFile

In order to move the database, it is necessary to create a script containing information about the files of the database.

This is done by executing the following commands.

a. 8i and prior : SQLDBA or SVRMGRL
9i, 10g : sqlplus /nolog

b. 8i and prior :
SQL> CONNECT INTERNAL, STARTUP MOUNT
9i, 10g :
SQL> connect / as sysdba

c. ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

This will create a trace file in the trace file directory. The file will have the extension .trc, and is located either in the directory defined by the initialization parameter 'user_dump_dest', or, if  this is not defined, in ORACLE_HOME/rdbms/log. The file should be copied to a name such as ccf<NEW_SID>.sql, where <NEW_SID> is to be the ORACLE_SID of the copied database.

2. Identify Files to Backup/Copy

a. Identify database and log files

The CREATE CONTROLFILE command in the file ccf<NEW_SID>.sql can then be  used to identify the various database files and redo log files that need  to be backed up. The file names will be in single quotes and separated  by commas after the words LOGFILE and DATAFILE, e.g:

CREATE CONTROLFILE REUSE DATABASE "FRITZ" RESETLOGS ARCHIVELOG
MAXLOGFILES 6
MAXLOGMEMBERS 2
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'/oracle/log_disk1/fritzlog1v713.dbf',
'/oracle/log_disk2/fritzlog1v713.dbf'
) SIZE 50K,
GROUP 2 (
'/oracle/log_disk1/fritzlog2v713.dbf',
'/oracle/log_disk2/fritzlog2v713.dbf'
) SIZE 50K
DATAFILE
'/oracle/fritz_system/fritz_system01.dbf' SIZE 8M,
'/oracle/fritz_data/fritz_data01.dbf' SIZE 20M,
'/oracle/fritz_rollback/fritz_rollback01.dbf' SIZE 20M,
'/oracle/fritz_temp/fritz_temp01.dbf' SIZE 20M,
'/oracle/fritz_data/fritz_data02.dbf' SIZE 5M,
'/oracle/d2/V7141/dbs/x' SIZE 1M
;

It is also possible to obtain a listing of the files of the database by executing the following sql commands:

SQLPLUS username/password
Note: The user must have sufficient privileges to be able to see the dba views 'sys.dba_data_files', 'sys.v$logfile' and in addition the database must be open.

SPOOL files.log

SQL> SELECT file_name FROM sys.dba_data_files ORDER BY tablespace_name;

SQL> SELECT member FROM sys.v$logfile

SPOOL OFF
Note: This will create a spool file called 'files.log' which will record the results of the previous commands.

b. Identify controlfiles

This can be done either by referring to the init<SID>.ora  'control_files' parameter, or from 7.0.16 onwards, the table  sys.v$controlfile can be used to identify the controlfiles of the database via the following statement:

SPOOL control.log
SELECT name FROM v$controlfile;
SPOOL OFF

This will create a file called control.log in the current directory which will contain the names of the controlfiles for the database.

3. Backup Existing Database

Shutdown instance via SQLDBA or SVRMGRL, SHUTDOWN NORMAL, and then take full cold  backup of:

a. All the files identified in step 2 above.

b. All parameter files.
Note: the main parameter file will usually be called init<SID>.ora, in addition to which there may also be other parameter files. These will  be identified by 'ifile' (included file) parameters in the init<SID>.ora.  These additional parameter files are usually called config<SID>.ora.

4. Make a Copy of the Database

Shutdown instance via SQLDBA, SVRMGRL or SQLPlus, SHUTDOWN NORMAL.

Copy all parameter files, controlfiles, and all files noted in step 2 above  to their new location taking care to preserve ownership and permissions.

When the database has been copied, it will not be possible to use the same  SID and therefore the ORACLE_SID environment variable must be changed to a new SID, and the copied init<SID>.ora must be  renamed to init<NEW_SID>.ora, and any parameter files pointed to by an  'ifile' parameter (e.g. parameter files such as config<SID>.ora) should be  renamed to incorporate the NEW_SID (i.e. config<NEW_SID>.ora).

5. Set Up Parameter Files for the Copied Database.

Edit the value of the control_files parameter in the init<NEW_SID>.ora to be the name and location that you want to use for the new control files. The controlfiles should be given a different name to distinguish them from  the old database. In addition, change the DB_NAME parameter in the init<NEW_SID>.ora to be an appropriate name for the new database. Any  'ifile' parameters of the parameter file will need to be edited to point to  the new name of the include file in the new location.

Ensure that the parameter DB_BLOCK_SIZE is the same as the originating database, otherwise when creating the controlfile you may receive:

ORA-01160 "file is not a %s"

6. Prepare the 'CREATE CONTROLFILE COMMAND' for the Copied Database

In order to establish the new database in the new location, the CREATE CONTROLFILE command in the file ccf<NEW_SID>.sql should be executed. The  following steps illustrate how CREATE CONTROLFILE command is prepared.

a. The file ccf<NEW_SID>.sql must be edited before use. The CREATE  CONTROLFILE command will be preceded by a series of comments and a STARTUP NOMOUNT command. These need to be stripped out of the file. In  addition, after the create controlfile command, there will be a number of comments and the commands RECOVER DATABASE and ALTER DATABASE OPEN,  which should also be stripped out, leaving just the create controlfile  command itself.

b. The CREATE CONTROLFILE command itself should also be edited. Change the CREATE CONTROLFILE command in 'ccf<NEW_SID>.sql' to have the new
database name, and add the word 'SET', e.g:

SQL> CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS
becomes
SQL> CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS

c. The CREATE CONTROLFILE command also specifies the files which make up the database, and these must also be changed to name the files of the new database in the new location, e.g:

LOGFILE
GROUP 1 (
'/old_path/old_logfile_name1',
'/old_path/old_logfile_name2'
) SIZE 50k

would become:

LOGFILE
GROUP 1 (
'/new_path/new_logfile_name1',
'/new_path/new_logfile_name2'
) SIZE 50k

and

DATAFILE
'/old_path/old_file_name1' SIZE 5M,
'/old_path/old_file_name2' SIZE 10M
;

would become:

DATAFILE
'/new_path/new_file_name1' SIZE 5M,
'/new_path/new_file_name2' SIZE 10M
;

d. After Recreating Controlfile (9.2.x)

o Tablespace TEMP is available
o DBA_TEMP_FILES does not show a tempfile


RDBMS 9.2., 10g
=============
If using ALTER DATABASE BACKUP CONTROLFILE TO TRACE, to get a  CREATE CONTROLFILE SCRIPT - the DAtafile for Tablespace TEMP
(TEMPFILE) is not listed in

"CREATE CONTROLFILE STATEMENT"

You must add the TEMPFILE manuaally after Creating the Controlfile

> Alter database backup controlfile to trace ;

You should check Tracefile: (in udump) to get Statement on how to  Add the Tempfile to temp Tablespace

--> see end of tracefile

EXAMPLE:
========
Create Controlfile .....
...
;
...
...
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\U01\ORADATA\T920\TEMP01.DBF'
SIZE 10485760 REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#


Simply adjust this command (path/name) to your requirements and do
for example:

> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\U01\ORADATA\T920\TEMP01.DBF'
SIZE 10485760 REUSE AUTOEXTEND OFF;

7. Check REMOTE_LOGIN_PASSWORDFILE Parameter

Prior to executing the create controlfile script, check whether the initialisation parameter REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared. If this is set, then a valid passwordfile should exist in ORACLE_HOME/dbs or created using orapwd, otherwise you will receive an ora-600 [kzsrsdn:1]. See Note 97630.1.

8. Execute the 'CREATE CONTROLFILE' Command for the Copied Database

Having prepared the create controlfile script, it is now necessary to run the script from within the new instance. This is done by executing the following:

a. At the operating system prompt, change the value of the environment  variable ORACLE_SID from OLD_SID to NEW_SID. This can be done by using the following unix command from within the C shell:

%setenv ORACLE_SID NEW_SID

b. SQLDBA, SVRMGRL or SQLPlus

c. CONNECT INTERNAL or CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=/<full path>/init<NEW_SID>.ora

d. SQL> @ccf<NEW_SID>

Note: if any files which should be specified in the CREATE CONTROLFILE command are omitted, these files cannot be added to the new database at a later date. In addition, if any of the files specified in the CREATE CONTROLFILE command are NOT changed from their original names, then the corresponding files of the original database will become part of the copied database, and it will not be possible to restore them to the original database. If this happens, and if the files in question  are important, this will mean that the original database will need to  be restored from a backup.

When recreating the controlfile, it is possible to encounter ORA-1565, ORA-1503 and ORA-9782, "another instance has this  database mounted", the ORACLE_SID of the original database is still in the header of the first datafile of the copy and oracle checks if an sgadef<ORACLE_SID>.dbf still exists in $ORACLE_HOME/dbs, to workaround this problem shutdown the other database of which you made a copy prior to issueing the create controlfile command.

NOTE: If you are using online 'hot' backup at this point you should issue:

recover database until cancel using backup controlfile;

Apply all the archives you have

e.g. SQL> ALTER DATABASE OPEN RESETLOGS

9. Make a Full Cold Backup of the Copied Database

SHUTDOWN and take a full cold backup of the database in the new location. The full cold backup can be done as detailed in steps 2 and 3.

-----------慎用这个nid命令-----------

How to Change the DBID and the DBNAME by using NID (Doc ID 224266.1)

​编辑To Bottom

---

For 10gr1 and higher version please refer How to Change the DBID, DBNAME Using NID Utility Document 863800.1 ============================================================================ NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner. For the purposes of this document, the following fictitious environment is used as an example to describe the procedure: Original Database name is : TEST1BY Database name once change using nid is TEST_DB2 ============================================================================= 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)@(Connect string of TEST1BY) 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/(password)@(Connect string of 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\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\TEST1BYCONTROL01.CTL - modified Datafile D:\ORACLE\TEST1BY\SYSTEM01.DBF - dbid changed Datafile D:\ORACLE\TEST1BY\UNDOTBS01.DBF - dbid changed Datafile D:\ORACLE\TEST1BY\DRSYS01.DBF - dbid changed Datafile D:\ORACLE\TEST1BY\EXAMPLE01.DBF - dbid changed Datafile D:\ORACLE\TEST1BY\INDX01.DBF - dbid changed Datafile D:\ORACLE\TEST1BY\TOOLS01.DBF - dbid changed Datafile D:\ORACLE\TEST1BY\USERS01.DBF - dbid changed Datafile D:\ORACLE\TEST1BY\XDB01.DBF - dbid changed Control File D:\ORACLE\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)@(Connect string of TEST1BY) 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)@(Connect string of TEST1BY) 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 database 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@(Connect string of TEST1BY) dbname=test_db2 logfile=d:\oracle\nid.log DBNEWID: Release 9.2.0.1.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. Connected to database TEST1BY (DBID=849281895) Control Files in database: D:\ORACLE\TEST1BYCONTROL01.CTL Changing database ID from 849281895 to 1395399949 Changing database name from TEST1 to TEST1BY Control File D:\ORACLE\TEST1BYCONTROL01.CTL - modified Datafile D:\ORACLE\TEST1BY\SYSTEM01.DBF - dbid changed, wrote new name Datafile D:\ORACLE\TEST1BY\UNDOTBS01.DBF - dbid changed, wrote new name Datafile D:\ORACLE\TEST1BY\DRSYS01.DBF - dbid changed, wrote new name Datafile D:\ORACLE\TEST1BY\EXAMPLE01.DBF - dbid changed, wrote new name Datafile D:\ORACLE\TEST1BY\INDX01.DBF - dbid changed, wrote new name Datafile D:\ORACLE\TEST1BY\TOOLS01.DBF - dbid changed, wrote new name Datafile D:\ORACLE\TEST1BY\USERS01.DBF - dbid changed, wrote new name Datafile D:\ORACLE\TEST1BY\XDB01.DBF - dbid changed, wrote new name Control File D:\ORACLE\TEST1BYCONTROL01.CTL - dbid changed, wrote new name Database name changed to test_db2 . 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 DB_NAME=TEST1BY to DB_NAME=TEST_DB2 6. create a new password file Usage: orapwd file=<fname> password=<password> entries=<users> 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. Show parameter db_name 8. Open the database 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. 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. . Documentation ============= Oracle9i Database Utilities Release 2 (9.2)