Last updated on September 3rd, 2016 at 01:29 am
Hot backup database cloning is more suitable for databases which are running 24X7 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.
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
1.
a) Find out the path and names of datafiles.
SQL> select name from v$datafile;
2.
b) Backup the parameter file
If ‘TEST_NEW’ database is using spfile create pfile,
SQL> create pfile=’/tmp/initTEST_NEW.ora’ from spfile;
If database is using pfile, use OS command to copy the pfile to a backup location.
c)
SQL> alter system switch logfile;
SQL> archive log list;
select SEQUENCE#,ARCHIVED,STATUS from v$log where STATUS = ‘ACTIVE’;
d. Place the database to backup mode
SQL> alter database begin backup;
If database begin backup is not supported,then do hotbackup for individual tablespace
select ‘alter tablespace ‘||tablespace_name||’ begin backup;’ from dba_tablespaces
/
e) Copy all data files of ‘TEST’ database to a backup location.We dont need to copy the tempfiles
f. After copying all datafiles, release the database from backup mode.
SQL> alter database end backup;
If database begin backup is not supported,then do hotbackup for individual tablespace
select ‘alter tablespace ‘||tablespace_name||’ end backup;’ from dba_tablespaces
/
g) Switch the current log file and note down the log sequence number
SQL> alter system switch logfile;
SQL> archive log list;
select NAME,RECID from v$archived_log where RECID=(select SEQUENCE#-1 from v$log where STATUS = ”ACTIVE”)
2) 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 backup location to the Target location. It could be different server or same server
$ mkdir /u001/oracle/TEST_NEW/oradata
cp or rcp depending the target nodes
3. 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 neccessary
$ 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 SOurce database to target node and then edit it for 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
Once the control file’s successfully created
4) Recover the database using 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;