rman duplicating an oracle database in a new sid / two host scenario


introduction

there are many ways to clone an oracle database. you may just copy and init a cold backup, do a rman restore / recover or employ datapump. according to the number of possibilities there is another multitude number of things you have to evaluate in advance, prepare, configure, keep in mind, postprocess etc., for every clone procedure. me, i mostly use cloning by cold backup, preferably if cloning to another host is requested and if the database system identifier (sid) remains the same. although this way of cloning nearly needs no additional work, you have to take into account that copying the (cold) files from source to destination may take a considerable amount of time, especially for large databases (i’m going to show in another post how to accelerate that by san-snapshotting, a technique becoming deservedly popular lately).

if you cannot go with source database downtimes at all, the rman duplicate functionality might be the preferred choice. it is sort of rman restore / recover, can therefore live on a hot backup, but saves yourself from playing around with controlfile backups / scripts or the nid tool for example. much more far from it, rman duplicate even uses a live, the source, controlfile for automated database structure setup on the destination. but now let’s see how a rman duplicate may work for a given scenario.

rman duplicate in a new sid / two host scenario

the given scenario regards a controlfile catalog rman duplicate to another host with a different destination sid. it may as well work for identical sid’s and different hosts and other possible variations with little modifications. to conceptualise, the source database sid will furthermore be called test01 and the destination database sid test02, respectively. according to the oracle flexible architecture (ofa) structure, the database sid’s, identical to the database unique names here, will be part of the instance path prefixes, leading to all database file types, just as data, redolog, controlfile etc. the scenario has been executed on a win xp, 10.2.0.4 enterprise environment.

the first thing to provide is another database, better an instance runtime setup, on the destination host. i usually do not reinvent the wheel to reach this end (executing oradim and friends) and just invoke dbca to have me created a dummy instance and more importantly here, the nessecary windows services, registry entries and network configuration etc. for the destination database sid test02. take care that the network configuration on the destination database also contains sqlnet access to the source database and vice versa for later use.

the next step comprises arranging a temporary but nevertheless dedicated spfile on the destination host, that supports rman duplication to different instance file path prefixes. because a clone of the source database is to be created, this spfile may at best be set up from the test01 original by employing the create pfile ... from spfile; functionality. with an editable pfile, two types of instance file path prefix interventions can be written down:

  • direct mapping, that is, changing the paths by modifying the accordant initialization parameters permanently. this is true for every database file type except data and redolog files, for example the controlfiles or the bdump destination. temp files remain unregarded at all, they will be autocreated by rman right at the end of the clone (recover) procedure.
  • indirect mapping, that is, specifying an exchange pattern to be applied to the paths during a transition from source to destination. this is, in contrary to the point above, true for data and redolog files exclusively. it is expressed by adding the initialization parameters db_file_name_convert and log_file_name_convert in an appropriate way.

having configured and suitably located the temporary spfile, as the third step, you must now precreate all avised instance file path prefixes on destination and start the database instance test02 in nomount state only. there is noise on the web that rman should do the mkdir automatically as long as it actually executes on the destination host (see below), has all relevant information and indeed posseses all nessecary permissions as user oracle. this all is true, but one thing forgotten. at the time, a database instance switches into nomount state, it will already see everything being defined in the spfile. since the database instance nomount startup precedes any rman duplicate invocation, there can be no automatism for mkdir.

the last but one step is somehow dizzy to understand at first, at least to me. it concerns the backup set and archivelog files that rman duplicate will use in creating the clone database instance. note that they also have to be made available on the destination host, being located in a directory structure identical to source. what seems obscure at first, becomes clear when one reviews the rman duplicate operation process on the destination host. because the controlfile of test01 will be used during cloning, rman duplicate expects an environment as it exists on source. i.e., if you have, like me, the sid test02 as a path component at destination, you may need to provide something like ...\test01\archive alongside ...\test02\archive for the time of the clone process on the destination host. time window considerations do also apply (and smoothly work) here because the possibly nessecary extraction of source backup sets will be done to ...\test02\ folders (;-) and, nevertheless, cleaned up eventually.

finally it is advisable to set a mark of some type that terminates the end of the clone process. that is, as long as rman duplicate is, under the covers, nothing more than a restore / recover and archivelog apply, leaving oracle alone in that question eventually results in an imcompletely recovered mounted instance asking you what to do next. on the other hand, specifying a termination mark, enables rman duplicate to run on through to a fresh resetlogged and bounced instance. me, i prefer to specify the ... until scn ... command term of rman duplicate.

after all that preparations, the clone process can now be started on the destination host. please do not mind the following being logged in german, i’m going to scetch what happens in english.

