Checked for relevance on 20-Oct-2010

PURPOSE
~~~~~~~~
This article explains how to make a copy of a database from one machine to 
another while the source database is up and running.

 
SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~~
Note:174226.1 describes how to copy (clone) a database from a cold backup to 
another system, but at this time you are unable to shutdown the production 
database and you wish to instead use an online backup.

In order to use this note the database to be copied must be running in 
ARCHIVELOG mode as the steps outlined in this note will require you to apply 
archived redo logs to the copied database.


How To Make A Copy Of An Open Database For Duplication On A Different Machine
==============================================================================

Method Summary:
- make a copy of the database
- move copied files to the new machine
- recover the copied database using the archive logs from original database 
- open the copied database with resetlogs.


NOTE:
Please read the entire document before attempting this procedure.
 

Detailed steps:
---------------

These steps assume the source database and the new copy of the database, 
hereafter called the target database, will have the same database name. 
For the example in this note the source and target database names will both be 
PROD.  If you wish to rename the database upon copying the database then follow 
the procedures in Note:135651.1.


1. Create a new init.ora file and password file (if used) from the production 
init.ora file.  If using an spfile, see Note:249664.1 on how to create a pfile
from an spfile.  Then can use Note:166601.1 to convert back to an spfile once 
this procedure is completed.  

Change all listed directory locations (for controlfiles, bdump, udump, ...) to 
the new directory locations as necessary.

2. Obtain a trace file script. to recreate the controlfile.  On the source 
database issue the following command:

      ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The trace file script. will be put into the user_dump_dest directory.  The name 
of the trace file script. will be something like PROD_ora_12345.trc. Compare the 
date and time of the new trace file script. to the time in which you entered the 
ALTER DATABASE BACKUP CONTROLFILE TO TRACE command.  This will ensure you will 
be using the most recent trace file script, the one you created in this step.

3. In the create controlfile script. just created in step 2 change:
     
      CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS
   to:  
      CREATE CONTROLFILE DATABASE "PROD" RESETLOGS ARCHIVELOG
   
You must specify RESETLOGS. 

The ARCHIVELOG mode may be changed to NOARCHIVELOG if you wish to run the 
copied database in noarchive log mode.

Change all directories in the create controlfile clause to point to the 
correct directories for the new target database, if necessary.  

Leave "only" the CREATE CONTROLFILE clause. The other statements, like the 
recover command, will be done manually. Be sure you also remove the STARTUP 
NOMOUNT command.

4. On the source database make an online copy of all datafiles using:
   
      ALTER TABLESPACE  BEGIN BACKUP;
   
Copy all datafiles within tablespace  to the new directory. On Unix 
systems, this can be done with the cp command. 
   
   Then do:

      ALTER TABLESPACE  END BACKUP;

Do NOT copy the controlfiles and redo log files as they will be recreated.  You 
must copy the datafiles only after the ALTER .. BEGIN BACKUP command has been 
executed, otherwise the datafiles may be corrupted.

The names of the datafiles and tablespaces to which the datafiles belong can be 
obtained using the following command:
   
      SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

Note: Instead of performing step 4 and step 5 to create a new online backup, 
you may instead use a previously taken online backup of your database. If you 
choose to use a previous online backup be sure to copy the required archived 
redo logs taken with the the previous online backup.
   
5. After all datafiles have been copied and the tablespaces taken out of backup 
mode issue the following command:
 
      ALTER SYSTEM ARCHIVE LOG CURRENT;
   
You will need all of the archivelog files from the start of datafile copy commands 
including the one just created with the command ALTER SYSTEM ARCHIVE LOG CURRENT.

6. On the target system set your ORACLE_SID to PROD and start Server Manager 
or SQL*Plus.

7. On the target system issue STARTUP NOMOUNT command. For example:

      SQL>  startup nomount pfile=initPROD.ora

8. Run the prepared script. created in step 3 to create the new controlfile.  
For example:

      SQL>  @PROD_ora_12345.trc

9. Issue the command:

      RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE 

You will be prompted to apply all of the archived redo logs from the source 
database including the last archive redo log file created with the ALTER 
DATABASE ARCHIVE LOG CURRENT command from step 5.  

After applying all of these archive log files issue the CANCEL command.

10. Open the database with resetlogs:

        ALTER DATABASE OPEN RESETLOGS

At this point the target database will have been successfully cloned and opened.

If this is a 10g database and you wish to add Enterprise Manager Database Control 
for the new database, follow the steps in this note:

  278100.1  How To Drop, Create And Recreate DB Control In A 10g Database


Related Documents:
==================
Note:73301.1  How to make a copy of a database on the same Windows NT machine
(Using a hot backup)

Note:135651.1  How to Make a Copy of an Open Database on the Same Machine 
(Using a hot backup)

Note:18070.1   How To Make A Copy Of A Database On The Same Unix Machine 
(Using a cold backup)

Note:375672.1  How to Copy a Noarchivelog Database to the Same Unix Machine.  
(Using a cold backup)

Note:174226.1  How To Copy An Oracle Database To Another Machine 
(Using a cold backup)

Note:228257.1  RMAN 'Duplicate Database' Feature in Oracle9i / Oracle 10G

Note:369644.1  Answers To FAQ For Restoring Or Duplicating Between Different 
Versions And Platforms