This post is about re-synchronized a duplicated database: the source database is PROD@vsi08 server and the target database will have the same SID, but located @vsi10 server.
All the steps to set up a duplicated database are already explained in this previous post.
Let’s duplicate for the first time our target database PROD located on a different server (vsi10) using a backup taken from the source database PROD located on vsi08 server: the duplicated database have the same SID and directories structure of source database.
So I need to take a backup of PROD database at vsi08 server
[oracle@vsi08 ~]$ export NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS' [oracle@vsi08 ~]$ export ORACLE_SID=PROD [oracle@vsi08 ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 11:46:59 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD (DBID=223010867) RMAN> backup database plus archivelog delete input; Starting backup at 04-04-2013 11:47:07 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=65 RECID=18 STAMP=811856828 channel ORA_DISK_1: starting piece 1 at 04-04-2013 11:47:09 channel ORA_DISK_1: finished piece 1 at 04-04-2013 11:47:10 piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114709_8otm0xgs_.bkp tag=TAG20130404T114709 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_65_8otm0wbf_.arc RECID=18 STAMP=811856828 Finished backup at 04-04-2013 11:47:10 Starting backup at 04-04-2013 11:47:10 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/opt/app/oracle/oradata/PROD/system01.dbf input datafile file number=00002 name=/opt/app/oracle/oradata/PROD/sysaux01.dbf input datafile file number=00003 name=/opt/app/oracle/oradata/PROD/undotbs01.dbf input datafile file number=00004 name=/opt/app/oracle/oradata/PROD/users01.dbf channel ORA_DISK_1: starting piece 1 at 04-04-2013 11:47:10 channel ORA_DISK_1: finished piece 1 at 04-04-2013 11:47:35 piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 Finished backup at 04-04-2013 11:47:35 Starting backup at 04-04-2013 11:47:35 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=66 RECID=19 STAMP=811856855 channel ORA_DISK_1: starting piece 1 at 04-04-2013 11:47:35 channel ORA_DISK_1: finished piece 1 at 04-04-2013 11:47:36 piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otm1qsm_.arc RECID=19 STAMP=811856855 Finished backup at 04-04-2013 11:47:36 Starting Control File and SPFILE Autobackup at 04-04-2013 11:47:37 piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 04-04-2013 11:47:38
The available backups for PROD database @vsi08 server are the following:
RMAN> list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 23 30.68M DISK 00:00:01 04-04-2013 11:47:10 BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20130404T114709 Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114709_8otm0xgs_.bkp List of Archived Logs in backup set 23 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 65 743889 03-04-2013 15:55:16 777811 04-04-2013 11:47:07 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 24 Full 740.51M DISK 00:00:23 04-04-2013 11:47:33 BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20130404T114710 Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp List of Datafiles in backup set 24 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 Full 777822 04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/system01.dbf 2 Full 777822 04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/sysaux01.dbf 3 Full 777822 04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/undotbs01.dbf 4 Full 777822 04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/users01.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 25 4.00K DISK 00:00:01 04-04-2013 11:47:36 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20130404T114735 Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp List of Archived Logs in backup set 25 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 66 777811 04-04-2013 11:47:07 777836 04-04-2013 11:47:35 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 26 Full 9.36M DISK 00:00:00 04-04-2013 11:47:37 BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20130404T114737 Piece Name: /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp SPFILE Included: Modification time: 03-04-2013 14:53:02 SPFILE db_unique_name: PROD Control File Included: Ckp SCN: 777847 Ckp time: 04-04-2013 11:47:37
I copy all backup sets from vsi08 to vsi10 server, creating the same directories on the remote server:
[oracle@vsi08 PROD]$ scp -r /opt/app/oracle/flash_recovery_area/PROD/*back* vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PRODoracle@vsi10.MYDOMAIN.it's password: o1_mf_s_811856857_8otm1s7l_.bkp 100% 9600KB 9.4MB/s 00:00 o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp 100% 4608 4.5KB/s 00:00 o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp 100% 741MB 39.0MB/s 00:19 o1_mf_annnn_TAG20130404T114709_8otm0xgs_.bkp 100% 31MB 30.7MB/s 00:00
To start the duplicate process I need to start in NOMOUNT mode the instance I want to duplicate:
[oracle@vsi10 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 4 11:51:17 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL@vsi10> startup nomount; ORACLE instance started. Total System Global Area 1686925312 bytes Fixed Size 2213976 bytes Variable Size 1006634920 bytes Database Buffers 671088640 bytes Redo Buffers 6987776 bytes
Connecting from vsi10 server to the target (@vsi08 server) database and to the auxiliary (@vsi10 server) I can execute the duplicate target database command.
[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 11:55:36 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database to PROD nofilenamecheck;
Starting Duplicate Db at 04-04-2013 11:56:52
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
Starting restore at 04-04-2013 11:57:00
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp tag=TAG20130404T114737
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 11:57:03
database mounted
contents of Memory Script:
{
set until scn 777836;
set newname for datafile 1 to
"/opt/app/oracle/oradata/PROD/system01.dbf";
set newname for datafile 2 to
"/opt/app/oracle/oradata/PROD/sysaux01.dbf";
set newname for datafile 3 to
"/opt/app/oracle/oradata/PROD/undotbs01.dbf";
set newname for datafile 4 to
"/opt/app/oracle/oradata/PROD/users01.dbf";
restore
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
Starting restore at 04-04-2013 11:57:08
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 11:57:53
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
set until scn 777836;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-04-2013 11:57:53
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otmo2js_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otmo2js_.arc RECID=20 STAMP=811857474
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-04-2013 11:57:55
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''PROD'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
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 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: alter system set db_name = ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/opt/app/oracle/oradata/PROD/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/opt/app/oracle/oradata/PROD/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/opt/app/oracle/oradata/PROD/sysaux01.dbf",
"/opt/app/oracle/oradata/PROD/undotbs01.dbf",
"/opt/app/oracle/oradata/PROD/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811857493
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811857493
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811857493
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811857493 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811857493 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811857493 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 04-04-2013 11:58:19
The database was duplicated using a backup coming from the target server. Let’s see if the two databases have the same data.
[oracle@vsi08 PROD]$ sqlplus / as sysdba SQL@vsi08> select count(*) from sys.test; COUNT(*) ---------- 1000
On vsi10 server I have of course the same amount of data:
[oracle@vsi10 ~]$ sqlplus / as sysdba SQL@vsi10> select count(*) from sys.test; COUNT(*) ---------- 1000
Now on vsi08 server (the target database) I executes some switch of logfile, inserting 2000 more rows on SYS.TEST table:
[oracle@vsi08 PROD]$ sqlplus / as sysdba SQL@vsi08> alter system switch logfile; System altered. SQL@vsi08> insert into sys.test select level from dual connect by level<1001; 1000 rows created. SQL@vsi08> commit; Commit complete. SQL@vsi08> alter system switch logfile; System altered. SQL@vsi08> insert into sys.test select level from dual connect by level<1001; 1000 rows created. SQL@vsi08> commit; Commit complete. SQL@vsi08> select count(*) from sys.test; COUNT(*) ---------- 3000
I would like to resynchronize the already duplicated database with these 2000 rows so I first have to backup the new archived redo log @vsi08 server:
[oracle@vsi08 PROD]$ rman target / ... RMAN> backup archivelog all delete input; Starting backup at 04-04-2013 12:05:03 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=67 RECID=20 STAMP=811857748 input archived log thread=1 sequence=68 RECID=21 STAMP=811857778 input archived log thread=1 sequence=69 RECID=22 STAMP=811857905 channel ORA_DISK_1: starting piece 1 at 04-04-2013 12:05:05 channel ORA_DISK_1: finished piece 1 at 04-04-2013 12:05:06 piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otmxn7m_.arc RECID=20 STAMP=811857748 archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otmylvo_.arc RECID=21 STAMP=811857778 archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otn2kfr_.arc RECID=22 STAMP=811857905 Finished backup at 04-04-2013 12:05:06 Starting Control File and SPFILE Autobackup at 04-04-2013 12:05:06 piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 04-04-2013 12:05:07 The new backup sets need to be copied into the appropriate directories on the remote vsi10 server: [oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04 oracle@vsi10.MYDOMAIN.it's password: o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp 100% 985KB 984.5KB/s 00:00 [oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04 oracle@vsi10.MYDOMAIN.it's password: o1_mf_s_811857906_8otn2lxd_.bkp 100% 9600KB 9.4MB/s 00:00
The PROD database @vsi10 server must run always in NOMOUNT mode to successfully re-synchronize it:
SQL@vsi10> shutdown immediate; ... SQL@vsi10> startup nomount ...
Using the same duplicate target database command as above on vsi10 server I can re-synchronize it with the new 2000 rows.
[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 12:19:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database to PROD nofilenamecheck;
Starting Duplicate Db at 04-04-2013 12:19:28
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
Starting restore at 04-04-2013 12:19:35
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp tag=TAG20130404T120506
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 12:19:39
database mounted
contents of Memory Script:
{
set until scn 778560;
set newname for datafile 1 to
"/opt/app/oracle/oradata/PROD/system01.dbf";
set newname for datafile 2 to
"/opt/app/oracle/oradata/PROD/sysaux01.dbf";
set newname for datafile 3 to
"/opt/app/oracle/oradata/PROD/undotbs01.dbf";
set newname for datafile 4 to
"/opt/app/oracle/oradata/PROD/users01.dbf";
restore
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
Starting restore at 04-04-2013 12:19:43
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 12:20:28
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
set until scn 778560;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-04-2013 12:20:28
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otnzfly_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otnzfly_.arc RECID=23 STAMP=811858829
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=67
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=68
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=69
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otnzgvh_.arc thread=1 sequence=67
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otnzgvh_.arc RECID=24 STAMP=811858830
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otnzgwf_.arc thread=1 sequence=68
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otnzgwf_.arc RECID=26 STAMP=811858830
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otnzgw1_.arc thread=1 sequence=69
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otnzgw1_.arc RECID=25 STAMP=811858830
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-04-2013 12:20:32
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''PROD'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
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 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: alter system set db_name = ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/opt/app/oracle/oradata/PROD/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/opt/app/oracle/oradata/PROD/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/opt/app/oracle/oradata/PROD/sysaux01.dbf",
"/opt/app/oracle/oradata/PROD/undotbs01.dbf",
"/opt/app/oracle/oradata/PROD/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811858850
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811858850
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811858850
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811858850 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811858850 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811858850 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 04-04-2013 12:20:56
Let’s see how many rows are into SYS.TEST table
[oracle@vsi10 ~]$ sqlplus / as sysdba ... SQL@vsi10> select count(*) from sys.test; COUNT(*) ---------- 3000
That’s all right.
My duplicated database is really synchronized with PROD database @vsi08 server: I have the same amount of rows into SYS.TEST table.
But now I want to go back in time on my vsi10 server and exactly I want to go back few minutes before I inserted 2000 more rows on vsi08 server.
After I inserted those rows on PROD database @vsi08 server I took a backup of the archived redo log: as you can seen above that backup started at 04-04-2013 12:05:03 (search in this post the following text: Starting backup at 04-04-2013 12:05:03) and, since it includes the new 2000 rows, I need simply to do not apply it during the duplicate process.
Let’s see what my current time is:
SQL@vsi10> select sysdate from dual; SYSDATE ------------------- 04-04-2013 12:38:17
So I need to go back in time for about 40 minutes that is before the backup including the new 2000 rows started (11:58:31 < 12:05:03)
SQL@vsi10> select sysdate-(40/(24*60)) from dual; SYSDATE-(40/(24*60) ------------------- 04-04-2013 11:58:31
With the already available backups copied previously on vsi10 server I start in NOMOUNT mode the instance…
SQL@vsi10> shutdown immediate; ... SQL@vsi10> startup nomount; ...
…and then I simply execute once again the duplicate target database command using also the until time clause.
In this way I’m able to have a complete copy on vsi10 server of the PROD database that was running on vsi08 server untill ’04-04-2013 11:58:31′ time:
[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 12:40:51 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database to PROD until time 'sysdate-(40/(24*60))' nofilenamecheck;
Starting Duplicate Db at 04-04-2013 12:40:59
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
set until scn 777847;
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
executing command: SET until clause
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
Starting restore at 04-04-2013 12:41:07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp tag=TAG20130404T114737
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 12:41:10
database mounted
contents of Memory Script:
{
set until scn 777847;
set newname for datafile 1 to
"/opt/app/oracle/oradata/PROD/system01.dbf";
set newname for datafile 2 to
"/opt/app/oracle/oradata/PROD/sysaux01.dbf";
set newname for datafile 3 to
"/opt/app/oracle/oradata/PROD/undotbs01.dbf";
set newname for datafile 4 to
"/opt/app/oracle/oradata/PROD/users01.dbf";
restore
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
Starting restore at 04-04-2013 12:41:15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 12:42:00
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
set until time "sysdate-(40/(24*60))";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-04-2013 12:42:00
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otp7sqx_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otp7sqx_.arc RECID=20 STAMP=811860121
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=67
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otp7v1l_.arc thread=1 sequence=67
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otp7v1l_.arc RECID=21 STAMP=811860123
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-04-2013 12:42:04
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''PROD'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
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 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: alter system set db_name = ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/opt/app/oracle/oradata/PROD/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/opt/app/oracle/oradata/PROD/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/opt/app/oracle/oradata/PROD/sysaux01.dbf",
"/opt/app/oracle/oradata/PROD/undotbs01.dbf",
"/opt/app/oracle/oradata/PROD/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811860141
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811860141
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811860141
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811860141 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811860141 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811860141 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 04-04-2013 12:42:29
As you can verify the above RMAN command didn’t apply the backup set o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp containing the 2000 rows: indeed when I query the SYS.TEST table I can see only the first 1000 rows.
[oracle@vsi10 ~]$ sqlplus / as sysdba ... SQL@vsi10> select count(*) from sys.test; COUNT(*) ---------- 1000
Let’s insert on PROD database @vsi08 server other 2000 rows:
[oracle@vsi08 PROD]$ sqlplus / as sysdba ... SQL@vsi08> select count(*) from sys.test; COUNT(*) ---------- 3000 SQL@vsi08> alter system switch logfile; System altered. SQL@vsi08> insert into sys.test select level from dual connect by level<1001; 1000 rows created. SQL@vsi08> alter system switch logfile; System altered. SQL@vsi08> insert into sys.test select level from dual connect by level<1001; 1000 rows created. SQL@vsi08> select count(*) from sys.test; COUNT(*) ---------- 5000 SQL@vsi08> commit; Commit complete.
Let’s take another backup of the new archived redo logs:
[oracle@vsi08 ~]$ rman target / ... RMAN> backup archivelog all delete input; Starting backup at 04-04-2013 12:49:06 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=16 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=70 RECID=23 STAMP=811860462 input archived log thread=1 sequence=71 RECID=24 STAMP=811860486 input archived log thread=1 sequence=72 RECID=25 STAMP=811860546 channel ORA_DISK_1: starting piece 1 at 04-04-2013 12:49:07 channel ORA_DISK_1: finished piece 1 at 04-04-2013 12:49:08 piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp tag=TAG20130404T124907 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_70_8otplgnq_.arc RECID=23 STAMP=811860462 archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_71_8otpm6bs_.arc RECID=24 STAMP=811860486 archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_72_8otpo2p9_.arc RECID=25 STAMP=811860546 Finished backup at 04-04-2013 12:49:08 Starting Control File and SPFILE Autobackup at 04-04-2013 12:49:08 piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 04-04-2013 12:49:09 Again I have to copy the backup sets on the same directories from vsi08 to vsi10 server: [oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04 oracle@vsi10.MYDOMAIN.it's password: o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp 100% 155KB 154.5KB/s 00:00 [oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04 oracle@vsi10.MYDOMAIN.it's password: o1_mf_s_811860548_8otpo4nr_.bkp 100% 9664KB 9.4MB/s 00:01 PROD database @vsi10 server must be in NOMOUNT mode: [oracle@vsi10 ~]$ sqlplus / as sysdba ... SQL@vsi10> shutdown immediate; ... SQL@vsi10> startup nomount; ...
Execute the same command to re-synchronize the PROD database @vsi10 server:
[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 12:57:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database to PROD nofilenamecheck;
Starting Duplicate Db at 04-04-2013 12:57:38
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
Starting restore at 04-04-2013 12:57:43
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp tag=TAG20130404T124908
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 12:57:46
database mounted
contents of Memory Script:
{
set until scn 779940;
set newname for datafile 1 to
"/opt/app/oracle/oradata/PROD/system01.dbf";
set newname for datafile 2 to
"/opt/app/oracle/oradata/PROD/sysaux01.dbf";
set newname for datafile 3 to
"/opt/app/oracle/oradata/PROD/undotbs01.dbf";
set newname for datafile 4 to
"/opt/app/oracle/oradata/PROD/users01.dbf";
restore
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
Starting restore at 04-04-2013 12:57:51
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 12:58:36
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
set until scn 779940;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-04-2013 12:58:36
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otq6xfh_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otq6xfh_.arc RECID=26 STAMP=811861117
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=67
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=68
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=69
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otq6yqw_.arc thread=1 sequence=67
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otq6yqw_.arc RECID=27 STAMP=811861118
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otq6yrv_.arc thread=1 sequence=68
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otq6yrv_.arc RECID=29 STAMP=811861118
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otq6yrh_.arc thread=1 sequence=69
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otq6yrh_.arc RECID=28 STAMP=811861118
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=70
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=71
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=72
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp tag=TAG20130404T124907
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_70_8otq70q6_.arc thread=1 sequence=70
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_70_8otq70q6_.arc RECID=30 STAMP=811861120
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_71_8otq70s6_.arc thread=1 sequence=71
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_71_8otq70s6_.arc RECID=31 STAMP=811861120
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_72_8otq70sm_.arc thread=1 sequence=72
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_72_8otq70sm_.arc RECID=32 STAMP=811861120
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-04-2013 12:58:42
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''PROD'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
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 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: alter system set db_name = ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/opt/app/oracle/oradata/PROD/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/opt/app/oracle/oradata/PROD/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/opt/app/oracle/oradata/PROD/sysaux01.dbf",
"/opt/app/oracle/oradata/PROD/undotbs01.dbf",
"/opt/app/oracle/oradata/PROD/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811861138
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811861138
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811861138
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811861138 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811861138 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811861138 file name=/opt/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 04-04-2013 12:59:07
Again on PROD database @vsi10 server there are the same rows existing in PROD database @vsi08 server: the two database are again synchronized.
[oracle@vsi10 ~]$ sqlplus / as sysdba SQL@vsi10> select count(*) from sys.test; COUNT(*) ---------- 5000
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
Thanks for the share.