HOWTO: Replicate a database

Here is a procedure for duplicating a database (say, from production to test) on the same machine. I have used this successfully, however, I disclaim responsibility if you crater your database using this procedure!

On your original (prod) instance, do
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS; This dumps a script that can be used later to construct a new set of controlfiles for your new instance. Copy the trace file someplace out of the way, like /tmp.

Shut down the (prod) instance.

Perform a cold backup of all files comprising the prod database. You might need this if you err in the following steps.

Copy all files comprising the prod database to a new location (or locations) on same system. If you change filenames rather than directory or disk locations, keep careful track of before and after names.

Create an init.ora file for the new database. Easiest if you copy the init.ora for your (prod) database. Modify the new init.ora to have some new SID [I'm using "test" in this example]. Take care to point to new location of control file(s) that you just copied. If you have an OFA-compliant installation, you will also want to copy and possibly modify config.ora.

Edit the trace file you copied a few steps ago, saving as a sql script. Example:

      STARTUP NOMOUNT
      CREATE CONTROLFILE REUSE SET DATABASE TEST RESETLOGS
      ARCHIVELOG
      LOGFILE
             GROUP 1 (
             '/some/disk/path/redo1.log',


      .

      . (lines omitted)


      .

      DATAFILE
             '/some/disk/path/system1.dbf' SIZE 40M,


      .

      . (lines omitted)


      .

      ALTER SYSTEM ARCHIVE LOG ALL;
      ALTER DATABASE OPEN RESETLOGS;

Notes:       the REUSE parm should be used since we are modifying a
             control file that already exists on disk (from the copy
             we did).

             the SET DATABASE (SID) parm, the sid must match what you
             put into the init.ora file when you edited it.

             if you are not archiving (why?) then change the
             ARCHIVELOG statement to NOARCHIVELOG

Be sure your original (PROD) database is still shut down. Run oraenv to set your ORACLE_SID environment variable to the new sid (TEST), then
CONNECT INTERNAL Now, either run the script you created as

      _at_/some/path/scriptname
(where the scriptname is stored on disk as scriptname.sql) -OR-
print that script, and execute it interactively from the SQLDBA prompt if you prefer.

Your new database should be up and open for business.

Close it with a SHUTDOWN command.

Change your ORACLE_SID to PROD, start the original database.

If it comes up okay, you've done okay. Shut it back down.

Backup both databases.

Go for coffee.

--
Lynn Osburn 
losburn_at_pace.com 
REAL men don't use porn.