17 | 10 | 2017
Latest Articles
Popular Articles

RMAN

Clone database with RMAN on the same host

User Rating:  / 0
PoorBest 

Clone database with RMAN on the same host

 

The 10G flashback database feature allows us to rewind the database to a previous point in time. But what when we haven' t enabled flashback database ? or what when we have to recover the database to a previous point in time of which the database SCN falls out of the flashback database retention window ?
In these circumstances we still have to do an old fashionned (partial) restore of a backup and recover forward up to the SCN we want.
Obviously we need a valid backup and archivelogs. We will see it is a piece of cake (very easy task) but it can be time consuming.


With the example below I clone the database MY_DB ( sid= MYDB ) to database BK on the same platform and recover it to a previous point in time. Note

1. I use OMF - Oracle Managed Files - and as such I specify the db_create_file_dest and db_create_online_log_dest_n parameters

2. I restore the entire db otherwise I would have used the skip tablespace command

3. Backups of the source database - the one we wanna clone - are made with RMAN

4. off course the database operates in archivelog mode and have I still the required archivelogs on disk.



I a nutshell this is the procedure I use



1. I clone manually the spfile and the passwordfile both in $ORACLE_HOME/dbs

2. and adjust some instance initialization parameters

3. I use the timestamp_to_scn function to retrieve the scn to which I have to recover

4. I use rman' s duplicate target database to "destination database name" + "until scn desired SCN"

5. I drink a coffee while RMAN is doing yet another time such a great recovery work.

oracle@myhost:/home/oracle $ export ORACLE_SID=MYDB

oracle@myhost:/home/oracle $ cd $ORACLE_HOME/dbs

SQL> show parameter db_create

NAME                                 TYPE        VALUE


db_create_file_dest                  string      /mnt/oradata/MY_DB
db_create_online_log_dest_1          string      /opt/oracle
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string


SQL> select name from v$controlfile;

NAME


/opt/oracle/MY_DB/controlfile/o1_mf_38q6k2z7_.ctl
/opt/oradata/MY_DB/controlfile/o1_mf_38q6k3pj_.ctl



Let us do some SCN gym

SQL> select current_scn from v$database;


CURRENT_SCN


    6488614


SQL> select scn_to_timestamp(current_scn) from v$database;

SCN_TO_TIMESTAMP(CURRENT_SCN)


17-MAR-08 10.10.16.000000000 AM


This is the SCN to which we wanna recover

SQL> select timestamp_to_scn('17-MAR-08 08.00.00.000000000 AM') from v$database;


TIMESTAMP_TO_SCN('17-MAR-0808.00.00.000000000AM')


                                          6485576


We clone the spfile and have some modifications in it ( db_name, db_unique_name, ... )

SQL> create pfile from spfile;

 

  • .db_create_file_dest='/mnt/oradata/bk'
  • .db_unique_name='bk'
  • .db_create_online_log_dest_1='/opt/oracle'
  • .db_create_online_log_dest_2='/opt/oradata'



We clone the passwordfile

oracle@myhost:/mnt/app/oracle/OraHome_2/dbs $ cp orapwMYDB orapwbk



Before starting the clone operation I manually set the NLS_LANG environment and we startup nomount the bk instance

export ORACLE_SID=bk

export NLS_LANG=american_america.AL32UTF8

oracle@myhost:/mnt/app/oracle/OraHome_2/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;


We connect with RMAN to the source database - the one we wanna clone - and we use the auxiliary command to connect with the previously started instance bk

oracle@myhost:/home/oracle $ rman target sys/admin_steria\@MY_DB


Recovery Manager: Release 10.2.0.3.0 - Production on Mon Mar 17 10:39:44 2008

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

connected to target database: MY_DB (DBID=3954204418)

RMAN> connect auxiliary sys/admin_steria

connected to auxiliary database: BK (not mounted)



And we are ready to invoke the clone operation, note we just type the 2 command lines below and RMAN is doing everything for us. You may want to tail -f the new alertfile of the new database. 

RMAN> duplicate target database to "bk"

2> until scn 6485576;

 

This is the screen output

Starting Duplicate Db at 17-MAR-08

using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=543 devtype=DISK

