How to copy an Oracle database

Contributor Raymond Lefebvre, a database director, explains how to do one of the most common things DBAs are required to do – copy an Oracle database.

More Oracle how-tos

How to create a database link in Oracle

How to apply an Oracle patch

How to uninstall Oracle

Part of our “How do I do that in Oracle?” series, this tip explains in detail how to make an exact copy of an Oracle database.

Making an exact copy of an Oracle database – also known as cloning, duplicating, or refreshing – is by far the most common task an Oracle DBA is asked to do.  Some common reasons for copying an Oracle database include refreshing a non-production database for test/dev, providing a backup copy of a database for disaster recovery purposes, and relocating a database from one server to another.

But before we get started, there are some assumptions you should be aware of:

  • You are already familiar with Oracle database architecture/administration concepts
  • The source database name is PROD and the destination database name is CLONE.
  • The source database, PROD, is running in Archive Log mode as all PRD databases should.
  • The database copy/clone is being done on the same operating system and Oracle version.
  • The operating system being used in this example is UNIX and the Oracle version is 11g.
  • The $ORACLE_HOME/dbs/initCLONE.ora file has been created with no obsolete params.
  • All PROD database datafiles are assumed to be in the /u01/oradata/PROD directory.
  • All CLONE database datafiles are assumed to be in the /u01/oradata/CLONE directory.
  • The /u01/oradata/CLONE and /u01/oradata/CLONE/arch1 directories have been created.
  • The /u01/oradata/CLONE/flash_recovery directory has been created.
  • The $ORACLE_BASE/admin/CLONE/adump directory has been created.
  • The $ORACLE_BASE/diag/rdbms/CLONE directory has been created.

And last but not least, from this point on the term CLONE will be used in place of the words COPY, DUPLICATE, or REFRESH to represent “copy database/database copy”.  That’s it.  So let’s get started.

First connect to the PROD instance using SQL*Plus as SYSDBA and enter the following command:

SQL> ALTER DATABASE BEGIN BACKUP;

This command places all tablespaces in backup mode, which defers all datafile writes until the “ALTER DATABASE END BACKUP;” command is run, which will allow you to copy the database files even though the database is open and active.

Next, copy all PROD database datafiles (.dbf) – not redo logs or control files – to the CLONE directory:

unix-prompt> cp /u01/oradata/PROD/*.dbf  /u01/oradata/CLONE

If cloning to another machine, you can use the 'rcp' command, the 'sftp' utility (binary mode), or have the system/storage administrators use BCV (business continuous volumes) for EMC Symmetrix or Snap Clones/Snap Shots for EMC Clariion to create a third mirror copy. If the BCV or Snap Clone/Shot approach are used the third mirror should be created, then broken out of the mirror set. Then the resulting BCV or Snap Clone/Shot should be imported as volume group and mounted as the /u01/oradata/CLONE directory/filesystem.

Connect to the PROD instance using SQL*Plus as SYSDBA and enter the following command:

SQL> ALTER DATABASE END BACKUP;

This command takes all tablespaces out of backup mode and allows datafile writes to resume.

Connect to the PROD instance using SQL*Plus as SYSDBA and enter the following command:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Write down the time of the log file switch as you will be using this time later. This command flushes all currently pending database datafile writes to disk and creates an archive log that will allow for a complete/consistent database copy.

Note the name and date/time stamp of the resulting archive log file created in the previous step, which will be located in the LOG_ARCHIVE_DEST_1 directory, and copy the archive log file that was just created to the /u01/oradata/CLONE/arch1 directory:

unix-prompt> cp /u01/oradata/PROD/arch1/archivelog#.arc  /u01/oradata/CLONE/arch1

Connect to the PROD instance using SQL*Plus as SYSDBA and enter the following command:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This command creates a trace file in the USER_DUMP_DEST directory, which will be used to create a SQL script that will be used to create the new database using the PROD database datafiles.

Locate the trace file (.trc) created by the “ALTER DATABASE BACKUP CONTROLFILE TO TRACE;” command in the USER_DUMP_DEST directory (look for the most recently created trace file in the trace directory with a name similar to - PROD_ora_#####.trc like PROD_ora_20902.trc).

Copy the trace file to “clonedb.sql”, and edit the “clonedb.sql” file/script as follows:

unix-prompt>  cp PROD_ora_#####.trc  clonedb.sql

  • Delete all lines leading up to, but not including, the 2nd “STARTUP NOMOUNT” line
  • Change line that reads “CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ...” to read “CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS …”
  • Change all PROD references in the file to CLONE references.
  • Change line that reads: “RECOVER DATABASE USING BACKUP CONTROLFILE” to read: “RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME '2012-mm-dd:hh:mm'” where the '2012-mm-dd:hh:mm' represents the date/time stamp of the PROD archive log file created during the previous step of this procedure

Add one minute to the date/time stamp of the PROD archive log file and use this date/time for the RECOVER DATABASE UNTIL TIME command.

Split the “clonedb.sql” script into two scripts – “clonedb1.sql” and “clonedb2.sql”. One script should contain the entire STARTUP NOMOUNT command string plus the entire RECOVER DATABASE command string, while the second script should contain the entire ALTER DATABASE OPEN RESETLOGS and ALTER TABLESPACE TEMP command strings similar to below:

clonedb1.sql

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG

MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8

MAXLOGHISTORY 292 LOGFILE

GROUP 1 '/u01/oradata/CLONE/redo01a.log'  SIZE 50M BLOCKSIZE 512,

GROUP 1 '/u01/oradata/CLONE/redo02a.log'  SIZE 50M BLOCKSIZE 512,

GROUP 1 '/u01/oradata/CLONE/redo03a.log'  SIZE 50M BLOCKSIZE 512,

DATAFILE '/u01/oradata/CLONE/system01.dbf', '/u01/oradata/CLONE/sysaux01.dbf',

'/u01/oradata/CLONE/undotbs01.dbf', '/u01/oradata/CLONE/users01.dbf',

'/u01/oradata/CLONE/data01.dbf', '/u01/oradata/CLONE/indx01.dbf'

CHARACTER SET WE8MSWIN1252;

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME '2012-02-20:12:42';

clonedb2.sql

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/CLONE/temp01.dbf'

SIZE 1024M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Set the ORACLE_SID to CLONE:

unix-prompt> export ORACLE_SID=CLONE

Connect to the CLONE instance using SQL*Plus as SYSDBA and run clonedb1.sql script:

SQL> @clonedb1.sql

This command starts the CLONE instance, creates the controlfiles, and prompts for the archive log to apply. Manually apply appropriate archive log by pressing the ENTER/RETURN key, then type CANCEL when no further archive log files remain to be applied.

Connect to the CLONE instance using SQL*Plus as SYSDBA and run clonedb2.sql script:

SQL> @clonedb2.sql

This command creates/resets the redo logs and recreates the temporary tablespace.

That’s it.  Congratulations on copying an Oracle database.  You have just successfully copied a live production database named PROD to a non-production database named CLONE, which is now ready for use.

This was first published in March 2012

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close