PURPOSE
-------

This article shows how to make copy of production database on the same
machine while production database is up and running.
 
SCOPE & APPLICATION
-------------------

You want to make copy of production database for testing purposes. New
database will be on same machine. You experience problems opening this
new database, because databases cannot have same name and ORACLE_HOME.
[NOTE:18070.1] describes possibility to copy database from cold backup,
however you cannot shutdown production database and must use online
backup.


MAKING COPY OF RUNNING DATABASE ON SAME MACHINE
-----------------------------------------------

After you make copy of database either from online backup or by copying 
files from running database by using ALTER TABLESPACE BEGIN/END BACKUP
and then cp from OS to new directory, you must change database name before
opening database. This can be done by recreating controlfile with a new 
database name. Then you can recover database using archive logs from original
database and finally you can open it with resetlogs. This is possible only
if production database is in ARCHIVELOG mode, because recovery from online
backups requires reaplying archived redo logs.

Here are detailed steps, how can this be done:
Assume production database is named PROD and new TEST.

1. Create new init file, password file (if used) from production init file.
   Change name of database from PROD to TEST. Change all directories 
   (to controlfiles, bdump, udump, ...) to new directory.

2. Prepace script for recreating controlfile - from production database issue:
   ALTER DATABASE BACKUP CONTROLFILE TO TRACE 
   and copy it from user_dump_dest

3. In create controlfile script change:
     CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ... 
   to:
     CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS (NO)ARCHIVELOG
   You must specify SET DATABASE and RESETLOGS. ARCHIVELOG mode can be changed,
   it depends in what mode you can run TEST database.
   
   Change all directories in create controlfile clause to point to directory
   for new database.
   Leave here only CREATE CONTROLFILE clause, other like recover we will
   run manualy.

4. Make online copy of all data files using:
   ALTER TABLESPACE <NAME> BEGIN BACKUP;
   copy of all datafiles within tablespace <NAME> to new directory (with cp)
   ALTER TABLESPACE <NAME> END BACKUP;

   Do not copy controlfiles and redo log files! They will be recreated.
   You must copy files only after ALTER .. BEGIN BACKUP, otherwise datafiles
   can be corrupted!
   Name of datafiles and tablespaces can be obtained using:
   SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

   Note: Instead of this and following step you can also use online backup 
   of your database including archived redo logs from time of backup.

5. After copy of all datafiles issue:
   ALTER SYSTEM ARCHIVE LOG CURRENT
   We need all redo logs including CURRENT to time of END BACKUP of last datafile.

6. Change ORACLE_SID to TEST and start svrmgrl

7. STARTUP NOMOUNT

8. Run prepared script to create new controlfile

9. Issue RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
   and reaply all archived redo logs from production database including last
   created with ALTER DATABASE ARCHIVE LOG CURRENT
   After that give CANCEL

10. Finally open database with resetlogs:
    ALTER DATABASE OPEN RESETLOGS

Database will be now successfully recovered and opend with new database name.





Hosted by www.Geocities.ws