Restoring Hot Backup Steps

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 pfilefrom 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