Oracle Tip Clone an Oracle Database

Updated : 2-November-1998
Version : 1.0

Description

Need to create a duplicate database on the same machine ? Database too big to use Export/Import ?
This is the easiest way to do it.

Assume the original database is called PROD1 and you want to create a CLONE1 duplicate database.

Procedure

- Find some disk space and create appropriate directories / file systems for you cloned database (conforming to the OFA guidelines)

- Connect to the PROD1 instance and dump the controlfile using the SQL command

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This will put a text copy of the controlfile in the USER_DUMP_DEST

- Shutdown Normal PROD1 database

- Perform an operating system copy of PROD1 to the new location where CLONE1 will reside.

- Startup the PROD1 instance

- Edit the controlfile you created and change all the path names of the database to the new location.

- Set your environment to the CLONE1 instance and run Server Manager (svrmgrl)

CONNECT INTERNAL
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "CLONE1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 64
MAXINSTANCES 8
MAXLOGHISTORY 800
LOGFILE
GROUP 1 '/oracle/data/CLONE1/redos/log1CLONE1.dbf' SIZE 512K,
GROUP 2 '/oracle/data/CLONE1/redos/log2CLONE1.dbf' SIZE 512K,
GROUP 3 '/oracle/data/CLONE1/redos/log3CLONE1.dbf' SIZE 512K
DATAFILE
'/oracle/data/CLONE1/system_ts/systCLONE1.dbf' SIZE 25M,
'/oracle/data/CLONE1/data_ts/data_CLONE1.dbf' SIZE 230M,
'/oracle/data/CLONE1/index_ts/index_CLONE1.dbf' SIZE 230M,
'/oracle/data/CLONE1/rbs_ts/rbs_CLONE1.dbf' SIZE 10M,
'/oracle/data/CLONE1/temp_ts/temp_CLONE1.dbf' SIZE 10M,
'/oracle/data/CLONE1/tools_ts/ts_tools_CLONE1_02.dbf' SIZE 15M,
'/oracle/data/CLONE1/users_ts/ts_users_CLONE1.dbf' SIZE 1M,
;

ALTER DATABASE OPEN RESETLOGS;

- That's it, an exact duplicate of PROD1 and a painless procedure.

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal