RMAN

Clone database with RMAN on another host ( option 1 )

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Thursday, 18 March 2010 21:03
Last Updated on Tuesday, 10 December 2019 15:01
Published on Thursday, 18 March 2010 21:03
Hits: 9166

Clone database with RMAN on another host (method 1)

 


This procedure can be used to clone your database backed up with RMAN on another host, note that

1. This procedure uses the "duplicate target database to" clause

2. The datafiles off the source database - the database we wanna clone - are on a traditional volume

3. The datafiles off the cloned database are OMF files created in an ASM diskgroup

4. I prefer a more granular method which does less in one shot, but which gives me more control


The backupsets are in the flash recovery area /mnt/oraarc/flash_recovery_area, but I haven' t a volume /mnt/oraarc/ on the host where i wanna create the clone database. I have however a volume /opt/oracle/backup which exists on both the hosts.

What I will do is

1. I transfer with ftp the backupsets from the flash recovery area (/mnt/oraarc/flash_recovery_area) towards the hosts on which I wanna clone the database (/opt/oracle/backup)

2. I will create a symbolic link in /opt/oracle/backup for all the backupsets I need on the hosts on which the database is up and running

3. I connect with the recovery catalog. Note that the catalog database is hosted on the hosts on which I wanna clone the database.

4. I connect with the target database and the catalog database. Once connected I connect with the auxiliary instance ( which is at nomount stage )

5. I will catalog the backuppieces in the recovery catalog. Note that the "duplicate target database to" requires a connection with the database which we wanna clone and that during the cloning process the backup pieces should exists or recognized on identical volumes on both nodes. (something I really dislike) ( that ' s why I create symbolic links on the target host )

6. I run the "duplicate target database to" command

cd /opt/oracle/backup

ln -s /opt/oracle/backup/o1_mf_s_649555987_3xvbd4vn_.bkp -> /mnt/oraarc/flash_recovery_area/MY_DB/backupset/2008_03_17/o1_mf_s_649555987_3xvbd4vn_.bkp

export ORACLE_SID=MYDB

oracle@myhost:/opt/oracle/ORA_HOME/dbs $ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2045912 bytes
Variable Size             117442600 bytes
Database Buffers          293601280 bytes
Redo Buffers                6340608 bytes

SQL> exit;

rman catalog rman/rman\@DB_CATALOG target sys/secret_password\@MY_DB

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Mar 17 15:38:02 2008

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

connected to target database: MY_DB (DBID=3954204418)
connected to recovery catalog database



RMAN> connect auxiliary sys/secret_password

connected to auxiliary database: MY_DB (not mounted)



RMAN> catalog start with '/opt/oracle/backup';

searching for all files that match the pattern /opt/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /opt/oracle/backup/o1_mf_s_649555987_3xvbd4vn_.bkp
File Name: /opt/oracle/backup/o1_mf_annnn_TAG20080317T001241_3xvbcc09_.bkp
File Name: /opt/oracle/backup/o1_mf_nnnd0_NIGHT_3xvb3fcc_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /opt/oracle/backup/o1_mf_s_649555987_3xvbd4vn_.bkp
File Name: /opt/oracle/backup/o1_mf_annnn_TAG20080317T001241_3xvbcc09_.bkp
File Name: /opt/oracle/backup/o1_mf_nnnd0_NIGHT_3xvb3fcc_.bkp


RMAN> duplicate target database to 'MY_DB';

Starting Duplicate Db at 17-MAR-08
using channel ORA_AUX_DISK_1

contents of Memory Script:
{
   set until scn  6489499;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  9 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   restore
   check readonly
   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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-MAR-08
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DG1
restoring datafile 00002 to +DG1
restoring datafile 00003 to +DG1
restoring datafile 00004 to +DG1
restoring datafile 00005 to +DG1
restoring datafile 00006 to +DG1
restoring datafile 00007 to +DG1
restoring datafile 00008 to +DG1
restoring datafile 00009 to +DG1
restoring datafile 00010 to +DG1
restoring datafile 00011 to +DG1
channel ORA_AUX_DISK_1: reading from backup piece /mnt/oraarc/flash_recovery_area/MY_DB/backupset/2008_03_17/o1_mf_nnnd0_NIGHT_3xvb3fcc_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
failover to piece handle=/opt/oracle/backup/o1_mf_nnnd0_NIGHT_3xvb3fcc_.bkp tag=NIGHT
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:26
Finished restore at 17-MAR-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "MY_DB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  4  SIZE 100 M ,
  GROUP  5  SIZE 100 M ,
  GROUP  6  SIZE 100 M ,
  GROUP  7  SIZE 100 M ,
  GROUP  8  SIZE 100 M
 DATAFILE
  '+DG1/MY_DB/datafile/system.671.649615433'
 CHARACTER SET AL32UTF8


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

executing Memory Script

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=649615648 filename=+DG1/MY_DB/datafile/undotbs1.507.649615433
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=649615648 filename=+DG1/MY_DB/datafile/sysaux.506.649615433
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=649615648 filename=+DG1/MY_DB/datafile/users.505.649615433
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=649615648 filename=+DG1/MY_DB/datafile/ts_my_db_admin_code.504.649615433
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=649615648 filename=+DG1/MY_DB/datafile/ts_my_db_logging_data_0208.503.649615435
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=649615648 filename=+DG1/MY_DB/datafile/ts_my_db_logging_data_0308.502.649615435
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=649615648 filename=+DG1/MY_DB/datafile/ts_my_db_logging_data_0408.514.649615435
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=649615648 filename=+DG1/MY_DB/datafile/ts_my_db_logging_data_0508.513.649615443
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=649615648 filename=+DG1/MY_DB/datafile/ts_my_db_logging_data_0608.512.649615467
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=649615648 filename=+DG1/MY_DB/datafile/ts_my_db_logging_indx.511.649615467

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

executing command: SET until clause

Starting recover at 17-MAR-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=1094 devtype=DISK

starting media recovery

RMAN-00571: ========================================================
RMAN-00569:
========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571:
========================================================
RMAN-03002: failure of Duplicate Db command at 03/17/2008 16:47:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 164 lowscn 6474868 found to restore


The clone process has restore and recovered the database files but since the process has restored a backup controlfile
the clone database needs to be opened with resetlogs. Which I manually run

RMAN> sql "alter database open resetlogs";


using target database control file instead of recovery catalog
sql statement: alter database open resetlogs

 

Note I prefer

a more granular method

which I will show once we have

dropped this database with RMAN

.