19 | 08 | 2017
Latest Articles
Popular Articles

RMAN

Clone database with RMAN on another host ( option 2 )

User Rating:  / 0
PoorBest 

Clone database with RMAN on another host (method 2)

 

With this second procedure I will show what I am doing when I have to clone a database on another host. Note I never use the "duplicate target database to" syntax. We assume our nightly backup succesfully ran last night. This is the RMAN script I use

run {

configure controlfile autobackup off;
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE MAXSETSIZE TO 20G;
crosscheck backupset;
crosscheck archivelog all;
delete expired backupset;
delete expired archivelog all;
backup incremental level 0 device type disk as compressed backupset database skip readonly;
backup device type disk as compressed backupset archivelog all;
delete noprompt obsolete device type disk;
backup current controlfile;
backup spfile;
configure controlfile autobackup on;
}


I ftp the backupsets towards the host on which I wanna clone the database, and additionally I backup the current controlfile
( before moving the backupsets since also this backup controlfile needs to be moved to the host on which I wanna clone the database )

RMAN> backup current controlfile;


Starting backup at 18-MAR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=470 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 18-MAR-08
channel ORA_DISK_1: finished piece 1 at 18-MAR-08
piece handle=/mnt/oraarc/flash_recovery_area/MY_DB/backupset/2008_03_18/o1_mf_ncnnf_TAG20080318T133709_3xzfvq6p_.bkp tag=TAG20080318T133709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 18-MAR-08

Starting Control File and SPFILE Autobackup at 18-MAR-08
piece handle=/mnt/oraarc/flash_recovery_area/MY_DB/autobackup/2008_03_18/o1_
mf_s_649690638_3xzfw18d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-08

I prepare an spfile for the new database on the host on which I wanna clone the database and adjust off course some parameters like
db_name, db_unique_name, background_dump_dest, core_dump_dest, user_dump_dest, audit_file_dest. Aftwards I startup nomount the instance.

oracle@cs:/opt/oracle/backup $ export ORACLE_SID=MYDB

oracle@cs:/opt/oracle/backup $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 18 12:33:49 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2050856 bytes
Variable Size             239076568 bytes
Database Buffers          826277888 bytes
Redo Buffers                6336512 bytes

And off course I startup with the restoration of the controlfile.
Any Oracle DBA should be aware of the importance of the database controlfile with respect to backup and recovery scenario' s. It might not be the right place but

1) ALWAYS mirror your control files
2) ALWAYS backup your controlfiles
3) RECOMMENDED is to have a separate backup piece dedicated for the controlfile

oracle@cs:/opt/oracle/backup $ rman target /


Recovery Manager: Release 10.2.0.3.0 - Production on Tue Mar 18 12:38:23 2008

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

connected to target database: MY_DB (not mounted)


RMAN> restore controlfile from '/opt/oracle/backup/o1_mf_ncnnf_TAG20080318T133709_3xzfvq6p_.bkp';

Starting restore at 18-MAR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1091 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
output filename=+DG1/MY_DB/controlfile/current.509.649687193
output filename=+DG2/MY_DB/controlfile/current.645.649687201
Finished restore at 18-MAR-08

Despite my devotion to OMF - Oracle Managed Files - I become sometimes a bit pissy when we restore OMF control files with RMAN and afterwards it turns out
the name of the control files in the spfile is different to the ones restored. I adjust the control files initialization parameter in advance

SQL> alter system set control_files='+DG1/MY_DB/controlfile/current.509.649687193','+DG2/MY_DB/controlfile/current.645.649687201' scope=spfile;

System altered.

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


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

Total System Global Area 1073741824 bytes
Fixed Size                  2050856 bytes
Variable Size             239076568 bytes
Database Buffers          826277888 bytes
Redo Buffers                6336512 bytes
Database mounted.

As soon as our database is at mount stage, we are ready to catalog RMAN backup pieces, catalogging backup pieces is

1) easy
2) a new 10G feature I really appreciate
3) sometimes great
4) sometimes very great

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


Starting implicit crosscheck backup at 18-MAR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1088 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 18-MAR-08

Starting implicit crosscheck copy at 18-MAR-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-MAR-08

