Creating a copy of a database

Import export is often used to copy data from one database to another, however often dba's are required to duplicate an entire database. it is assume that the original database is called PROD and you want to create a TEST duplicate database. This tip is an example, TRU (UK) would not advocate placing production and test/development database on same machine.

 
Locate and note files.
The first step is to locate and copy all database files to their new location. You can use the view V$DATAFILE in the PROD database to locate these files. Before running the query from V$DATAFILE, ensure that you are connected to the PROD database by selecting from V$DATABASE. An additional check to make ensure that enough disk space is available to house the TEST database.
SQL> select name from v$database;

NAME
---------------------------------------
PROD


SQL> select name from v$datafile;

NAME
---------------------------------------
/u02/oradata/PROD/PROD_system01.dbf
/u03/oradata/PROD/PROD_rbs01.dbf
/u02/app/oradata/PROD/PROD_temp01.dbf
/u02/app/oradata/PROD/PROD_data01.dbf
/u03/app/oradata/PROD/PROD_index01.dbf

 

Create the script that will re-create the controlfile

SQL > alter database backup controlfile to trace;

The above statement will put a text copy of the controlfile in the USER_DUMP_DEST directory.It is sometime worth renaming it to something more meaningfull. e.g. create_test.sql. This file will be edited and utilised later.

Editing the initTEST.ora file.

Create the appropriate directory structure to hold alert logs, pfile etc, normally under $ORACLE_BASE/admin/TEST. Copy the initPROD.ora TO $ORACLE_BASE/admin/TEST/pfile/initTEST.ora. Edit the initTEST.ora file to reflect new loaction for alert,trace,core,archive,control files. Additionally change the dbname, service_names and instance_name from PROD to TEST.

Finally on if appropraiate create symbolik link from $ORACLE_HOME/dbs/initTEST.ora to initTEST.ora and also create a new password file using orapwd.
The database should be shutdown and files copied to new location and renamed if necessary.
Source Database Destination Database
/u02/oradata/PROD/PROD_system01.dbf /u02/oradata/TEST/TEST_system01.dbf
/u03/oradata/PROD/PROD_rbs01.dbf /u03/oradata/TEST/TEST_rbs01.dbf
/u02/app/oradata/PROD/PROD_temp01.dbf /u02/app/oradata/TEST/TEST_temp01.dbf
/u03/app/oradata/PROD/PROD_index01.dbf /u03/app/oradata/TEST/TEST_index01.dbf
/u02/app/oradata/PROD/PROD_data01.dbf /u02/app/oradata/TEST/TEST_data01.dbf

Edit Trace File

Edit the trace file created earlier i.e. create_test.sql.
Remove everything up to the "START NOMOUNT" statement and everything after the semicolon at the end of the "CREATE CONTROLFILE" statement.

Edit the line starting with "CREATE CONTROLFILE" and insert the word "SET" right before thekeyword DATABASE.
On the same line, modify the database name changing it from PROD to TEST.
On the same line, change the keyword NORESETLOGS to RESETLOGS. 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 600
    MAXINSTANCES 20
    MAXLOGHISTORY 2000
LOGFILE
  GROUP 1 (
    '/u04/oradata/TEST/redo1/redo1a.log',
    '/u04/oradata/TEST/redo1/redo1b.log',
    '/u04/oradata/TEST/redo1/redo1c.log',
  ) SIZE 5000K,
  GROUP 2 (
    '/u05/oradata/TEST/redo1/redo2a.log',
    '/u05/oradata/TEST/redo1/redo2b.log',
    '/u05/oradata/TEST/redo1/redo2c.log',
  ) SIZE 5000K
DATAFILE
  '/u02/oradata/TEST/TEST_system01.dbf',
  '/u03/oradata/TEST/TEST_rbs01.dbf',
  '/u02/oradata/TEST/TEST_temp01.dbf',
  '/u03/oradata/TEST/TEST_index01.dbf',
  '/u02/oradata/TEST/TEST_data01.dbf'
; 
Start-up and recover Test Database
export ORACLE_SID=TEST
sqlplus /nolog
connect / as sysdba
@create_test.sql              - the database instance should start-up successfully.
alter database recover database until cancel using backup controlfile;
alter database open resetlogs;

sQL > select name from v$database;

NAME
---------
TEST
1 row selected.

The database is now available for use. Remember to configure the listener, add entries in tnsnames.ora and finally change passwords, especially if copy was taken from production.

 

This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.