
Steps for Database Cloning using Hot Backup :-
- Backup the parameter file
If SOURCE database is using spfile create pfile or if database is using pfile, use OS command to copy the pfile to a backup location.
- Note down the oldest log sequence number.
- Place the database to backup mode
- Copy all data files of ‘Source’ database to a clone location.
- After copying all data files, release the database from backup mode.
- Switch the current log file and note down the oldest log sequence number
- 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.
- Take the control file trace backup to the trace path
- Edit the clone database parameter file and make necessary changes to the clone
- Startup the cloned database in NOMOUNT mode, using target pfile.
- Create the control file for the clone database using the trace control file.
- Create the control file by running trace file from the trace path
- Recover the database using backup control file option.
- Open the database with reset logs option
Hot Backup :-
Hot Backups can be performed when the database is up and in Archive log mode.
- First, check the v$database view to see if your database is set in Archive log mode:
SQL> select log_mode from v$database;
LOG_MODE
----------
ARCHIVELOG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ARC_BKP
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
Switch the current archivelogs,
SQL> alter system archive log current;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ARC_BKP
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
Preparing Hot Backup for Database clone :-
- Create parameter file
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
spfile string /u01/app/oracle /product/19.0.0/dbhome_1/dbs/spfiledbwr.ora
SQL> create pfile from spfile;
File created.
Check number of tablespaces associated with database.
SQL> Select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
DBWRTBS
6 rows selected.
Ensure the tablespaces are ready for taking hot backup.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- --------- ----------
1 NOT ACTIVE 0 0
2 NOT ACTIVE 0 0
3 NOT ACTIVE 0 0
4 NOT ACTIVE 0 0
5 NOT ACTIVE 0 0
7 NOT ACTIVE 0 0
6 rows selected.
Put database in hot backup mode.
SQL> alter database begin backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- --------- ----------
1 ACTIVE 2642018 21-MAY-19 0
2 ACTIVE 2642018 21-MAY-19 0
3 ACTIVE 2642018 21-MAY-19 0
4 ACTIVE 2642018 21-MAY-19 0
5 ACTIVE 2642018 21-MAY-19 0
7 ACTIVE 2642018 21-MAY-19 0
6 rows selected.
Create appropriate directory structure in clone database.
[oracle@ram ~]$ mkdir -p CLONE/controlfile
[oracle@ram ~]$ mkdir -p CLONE/datafile
[oracle@ram ~]$ mkdir -p CLONE/onlinelog
Copy the tablespaces from source to target location.
[oracle@ram ~]$ cp -r /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_g91ppy2b_.dbf /u01/CLONE/datafile/
[oracle@ram ~]$ cp -r /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_g91pxgmq_.dbf /u01/CLONE/datafile/
[oracle@ram ~]$ cp -r /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_g91q0521_.dbf /u01/CLONE/datafile/
[oracle@ram ~]$ cp -r /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_g91q08bm_.dbf /u01/CLONE/datafile/
After copying datafiles to backup location,release database from Hot backup mode.
SQL> alter database end backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- --------- ----------
1 NOT ACTIVE 2642018 21-MAY-19 0
2 NOT ACTIVE 2642018 21-MAY-19 0
3 NOT ACTIVE 2642018 21-MAY-19 0
4 NOT ACTIVE 2642018 21-MAY-19 0
5 NOT ACTIVE 2642018 21-MAY-19 0
7 NOT ACTIVE 2642018 21-MAY-19 0
6 rows selected.
Switch the current log sequence and note down the oldest log sequence number.
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ARC_BKP
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
Backup the controlfile as trace.
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';
Database altered.
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.
[oracle@ram ARC_BKP]$ ls -lrth
-rw-r—–. 1 oracle oinstall 1.0K May 21 22:24 1_7_1008886911.dbf
-rw-r—–. 1 oracle oinstall 1.0K May 21 22:25 1_8_1008886911.dbf
-rw-r—–. 1 oracle oinstall 1.0K May 21 22:25 1_9_1008886911.dbf
-rw-r—–. 1 oracle oinstall 1.5K May 21 22:25 1_10_1008886911.dbf
-rw-r—–. 1 oracle oinstall 8.8M May 21 22:47 1_11_1008886911.dbf
Restoration using hot backup :-
- Edit the clone database parameter file and make necessary changes to the HCLONE database.
dbwr.__data_transfer_cache_size=0
dbwr.__db_cache_size=1258291200
dbwr.__inmemory_ext_roarea=0
dbwr.__inmemory_ext_rwarea=0
dbwr.__java_pool_size=16777216
dbwr.__large_pool_size=33554432
dbwr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dbwr.__pga_aggregate_target=603979776
dbwr.__sga_target=1778384896
dbwr.__shared_io_pool_size=83886080
dbwr.__shared_pool_size=369098752
dbwr.__streams_pool_size=0
dbwr.__unified_pga_pool_size=0
audit_file_dest='/u01/CLONE/adump'
audit_trail='db'
*.compatible='19.0.0'
*.control_files='/home/oracle/control.sql'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
db_name='clone'
*.db_recovery_file_dest_size=8256m
*.db_recovery_file_dest=''
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbwrXDB)'
*.log_archive_dest='/u01/ARC_BKP'
*.open_cursors=300
Startup database in NOMOUNT stage using target PFILE.
[oracle@ram ~]$ export ORACLE_SID=clone
[oracle@ram ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 21 23:08:20 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/u01/CLONE/initclone.ora' nomount;
ORACLE instance started.
Total System Global Area 1778381832 bytes
Fixed Size 8897544 bytes
Variable Size 536870912 bytes
Database Buffers 1224736768 bytes
Redo Buffers 7876608 bytes
- Create the control file trace for the clone database using the trace control file and specify the appropriate paths for redolog and datafiles
Note: Change the “REUSE” parameter to “SET” and RESETLOGS option.
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/CLONE/onlinelog/o1_mf_1_gg8c57c4_.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/u01/CLONE/onlinelog/o1_mf_2_gg8c57dm_.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/u01/CLONE/onlinelog/o1_mf_3_gg8c5f99_.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/CLONE/datafile/o1_mf_system_g91ppy2b_.dbf',
'/u01/CLONE/datafile/o1_mf_sysaux_g91pxgmq_.dbf',
'/u01/CLONE/datafile/o1_mf_undotbs1_g91q0521_.dbf',
'/u01/CLONE/datafile/o1_mf_users_g91q08bm_.dbf'
CHARACTER SET AL32UTF8
;
SQL> @/u01/CLONE/control.sql;
Control file created.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Recover the database using backup controlfile option.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2642018 generated at 05/21/2019 22:32:22 needed for thread 1
ORA-00289: suggestion : /u01/ARC_BKP/1_11_1008886911.dbf
ORA-00280: change 2642018 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Open database using resetlogs option :
SQL> alter database open resetlogs;
Database altered.
Check the database name and status :
SQL> select database_name, open_mode from v$database;
DATABASE_NAME OPEN_MODE
-------------------- -------------------------
CLONE READ WRITE
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba