RMAN
Clone database with RMAN on the same host
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 21:01
-
Last Updated on Tuesday, 10 December 2019 15:07
-
Published on Thursday, 18 March 2010 21:01
-
Hits: 8617
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