Thursday, February 10, 2011

Cloning a database manually

Using the following steps one can clone a database manually. In the following example PRD represents the source and DEV represents target/new database.

1) Get list of datafiles on the PRD database. In the following example this database has 3 datafiles.

SQL> SELECT name FROM v$datafile;
NAME
——————————————————————————–
/u03/oradata/PRD/system01.dbf
/u03/oradata/PRD/undotbs01.dbf
/u03/oradata/PRD/sysaux01.dbf

3 rows selected.

2) On the PRD (source) database, run the following query to find the last archived logs, the archive logs created after backup begin is run needs to be copied, so make a note of sequence # shown needs to be copied on the DEV (target) database to restore.

3) Execute the following SQL to make the datafiles in backup mode. This command is supported in 10g for 9i, each tablespace would need to placed in backup mode by using “ALTER TABLESPACE name BEGIN BACKUP;”

SQL> ALTER DATABASE BEGIN BACKUP;

Database altered.

4) Now the datafiles can be copied from PRD (source) to DEV (target) machine from the list created in step (1)

4) Execute the following SQL to make the datafiles out of backup mode.

SQL> ALTER DATABASE END BACKUP;

Database altered.

5) Perform some logswitches on the PRD (source) database, this step will create archive logs on the source database.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

6) On the PRD (source) database, run the SQL in step (2) again to get the current sequence# of archived logs. The archived log files created for the sequence# between step (2) and step (6) would need to copied to the DEV (target) machine. In this example archive logs with sequence# 6-12 would need to be copied on the target host.

SEQUENCE# TO_CHAR(NEXT_TIME,’D
———- ——————–
14 12-JAN-2009 20:43:05

1 row selected.

7) Modify the pfile by copying the pfile from PRD (source) to target or if the source is using spfile, run the following SQL to create the pfile “CREATE PFILE=’/tmp/initDEV.ora’ TO SPFILE;” The parameters in the pfile needs to be modified for the target database. In the following example the following parameters where modified where “PRD” was replaced with “DEV”.

8 ) On the PRD (source) instance create the backup control file to trace using the following SQL

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database Altered

Modify the control file creation script in udump folder, section where the resetlogs and change REUSE TO SET, modify the name PRD to DEV, change the path of the datafiles if needed, only the following section would needs to be run.

9) On the target machine, and start the instance in nomount, make sure it will use the pfile created in step (7

export ORACLE_SID=DEV
SQL> startup nomount

10) Using the script create the control file created in step ( 8 )

11) Now database can be recovered, at this step Oracle will prompt for the archive logs copied in step (2) and (6) or a specific point in time can be specified. When all the archive logs are applied, type CANCEL
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;