Home » Oracle » Oracle Database » How to clone the database using Manual Hot backup

How to clone the database using Manual Hot backup

Hot backup database cloning is more suitable for databases that are running 24/7 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 the ‘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

See also  How to compare statistics using history for a table in Oracle

(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 an appropriate directory structure in the 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 is 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 the resetlogs option.

SQL> alter database open resetlogs;

Now you have a cloned running on your destination location

See also  Recovery Manager (RMAN) :Benefits, media manager software

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
How to clone R12.2 Environment: This page provides a step-by-step approach for cloning the R12.2 environment successfully. R12.2 is the latest release of Oracle apps
How to clone R12.1 Environment: This page gives step-by-step instructions to clone the R12.1 environment without issues and errors.
How to Clone Oracle Application Server 10g: check out this post for step-by-step instructions to clone application server 10g.It involves preparing clone, copying, and then cloning

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top