Backup Based RMAN Duplicate Database

Backup Based RMAN Duplicate Database

Contents
______________________________________________________________________________________________________

1. Overview
2. Environment
3. Backup Source Database
4. Create PFILE from source db and Edit
5. Create required Directories
6. Copy Password for Clone Database
7. Add oratab entry
8. Startup Nomount TESTARC
9. Run RMAN Duplicate
10. Verify DB ID for Clone DB (TESTARC)
11. Add TNS entry
12. Verify connectivity
______________________________________________________________________________________________________


1. Overview

++ Duplicate a database from its backups without connecting to source database.

++ This technique is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. 

++ In duplication without a TARGET connection, the source database is unaffected by the duplication.

++ RMAN duplication, we will connect rman to the auxiliary instance and run duplicate command.

++ In this Demo we are doing RMAN Cloning on same server.

++ Please plan Hardware requirement such as Storage,CPU,RAM before perform duplicate. 


2. Environment

Hostname			: rac1.rajasekhar.com
Source DB Name		        : TEST
Duplicate DB Name	        : TESTARC
Version				: 11.2.0.4
Filesystem			: Normal
Non-RAC				: Single Instance
Oracle Home			: /u01/app/oracle/product/11.2.0.4


3. Backup Source Database

SQL> select name,open_mode,dbid from v$database;

NAME      OPEN_MODE                  DBID
--------- -------------------- ----------
TEST      READ WRITE           2289714844 <----

SQL>

[oracle@rac1 ~]$ cat rmanclone.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=TEST
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF
connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup database format '/u02/oracle/backup/database_%d_%u_%s';
release channel t1;
release channel t2;
release channel t3;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
allocate channel a3 type disk;
backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s';
release channel a1;
release channel a2;
release channel a3;
}
run {
allocate channel c1 type disk;
backup current controlfile format '/u02/oracle/backup/Control_%d_%u_%s';
release channel c1;
}
exit;
EOF

[oracle@rac1 ~]$

[oracle@rac1 ~]$ chmod 775 rmanclone.sh
[oracle@rac1 ~]$ nohup ./rmanclone.sh &
[oracle@rac1 ~]$ jobs -l
[1]+ 25183 Running                 nohup ./rmanclone.sh &
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ disown

Output:

[oracle@rac1 ~]$ cat clone_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 9 07:35:48 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to target database: TEST (DBID=2289714844)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=34 device type=DISK

allocated channel: t2
channel t2: SID=156 device type=DISK

allocated channel: t3
channel t3: SID=33 device type=DISK

Starting backup at 09-NOV-18
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/TEST/system01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/TEST/users01.dbf
channel t1: starting piece 1 at 09-NOV-18
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u02/oracle/oradata/TEST/sysaux01.dbf
input datafile file number=00003 name=/u02/oracle/oradata/TEST/undotbs01.dbf
channel t2: starting piece 1 at 09-NOV-18
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current control file in backup set
channel t3: starting piece 1 at 09-NOV-18
channel t3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_0athp2um_10 tag=TAG20181109T073550 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel t3: starting piece 1 at 09-NOV-18
channel t3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_0bthp2uo_11 tag=TAG20181109T073550 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_08thp2um_8 tag=TAG20181109T073550 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:27
channel t2: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_09thp2um_9 tag=TAG20181109T073550 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:27
Finished backup at 09-NOV-18

released channel: t1

released channel: t2

released channel: t3

RMAN>
sql statement: alter system archive log current

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: SID=34 device type=DISK

allocated channel: a2
channel a2: SID=156 device type=DISK

allocated channel: a3
channel a3: SID=33 device type=DISK

