Clone a Database without Catalog DB Using a Timestamp in RMAN

 

Cloning a database to a specific timestamp using RMAN (Recovery Manager) is a common practice for point-in-time recovery (PITR). 

Note:- Cloning without connecting CATALOG database 

Below is a step-by-step guide to achieve this:

Prerequisites

1. Backup Availability: Ensure you have an RMAN full backup and the required archive logs for the specified timestamp.
2. Sufficient Disk Space: Verify that there is enough storage for the cloned database.
3. Auxiliary Instance: Set up the auxiliary instance on the target server with the required initialization parameter file (pfile/spfile).
4. Oracle Net Configuration: Ensure the primary and auxiliary instances can communicate via Oracle Net.

Steps to Clone a Database Using a Timestamp in RMAN

1. Start the Auxiliary Instance in NOMOUNT Mode

$] export ORACLE_SID=SAMIK

$] cd $ORACLE_HOME/dbs

$] sqlplus / as sysdba

SQL> startup nomount;

2. Connect to RMAN (Target & Auxiliary) :-

[oracle@rac3 ~]$ rman target sys/welcome#1234@ORCL auxiliary /

3. Set the Time for PITR Identify the timestamp you want to recover to, in the format YYYY-MM-DD HH24:MI:SS. For example:

run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database to samik
SPFILE
set db_name='samik'
set CONTROL_FILES='/data1/oradata/samik/control.ctl'
set db_file_name_convert='/data2/oradata/','/data1/oradata/samik/'
set log_file_name_convert='/data2/oradata/','/data1/oradata/samik/'
NOFILENAMECHECK;
set UNTIL TIME "TO_DATE('2024-09-19 23:39:00', 'YYYY-MM-DD HH24:MI:SS')";
}

4.  Monitor the Cloning process:-

Monitor the Process RMAN will restore the necessary files, recover them to the specified timestamp, and open the database

[oracle@rac3 ~]$ rman target sys/welcome#1234@ORCL auxiliary /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Sep 20 00:56:10 2024

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

connected to target database: ORCL (DBID=1483248919)
connected to auxiliary database: SAMIK (not mounted)

RMAN>

RMAN>

RMAN>

RMAN> run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database to samik
SPFILE
set db_name='samik'
set CONTROL_FILES='/data1/oradata/samik/control.ctl'
set db_file_name_convert='/data2/oradata/','/data1/oradata/samik/'
set log_file_name_convert='/data2/oradata/','/data1/oradata/samik/'
NOFILENAMECHECK;
set UNTIL TIME "TO_DATE('2024-09-19 23:39:00', 'YYYY-MM-DD HH24:MI:SS')";
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=35 device type=DISK

allocated channel: c2
channel c2: SID=36 device type=DISK

allocated channel: c3
channel c3: SID=37 device type=DISK

allocated channel: c4
channel c4: SID=38 device type=DISK

Starting Duplicate Db at 20-SEP-24
current log archived

contents of Memory Script:
{
   set until scn  831620;
   restore clone spfile to  '/data1/app/oracle/product/12.2.0/dbhome_1/dbs/spfilesamik.ora';
   sql clone "alter system set spfile= ''/data1/app/oracle/product/12.2.0/dbhome_1/dbs/spfilesamik.ora''";
}
executing Memory Script

executing command: SET until clause

Starting restore at 20-SEP-24

WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel c1: starting datafile backup set restore
channel c1: restoring SPFILE
output file name=/data1/app/oracle/product/12.2.0/dbhome_1/dbs/spfilesamik.ora
channel c1: reading from backup piece /backup/rman_backup_orcl/c-1483248919-20240919-00
channel c1: piece handle=/backup/rman_backup_orcl/c-1483248919-20240919-00 tag=TAG20240919T234045
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
Finished restore at 20-SEP-24

sql statement: alter system set spfile= ''/data1/app/oracle/product/12.2.0/dbhome_1/dbs/spfilesamik.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''SAMIK'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  db_name =
 ''samik'' comment=
 '''' scope=spfile";
   sql clone "alter system set  CONTROL_FILES =
 ''/data1/oradata/samik/control.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/data2/oradata/'', ''/data1/oradata/samik/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/data2/oradata/'', ''/data1/oradata/samik/'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''SAMIK'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  db_name =  ''samik'' comment= '''' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''/data1/oradata/samik/control.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/data2/oradata/'', ''/data1/oradata/samik/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/data2/oradata/'', ''/data1/oradata/samik/'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1342177280 bytes

Fixed Size                     8620464 bytes
Variable Size                352323152 bytes
Database Buffers             973078528 bytes
Redo Buffers                   8155136 bytes
allocated channel: c1
channel c1: SID=34 device type=DISK
allocated channel: c2
channel c2: SID=35 device type=DISK
allocated channel: c3
channel c3: SID=36 device type=DISK
allocated channel: c4
channel c4: SID=37 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''samik'' 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 =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1342177280 bytes

Fixed Size                     8620464 bytes
Variable Size                352323152 bytes
Database Buffers             973078528 bytes
Redo Buffers                   8155136 bytes
allocated channel: c1
channel c1: SID=34 device type=DISK
allocated channel: c2
channel c2: SID=35 device type=DISK
allocated channel: c3
channel c3: SID=36 device type=DISK
allocated channel: c4
channel c4: SID=37 device type=DISK

Starting restore at 20-SEP-24

channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece /backup/rman_backup_orcl/c-1483248919-20240919-00
channel c1: piece handle=/backup/rman_backup_orcl/c-1483248919-20240919-00 tag=TAG20240919T234045
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
output file name=/data1/oradata/samik/control.ctl
Finished restore at 20-SEP-24

database mounted

contents of Memory Script:
{
   set until scn  831620;
   set newname for datafile  1 to
 "/data1/oradata/samik/system01.dbf";
   set newname for datafile  2 to
 "/data1/oradata/samik/sysaux01.dbf";
   set newname for datafile  3 to
 "/data1/oradata/samik/undotbs01.dbf";
   set newname for datafile  4 to
 "/data1/oradata/samik/users01.dbf";
   set newname for datafile  5 to
 "/data1/oradata/samik/usertbs01.dbf";
   set newname for datafile  6 to
 "/data1/oradata/samik/sam_ts1.dbf";
   set newname for datafile  7 to
 "/data1/oradata/samik/sam_idx1.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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-SEP-24

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /data1/oradata/samik/system01.dbf
channel c1: restoring datafile 00002 to /data1/oradata/samik/sysaux01.dbf
channel c1: restoring datafile 00003 to /data1/oradata/samik/undotbs01.dbf
channel c1: restoring datafile 00004 to /data1/oradata/samik/users01.dbf
channel c1: restoring datafile 00005 to /data1/oradata/samik/usertbs01.dbf
channel c1: restoring datafile 00006 to /data1/oradata/samik/sam_ts1.dbf
channel c1: restoring datafile 00007 to /data1/oradata/samik/sam_idx1.dbf
channel c1: reading from backup piece /backup/rman_backup_orcl/orcl_ORCL_T987291507_S68_P1
channel c1: piece handle=/backup/rman_backup_orcl/orcl_ORCL_T987291507_S68_P1 tag=TAG20240919T233827
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:02:29
Finished restore at 20-SEP-24

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

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=987296392 file name=/data1/oradata/samik/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=987296392 file name=/data1/oradata/samik/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=987296392 file name=/data1/oradata/samik/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=987296392 file name=/data1/oradata/samik/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=987296392 file name=/data1/oradata/samik/usertbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=987296392 file name=/data1/oradata/samik/sam_ts1.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=987296392 file name=/data1/oradata/samik/sam_idx1.dbf

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

executing command: SET until clause

Starting recover at 20-SEP-24

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /data2/oradata/arch/1_3_976441199.arch
archived log for thread 1 with sequence 4 is already on disk as file /data2/oradata/arch/1_4_976441199.arch
archived log for thread 1 with sequence 5 is already on disk as file /data2/oradata/arch/1_5_976441199.arch
archived log for thread 1 with sequence 6 is already on disk as file /data2/oradata/arch/1_6_976441199.arch
archived log file name=/data2/oradata/arch/1_3_976441199.arch thread=1 sequence=3
archived log file name=/data2/oradata/arch/1_4_976441199.arch thread=1 sequence=4
archived log file name=/data2/oradata/arch/1_5_976441199.arch thread=1 sequence=5
archived log file name=/data2/oradata/arch/1_6_976441199.arch thread=1 sequence=6
media recovery complete, elapsed time: 00:00:02
Finished recover at 20-SEP-24
released channel: c1
released channel: c2
released channel: c3
released channel: c4
Oracle instance started

Total System Global Area    1342177280 bytes

Fixed Size                     8620464 bytes
Variable Size                352323152 bytes
Database Buffers             973078528 bytes
Redo Buffers                   8155136 bytes

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

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

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    1342177280 bytes

Fixed Size                     8620464 bytes
Variable Size                352323152 bytes
Database Buffers             973078528 bytes
Redo Buffers                   8155136 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SAMIK" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/data1/oradata/samik/redo01a.log', '/data1/oradata/samik/redo01b.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/data1/oradata/samik/redo02a.log', '/data1/oradata/samik/redo02b.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/data1/oradata/samik/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/data1/oradata/samik/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/data1/oradata/samik/sysaux01.dbf",
 "/data1/oradata/samik/undotbs01.dbf",
 "/data1/oradata/samik/users01.dbf",
 "/data1/oradata/samik/usertbs01.dbf",
 "/data1/oradata/samik/sam_ts1.dbf",
 "/data1/oradata/samik/sam_idx1.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data1/oradata/samik/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/data1/oradata/samik/sysaux01.dbf RECID=1 STAMP=987296419
cataloged datafile copy
datafile copy file name=/data1/oradata/samik/undotbs01.dbf RECID=2 STAMP=987296419
cataloged datafile copy
datafile copy file name=/data1/oradata/samik/users01.dbf RECID=3 STAMP=987296419
cataloged datafile copy
datafile copy file name=/data1/oradata/samik/usertbs01.dbf RECID=4 STAMP=987296419
cataloged datafile copy
datafile copy file name=/data1/oradata/samik/sam_ts1.dbf RECID=5 STAMP=987296419
cataloged datafile copy
datafile copy file name=/data1/oradata/samik/sam_idx1.dbf RECID=6 STAMP=987296419

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=987296419 file name=/data1/oradata/samik/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=987296419 file name=/data1/oradata/samik/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=987296419 file name=/data1/oradata/samik/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=987296419 file name=/data1/oradata/samik/usertbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=987296419 file name=/data1/oradata/samik/sam_ts1.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=987296419 file name=/data1/oradata/samik/sam_idx1.dbf

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

database opened
Finished Duplicate Db at 20-SEP-24

executing command: SET until clause

RMAN>