RMAN Backup Based Cloning in Oracle 11g | Karan Rajpoot

Introduction:
RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure. 

In this demonstration, I am using the "prod" as a Source database and "clone" as an Auxiliary database.

Prerequisites:
RMAN must be connected as AUXILIARY to the instance of the duplicate database. The instance of the duplicate database is called the auxiliary instance. The auxiliary instance must be started with the NOMOUNT option.

High-Level steps of RMAN backup based cloning in Oracle 11g


Source Server: -

1) The database should be in Archive log mode.

2) Set the recovery parameters.

3) Set the datafile and logfile convert parameter.

4) Create Parameter file (pfile).

5) Take a fresh backup.

6) Create Password file.

7) Configure the Listener and Tnsnames files.

Target Server: -

1) Add the database entry in /etc/oratab file.

2) Configure the Listener and Tnsnames files.

3) Copy the parameter file, password file and backups from source to the target server.

4) Edit the pfile and make the database in the nomount stage.

5) Make the necessary directories paths.


Let's start the Practical to understand in a better way.

Source Server: -

Step 1) Check the database mode, It should be in Archive log.

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     71
Next log sequence to archive   73
Current log sequence        73

Step 2) Set the recovery Parameters. Below db_recovery_file_dest and db_recovery_file_dest_size has been set perfectly.


SQL> show parameter recover

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest      string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size      big integer 4182M
db_unrecoverable_scn_tracking      boolean TRUE
recovery_parallelism      integer 0

Step 3) Now set the datafile and logfile parameter, but before setting the parameters, check you datafile and logfiles locations.

Datafile location: - /u01/app/oracle/oradata/prod

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/users01.dbf
/u01/app/oracle/oradata/prod/undotbs01.dbf
/u01/app/oracle/oradata/prod/sysaux01.dbf
/u01/app/oracle/oradata/prod/system01.dbf

8 rows selected.

Log file location: - /u01/app/oracle/oradata/prod

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER      
3    ONLINE  /u01/app/oracle/oradata/prod/redo03.log      NO
2    ONLINE  /u01/app/oracle/oradata/prod/redo02.log      NO
1    ONLINE  /u01/app/oracle/oradata/prod/redo01.log

Setting the parameters for both the files: -

SQL> alter system set db_file_name_convert ='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/clone' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert ='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/clone' scope=spfile;

System altered.

Step 4) Create pfile from spfile.

SQL> create pfile from spfile;

File created.

Step 5) Now shut down and start the database again and check the status of datafile and logfile parameters.


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
Database mounted.
Database opened.

SQL> show parameter db_file_name_convert

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert      string /u01/app/oracle/oradata/prod,
/u01/app/oracle/oradata/clone

SQL> show parameter log_file_name_convert;

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert      string /u01/app/oracle/oradata/prod,
/u01/app/oracle/oradata/clone

As we can see from the above output, both the parameter are successfully set.


Step 6) Take a fresh backup plus archive log as well.


RMAN> backup database plus archivelog;


Starting backup at 17-FEB-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=26 STAMP=997740572
input archived log thread=1 sequence=29 RECID=27 STAMP=997744658
input archived log thread=1 sequence=30 RECID=28 STAMP=997744941
input archived log thread=1 sequence=31 RECID=29 STAMP=997745067
input archived log thread=1 sequence=32 RECID=33 STAMP=997745614
input archived log thread=1 sequence=33 RECID=36 STAMP=997746012
input archived log thread=1 sequence=34 RECID=38 STAMP=997746396
input archived log thread=1 sequence=35 RECID=40 STAMP=997746399
input archived log thread=1 sequence=36 RECID=42 STAMP=997746404
input archived log thread=1 sequence=37 RECID=44 STAMP=997747735
input archived log thread=1 sequence=38 RECID=45 STAMP=997748661
input archived log thread=1 sequence=39 RECID=47 STAMP=997749207
input archived log thread=1 sequence=40 RECID=48 STAMP=997749208
input archived log thread=1 sequence=41 RECID=49 STAMP=997749254
input archived log thread=1 sequence=42 RECID=50 STAMP=997749259
input archived log thread=1 sequence=43 RECID=51 STAMP=997749465
input archived log thread=1 sequence=44 RECID=52 STAMP=997749662
input archived log thread=1 sequence=45 RECID=54 STAMP=997749668
input archived log thread=1 sequence=46 RECID=57 STAMP=997749698
input archived log thread=1 sequence=47 RECID=59 STAMP=997749857
input archived log thread=1 sequence=48 RECID=60 STAMP=997908946
input archived log thread=1 sequence=49 RECID=61 STAMP=997909993
input archived log thread=1 sequence=50 RECID=62 STAMP=997910037
input archived log thread=1 sequence=51 RECID=63 STAMP=997910209
input archived log thread=1 sequence=52 RECID=64 STAMP=997913689
input archived log thread=1 sequence=53 RECID=65 STAMP=998335888
input archived log thread=1 sequence=54 RECID=66 STAMP=998338260

channel ORA_DISK_1: starting piece 1 at 17-FEB-19
channel ORA_DISK_1: finished piece 1 at 17-FEB-19
piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp tag=TAG20190217T001730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 17-FEB-19

Starting backup at 17-FEB-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-FEB-19
channel ORA_DISK_1: finished piece 1 at 17-FEB-19
piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp tag=TAG20190217T001807 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 17-FEB-19

Starting backup at 17-FEB-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=76 RECID=88 STAMP=1000426803
channel ORA_DISK_1: starting piece 1 at 17-FEB-19
channel ORA_DISK_1: finished piece 1 at 17-FEB-19
piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp tag=TAG20190217T002003 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-FEB-19

Starting Control File and SPFILE Autobackup at 17-FEB-19
piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-FEB-19


Step 7) Configure the Listener and Tnsnames to resolve the connection from source to auxiliary clone servers.

 Source listener file output: -

[oracle@prod admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod.radical.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod)
          (SID_NAME=prod)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=clone)
          (SID_NAME=clone)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )

 Source Tnsnames file output: -

[oracle@prod admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

CLONE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = clone)
      (UR = A)
    )


Target Server: - 

Step 1) Add the database entry in /etc/oratab file.

[oracle@dr ~]$ cat /etc/oratab

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating an ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should, "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
clone:/u01/app/oracle/product/11.2.0/db_1


Step 2) Configuration of Listener and Tnsnames files.

 Target listener file Output: -

[oracle@dr admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dr.radical.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod)
          (SID_NAME=prod)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=clone)
          (SID_NAME=clone)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
    )

Target Tnsnames file Output: -

[oracle@dr admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CLONE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = clone)
      (UR = A)
    )
  )

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

[oracle@dr admin]$ 

Step 3) Copy the parameter file, password file and backups from source to the target server.

Parameter file: -

[oracle@prod dbs]$ scp initprod.ora oracle@192.168.2.21:/u01/app/oracle/product/11.2.0/db_1/dbs/initclone.ora
oracle@192.168.2.21's password: 
initprod.ora                                                                                                                         100% 1393     1.4KB/s   00:00    
[oracle@prod dbs]$ 

Password file: -

[oracle@prod dbs]$ scp orapwprod oracle@192.168.2.21:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwclone
oracle@192.168.2.21's password: 
orapwprod                                                                                                                            100% 1536     1.5KB/s   00:00    
[oracle@prod dbs]$ 

Directory creation output: -

Note:- Create the backup directory in the target server side, It is the same as the source server.

