BACKUP BASED CLONING USING RMAN

BACKUP BASED CLONING USING RMAN 

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. In this we clone the database using RMAN backup.

We do this clone activity in same host.

Live database name  :  livedb

Clone database name :clone

Step 1: check live database mode and take backup with the help of rman :

Connect database and check database mode:

[oracle@vignesh ~]$ . livedb.env

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

LIVEDB    READ WRITE

Connect RMAN and take backup:

[oracle@vignesh ~]$ rman target /

connected to target database: LIVEDB (DBID=3038400691)

check the configure of controlfile and spfile backups:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name LIVEDB are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_livedb.f'; # default

Take full backup with archivelogs

RMAN> backup database plus archivelog;

Starting backup at 13-SEP-21

Oracle error from target database:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_2_jmqgqc3d_.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/LIVEDB/onlinelog/o1_mf_2_jmqgq8sd_.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=75 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=10 RECID=44 STAMP=1083111801

input archived log thread=1 sequence=11 RECID=45 STAMP=1083112128

input archived log thread=1 sequence=12 RECID=46 STAMP=1083112200

input archived log thread=1 sequence=13 RECID=47 STAMP=1083112801

input archived log thread=1 sequence=14 RECID=48 STAMP=1083113808

input archived log thread=1 sequence=15 RECID=49 STAMP=1083114327

input archived log thread=1 sequence=16 RECID=50 STAMP=1083114675

input archived log thread=1 sequence=17 RECID=51 STAMP=1083114935

input archived log thread=1 sequence=18 RECID=52 STAMP=1083115759

input archived log thread=1 sequence=19 RECID=53 STAMP=1083116127

input archived log thread=1 sequence=20 RECID=54 STAMP=1083118437

input archived log thread=1 sequence=21 RECID=55 STAMP=1083119550

input archived log thread=1 sequence=22 RECID=56 STAMP=1083119770

channel ORA_DISK_1: starting piece 1 at 13-SEP-21

channel ORA_DISK_1: finished piece 1 at 13-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_13/o1_mf_annnn_TAG20210913T031321_jmwx3tjg_.bkp tag=TAG20210913T031321 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 13-SEP-21

Starting backup at 13-SEP-21

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=00005 name=/u01/app/oracle/oradata/LIVEDB/datafile/rman.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmhwt5ld_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmhwt5n5_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmhwt5pt_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmhwt5pw_.dbf

channel ORA_DISK_1: starting piece 1 at 13-SEP-21

channel ORA_DISK_1: finished piece 1 at 13-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_13/o1_mf_nnndf_TAG20210913T031330_jmwx42f2_.bkp tag=TAG20210913T031330 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Finished backup at 13-SEP-21

Starting backup at 13-SEP-21

Oracle error from target database:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_2_jmqgqc3d_.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/LIVEDB/onlinelog/o1_mf_2_jmqgq8sd_.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

 

using channel ORA_DISK_1

specification does not match any archived log in the repository

backup cancelled because there are no files to backup

Finished backup at 13-SEP-21

 

Starting Control File and SPFILE Autobackup at 13-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_13/o1_mf_s_1083122066_jmwx5vb3_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 13-SEP-21

RMAN>

Crosscheck the database to check the availability of the backups

