• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to clone the database using Manual Hot backup

How to clone the database using Manual Hot backup

December 21, 2015 by techgoeasy Leave a Comment


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;


Filed Under: Oracle, Oracle Database Tagged With: How to clone the database using manual hot backup

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1
  • Step by step upgrade process for R12.2 Upgrade Part -4(Applying 12.2.x Release Update Pack)

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us