How to clone the database using Manual Hot backup

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

The high-level steps for How to clone the database using Hot backup database cloning
1) Take the hot backup of the database to be cloned
2) Copy all the backup files to the destination server
3) Create the init.ora, then startup nomount then create the control file on the new location
4) Recover the database and alter database open resetlogs to bring the new clone online

Let us take an example

Source Database Name: TEST
Source Database physical files path=/u001/oracle/TEST/oradata

Cloned Database Name: TEST_NEW
Target Database physical files path=/u001/oracle/TEST_NEW/oradata

How to take Manual hot backup of the database to be cloned

(1) Find out the path and names of data files.

SQL> select name from v$datafile;

(2) Backup the parameter file
If ‘TEST’ database is using spfile create pfile

SQL> create pfile=’/tmp/initTEST.ora’ from spfile;

Use the OS command to copy the pfile to a backup location.

(3) Switch the log file

SQL> alter system switch logfile;
SQL> archive log list;
SQL>select SEQUENCE#,ARCHIVED,STATUS from v$log where STATUS = 'ACTIVE';

(4) Place the database in backup mode

SQL> alter database begin backup;

If database begin backup is not supported, then do hot backup for individual tablespace

select 'alter tablespace '||tablespace_name||' begin backup;' from dba_tablespaces
/

(5) Copy all data files of the ‘TEST’ database to a backup location. We dont need to copy the temp files

(6) After copying all data files, release the database from backup mode.

SQL> alter database end backup;

If database begin backup is not supported, then do hot backup for individual tablespace

select 'alter tablespace '||tablespace_name||' end backup;' from dba_tablespaces
/

(7) Switch the current log file and note down the log sequence number

SQL> alter system switch logfile;
SQL> archive log list;
SQL>select NAME,RECID from v$archived_log where RECID=(select SEQUENCE#-1 from v$log where STATUS = ''ACTIVE'');

Copy all the backup files to the destination server

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
Copy all data files from the backup location to the Target location. It could be a different server or the same server

$ mkdir /u001/oracle/TEST_NEW/oradata
cp or rcp depending the target nodes

Create the init.ora, then startup nomount then create the control file on the new location

On the target node

a) Copy the backup init. ora to the $ORACLE_HOME/dbs and then change the db_name, control file paths, dump parameter and other things as necessary

$ cd $ORACLE_HOME/dbs
$ vi initTEST_NEW.ora
db_name=TEST_NEW
control_files=/u001/oracle/TEST_NEW/oradata
....

b) Create appropriate directory structure in clone database for dumps as specified in init.ora file

c) Copy the control file trace file from the source database to the target node and then edit it to redo log files location, datafiles location, database name

CREATE CONTROLFILE SET DATABASE “TEST_NEW” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u001/oracle/TEST_NEW/oradata/redo01.log’ SIZE 200M,
GROUP 2 ‘/u001/oracle/TEST_NEW/oradata/redo02.log’ SIZE 200M,
DATAFILE
‘/u001/oracle/TEST_NEW/oradata/system01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/undotbs01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/sysaux01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/users01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/example01.dbf’
CHARACTER SET UTF8;

d) Source the target database env like ORACL_SID, Oracle_HOME

$ export ORACLE_SID=TEST_NEW
SQL> startup nomount
SQL> @$ORACLE_HOME/dbs/cntrl.sql

Recover the database and alter database open resetlogs to bring the new clone online

Once the control file’s successfully created, Recover the database using the backup controlfile option.

SQL> recover database using backup controlfile until cancel;

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 sequence no. , type CANCEL to end the media recovery.
Open the database with resetlogs option.

SQL> alter database open resetlogs;

Now you have a cloned running on your destination location

Hope you like this post on How to clone the database using a Manual Hot backup

Related Articles

backup controlfile in RMAN : Check out this post on how to backup controlfile in RMAN, How to backup controlfile to trace, and How to enable auto backup of controlfile
check rman backup status: Check out How to check RMAN backup status in sql, what is the status of the backup and input_type of the backup
RMAN List backup : RMAN List backup commands are used to list the backup taken using RMAN,Date and Time and many other details are included
RMAN Backup commands :Check out the RMAN Backup commands in this post. This is going to be very helpful for the person who is involved in backup and recovery