RMAN> CROSSCHECK BACKUP OF DATABASE;`

backup piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_12/o1_mf_nnndf_TAG20210912T013338_jmt2wvp1_.bkp RECID=40 STAMP=1083029619

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_12/o1_mf_nnndf_TAG20210912T234452_jmwjwwyt_.bkp RECID=49 STAMP=1083109492

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_13/o1_mf_nnndf_TAG20210913T002904_jmwmhs5s_.bkp RECID=54 STAMP=1083112145

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_13/o1_mf_nnndf_TAG20210913T031330_jmwx42f2_.bkp RECID=58 STAMP=1083122010

Crosschecked 10 objects

List the backup

RMAN>  list backup of database;

 

List of Backup Sets

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

58      Full    1.35G      DISK        00:00:46     13-SEP-21

        BP Key: 58   Status: AVAILABLE  Compressed: NO  Tag: TAG20210913T031330

        Piece Name: /u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_13/o1_mf_nnndf_TAG20210913T031330_jmwx42f2_.bkp

  List of Datafiles in backup set 58

  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- --------- ----------- ------ ----

  1       Full 2799510    13-SEP-21              NO    /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmhwt5ld_.dbf

  3       Full 2799510    13-SEP-21              NO    /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmhwt5n5_.dbf

  4       Full 2799510    13-SEP-21              NO    /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmhwt5pt_.dbf

  5       Full 2799510    13-SEP-21              NO    /u01/app/oracle/oradata/LIVEDB/datafile/rman.dbf

  7       Full 2799510    13-SEP-21              NO    /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmhwt5pw_.dbf

Step 2: create a pfile for clone database

[oracle@vignesh dbs]$ cat initclone.ora

*.db_name=CLONE

*.control_files='/u01/app/oracle/oradata/CLONE/control01.ctl','/u01/app/oracle/flash_recovery_area/clone/control02.ctl'

*.db_recovery_file_dest_size=4g

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_file_name_convert='/u01/app/oracle/oradata/LIVEDB/datafile','/u01/app/oracle/oradata/CLONE'

*.log_file_name_convert='/u01/app/oracle/oracle/oradata/LIVEDB/onlinelog','/u01/app/oracle/oradata/CLONE'

[oracle@vignesh dbs]$

Step 3: copy the live database password file to clone database

[oracle@vignesh dbs]$ cp orapwlivedb orapwclone

Step 4:create directory for clone database

[oracle@vignesh ~]$ mkdir -p /u01/app/oracle/oradata/clone

[oracle@vignesh ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/CLONE

Step 5:copy the files using scp to clone directory location Archivelogs , autobackup, backupsets.

[oracle@vignesh ~]$ scp -r oracle@192.168.*.*:/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog /u01/app/oracle/fast_recovery_area/CLONE

oracle@192.168.164.128's password:

o1_mf_1_10_jmwm4yxo_.arc                                             100%   83MB  37.5MB/s   00:02

o1_mf_1_11_jmwmh80n_.arc                                             100%   73KB 436.4KB/s   00:00

o1_mf_1_12_jmwmkjvm_.arc                                             100% 2560    36.2KB/s   00:00

o1_mf_1_13_jmwn49nb_.arc                                             100% 3682KB  14.6MB/s   00:00

o1_mf_1_14_jmwo3rmo_.arc                                             100%  177KB   5.4MB/s   00:00

o1_mf_1_15_jmwomzqv_.arc                                             100% 2773KB  28.7MB/s   00:00

 [oracle@vignesh ~]$ scp -r oracle@192.168.*.*:/u01/app/oracle/fast_recovery_area/LIVEDB/backupset /u01/app/oracle/fast_recovery_area/CLONE

oracle@192.168.164.128's password:

o1_mf_nnsnf_TAG20210912T024500_jmt72nbf_.bkp                         100%  112KB   1.7MB/s   00:00

o1_mf_nnsnf_TAG20210912T024921_jmt7bs6g_.bkp                         100%  112KB  32.0MB/s   00:00

o1_mf_nnndf_TAG20210912T234452_jmwjwwyt_.bkp                         100% 1374MB  35.8MB/s   00:38

220.8KB/s   00:00

[oracle@vignesh ~]$ scp -r oracle@192.168.*.*:/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup /u01/app/oracle/fast_recovery_area/CLONE

oracle@192.168.164.128's password:

o1_mf_s_1082695499_jmhwmn2d_.bkp                                     100%   10MB  35.3MB/s   00:00

o1_mf_s_1082938600_jmqb0jtk_.bkp                                     100%   10MB  32.4MB/s   00:00

o1_mf_s_1082938906_jmqbb368_.bkp                                     100%   10MB  34.0MB/s  

[oracle@vignesh ~]$

Step 6: connect clone database start the database with pfile in nomount stage:

[oracle@vignesh ~]$ . clone.env

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> startup pfile='u01/app/oracle/19.0.0/dbhome_1/dbs/initclone.ora' nomount

ORACLE instance started.

Total System Global Area  243268216 bytes

Fixed Size                  8895096 bytes

Variable Size             180355072 bytes

Database Buffers           50331648 bytes

Redo Buffers                3686400 bytes

Step 7: connect rman in clone database it show database not mounted stage

[oracle@vignesh ~]$ . clone.env

[oracle@vignesh ~]$ rman target /

connected to target database: CLONE (not mounted)

RMAN> exit

Step 8:create listener and tnsnames files to connect auxiliary database

Listener.ora

[oracle@vignesh admin]$ cat listener.ora

livedb =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.*.*)(PORT = 1521))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = livedb)

      (ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)

      (SID_NAME = livedb)

    )

  )

Tnsname.ora

Create a clone database tns entry

[oracle@vignesh admin]$ cat tnsnames.ora

livedb=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.*.*)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = livedb)

    )

  )

clone=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.164.128)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = clone)

        (UR=A)

    )

  )

Step 9: connect target database as open stage check it is opened.

[oracle@vignesh ~]$ rman target /

connected to target database: LIVEDB (DBID=3038400691)

RMAN>

Step 10: connect clone database and perform clone using live database auxiliary method

[oracle@vignesh ~]$ . clone.env

[oracle@vignesh ~]$ rman target sys/oracle@livedb auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 13 02:59:14 2021

Version 19.3.0.0.0

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

connected to target database: LIVEDB (DBID=3038400691)

connected to auxiliary database: CLONE (not mounted)

Step 11: run the script to clone the database

RMAN> RUN {

 set until time "to_date('sep 13 2021 00:32:55','Mon DD YYYY HH24:MI:SS')";

 DUPLICATE DATABASE TO 'clone' backup location'/u01/app/oracle/oradata/clone' nofilenamecheck;

}

executing command: SET until clause

Starting Duplicate Db at 13-SEP-21

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=38 device type=DISK

contents of Memory Script:

{

   set until scn  2790353;

   sql clone "alter system set  db_name =

 ''LIVEDB'' 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

executing command: SET until clause

sql statement: alter system set  db_name =  ''LIVEDB'' 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     243268216 bytes

Fixed Size                     8895096 bytes

Variable Size                180355072 bytes

Database Buffers              50331648 bytes

Redo Buffers                   3686400 bytes

Starting restore at 13-SEP-21

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 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/LIVEDB/autobackup/2021_09_13/o1_mf_s_1083112202_jmwmklyy_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_13/o1_mf_s_1083112202_jmwmklyy_.bkp tag=TAG20210913T003002

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/CLONE/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/clone/control02.ctl

Finished restore at 13-SEP-21

database mounted

RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/oradata/LIVEDB/onlinelog/o1_mf_1_jmqgq3r7_.log conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_1_jmqgq64s_.log conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/oradata/LIVEDB/onlinelog/o1_mf_2_jmqgq8sd_.log conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_2_jmqgqc3d_.log conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/oradata/LIVEDB/onlinelog/o1_mf_3_jmqgqflb_.log conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_3_jmqgqk77_.log conflicts with a file used by the target database

contents of Memory Script:

{

   set until scn  2790353;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/CLONE/o1_mf_system_jmhwt5ld_.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/CLONE/o1_mf_sysaux_jmhwt5n5_.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/CLONE/o1_mf_undotbs1_jmhwt5pt_.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/CLONE/rman.dbf";

   set newname for datafile  7 to

 "/u01/app/oracle/oradata/CLONE/o1_mf_users_jmhwt5pw_.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

Starting restore at 13-SEP-21

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/o1_mf_system_jmhwt5ld_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CLONE/o1_mf_sysaux_jmhwt5n5_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CLONE/o1_mf_undotbs1_jmhwt5pt_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/CLONE/rman.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CLONE/o1_mf_users_jmhwt5pw_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_13/o1_mf_nnndf_TAG20210913T002904_jmwmhs5s_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_13/o1_mf_nnndf_TAG20210913T002904_jmwmhs5s_.bkp tag=TAG20210913T002904

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25

Finished restore at 13-SEP-21

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=14 STAMP=1083121297 file name=/u01/app/oracle/oradata/CLONE/o1_mf_system_jmhwt5ld_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=15 STAMP=1083121297 file name=/u01/app/oracle/oradata/CLONE/o1_mf_sysaux_jmhwt5n5_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=16 STAMP=1083121297 file name=/u01/app/oracle/oradata/CLONE/o1_mf_undotbs1_jmhwt5pt_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=17 STAMP=1083121298 file name=/u01/app/oracle/oradata/CLONE/rman.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=18 STAMP=1083121298 file name=/u01/app/oracle/oradata/CLONE/o1_mf_users_jmhwt5pw_.dbf

contents of Memory Script:

{

   set until time  "to_date('SEP 13 2021 00:32:55', 'MON DD YYYY HH24:MI:SS')";

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 13-SEP-21

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_13/o1_mf_1_12_jmwmkjvm_.arc

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_13/o1_mf_1_13_jmwn49nb_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_13/o1_mf_1_12_jmwmkjvm_.arc thread=1 sequence=12

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_13/o1_mf_1_13_jmwn49nb_.arc thread=1 sequence=13

media recovery complete, elapsed time: 00:00:04

Finished recover at 13-SEP-21

Oracle instance started

Total System Global Area     243268216 bytes

Fixed Size                     8895096 bytes

Variable Size                180355072 bytes

Database Buffers              50331648 bytes

Redo Buffers                   3686400 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";

}

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 started

Total System Global Area     243268216 bytes

Fixed Size                     8895096 bytes

Variable Size                180355072 bytes

Database Buffers              50331648 bytes

Redo Buffers                   3686400 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/LIVEDB/onlinelog/o1_mf_1_jmqgq3r7_.log', '/u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_1_jmqgq64s_.log' ) SIZE 200 M  REUSE,

  GROUP     2 ( '/u01/app/oracle/oradata/LIVEDB/onlinelog/o1_mf_2_jmqgq8sd_.log', '/u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_2_jmqgqc3d_.log' ) SIZE 200 M  REUSE,

  GROUP     3 ( '/u01/app/oracle/oradata/LIVEDB/onlinelog/o1_mf_3_jmqgqflb_.log', '/u01/app/oracle/fast_recovery_area/LIVEDB/onlinelog/o1_mf_3_jmqgqk77_.log' ) SIZE 200 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/CLONE/o1_mf_system_jmhwt5ld_.dbf'

 CHARACTER SET AL32UTF8

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/CLONE/o1_mf_temp_jmhwvrfj_.tmp";

   switch clone tempfile all;

   catalog clone datafilecopy  "/u01/app/oracle/oradata/CLONE/o1_mf_sysaux_jmhwt5n5_.dbf",

 "/u01/app/oracle/oradata/CLONE/o1_mf_undotbs1_jmhwt5pt_.dbf",

 "/u01/app/oracle/oradata/CLONE/rman.dbf",

 "/u01/app/oracle/oradata/CLONE/o1_mf_users_jmhwt5pw_.dbf";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/CLONE/o1_mf_temp_jmhwvrfj_.tmp in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/CLONE/o1_mf_sysaux_jmhwt5n5_.dbf RECID=1 STAMP=1083121329

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/CLONE/o1_mf_undotbs1_jmhwt5pt_.dbf RECID=2 STAMP=1083121329

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/CLONE/rman.dbf RECID=3 STAMP=1083121329

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/CLONE/o1_mf_users_jmhwt5pw_.dbf RECID=4 STAMP=1083121330

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=1083121329 file name=/u01/app/oracle/oradata/CLONE/o1_mf_sysaux_jmhwt5n5_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=1083121329 file name=/u01/app/oracle/oradata/CLONE/o1_mf_undotbs1_jmhwt5pt_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=3 STAMP=1083121329 file name=/u01/app/oracle/oradata/CLONE/rman.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=4 STAMP=1083121330 file name=/u01/app/oracle/oradata/CLONE/o1_mf_users_jmhwt5pw_.dbf

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 13-SEP-21

RMAN>

Step 12: connect clone database and check the stage once clone completed the nomount can automatically changed as read write

[oracle@vignesh ~]$ . clone.env

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

CLONE     READ WRITE

Check the tablespaces its as samed the live database

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

TEMP

SYSAUX

UNDOTBS1

RMAN

USERS