Oracle Hot Database Cloning

Hot database cloning is more suitable for databases which are running 24X7X365 type of databases and is done using the hot backup. For hot database cloning, database has to be in archivelog mode and there no need to shutdown the database.

Considerations

• Source Database Name: RIS

• Clone Database Name: RISCLON

Source Database physical files path=/u01/RIS/oradata

Cloned Database physical files path=/u02/RISCLON/oradata

Steps to be followed

1. Find out the path and names of datafiles.

SQL> select name from v$datafile;

2. Backup the parameter file

If ‘RIS’ database is using spfile create pfile,

SQL> create pfile=’/u02/RISCLON/initRISCLON.ora’ from spfile;

If database is using pfile, use OS command to copy the pfile to a backup location.

3. Note down the oldest log sequence number.

SQL> alter system switch logfile;
SQL> archive log list

4. Place the database to backup mode

SQL> alter database begin backup;

5. Copy all data files of ‘RIS’ database to a clone location.

$ mkdir /u02/RISCLON/oradata
$ cp /u01/RIS/source/oradata/*.dbf /u02/RISCLON/oradata/

6. After copying all datafiles, release the database from backup mode.

SQL> alter database end backup;

7. Switch the current log file and note down the oldest log sequence number

SQL> alter system switch logfile;
SQL> archive log list;

8. Copy all archive log files generated during FIRST old log sequence no. to the LAST old log sequence no. during which the database was in backup mode.

9. Take the control file trace backup to the trace path

SQL> alter database backup controlfile to trace;

10. Create appropriate directory structure for the clone database and specify the same

$ cd /u02/RISCLON
$ mkdirbdumpudump

11. Edit the clone database parameter file and make necessary changes to the clone database

$ cd /u02/RISCLON

$ viinitRISCLON.ora

db_name=RISCLON

control_files=/u02/RISCLON/oradata/cntrl01.ctl

background_dump_dest=/u02/RISCLON/bdump

user_dump_dest=/u02/RISCLON/udump

. . .

. . .

:wq!

12. Startup the cloned database in NOMOUNT phase.

$ export ORACLE_SID=RISCLON

SQL> startup nomountpfile=’/u02/RISCLON/initRISCLON.ora’

13. Create the control file for the clone database using the trace control file.

CREATE CONTROLFILE SET DATABASE “RISCLON” RESETLOGS ARCHIVELOG

• MAXLOGFILES 16

• MAXLOGMEMBERS 3

• MAXDATAFILES 100

• MAXINSTANCES 8

• MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘/u02/RISCLON/oradata/redo01.log’ SIZE 5M,

GROUP 2 ‘/u02/RISCLON/oradata/redo02.log’ SIZE 5M,

DATAFILE

‘/u02/RISCLON/oradata/system01.dbf’,

‘/u02/RISCLON/oradata/undotbs01.dbf’,

‘/u02/RISCLON/oradata/sysaux01.dbf’,

‘/u02/RISCLON/oradata/users01.dbf’,

‘/u02/RISCLON/oradata/example01.dbf’

CHARACTER SET AL32UTF8;

14. Create the control file by running trace file from the trace path

SQL> @u01/RIS/source/udump/cntrl.sql

15. Recover the database using backup controlfile option.

SQL> recover database using backup controlfile until cancel;

16. You will be prompted to feed the archive log files henceforth. Specify the absolute path and file name for the archive log files and keep feeding them until you cross the LAST old sequence no. (Refer: Step 8), typeCANCEL to end the media recovery.

17. Open the database with resetlogs option.

SQL> alter database open resetlogs;