- Arun Kumar
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 SID: RIS
Clone Database SID: RIS
Source and target files locations is same
Parameter file cloning
Find out the path and names of datafiles on the source database
SQL> select name from v$datafile;
Create pfile from spfile and copy it to target server
SQL> create pfile from spfile;
scp $ORACLE_HOME/dba/initRIS.ora oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs/
On the target server, open the pfile and create locations to hold controlfiles and dump files
vi $ORACLE_HOME/dbs/initRIS.ora
In my case I had to create below two main locations
mkdir -p <control_file_locations>
mkdir -p <adump_locations>
Controlfile cloning
On the target server, take controlfile backup to trace location
SQL> alter database backup controlfile to trace as '/tmp/RIS_ctl.sql' REUSE RESETLOGS;
Copy the trace file on target server
scp /tmp/RIS_ctl.sql oracle@clone.dbagenesis.com:/tmp/
On target server, open the trace file (/tmp/RIS.ctl) and follow below steps
vi /tmp/RIS_ctl.sql
Delete everything before "CREATE CONTROLFILE"
Delete everything after ";"
And you must be left with below. Save and close the file
CREATE CONTROLFILE REUSE DATABASE "RIS" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/db_files/RIS/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/u01/db_files/RIS/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/u01/db_files/RIS/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/db_files/RIS/system01.dbf',
'/u01/db_files/RIS/sysaux01.dbf',
'/u01/db_files/RIS/undotbs01.dbf',
'/u01/db_files/RIS/users01.dbf'
CHARACTER SET AL32UTF8
;
Create the logfile and datafiles locations on clone server. In my case, all files reside under one location
mkdir -p /u01/db_files/RIS
Our CREATE CONTROLFILE script is ready, we will use it later in the cloning process.
Datafiles cloning
Note down the oldest log sequence number on the source database
SQL> alter system switch logfile;
SQL> archive log list
Place the source database into to backup mode
SQL> alter database begin backup;
Copy all datafiles and logfiles from database to target server
scp /u01/db_files/RIS/*log /u01/db_files/RIS/*.dbf oracle@clone.dbagenesis.com:/u01/db_files/RIS/
Release the source database from backup mode
SQL> alter database end backup;
Switch the current log file on source database and note down the oldest log sequence number
SQL> alter system switch logfile;
SQL> archive log list;
Copy all archive log files generated on the source database during begin backup and the end backup mode to the target server.
Make sure to copy the archivelogs in the same location on target server as on the source server
Recover clone database
Let us create the controlfiles for the clone database and put it in mount mode
sqlplus / as sysdba
SQL> startup nomount;
SQL> @/tmp/RIS_ctl.sql
SQL> alter database mount;
Recover the clone database using backup controlfile option
SQL> recover database using backup controlfile until cancel;
You will be prompted to feed the archive log files henceforth. Hit enter, enter until all the archive logs are applied and then type CANCEL;
Open the database with resetlogs option
SQL> alter database open resetlogs;
Enjoy!