Oracle: Copy DB w/o rman

Title:

Oracle: Copy DB w/o rman

Author:

Douglas O’Leary <dkoleary@olearycomputers.com>

Description:

Oracle: Copy DB w/o rman

Date created:

08/16/2010

Date updated:

08/16/2010

Disclaimer:

Standard: Use the information that follows at your own risk. If you screw up a system, don’t blame it on me…

The rman duplicate database command is a pretty straight forward method of creating copies of your database. Some applications, like SAP, don’t use rman, though. The process below outlines how to copy a database manually.

I included the steps for copying an open as well as closed database. The process seems a little more straight forward and reliable for closed databases; however, users being who they are, will probably pick the more difficult of the options.

The process should be fairly similar regardless of environment. This example shows copying a source database, oci1, to a target database, oci3, on the same host.

1. Create admin directories as needed:

   cd /oracle/admin
   find oci1 -type d -print | sed 's/oci1/oci3/g' | \
   xargs -i mkdir -p -m 755 {}
   mkdir -p -m 755 /oracle/oradata/oci3
   

2. Backup/restore the datafiles - On the source database:

   1. alter database backup controlfile to trace Creates a trace file in the udump directory with sql instructions on recreating the controlfile.

   2. if hot:

      alter system checkpoint;
      alter database begin backup;
      

   3. if cold: shutdown immediate;

   4. Backup the datafiles using a backup means of your choice. [rs]cp/ftp the datafiles to their new location if not using backup software.

   5. if hot:

      alter database end backup
      alter system checkpoint
      

   6. if cold: startup

   7. Force a log switch/archive

      1. alter system switch logfile to force a log switch.

      2. Wait, patiently, for the logfile to get archived and become inactive.

      3. [rs]cp/backup/restore the archivelogs to their new location.

3. Update the controlfile creation script and create a new controlfile

   1. cd ${db}/udump directory and identify the file that was just created. ls -lart | tail

   2. Copy/rename the trace file to a sql script and edit.

      1. Remove lines 1 through the line above the second startup nomount line.

      2. Update the sid names as needed.

      3. Update the create controlfile lines:

         From: CREATE CONTROLFILE REUSE DATABASE "OCI1" NORESETLOGS ARCHIVELOG
         To: CREATE CONTROLFILE SET DATABASE "OCI3" RESETLOGS
         

      4. In order to get this to work cleanly regardless of hot/cold, I had to comment out everything after the pl/sql code.

      5. Update file locations as needed.

4. Create a backup of the init.ora file:

   create pfile = '${file}' from spfile;
   Edit the pfile updating sid names, etc as needed.
   

5. Run the contolfile creation script:

   export ORACLE_SID="oci3"
   sqlplus / as sysdba
   @cr_oci3
   

6. Recover/open the database:

   recover database using backup controlfile until cancel
   Supply archive log names as requested.
   alter database open resetlogs