Oracle 12c Database Cloning Using Hot Backup

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 datafiles, 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 controlfile option.
  • Open the database with resetlogs 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          No Archive Mode
Automatic archival         Enabled
Archive destination        /u01/app/oracle/product/12.1.0/db_1/dbs/arch
Oldest online log sequence 186
Current log sequence       187
  • 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/archivelog
Oldest online log sequence     187
Next log sequence to archive   188
Current log sequence           188

Preparing Hot Backup for Database clone :-

  • Create parameter file
SQL> show parameter spfile;

NAME   TYPE	VALUE
------ -------- ------------------------------
spfile string   +DATA/orcl11g/spfileorcl11g.ora

SQL> create pfile='/home/oracle/initHCLONE.ora' 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
UNDOTBS2

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
  • 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             4282518    25-JAN-19 0
2          ACTIVE             4282518    25-JAN-19 0
3          ACTIVE             4282518    25-JAN-19 0
4          ACTIVE             4282518    25-JAN-19 0
5          ACTIVE             4282518    25-JAN-19 0
  • Create appropriate directory structure in clone database.
asmcmd> mkdir  +DATA/HCLONE/CONTROLFILE
asmcmd> mkdir  +DATA/HCLONE/DATAFILE
asmcmd> mkdir  +DATA/HCLONE/ONLINELOG

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/HCLONE

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/admin/HCLONE

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/admin/HCLONE/adump
  • Copy the tablespaces from source to target location.
ASMCMD> cd ORCL11G/DATAFILE

ASMCMD> cp SYSTEM.256.995570219 +DATA/HCLONE/DATAFILE/system.dbf
copying +DATA/ORCL11G/DATAFILE/SYSTEM.256.995570219 -> +DATA/HCLONE/DATAFILE/system.dbf

ASMCMD> cp SYSAUX.257.995570219 +DATA/HCLONE/DATAFILE/sysaux.dbf
copying +DATA/ORCL11G/DATAFILE/SYSAUX.257.995570219 -> +DATA/HCLONE/DATAFILE/sysaux.dbf

ASMCMD> cp UNDOTBS1.258.995570219 +DATA/HCLONE/DATAFILE/undotbs1.dbf
copying +DATA/ORCL11G/DATAFILE/UNDOTBS1.258.995570219 -> +DATA/HCLONE/DATAFILE/undotbs1.dbf

ASMCMD> cp UNDOTBS2.264.995570411 +DATA/HCLONE/DATAFILE/undotbs2.dbf
copying +DATA/ORCL11G/DATAFILE/UNDOTBS2.264.995570411 -> +DATA/HCLONE/DATAFILE/undotbs2.dbf

ASMCMD> cp USERS.259.995570219 +DATA/HCLONE/DATAFILE/users.dbf
copying +DATA/ORCL11G/DATAFILE/USERS.259.995570219 -> +DATA/HCLONE/DATAFILE/users.dbf
  • 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 4282518 25-JAN-19 0
2          NOT ACTIVE 4282518 25-JAN-19 0
3          NOT ACTIVE 4282518 25-JAN-19 0
4          NOT ACTIVE 4282518 25-JAN-19 0
5          NOT ACTIVE 4282518 25-JAN-19 0
  • 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/archivelog
Oldest online log sequence 188
Next log sequence to archive 189
Current log sequence 189
  • 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@racpb1 archivelog]$ ls -lrt
total 6636

-rw-r----- 1 oracle oinstall 4622848 Jan 25 00:39 1_187_995570300.dbf
-rw-r----- 1 oracle oinstall 2160128 Jan 25 01:19 1_188_995570300.dbf

Restoration using hot backup :-

  • Edit the clone database parameter file and make necessary changes to the HCLONE database.
HCLONE.__db_cache_size=436207616
HCLONE.__java_pool_size=4194304
HCLONE.__large_pool_size=8388608
HCLONE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
HCLONE.__pga_aggregate_target=373293056
HCLONE.__sga_target=700448768
HCLONE.__shared_io_pool_size=0
HCLONE.__shared_pool_size=243269632
HCLONE.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/HCLONE/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/HCLONE/controlfile/current.260.995570299'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='localdomain.com'
*.db_name='HCLONE'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HCLONEXDB)'
*.event=''
*.log_archive_dest_1='LOCATION=/u01/archivelog'
*.memory_target=1073741824
*.open_cursors=300
*.processes=300
*.remote_listener='racsn:1521'
*.remote_login_passwordfile='EXCLUSIVE'
HCLONE.undo_tablespace='UNDOTBS'
  • Startup database in NOMOUNT stage using target PFILE.
[oracle@racpb1 ~]$ export ORACLE_SID=HCLONE
[oracle@racpb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 25 01:40:01 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup pfile='/home/oracle/initHCLONE.ora' nomount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 675283048 bytes
Database Buffers 390070272 bytes
Redo Buffers 5455872 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 "HCLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/HCLONE/onlinelog/group_1'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/HCLONE/onlinelog/group_2'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/HCLONE/onlinelog/group_3'  SIZE 50M BLOCKSIZE 512,
  GROUP 4 '+DATA/HCLONE/onlinelog/group_4'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/HCLONE/datafile/system.dbf',
  '+DATA/HCLONE/datafile/sysaux.dbf',
  '+DATA/HCLONE/datafile/undotbs1.dbf',
  '+DATA/HCLONE/datafile/users.dbf',
  '+DATA/HCLONE/datafile/undotbs2.dbf'
CHARACTER SET AL32UTF8
;

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 4283965 generated at 01/25/2019 01:19:16 needed for thread 1
ORA-00289: suggestion : /u01/archivelog/1_189_995570300.dbf
ORA-00280: change 4283965 for thread 1 is in sequence #189


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
------------- ------------
HCLONE        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
12c databasecloning

You may also like

In 11g Database

Cloning Oracle 12c Database Using Rman Backup Based Duplication

View Post

Next Post

Purging/Cleaning Sysaux Tablespace In Oracle

In 11g Database

Purging/Cleaning Sysaux Tablespace In Oracle

View Post

Get updated

Share via
Facebook
Twitter
LinkedIn
Mix
Email
Print
Copy Link
Powered by Social Snap
Copy link
CopyCopied
Powered by Social Snap