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:38The 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:00To 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 bytesConnecting 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:07The 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:00The PROD database @vsi10 server must run always in NOMOUNT mode to successfully resynchronize it:
SQL@vsi10> shutdown immediate;
...
SQL@vsi10> startup nomount ...Using the same duplicate target database command as above on vsi10 server I can resynchronize 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:17So 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:31With 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 untill 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:09Again 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:01PROD 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 resynchronize 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
That's all.
26 comments:
When some one searches for his vital thing, therefore he/she needs to be available that in detail, so that thing is maintained over here.
my web page - funny pictures of celebrities
It's hard to find well-informed people in this particular topic, but you seem like you know what you're talκing about!
Thankѕ
Here is my web page http://dig.gr
It іs actuallу a nice and helpful piеce of informatіon.
ӏ am hаppy that you shared this hеlрful infοrmation
wіth us. Please stay us infοrmeԁ like thiѕ.
Thank you for sharing.
my webpage; alojamiento web
Hi, еverу timе i uѕed to checκ
blοg posts here in the earlу houгѕ in the morning,
since i like to learn morе аnd mοre.
Look at my page: CRIAR SITE
I like looking through a post that will make men and women think.
Also, thanks for allowing me to comment!
my page: calories burned walking calculator
Appreciating the time and energy you put into your website and detailed information you provide.
It's great to come across a blog every once in a while that isn't the
same out of date rehashed material. Great read! I've bookmarked your site and I'm adding
your RSS feeds to my Google account.
Here is my web blog ... Funny
hey there and thank you for your information – I've definitely picked up something new from right here. I did however expertise a few technical issues using this website, as I experienced to reload the web site many times previous to I could get it to load properly. I had been wondering if your web host is OK? Not that I am complaining, but slow loading instances times will very frequently affect your placement in google and can damage your high-quality score if advertising and marketing with Adwords. Anyway I am adding this RSS to my email and can look out for much more of your respective interesting content. Make sure you update this again very soon.
Also visit my blog post - www.essweb.com
Thanks for sharing your thoughts about LOL videos.
Regards
my web blog Funny Videos
We're a gaggle of volunteers and starting a brand new scheme in our community. Your web site offered us with useful info to work on. You have done an impressive task and our whole neighborhood will likely be thankful to you.
My weblog - Winrar unlocker
fantastic points altogether, you just received a brand new reader.
What would you recommend in regards to your put up that you
made a few days ago? Any sure?
my blog post - bypass sharecash
Yes! Finally someone writes about dragonvale cheats jailbreak.
Have a look at my web site; the logo game iphone cheats
Wow! At last I got a website from where I know how to actually obtain helpful data regarding my study and knowledge.
my webpage - saffron extract
When someone writes an piece of writing he/she retains
the plan of a user in his/her mind that how a user can be aware of it.
Thus that's why this post is great. Thanks!
My web site - sharecash downloader
Every weekend i used to go to see this web page, as i want enjoyment,
as this this website conations truly good funny data too.
Have a look at my homepage :: Free PSN Codes
Incredible points. Outstanding arguments. Keep up the
great effort.
Feel free to visit my homepage Forgot my password
Woah! I'm really loving the template/theme of this website. It's simple, yet effective.
A lot of times it's difficult to get that "perfect balance" between superb usability and visual appeal. I must say you have done a awesome job with this. Additionally, the blog loads extremely quick for me on Chrome. Outstanding Blog!
My blog ... funniest home videos cats
Pretty nice post. I just stumbled upon your weblog and
wanted to say that I have truly enjoyed surfing around your blog posts.
After all I'll be subscribing to your feed and I hope you write again very soon!
My website: Natural Remedies For Acid Reflux
If you want a Premium Minecraft Account check out this generator.
With it you can generate a unique Minecraft Premium Account which no one else has!
You can Download the Free Premium Minecraft Account Generator http://www.
MinecraftDownload4Free.tk
I loved as much as you'll receive carried out right here. The sketch is tasteful, your authored subject matter stylish. nonetheless, you command get bought an shakiness over that you wish be delivering the following. unwell unquestionably come more formerly again as exactly the same nearly very often inside case you shield this increase.
This is cool!
oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet
ugg, hollister, abercrombie and fitch, longchamp, nfl jerseys, lululemon outlet, nike trainers, abercrombie and fitch, soccer shoes, valentino shoes, nike roshe, birkin bag, reebok outlet, insanity workout, instyler, mont blanc, vans shoes, new balance shoes, beats by dre, uggs outlet, ugg australia, giuseppe zanotti, rolex watches, nike roshe run, herve leger, babyliss pro, marc jacobs, barbour, ghd, north face outlet, celine handbags, jimmy choo outlet, asics running shoes, mac cosmetics, north face jackets, bottega veneta, wedding dresses, soccer jerseys, ugg boots, chi flat iron, mcm handbags, p90x, ugg pas cher, nike huarache, ferragamo shoes
converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose
Hi , this is a wonderful blog for learning, thank you, needed to ask some questions, if you are taking the backup and scping it to the target server, why are you connecting to the target database and duplicating the database. correct me if i missed anything.
michael kors handbags
longchamp handbags
golden gooses sneakers
golden goose
yeezy
michael kors handbags
ferragamo belt
yeezy shoes
offwhite
coach factory outlet
calvin klein
jordan shoes
off white hoodie
jordan 4
adidas yeezy
supreme
hermes
lebron james shoes
supreme sweatshirt
pandora
take a look at the site here best replica bags click for source high quality replica bags look at this site best replica designer bags
Post a Comment