searching for all files in the recovery area
cataloging files...
no files cataloged

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_annnn_TAG20080318T005408_3xy1524l_.bkp
File Name: /opt/oracle/backup/o1_mf_nnnd0_NIGHT_3xy0vmmv_.bkp
File Name: /opt/oracle/backup/o1_mf_s_649644888_3xy16br4_.bkp
File Name: /opt/oracle/backup/o1_mf_ncnnf_TAG20080318T133709_3xzfvq6p_.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_annnn_TAG20080318T005408_3xy1524l_.bkp
File Name: /opt/oracle/backup/o1_mf_nnnd0_NIGHT_3xy0vmmv_.bkp
File Name: /opt/oracle/backup/o1_mf_s_649644888_3xy16br4_.bkp
File Name: /opt/oracle/backup/o1_mf_ncnnf_TAG20080318T133709_3xzfvq6p_.bkp

And now we are ready for a restore and recover operation

RMAN> restore database;


Starting restore at 18-MAR-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_system_38q65d7k_.dbf
restoring datafile 00002 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_undotbs1_38q65f13_.dbf
restoring datafile 00003 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_sysaux_38q65dlo_.dbf
restoring datafile 00004 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_users_38q65fk1_.dbf
restoring datafile 00005 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_ts_my_db_a_3vofg44v_.dbf
restoring datafile 00006 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_ts_my_db_l_3vofgswv_.dbf
restoring datafile 00007 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_ts_my_db_l_3vofhhxy_.dbf
restoring datafile 00008 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_ts_my_db_l_3vofj6k5_.dbf
restoring datafile 00009 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_ts_my_db_l_3vofjxpw_.dbf
restoring datafile 00010 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_ts_my_db_l_3vofknkv_.dbf
restoring datafile 00011 to /mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_ts_my_db_l_3voflmnh_.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/o1_mf_nnnd0_NIGHT_3xy0vmmv_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/o1_mf_nnnd0_NIGHT_3xy0vmmv_.bkp tag=NIGHT
channel ORA_DISK_1: restore complete, elapsed time: 00:03:26
Finished restore at 18-MAR-08


RMAN> recover database;

Starting recover at 18-MAR-08
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=165
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/o1_mf_annnn_TAG20080318T005408_3xy1524l_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/o1_mf_annnn_TAG20080318T005408_3xy1524l_.bkp tag=TAG20080318T005408
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
archive log filename=+DG1/MY_DB/archivelog/2008_03_18/thread_1_seq_165.513.649688627 thread=1 sequence=165
channel default: deleting archive log(s)
archive log filename=+DG1/MY_DB/archivelog/2008_03_18/thread_1_seq_165.513.649688627 recid=165 stamp=649688636
unable to find archive log
archive log thread=1 sequence=166
RMAN-00571: ========================================================
RMAN-00569:
========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571:
========================================================
RMAN-03002: failure of recover command at 03/18/2008 13:04:05
RMAN-06054: media recovery requesting unknown log: thread 1 seq 166 lowscn 6519719

This error is actually not an error. When we restore a controlfile and use it afterwards for our recovery operations we are actually doing a cancel based recovery. After a cancel based recovery we have to open our database with resetlogs.  

RMAN> sql "alter database open resetlogs";

sql statement: alter database open resetlogs


Prior to 10G it was not possible to recover through a resetlogs and it was recommended (and we do not exagerate here) to backup the entire database as soon as the resetlogs command was used. One of the marvelleous new 10G features is the ability to recover through resetlogs. 

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES


STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED


           1                 1 26-FEB-07                       0
PARENT     615524994                  0 NO

           2            547422 04-JUL-07                       1 26-FEB-07
PARENT     627058887                  1 NO

           3           6519720 18-MAR-08                  547422 04-JUL-07
CURRENT    649688674                  2 NO

 

SQL> select recovery_target_incarnation#,last_open_incarnation# from v$database;

RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION#


                           3                      3



RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> reset database to incarnation 3;

database reset to incarnation 3


More about incarnations, activation id and recovery through resetlogs later.


OK, the clone database on another host method 2 has finished.
Is there any other final difference between method 1 and method 2 ?
Yes there is
Method 1 uses the duplicate target database for standby which resets the dbid.
Method 2 results in a target database and a clone database with the same dbid.
When you want to register the clone database in the same recovery catalog as the original database you face the error below.

oracle@cs:/opt/oracle/backup $ rman target / catalog rman/rman\@DB_CATALOG


Recovery Manager: Release 10.2.0.3.0 - Production on Tue Mar 18 13:52:50 2008

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

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

RMAN> register database;

new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN-00571: ========================================================
RMAN-00569:
========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571:
========================================================
RMAN-03009: failure of register command on default channel at 03/18/2008 13:53:06
RMAN-20002: target database already registered in recovery catalog

 



   We can change the DBID with nid ( new database id )