Oracle Database Cloning using Backup sets

Development environment required copy of production database, below are the steps for cloning

High Level Steps

A. Target Side
     1. Prepare Target Environment for Oracle Database cloning
     2. Install Oracle binaries same as source environment
     3. Create necessary directory structure for target database

B. Source Side
     1. Create pfile from source database and transfer to target system
     2. Full backup of database and transfer to target system

C. Target Side
     1. Verify all backup pieces copied to target
     2. Modify pfile as per the target environment
     3. Start the target database in NMOUNT using modified pfile
     4. Connect to RMAN utility
                clone database using “duplicate database to <CloneDB Name>  backup location”.
     5. Check the database status, datafile, redologs, tempfile

Create necessary directory structure at target

mkdir -p /u01/app/oracle/oradata/orclcln
mkdir -p /home/oracle/DILIP/oradata/orclcln/ARCHIVELOG
mkdir -p /u01/app/oracle/admin/orclcln/adump
mkdir -p /u01/app/oracle/admin/orclcln/adump

Take Backup of source database

RMAN_FULL_TEST.sh

#RMAN Full backup Scripts

#!/bin/ksh

ORACLE_HOSTNAME=server1.example.com; export ORACLE_HOSTNAME

ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=oracle; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

mkdir /home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"

test=/home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"

rman target=/ << EOF

RUN {

  ALLOCATE CHANNEL ch1 TYPE

    DISK FORMAT '$test/%d_DB_%u_%s_%p';

  BACKUP DATABASE CURRENT CONTROLFILE SPFILE PLUS ARCHIVELOG;

  DELETE FORCE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;

  DELETE FORCE NOPROMPT OBSOLETE;

  RELEASE CHANNEL ch1;

}

EXIT;

EOF

Check the backup files created

ls -lrt /home/oracle/TEST_2016/ORACLE_06-02-2020

total 1096672

-rw-r----- 1 oracle oinstall   2963456 Feb  6 20:14 ORACLE_DB_tqunskku_17338_1

-rw-r----- 1 oracle oinstall 559357952 Feb  6 20:15 ORACLE_DB_trunskl0_17339_1

-rw-r----- 1 oracle oinstall   1523712 Feb  6 20:16 ORACLE_DB_tsunsknv_17340_1

-rw-r----- 1 oracle oinstall     98304 Feb  6 20:16 ORACLE_DB_ttunsko1_17341_1

-rw-r----- 1 oracle oinstall     14848 Feb  6 20:16 ORACLE_DB_tuunsko2_17342_1

Now transfer the backup files from source to target system using scp command

scp /home/oracle/TEST_2016/ORACLE_06-02-2020/* oracle@192.168.5.10:/home/oracle/TEST_2016/ORACLE_06-02-2020

oracle@192.168.5.10's password:

ORACLE_DB_tqunskku_17338_1                           100% 2894KB   2.8MB/s   00:00

ORACLE_DB_trunskl0_17339_1                             100%  533MB 133.4MB/s   00:04

ORACLE_DB_tsunsknv_17340_1                            100% 1488KB   1.5MB/s   00:00

ORACLE_DB_ttunsko1_17341_1                            100%   96KB  96.0KB/s   00:00

ORACLE_DB_tuunsko2_17342_1                           100%   15KB  14.5KB/s   00:00

Modify pfile as per the target environment

cat /home/oracle/DILIP/oradata/orclcln/PFILE_ORCLCLN_NEW.ora'

orclcln.__db_cache_size=301989888

orclcln.__java_pool_size=33554432

orclcln.__large_pool_size=16777216

orclcln.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orclcln.__pga_aggregate_target=553648128

orclcln.__sga_target=1056964608

orclcln.__shared_io_pool_size=0

orclcln.__shared_pool_size=654311424

orclcln.__streams_pool_size=33554432

*.audit_file_dest='/u01/app/oracle/admin/orclcln/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orclcln/control01.ctl','/u01/app/oracle/oradata/orclcln/control02.ctl'#Restore Controlfile

*.db_file_name_convert='/u01/app/oracle/oradata/oracle/','/u01/app/oracle/oradata/orclcln/'

*.log_file_name_convert='/u01/app/oracle/oradata/oracle/','/u01/app/oracle/oradata/orclcln/'

*.db_block_size=8192

*.db_domain=''

*.db_name='orclcln'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCLCLN'

*.db_recovery_file_dest_size=8589934592

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclclnXDB)'

*.log_archive_dest_1='LOCATION=/home/oracle/DILIP/oradata/orclcln/ARCHIVELOG'

*.log_archive_dest_10=''

*.memory_target=1607467008

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS'

set environment variable and start the database in nomount mode

export ORACLE_SID orclcln
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
sqlplus / as sysdba
startup nomount pfile='/home/oracle/DILIP/oradata/orclcln/PFILE_ORCLCLN_NEW.ora';

connect to rman utility and clone the database using backup location

$ORACLE_HOME/bin/rman auxiliary '/'

run
{
SET NEWNAME FOR DATABASE  TO '/u01/app/oracle/oradata/orclcln/%b';
duplicate database to orclcln  backup location '/home/oracle/TEST_2016/ORACLE_06-02-2020' nofilenamecheck;
}