More Oracle how-tos

How to create a database link in Oracle

How to apply an

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.