Starting backup at 09-NOV-18
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=991586176
input archived log thread=1 sequence=4 RECID=2 STAMP=991586178
input archived log thread=1 sequence=5 RECID=3 STAMP=991586190
input archived log thread=1 sequence=6 RECID=4 STAMP=991590072
channel a1: starting piece 1 at 09-NOV-18
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=5 STAMP=991590214
input archived log thread=1 sequence=8 RECID=6 STAMP=991590217
input archived log thread=1 sequence=9 RECID=7 STAMP=991590220
input archived log thread=1 sequence=10 RECID=9 STAMP=991590841
input archived log thread=1 sequence=11 RECID=14 STAMP=991590922
input archived log thread=1 sequence=12 RECID=16 STAMP=991590926
input archived log thread=1 sequence=13 RECID=18 STAMP=991590929
channel a2: starting piece 1 at 09-NOV-18
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=20 STAMP=991599494
input archived log thread=1 sequence=15 RECID=21 STAMP=991599606
input archived log thread=1 sequence=16 RECID=24 STAMP=991717144
input archived log thread=1 sequence=17 RECID=26 STAMP=991717147
input archived log thread=1 sequence=18 RECID=28 STAMP=991717527
input archived log thread=1 sequence=19 RECID=30 STAMP=991717529
input archived log thread=1 sequence=20 RECID=32 STAMP=991717532
channel a3: starting piece 1 at 09-NOV-18
channel a2: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0dthp2vj_13 tag=TAG20181109T073618 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:01
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=35 STAMP=991720817
input archived log thread=1 sequence=22 RECID=37 STAMP=991726578
channel a2: starting piece 1 at 09-NOV-18
channel a3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0ethp2vj_14 tag=TAG20181109T073618 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:01
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=38 STAMP=991726578
channel a3: starting piece 1 at 09-NOV-18
channel a1: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0cthp2vj_12 tag=TAG20181109T073618 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
channel a3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0gthp2vk_16 tag=TAG20181109T073618 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:01
channel a2: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0fthp2vk_15 tag=TAG20181109T073618 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:03
Finished backup at 09-NOV-18

released channel: a1

released channel: a2

released channel: a3

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=34 device type=DISK

Starting backup at 09-NOV-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 09-NOV-18
channel c1: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/Control_TEST_0hthp2vo_17 tag=TAG20181109T073624 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-NOV-18

released channel: c1

RMAN>

Recovery Manager complete.
[oracle@rac1 ~]$

-- Verify backup 
[oracle@rac1 ~]$ cd /u02/oracle/backup/
[oracle@rac1 backup]$ ls -ltr
total 1170168
-rw-r-----. 1 oracle dba   9928704 Nov  9 07:35 database_TEST_0athp2um_10
-rw-r-----. 1 oracle dba     98304 Nov  9 07:35 database_TEST_0bthp2uo_11
-rw-r-----. 1 oracle dba 381927424 Nov  9 07:36 database_TEST_09thp2um_9
-rw-r-----. 1 oracle dba 671031296 Nov  9 07:36 database_TEST_08thp2um_8
-rw-r-----. 1 oracle dba    475136 Nov  9 07:36 arch_TEST_0dthp2vj_13
-rw-r-----. 1 oracle dba  34190848 Nov  9 07:36 arch_TEST_0ethp2vj_14
-rw-r-----. 1 oracle dba  44929024 Nov  9 07:36 arch_TEST_0cthp2vj_12
-rw-r-----. 1 oracle dba      2560 Nov  9 07:36 arch_TEST_0gthp2vk_16
-rw-r-----. 1 oracle dba  45725184 Nov  9 07:36 arch_TEST_0fthp2vk_15
-rw-r-----. 1 oracle dba   9928704 Nov  9 07:36 Control_TEST_0hthp2vo_17
[oracle@rac1 backup]$

 


4. Create PFILE from source db and Edit

SQL> create pfile='/home/oracle/initTESTARC.ora' from spfile;

File created.

SQL>

[oracle@rac1 ~]$ cat initTESTARC.ora

TESTARC.__db_cache_size=385875968
TESTARC.__java_pool_size=16777216
TESTARC.__large_pool_size=33554432
TESTARC.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTARC.__pga_aggregate_target=620756992
TESTARC.__sga_target=620756992
TESTARC.__shared_io_pool_size=0
TESTARC.__shared_pool_size=167772160
TESTARC.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TESTARC/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oracle/oradata/TESTARC/control01.ctl','/u02/oracle/archive/fast_recovery_area/TESTARC/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u02/oracle/oradata/TEST','/u02/oracle/oradata/TESTARC'
*.db_name='TESTARC'
*.db_recovery_file_dest='/u02/oracle/TESTARC/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='TESTARC'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTARCXDB)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/TESTARC'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u02/oracle/oradata/TEST','/u02/oracle/oradata/TESTARC'
*.memory_target=1231028224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac1 ~]$


5. Create required Directories

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/TESTARC/adump
[oracle@rac1 ~]$ mkdir -p /u02/oracle/oradata/TESTARC
[oracle@rac1 ~]$ mkdir -p /u02/oracle/TESTARC/fast_recovery_area
[oracle@rac1 ~]$ mkdir -p /u02/oracle/archive/TESTARC
[oracle@rac1 ~]$


6. Copy Password for Clone Database

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0.4/dbs/
[oracle@rac1 dbs]$ ls -ltr orapwTEST
-rw-r-----. 1 oracle dba 1536 Nov  7 11:40 orapwTEST
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cp orapwTEST orapwTESTARC
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr orapwTEST*
-rw-r-----. 1 oracle dba 1536 Nov  7 11:40 orapwTEST
-rw-r-----. 1 oracle dba 1536 Nov  9 07:58 orapwTESTARC
[oracle@rac1 dbs]$