[oracle@dr admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17
[oracle@dr admin]$

Backup pieces copy output: -

[oracle@prod 2019_02_17]$ scp /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/*.bkp oracle@192.168.2.21:/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/
oracle@192.168.2.21's password: 
o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp                                                                                         100%  436MB  11.2MB/s   00:39    
o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp                                                                                         100%   68KB  68.0KB/s   00:00    
o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp                                                                                         100% 1107MB   9.9MB/s   01:52    
[oracle@prod 2019_02_17]$ 

 Step 4) Edit the pfile, make necessary directories and then start the database in the nomount stage.

[oracle@dr dbs]$ mkdir -p /u01/app/oracle/admin/clone/adump
[oracle@dr dbs]$ mkdir -p /u01/app/oracle/oradata/clone
[oracle@dr dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area


SQL> startup nomount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
SQL> 

Now create spfile from pfile and then start the database again using spfile.

SQL> create spfile from pfile;

File created.

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


ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
SQL> 

SQL> show parameter spfile;

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile      string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileclone.ora


Now go to the Source server-side and log in from RMAN to connect source as well as Auxiliary database.

[oracle@prod 2019_02_17]$ rman target / auxiliary sys/sys@clone

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Feb 17 01:30:02 2019

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

connected to target database: PROD (DBID=407246072)
connected to auxiliary database: CLONE (not mounted)


RMAN> duplicate target database to "clone";

Starting Duplicate Db at 17-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''CLONE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes

Starting restore at 17-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp
ORA-19505: failed to identify file "/u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_09/o1_mf_s_999737268_g5vogwod_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_09/o1_mf_s_999737268_g5vogwod_.bkp tag=TAG20190209T004748
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/clone/control01.ctl
output file name=/u01/app/oracle/oradata/clone/control03.ctl
output file name=/u01/app/oracle/oradata/clone/control04.ctl
Finished restore at 17-FEB-19

database mounted

contents of Memory Script:
{
   set until scn  1619368;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/clone/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/clone/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/clone/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/clone/users01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/clone/goms01.dbf";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/clone/goms_data01.dbf";
   set newname for datafile  7 to 
 "/u01/app/oracle/oradata/clone/users03.dbf";
   set newname for datafile  8 to 
 "/u01/app/oracle/oradata/clone/recovery_catalog.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing the command: SET until clause

executing the command: SET NEWNAME

executing the command: SET NEWNAME

executing the command: SET NEWNAME

executing the command: SET NEWNAME

executing the command: SET NEWNAME

executing the command: SET NEWNAME

executing the command: SET NEWNAME

executing the command: SET NEWNAME

Starting restore at 17-FEB-19
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/clone/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/clone/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/clone/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/clone/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/clone/goms01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/clone/goms_data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/clone/users03.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/clone/recovery_catalog.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp tag=TAG20190217T001807
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 17-FEB-19

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

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/goms01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/goms_data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=18 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/users03.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/recovery_catalog.dbf

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

executing the command: SET until clause

Starting recover at 17-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=72
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=73
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=74
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=75
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp tag=TAG20190217T001730
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_72_g6jv0m02_.arc thread=1 sequence=72
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_72_g6jv0m02_.arc RECID=84 STAMP=1000431083
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_73_g6jv0n24_.arc thread=1 sequence=73
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_73_g6jv0n24_.arc RECID=85 STAMP=1000431084
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_74_g6jv0nmf_.arc thread=1 sequence=74
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_74_g6jv0nmf_.arc RECID=86 STAMP=1000431084
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_75_g6jv0nsv_.arc thread=1 sequence=75
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_75_g6jv0nsv_.arc RECID=87 STAMP=1000431084
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=76
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp tag=TAG20190217T002003
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_76_g6jv0xok_.arc thread=1 sequence=76
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_76_g6jv0xok_.arc RECID=88 STAMP=1000431093
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-FEB-19
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''CLONE'' 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 =  ''CLONE'' 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 an auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes
SQL statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/clone/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/clone/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/clone/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/clone/system01.dbf'
 CHARACTER SET WE8MSWIN1252


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

executing the command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/clone/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/sysaux01.dbf RECID=1 STAMP=1000431109
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/undotbs01.dbf RECID=2 STAMP=1000431109
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/users01.dbf RECID=3 STAMP=1000431109
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/goms01.dbf RECID=4 STAMP=1000431109
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/goms_data01.dbf RECID=5 STAMP=1000431109
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/users03.dbf RECID=6 STAMP=1000431109
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/recovery_catalog.dbf RECID=7 STAMP=1000431109

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/goms01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/goms_data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/users03.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/recovery_catalog.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 17-FEB-19

RMAN>

Now login to the Auxiliary database and check the status. It's up and running.

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

NAME   OPEN_MODE        DATABASE_ROLE
--------- -------------------- ----------------
CLONE   READ WRITE        PRIMARY


If this blog is helpful please like, comment below and let me know in which topic should I create a blog.

Thank you!!


















Comments

Popular posts from this blog

How to resolve "No protocol specified" error

How to resolve "TNS-12541 : TNS : no listener" error

Oracle "Redo log Groups addition/deletion" | Karan Rajpoot