contents of Memory Script:
{
   set until scn  6485576;
   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 /mnt/oradata/bk/BK/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /mnt/oradata/bk/BK/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00003 to /mnt/oradata/bk/BK/datafile/o1_mf_sysaux_%u_.dbf
restoring datafile 00004 to /mnt/oradata/bk/BK/datafile/o1_mf_users_%u_.dbf
restoring datafile 00005 to /mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_a_%u_.dbf
restoring datafile 00006 to /mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_%u_.dbf
restoring datafile 00007 to /mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_%u_.dbf
restoring datafile 00008 to /mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_%u_.dbf
restoring datafile 00009 to /mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_%u_.dbf
restoring datafile 00010 to /mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_%u_.dbf
restoring datafile 00011 to /mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_%u_.dbf
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: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=163
channel ORA_AUX_DISK_1: reading from backup piece /mnt/oraarc/flash_recovery_area/MY_DB/backupset/2008_03_17/o1_mf_annnn_TAG20080317T001241_3xvbcc09_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/oraarc/flash_recovery_area/MY_DB/backupset/2008_03_17/o1_mf_annnn_TAG20080317T001241_3xvbcc09_.bkp tag=TAG20080317T001241
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
archive log filename=/mnt/oraarc/flash_recovery_area/BK/archivelog/2008_03_17/o1_mf_1_163_3xwjzgv9_.arc thread=1 sequence=163
channel clone_default: deleting archive log(s)
archive log filename=/mnt/oraarc/flash_recovery_area/BK/archivelog/2008_03_17/o1_mf_1_163_3xwjzgv9_.arc recid=1 stamp=649595526
archive log filename=/mnt/oradata/MY_DB/archive/1_164_627058887.dbf thread=1 sequence=164
media recovery complete, elapsed time: 00:00:19
Finished recover at 17-MAR-08

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
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 statement: CREATE CONTROLFILE REUSE SET DATABASE "BK" 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
  '/mnt/oradata/bk/BK/datafile/o1_mf_system_3xwhjd07_.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   set newname for clone tempfile  2 to new;
   set newname for clone tempfile  3 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_undotbs1_3xwhjd88_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_sysaux_3xwhjdf6_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_users_3xwhjdor_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_a_3xwhjdtg_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjdyr_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjfc9_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjg2c_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhlomx_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvjy0_.dbf";
   catalog clone datafilecopy  "/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvko2_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /mnt/oradata/bk/BK/datafile/o1_mf_temp_%u_.tmp in control file
renamed temporary file 2 to /mnt/oradata/bk/BK/datafile/o1_mf_temp01_%u_.tmp in control file
renamed temporary file 3 to /mnt/oradata/bk/BK/datafile/o1_mf_temp02_%u_.tmp in control file

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_undotbs1_3xwhjd88_.dbf
recid=1 stamp=649595565

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_sysaux_3xwhjdf6_.dbf
recid=2 stamp=649595565

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_users_3xwhjdor_.dbf
recid=3 stamp=649595566

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_a_3xwhjdtg_.dbf
recid=4 stamp=649595566

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjdyr_.dbf
recid=5 stamp=649595566

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjfc9_.dbf
recid=6 stamp=649595566

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjg2c_.dbf
recid=7 stamp=649595567

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhlomx_.dbf
recid=8 stamp=649595567

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvjy0_.dbf
recid=9 stamp=649595567

cataloged datafile copy
datafile copy filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvko2_.dbf
recid=10 stamp=649595568

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=649595565 filename=/mnt/oradata/bk/BK/datafile/o1_mf_undotbs1_3xwhjd88_.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=649595565 filename=/mnt/oradata/bk/BK/datafile/o1_mf_sysaux_3xwhjdf6_.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=649595566 filename=/mnt/oradata/bk/BK/datafile/o1_mf_users_3xwhjdor_.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=649595566 filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_a_3xwhjdtg_.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=649595566 filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjdyr_.dbf
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=649595566 filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjfc9_.dbf
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=649595567 filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjg2c_.dbf
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=649595567 filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhlomx_.dbf
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=649595567 filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvjy0_.dbf
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=649595568 filename=/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvko2_.dbf

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

database opened
Finished Duplicate Db at 17-MAR-08


Still there ? Where are the new datafiles, controlfiles, redolog files and tempfiles ?
Apparently they are created in

1. db_create_file_dest : both tempfiles and datafiles
2. db_create_onlne_log_dest_1 : member 1 redolog group and controlfile
3. db_create_onlne_log_dest_2 : member 2 redolog group and controlfile

If you don' t use OMF you may want to use

1. SET NEWNAME
2. DB_FILE_NAME_CONVERT
3. LOG_FILE_NAME_CONVERT

in order to manage the new file destinations

SQL> select member from v$logfile;


MEMBER


/opt/oracle/BK/onlinelog/o1_mf_8_3xwk4pdk_.log
/opt/oradata/BK/onlinelog/o1_mf_8_3xwk52o1_.log
/opt/oracle/BK/onlinelog/o1_mf_7_3xwk3vx0_.log
/opt/oradata/BK/onlinelog/o1_mf_7_3xwk4cko_.log
/opt/oracle/BK/onlinelog/o1_mf_6_3xwk2vqc_.log
/opt/oradata/BK/onlinelog/o1_mf_6_3xwk3c23_.log
/opt/oracle/BK/onlinelog/o1_mf_5_3xwk1ypf_.log
/opt/oradata/BK/onlinelog/o1_mf_5_3xwk2fv0_.log
/opt/oracle/BK/onlinelog/o1_mf_4_3xwk1239_.log
/opt/oradata/BK/onlinelog/o1_mf_4_3xwk1khh_.log

10 rows selected.

SQL> select name from v$controlfile;

NAME


/opt/oracle/BK/controlfile/o1_mf_3xwk0q83_.ctl
/opt/oradata/BK/controlfile/o1_mf_3xwk0r1s_.ctl

SQL> select name from v$tempfile;

NAME


/mnt/oradata/bk/BK/datafile/o1_mf_temp_3xwk5mm4_.tmp
/mnt/oradata/bk/BK/datafile/o1_mf_temp01_3xwk5n1y_.tmp
/mnt/oradata/bk/BK/datafile/o1_mf_temp02_3xwk5nft_.tmp

SQL> select name from v$datafile;

NAME


/mnt/oradata/bk/BK/datafile/o1_mf_system_3xwhjd07_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_undotbs1_3xwhjd88_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_sysaux_3xwhjdf6_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_users_3xwhjdor_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_a_3xwhjdtg_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjdyr_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjfc9_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhjg2c_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhlomx_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvjy0_.dbf
/mnt/oradata/bk/BK/datafile/o1_mf_ts_mydb_l_3xwhvko2_.dbf

What is the dbid from the new database bk ?

SQL> select dbid from v$database;


      DBID


 589716967

What is the dbid from the target database MY_DB ?

oracle@myhost:/home/oracle $ export ORACLE_SID=MYDB

SQL> select dbid from v$database;

      DBID


3954204418


When we clone a database with RMAN' duplicate indicated as above there will be a new dbid.
When we clone a database with RMAN' duplicate for standby the dbid of both primary and standby will stay equal ( which is mandatory )

Once we do not need our cloned db anymore we can drop it