cmd> rman target sys/***@test01 auxiliary sys/***@test02

Mit Ziel-Datenbank verbunden: TEST01 (DBID=4233757478)
Bei Hilfsdatenbank angemeldet: TEST02 (nicht mit MOUNT angeschlossen)

take care that the rman’s target / test02 (auxiliary) pair below equates to the terms of source / destination used throughout this article. also, rman executes in nocatalog mode.

RMAN> duplicate target database to test02 until scn 4976047;

Starten Duplicate Db um 05.01.10
Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet
Zugewiesener Kanal: ORA_AUX_DISK_1
Kanal ORA_AUX_DISK_1: SID=156 Gerõtetyp=DISK

rman duplicate will firstly set the termination mark and path prefix mappings mentioned above and than do a restore of a couple of backup sets, here a mixed set of level 0 baseline, level 1 increment and archivelog backups.

Inhalt von Speicher-Skript:
{
   set until scn  4976047;
   set newname for datafile  1 to "H:\ORADATA\TEST02\SYSTEM01.DBF";
   set newname for datafile  2 to "H:\ORADATA\TEST02\UNDOTBS01.DBF";
   set newname for datafile  3 to "H:\ORADATA\TEST02\SYSAUX01.DBF";
   set newname for datafile  4 to "H:\ORADATA\TEST02\USERS01.DBF";
   restore
   check readonly
   clone database
   ;
}
Speicher-Skript wird ausgef³hrt

Befehl wird ausgef³hrt: SET until clause
Befehl wird ausgef³hrt: SET NEWNAME
Befehl wird ausgef³hrt: SET NEWNAME
Befehl wird ausgef³hrt: SET NEWNAME
Befehl wird ausgef³hrt: SET NEWNAME

Starten restore um 05.01.10
Kanal ORA_AUX_DISK_1 wird benutzt

Kanal ORA_AUX_DISK_1: Recovery von Datendatei-Backup Set beginnt
Kanal ORA_AUX_DISK_1: Datendatei(en) werden zum Wiederherstellen aus Backup Set angegeben
Datendatei 00001 wird wiederhergestellt zu H:\ORADATA\TEST02\SYSTEM01.DBF
Datendatei 00002 wird wiederhergestellt zu H:\ORADATA\TEST02\UNDOTBS01.DBF
Datendatei 00003 wird wiederhergestellt zu H:\ORADATA\TEST02\SYSAUX01.DBF
Datendatei 00004 wird wiederhergestellt zu H:\ORADATA\TEST02\USERS01.DBF
Kanal ORA_AUX_DISK_1: Lesen aus Backup Piece J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_04\O1_MF_NNND0_TAG20100104T175403_5N479JPX_.BKP
Kanal ORA_AUX_DISK_1: Backup Piece 1 wurde wiederhergestellt
Piece Handle=J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_04\O1_MF_NNND0_TAG20100104T175403_5N479JPX_.BKP Tag=TAG20100104T175403
Kanal ORA_AUX_DISK_1: Restore abgeschlossen, abgelaufene Zeit: 00:04:26
Beendet restore um 05.01.10

a new controlfile will be created and the restored datafiles being bound.

SQL-Anweisung: CREATE CONTROLFILE REUSE SET DATABASE "TEST02" RESETLOGS ARCHIVELOG
  MAXLOGFILES   16
  MAXLOGMEMBERS 3
  MAXDATAFILES  100
  MAXINSTANCES  8
  MAXLOGHISTORY 454
 LOGFILE
  GROUP  1 ( 'F:\ORADATA\TEST02\REDO\REDO01.LOG' ) SIZE 100 M  REUSE,
  GROUP  2 ( 'F:\ORADATA\TEST02\REDO\REDO02.LOG' ) SIZE 100 M  REUSE,
  GROUP  3 ( 'F:\ORADATA\TEST02\REDO\REDO03.LOG' ) SIZE 100 M  REUSE,
  GROUP  4 ( 'F:\ORADATA\TEST02\REDO\REDO04.LOG' ) SIZE 100 M  REUSE
 DATAFILE
  'H:\ORADATA\TEST02\SYSTEM01.DBF'
 CHARACTER SET UTF8

Inhalt von Speicher-Skript:
{
   switch clone datafile all;
}
Speicher-Skript wird ausgef³hrt

Freigegebener Kanal: ORA_AUX_DISK_1
Datendatei 2 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=1 Stempel=707508376 Dateiname=H:\ORADATA\TEST02\UNDOTBS01.DBF
Datendatei 3 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=2 Stempel=707508376 Dateiname=H:\ORADATA\TEST02\SYSAUX01.DBF
Datendatei 4 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=3 Stempel=707508376 Dateiname=H:\ORADATA\TEST02\USERS01.DBF

after restoring from the backups, now follows the recovery that comprises unpacking a level 1 increment and archivelog backups and just plain archivelogs as well as applying them.

Inhalt von Speicher-Skript:
{
   set until scn  4976047;
   recover
   clone database
   delete archivelog
   ;
}
Speicher-Skript wird ausgef³hrt

Befehl wird ausgef³hrt: SET until clause

Starten recover um 05.01.10
Zugewiesener Kanal: ORA_AUX_DISK_1
Kanal ORA_AUX_DISK_1: SID=156 Gerõtetyp=DISK
Kanal ORA_AUX_DISK_1: Schrittweise Recovery des Datendatei-Backup Setes beginnt
Kanal ORA_AUX_DISK_1: Datendatei(en) werden zum Wiederherstellen aus Backup Set angegeben
Ziel f³r Zur³ckschreiben von Datendatei 00001: H:\ORADATA\TEST02\SYSTEM01.DBF
Ziel f³r Zur³ckschreiben von Datendatei 00002: H:\ORADATA\TEST02\UNDOTBS01.DBF
Ziel f³r Zur³ckschreiben von Datendatei 00003: H:\ORADATA\TEST02\SYSAUX01.DBF
Ziel f³r Zur³ckschreiben von Datendatei 00004: H:\ORADATA\TEST02\USERS01.DBF
Kanal ORA_AUX_DISK_1: Lesen aus Backup Piece J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_NNND1_TAG20100105T001527_5N4XNK1X_.BKP
Kanal ORA_AUX_DISK_1: Backup Piece 1 wurde wiederhergestellt
Piece Handle=J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_NNND1_TAG20100105T001527_5N4XNK1X_.BKP Tag=TAG20100105T001527
Kanal ORA_AUX_DISK_1: Restore abgeschlossen, abgelaufene Zeit: 00:00:15

the actual media recovery phase. please note, as depicted above, that archivelog backups will be extracted from the copied directory structure of test01 to the archivelog destination of test02 (as given in the spfile).

Media Recovery starten

Archive Log-Thread 1 Sequenz 55 bereits auf Platte als Datei G:\ORACLE\SAN_2\ORADATA\TEST01\ARCHIVE\ARC00055_0705778790.001 vorhanden
Archive Log-Thread 1 Sequenz 56 bereits auf Platte als Datei G:\ORACLE\SAN_2\ORADATA\TEST01\ARCHIVE\ARC00056_0705778790.001 vorhanden
Archive Log-Thread 1 Sequenz 57 bereits auf Platte als Datei G:\ORACLE\SAN_2\ORADATA\TEST01\ARCHIVE\ARC00057_0705778790.001 vorhanden
Kanal ORA_AUX_DISK_1: Recovery von Archive Log in Standardziel wird begonnen
Kanal ORA_AUX_DISK_1: Archive Log wird wiederhergestellt
Archive Log-Thread=1 Sequenz=52
Kanal ORA_AUX_DISK_1: Lesen aus Backup Piece J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_ANNNN_TAG20100105T001833_5N4XTC4V_.BKP
Kanal ORA_AUX_DISK_1: Backup Piece 1 wurde wiederhergestellt
Piece Handle=J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_ANNNN_TAG20100105T001833_5N4XTC4V_.BKP Tag=TAG20100105T001833
Kanal ORA_AUX_DISK_1: Restore abgeschlossen, abgelaufene Zeit: 00:00:02
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST02\ARCHIVE\ARC00052_0705778790.001 Thread=1 Sequenz=52
Kanal clone_default: Archive Log(s) werden gel÷scht
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST02\ARCHIVE\ARC00052_0705778790.001 Recid=1 Stempel=707508404
Kanal ORA_AUX_DISK_1: Recovery von Archive Log in Standardziel wird begonnen
Kanal ORA_AUX_DISK_1: Archive Log wird wiederhergestellt
Archive Log-Thread=1 Sequenz=53
Kanal ORA_AUX_DISK_1: Lesen aus Backup Piece J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_ANNNN_TAG20100105T030508_5N57LOH3_.BKP
Kanal ORA_AUX_DISK_1: Backup Piece 1 wurde wiederhergestellt
Piece Handle=J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_ANNNN_TAG20100105T030508_5N57LOH3_.BKP Tag=TAG20100105T030508
Kanal ORA_AUX_DISK_1: Restore abgeschlossen, abgelaufene Zeit: 00:00:03
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST02\ARCHIVE\ARC00053_0705778790.001 Thread=1 Sequenz=53
Kanal clone_default: Archive Log(s) werden gel÷scht
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST02\ARCHIVE\ARC00053_0705778790.001 Recid=2 Stempel=707508409
Kanal ORA_AUX_DISK_1: Recovery von Archive Log in Standardziel wird begonnen
Kanal ORA_AUX_DISK_1: Archive Log wird wiederhergestellt
Archive Log-Thread=1 Sequenz=54
Kanal ORA_AUX_DISK_1: Lesen aus Backup Piece J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_ANNNN_TAG20100105T111010_5N6402NH_.BKP
Kanal ORA_AUX_DISK_1: Backup Piece 1 wurde wiederhergestellt
Piece Handle=J:\ORACLE\SAN_2\FLASH\TEST01\BACKUPSET\2010_01_05\O1_MF_ANNNN_TAG20100105T111010_5N6402NH_.BKP Tag=TAG20100105T111010
Kanal ORA_AUX_DISK_1: Restore abgeschlossen, abgelaufene Zeit: 00:00:07
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST02\ARCHIVE\ARC00054_0705778790.001 Thread=1 Sequenz=54
Kanal clone_default: Archive Log(s) werden gel÷scht
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST02\ARCHIVE\ARC00054_0705778790.001 Recid=3 Stempel=707508422
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST01\ARCHIVE\ARC00055_0705778790.001 Thread=1 Sequenz=55
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST01\ARCHIVE\ARC00056_0705778790.001 Thread=1 Sequenz=56
Archive Log-Dateiname=G:\ORACLE\SAN_2\ORADATA\TEST01\ARCHIVE\ARC00057_0705778790.001 Thread=1 Sequenz=57
Media Recovery abgeschlossen, abgelaufene Zeit: 00:08:27
Beendet recover um 05.01.10

following an instance bounce of test02, everything being prepared so far will be tight togehter into a running new database instance. this includes the recreation of the tempfiles according to the layout of the source instance test01 and the cataloging of the cloned datafiles with the new controlfile. indeed, the auxiliary test02 instance does seem to hang at the end of applying the latest archivelogs (as andrew barry mentioned in http://www.shutdownabort.com/quickguides/clone_rman.php). me too, i think that the shutdown normal will be responsible for that behaviour.

Inhalt von Speicher-Skript:
{
   shutdown clone;
   startup clone nomount ;
}
Speicher-Skript wird ausgef³hrt

Datenbank nicht angeschlossen
Oracle-Instance heruntergefahren

Mit Hilfsdatenbank verbunden (nicht gestartet)
Oracle-Instance gestartet

Inhalt von Speicher-Skript:
{
   set newname for tempfile  1 to "F:\ORACLE\SAN_1\ORADATA\TEST02\TEMP01.DBF";
   switch clone tempfile all;
   catalog clone datafilecopy  "H:\ORADATA\TEST02\UNDOTBS01.DBF";
   catalog clone datafilecopy  "H:\ORADATA\TEST02\SYSAUX01.DBF";
   catalog clone datafilecopy  "H:\ORADATA\TEST02\USERS01.DBF";
   switch clone datafile all;
}
Speicher-Skript wird ausgef³hrt

Befehl wird ausgef³hrt: SET NEWNAME

Temporõre Datei 1 in Kontrolldatei in F:\ORACLE\SAN_1\ORADATA\TEST02\TEMP01.DBF umbenannt

Datendateikopie katalogisiert
Datendateikopie, Dateiname=H:\ORADATA\TEST02\UNDOTBS01.DBF Recid=1 Stempel=707509296
Datendateikopie katalogisiert
Datendateikopie, Dateiname=H:\ORADATA\TEST02\SYSAUX01.DBF Recid=2 Stempel=707509296
Datendateikopie katalogisiert
Datendateikopie, Dateiname=H:\ORADATA\TEST02\USERS01.DBF Recid=3 Stempel=707509296

Datendatei 2 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=1 Stempel=707509296 Dateiname=H:\ORADATA\TEST02\UNDOTBS01.DBF
Datendatei 3 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=2 Stempel=707509296 Dateiname=H:\ORADATA\TEST02\SYSAUX01.DBF
Datendatei 4 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=3 Stempel=707509296 Dateiname=H:\ORADATA\TEST02\USERS01.DBF

finally, we see the open resetlogs and the cloned instance is ready for application.

Inhalt von Speicher-Skript:
{
   Alter clone database open resetlogs;
}
Speicher-Skript wird ausgef³hrt

Datenbank ge÷ffnet
Beendet Duplicate Db um 05.01.10

RMAN>exit

great stuff, right? now, do not forget to

  • cleanup the temporary spfile from the initialization parameters db_file_name_convert and log_file_name_convert
    	  alter system reset db_file_name_convert scope=spfile sid='*';
    	  alter system reset log_file_name_convert scope=spfile sid='*';
    	
  • immediately create a fresh level 0 baseline backup and
  • cleanup the temporary test01 directory structures for backups and archivelogs on destination

for test02.

conclusion

i love rman.

regards

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s