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