7. Add oratab entry

[oracle@rac1 ~]$ echo "TESTARC:/u01/app/oracle/product/11.2.0.4:N" >> /etc/oratab
[oracle@rac1 ~]$ cat /etc/oratab | grep -i TESTARC
TESTARC:/u01/app/oracle/product/11.2.0.4:N  <-----
[oracle@rac1 ~]$


8. Startup Nomount TESTARC

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [TEST] ? TESTARC
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 9 08:11:25 2018

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

Connected to an idle instance.

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

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             872415248 bytes
Database Buffers          352321536 bytes
Redo Buffers                8970240 bytes
SQL>
SQL> create spfile from pfile='/home/oracle/initTESTARC.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             872415248 bytes
Database Buffers          352321536 bytes
Redo Buffers                8970240 bytes
SQL>
SQL> !ps -ef | grep pmon
oracle   18200     1  0 04:58 ?        00:00:02 ora_pmon_TEST
oracle   26741     1  0 08:16 ?        00:00:00 ora_pmon_TESTARC  <----- oracle 26781 26652 0 08:16 pts/0 00:00:00 /bin/bash -c ps -ef | grep pmon oracle 26783 26781 0 08:16 pts/0 00:00:00 grep pmon SQL>


9. Run RMAN Duplicate

[oracle@rac1 ~]$ cat clone_TESTARC_db.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=TESTARC
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_testarc_db.log
/u01/app/oracle/product/11.2.0.4/bin/rman msglog=${LOG_FILE} <<EOF
connect auxiliary /
run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
duplicate target database to TESTARC backup location '/u02/oracle/backup';
}
exit
EOF
[oracle@rac1 ~]$
[oracle@rac1 ~]$ chmod 775 clone_TESTARC_db.sh
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [TESTARC] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ nohup ./clone_TESTARC_db.sh &
[1] 28021
nohup: ignoring input and appending output to `nohup.out'
[oracle@rac1 ~]$
[oracle@rac1 ~]$ jobs -l
[1]+ 28021 Running                 nohup ./clone_TESTARC_db.sh &
[oracle@rac1 ~]$
[oracle@rac1 ~]$ disown
[oracle@rac1 ~]$

Output:

[oracle@rac1 ~]$ cat clone_testarc_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 9 08:39:08 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to auxiliary database: TESTARC (not mounted)

RMAN> 2> 3> 4> 5> 6>
allocated channel: t1
channel t1: SID=134 device type=DISK

allocated channel: t2
channel t2: SID=10 device type=DISK

allocated channel: t3
channel t3: SID=135 device type=DISK

Starting Duplicate Db at 09-NOV-18

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TESTARC'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u02/oracle/backup/Control_TEST_0hthp2vo_17';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TESTARC'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1235959808 bytes

Fixed Size                     2252784 bytes
Variable Size                872415248 bytes
Database Buffers             352321536 bytes
Redo Buffers                   8970240 bytes
allocated channel: t1
channel t1: SID=133 device type=DISK
allocated channel: t2
channel t2: SID=10 device type=DISK
allocated channel: t3
channel t3: SID=134 device type=DISK

Starting restore at 09-NOV-18

channel t2: skipped, AUTOBACKUP already found
channel t3: skipped, AUTOBACKUP already found
channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:08
output file name=/u02/oracle/oradata/TESTARC/control01.ctl
output file name=/u02/oracle/archive/fast_recovery_area/TESTARC/control02.ctl
Finished restore at 09-NOV-18

database mounted

contents of Memory Script:
{
   set until scn  980479;
   set newname for datafile  1 to
 "/u02/oracle/oradata/TESTARC/system01.dbf";
   set newname for datafile  2 to
 "/u02/oracle/oradata/TESTARC/sysaux01.dbf";
   set newname for datafile  3 to
 "/u02/oracle/oradata/TESTARC/undotbs01.dbf";
   set newname for datafile  4 to
 "/u02/oracle/oradata/TESTARC/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-NOV-18

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /u02/oracle/oradata/TESTARC/system01.dbf
channel t1: restoring datafile 00004 to /u02/oracle/oradata/TESTARC/users01.dbf
channel t1: reading from backup piece /u02/oracle/backup/database_TEST_08thp2um_8
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00002 to /u02/oracle/oradata/TESTARC/sysaux01.dbf
channel t2: restoring datafile 00003 to /u02/oracle/oradata/TESTARC/undotbs01.dbf
channel t2: reading from backup piece /u02/oracle/backup/database_TEST_09thp2um_9
channel t2: piece handle=/u02/oracle/backup/database_TEST_09thp2um_9 tag=TAG20181109T073550
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:25
channel t1: piece handle=/u02/oracle/backup/database_TEST_08thp2um_8 tag=TAG20181109T073550
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:35
Finished restore at 09-NOV-18

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/users01.dbf

contents of Memory Script:
{
   set until scn  980479;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 09-NOV-18

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=22
channel t1: reading from backup piece /u02/oracle/backup/arch_TEST_0fthp2vk_15
channel t2: starting archived log restore to default destination
channel t2: restoring archived log
archived log thread=1 sequence=23
channel t2: reading from backup piece /u02/oracle/backup/arch_TEST_0gthp2vk_16
channel t1: piece handle=/u02/oracle/backup/arch_TEST_0fthp2vk_15 tag=TAG20181109T073618
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oracle/archive/TESTARC/1_22_991568414.dbf thread=1 sequence=22
channel clone_default: deleting archived log(s)
archived log file name=/u02/oracle/archive/TESTARC/1_22_991568414.dbf RECID=2 STAMP=991730408
channel t2: piece handle=/u02/oracle/backup/arch_TEST_0gthp2vk_16 tag=TAG20181109T073618
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oracle/archive/TESTARC/1_23_991568414.dbf thread=1 sequence=23
channel clone_default: deleting archived log(s)
archived log file name=/u02/oracle/archive/TESTARC/1_23_991568414.dbf RECID=1 STAMP=991730408
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-NOV-18
Oracle instance started

Total System Global Area    1235959808 bytes

Fixed Size                     2252784 bytes
Variable Size                889192464 bytes
Database Buffers             335544320 bytes
Redo Buffers                   8970240 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TESTARC'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TESTARC'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1235959808 bytes

Fixed Size                     2252784 bytes
Variable Size                889192464 bytes
Database Buffers             335544320 bytes
Redo Buffers                   8970240 bytes
allocated channel: t1
channel t1: SID=133 device type=DISK
allocated channel: t2
channel t2: SID=10 device type=DISK
allocated channel: t3
channel t3: SID=134 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTARC" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u02/oracle/oradata/TESTARC/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u02/oracle/oradata/TESTARC/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u02/oracle/oradata/TESTARC/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u02/oracle/oradata/TESTARC/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u02/oracle/oradata/TESTARC/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u02/oracle/oradata/TESTARC/sysaux01.dbf",
 "/u02/oracle/oradata/TESTARC/undotbs01.dbf",
 "/u02/oracle/oradata/TESTARC/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oracle/oradata/TESTARC/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/oracle/oradata/TESTARC/sysaux01.dbf RECID=1 STAMP=991730440
cataloged datafile copy
datafile copy file name=/u02/oracle/oradata/TESTARC/undotbs01.dbf RECID=2 STAMP=991730440
cataloged datafile copy
datafile copy file name=/u02/oracle/oradata/TESTARC/users01.dbf RECID=3 STAMP=991730440

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=991730440 file name=/u02/oracle/oradata/TESTARC/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=991730440 file name=/u02/oracle/oradata/TESTARC/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=991730440 file name=/u02/oracle/oradata/TESTARC/users01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database force logging

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 09-NOV-18
released channel: t1
released channel: t2
released channel: t3

RMAN>

Recovery Manager complete.
[oracle@rac1 ~]$


10. Verify DB ID for Clone DB (TESTARC)

SQL> select name,open_mode,dbid from v$database;

NAME      OPEN_MODE                  DBID
--------- -------------------- ----------
TESTARC   READ WRITE           3854160199 <----

SQL>


11. Add TNS entry

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTARC   READ WRITE

++ We are using non default port number 1621, hence added local_listener

SQL> alter system set local_listener='LISTENER_11G' scope=both;  

System altered.

SQL> 

TESTARC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTARC)
    )
  )


12. Verify connectivity

[oracle@rac1 ~]$ sqlplus sys/SYS@TESTARC as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 9 10:37:39 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com

6 thoughts on “Backup Based RMAN Duplicate Database

  1. HI Rajasekhar,

    Thanks for notes but when i duplicating i need target folder specially datafile ..

    i don’t where i am mistake .. spfile or any other parameter can i guide ..

    BR,

    Abdul Taher

    1. If you want to duplicate to specific target directory, i have not tried before you can try like this.

      Eg:
      RMAN> run
      {
      set newname for datafile 1 to /u02/data01.dbf’;
      duplicate database;
      }

      Thanks,
      Rajasekhar

Leave a Reply

Your email address will not be published. Required fields are marked *