按照文档的介绍,使用duplicate database targetdb incarnation YYY to dupdb可以针对targetdb的某个incarnation进行duplicate,但实际测试下来却有些问题
// 多incarnation环境搭建
假设数据库当前的incarnation key为A,在SCN1时刻创建t0704_11表,在SCN2时刻又创建了t0704_22表,在SCN3时刻drop掉t0704_11、t0704_22表;
发现t0704_11、t0704_22表被删除后,进行了incomplete recovery,recover到SCN1点后open resetlogs,生成新的incarnation key B,立即对数据库进行备份,需要确保备份完成的SCN值要小于SCN2,创建t0704_33表,确保t0704_33表创建时的SCN也要小于SCN2,等待current SCN值大于SCN2值之后,drop掉t0704_33表;
在catalog的情况下执行backup-based duplicate,分别尝试将db恢复到incarnation A的SCN2时刻、incarnation B的SCN2时刻
###tstdb1源库生成一个最新备份
rman target / catalog rman/773946@tstdb2
backup database include current controlfile format '/oradata06/dupuse/incartest/tstdb2_df_%u';
datafile备份:piece handle=/oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4 tag=TAG20150705T193458 comment=NONE
controlfile备份:piece handle=/oradata06/dupuse/incartest/tstdb2_df_7gqbaak7 tag=TAG20150705T193458 comment=NONE
create table t0704_11 tablespace ts0422_1 as select * from all_users;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
SYS@tstdb1-SQL> set numwidth 16
SYS@tstdb1-SQL> select current_scn from v$database;
CURRENT_SCN
----------------
12723363828513
create table t0704_22 tablespace ts0422_1 as select * from all_users;
SYS@tstdb1-SQL> select count(*) from t0704_22;
COUNT(*)
----------------
21
SYS@tstdb1-SQL> select count(*) from t0704_11;
COUNT(*)
----------------
21
---等待足够长时间,以给incarnation B留出一个backup database和建表的时间,确保backup和建表的操作执行完成后SCN仍然小于SCN2
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829157
drop table t0704_11;
drop table t0704_22;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select current_scn from v$database;
CURRENT_SCN
----------------
12723363830194
RMAN> list backup of database completed after '20150705 19:30:00';
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
92402 Full 2.19G DISK 00:00:27 20150705 19:35:27
BP Key: 92406 Status: AVAILABLE Compressed: NO Tag: TAG20150705T193458
Piece Name: /oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4
List of Datafiles in backup set 92402
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/system01.dbf
2 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/users01.dbf
5 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/ts0422_1.dbf
6 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/ts0212_1.dbf
10 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/undotbs3.dbf
***列出recover时需要用到的archivelog
SQL> select sequence# from v$archived_log where next_change#>12723363827456 and resetlogs_change#=12723362562754;
SEQUENCE#
----------------
143
144
145
146
147
148
backup archivelog sequence between 143 and 148 format '/oradata06/dupuse/incartest/tstdb2_arc_%u';
Archivelog备份:piece handle=/oradata06/dupuse/incartest/tstdb2_arc_7hqbabqb tag=TAG20150705T195554 comment=NONE
***当前的incarnation key是91140
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
91139 91140 TSTDB1 2030654775 CURRENT 12723362562754 20150625 10:44:56
###tstdb1 open resetlogs打开,形成一个新的incarnation,dbid和原先保持一致
shutdown immediate;
sqlplus '/as sysdba'
startup nomount
rman target / catalog rman/773946@tstdb2
***恢复到SCN1:12723363828513时刻
MAN> restore controlfile until scn 12723363828513;
Starting restore at 20150705 20:27:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=463 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /oradata06/dupuse/incartest/tstdb2_df_7gqbaak7
channel ORA_DISK_1: piece handle=/oradata06/dupuse/incartest/tstdb2_df_7gqbaak7 tag=TAG20150705T193458
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/testaaaaa/control01.ctl
output file name=/oradata06/testaaaaa/control02.ctl
Finished restore at 20150705 20:27:15
RMAN> restore database until scn 12723363828513;
Starting restore at 20150705 20:27:58
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata06/testaaaaa/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata06/testaaaaa/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata06/testaaaaa/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata06/testaaaaa/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata06/testaaaaa/ts0422_1.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata06/testaaaaa/xdbts1.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oradata06/testaaaaa/ts0212.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oradata06/testaaaaa/ts0212_1.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradata06/testaaaaa/undotbs3.dbf
channel ORA_DISK_1: reading from backup piece /oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4
channel ORA_DISK_1: piece handle=/oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4 tag=TAG20150705T193458
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 20150705 20:29:23
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
***recover database until scn1
run
{
allocate channel d1 device type disk;
recover database until scn 12723363828513;
}
allocated channel: d1
channel d1: SID=530 device type=DISK
Starting recover at 20150705 20:31:23
Starting implicit crosscheck backup at 20150705 20:31:23
Crosschecked 27 objects
Finished implicit crosscheck backup at 20150705 20:31:24
Starting implicit crosscheck copy at 20150705 20:31:24
Finished implicit crosscheck copy at 20150705 20:31:25
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_136_1kf2sHC1D_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_137_1kf2sO192_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_139_1kf3KCnue_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_138_1kf3KCpeO_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_141_1kf3mjXkA_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_140_1kf3mjWms_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_143_1kfdtvzAq_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_142_1kfdtvoMB_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_145_1kfe76afR_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_144_1kfe76Z1y_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_145_1kfhlkmug_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_146_1kfhlktc4_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_144_1kfhll3pW_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_137_1kg8TVNAW_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_136_1kg8TVKkE_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_139_1kg94IPtL_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_138_1kg94IR0M_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_140_1kg94bcrC_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_141_1kg9H_x6L_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_142_1kg9H_yM2_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kg9P1Q8u_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kg9dt_9p_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kg9e0CM__.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kg9eO9mN_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_147_1kg9eYJKr_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_148_1kgAnGKwI_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgAnHJWT_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_147_1kgAnHPIC_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_1_1kgAuFrWR_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_2_1kgB5juPx_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_3_1kgCOa0fM_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_137_1kgqPPjZS_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_136_1kgqPPhRZ_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_139_1kgqxkv-j_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_138_1kgqxkyR0_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_140_1kgqy5EYZ_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_141_1kgqyIGQx_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kgvP1hFx_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kgvP1jiB_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kgvP7FF__.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgwBp9Zh_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_147_1kgwBpJKh_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_148_1kgwBpX-C_.arc
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_04/o1_mf_s_884155780_1ke-6nyJC_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_04/o1_mf_s_884160047_1kf2575JA_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_04/o1_mf_s_884161722_1kf3e-Yih_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_05/o1_mf_s_884282538_1kgqA82V-_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_05/o1_mf_s_884283494_1kgr386uR_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_07_05/o1_mf_nnsnf_TAG20150705T190710_1kgtoFq4d_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T104507_1kV7Y7e_d_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T163249_1kVQzYYJt_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_nnndf_TAG20150627T160630_1kWfzM_ge_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_ncsnf_TAG20150627T160630_1kWf_X2lE_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_nnndf_TAG20150627T163324_1kWhTZ3zY_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_ncsnf_TAG20150627T163324_1kWhVjGiv_.bkp
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/05/2015 20:31:26
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog
***调查上述错误的原因,连接target查看database incarnation,发现自动回到了最新的incarnation
rman target /
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
3 3 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
2 2 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
4 4 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
5 5 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
6 6 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
7 7 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
8 8 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
9 9 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
10 10 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
11 11 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
12 12 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
13 13 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
14 14 TSTDB1 2030654775 CURRENT 12723363819873 20150705 05:41:37
在recover的过程中RMAN自动将FRA里的备份信息都Catalog进控制文件,导致数据库切换到了最新的incarnation,对于这个问题MOS上有个文档说明了这个问题
此时我们要做的是手工将incarnation切到我们所需要的版本上
RMAN> reset database to incarnation 13;
database reset to incarnation 13
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
13 13 TSTDB1 2030654775 CURRENT 12723362562754 20150625 10:44:56
14 14 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
###再次进行recover
run
{
allocate channel d1 device type disk;
recover database until scn 12723363828513;
}
allocated channel: d1
channel d1: SID=463 device type=DISK
Starting recover at 20150705 20:46:12
starting media recovery
archived log for thread 1 with sequence 138 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_138_1kgqxkyR0_.arc
archived log for thread 1 with sequence 139 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_139_1kgqxkv-j_.arc
archived log for thread 1 with sequence 140 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_140_1kgqy5EYZ_.arc
archived log for thread 1 with sequence 141 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_141_1kgqyIGQx_.arc
archived log for thread 1 with sequence 142 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_142_1kfdtvoMB_.arc
archived log for thread 1 with sequence 143 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kgvP1hFx_.arc
archived log for thread 1 with sequence 144 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kgvP1jiB_.arc
archived log for thread 1 with sequence 145 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kgvP7FF__.arc
archived log for thread 1 with sequence 146 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgwBp9Zh_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kgvP1hFx_.arc thread=1 sequence=143
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kgvP1jiB_.arc thread=1 sequence=144
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kgvP7FF__.arc thread=1 sequence=145
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgwBp9Zh_.arc thread=1 sequence=146
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150705 20:46:16
released channel: d1
***recover后检查datafile header的checkpoing_change#=SCN1+1
col name format a50
set linesize 150 pagesize 80 numwidth 16
SQL> select checkpoint_change#,file#,name,status from v$datafile_header;
CHECKPOINT_CHANGE# FILE# NAME STATUS
------------------ ---------------- -------------------------------------------------- -------
12723363828513 1 /oradata06/testaaaaa/system01.dbf ONLINE
12723363828513 2 /oradata06/testaaaaa/sysaux01.dbf ONLINE
12723363828513 3 /oradata06/testaaaaa/undotbs01.dbf ONLINE
12723363828513 4 /oradata06/testaaaaa/users01.dbf ONLINE
12723363828513 5 /oradata06/testaaaaa/ts0422_1.dbf ONLINE
12723363828513 6 /oradata06/testaaaaa/xdbts1.dbf ONLINE
12723363828513 7 /oradata06/testaaaaa/ts0212.dbf ONLINE
12723363828513 8 /oradata06/testaaaaa/ts0212_1.dbf ONLINE
12723363828513 10 /oradata06/testaaaaa/undotbs3.dbf ONLINE
alter database open resetlogs;
**确认当前scn远小于SCN2:12723363829157 再进行下面的备份
select current_scn from v$database;
CURRENT_SCN
----------------
12723363828778
backup database include current controlfile format '/oradata06/dupuse/incartest/new_tstdb2_df_%u'
datafile备份:piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp tag=TAG20150705T204823 comment=NONE
controlfile备份:piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets tag=TAG20150705T204823 comment=NONE
**备份完后确认当前SCN亦小于SCN2:12723363829157
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829016
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
40258 40259 SHZW 617151977 CURRENT 1 20120920 14:52:25
91139 91164 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
91139 91165 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
91139 91166 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
91139 91167 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
91139 91168 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
91139 91169 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
91139 91170 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
91139 91171 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
91139 91172 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
91139 91173 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
91139 91174 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
91139 91175 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
91139 91769 UNKNOWN 2030654775 ORPHAN 12723362562754 20150625 10:08:09
91139 91140 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
91139 92114 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
91139 92839 TSTDB1 2030654775 CURRENT 12723363828514 20150705 20:47:27
77120 77121 TSTDB2 2720835966 CURRENT 1 20150302 14:15:26
***是否恢复到SCN1:12723363828512的检查
SQL> select count(*) from t0704_11;
COUNT(*)
----------------
21
SQL> select count(*) from t0704_22;
select count(*) from t0704_22
*
ERROR at line 1:
ORA-00942: table or view does not exist
***确认当前SCN小于等于SCN2:12723363829157,再创建表t0704_33
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829042
create table t0704_33 tablespace ts0422_1 as select * from all_users;
SQL> select count(*) from t0704_33;
COUNT(*)
----------------
21
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
***确认t0704_33表存在时刻的SCN小于SCN2: 12723363829157
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829113
***由于是新的incarnation所以archivelog从1开始编号,能确定备份范围为1~4
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
backup archivelog sequence between 1 and 4 format '/oradata06/dupuse/incartest/new_tstdb2_arc_%u';
归档备份:piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7kqbaf6v tag=TAG20150705T205350 comment=NONE
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829329 =SCN2:12723363829157后再执行下面的步骤
***确保下列查询有返回,否则执行alter system switch logfile将需要recover的日志归档出来
select sequence#,first_change#,next_change# from v$archived_log where 12723363829157 between first_change# and next_change#;
// duplicate时不加incarnation参数,仅连接target进行duplicate,能够正常恢复到当前incarnation的指定SCN
###先把/oradata06/dupuse/incartest/目录下的备份都传输到dup db的同名目录下
oracle@jq570322b:/oradata06/dupuse/incartest>ls -rlt
total 11611568
-rw-r----- 1 oracle oinstall 2356092928 Jul 05 19:35 tstdb2_df_7fqbaaj4
-rw-r----- 1 oracle oinstall 14385152 Jul 05 19:35 tstdb2_df_7gqbaak7
-rw-r----- 1 oracle oinstall 220084224 Jul 05 19:55 tstdb2_arc_7hqbabqb
-rw-r----- 1 oracle oinstall 3339714560 Jul 05 20:48 new_tstdb2_df_7iqbaesp
-rw-r----- 1 oracle oinstall 14385152 Jul 05 20:49 new_tstdb2_df_7jqbaets
-rw-r----- 1 oracle oinstall 436224 Jul 05 20:53 new_tstdb2_arc_7kqbaf6v
scp /oradata06/dupuse/incartest/* oracle@10.10.149.110:/oradata06/dupuse/incartest/
###准备dup db3实例参数,启动dupdb3实例
db_name=dupdb3
control_files='/oradata06/testccccc/control01.ctl','/oradata06/testccccc/control02.ctl'
log_file_name_convert='/oradata06/testaaaaa/','/oradata06/testccccc/'
export ORACLE_SID=dupdb3
sqlplus '/as sysdba'
startup nomount
###执行duplicate ...
dupdb目录下的所有备份文件如下:
oracle@qcp570717a:/oracle>ls -rlt /oradata06/dupuse/incartest/
total 11638688
-rw-r----- 1 oracle oinstall 436224 Jul 5 21:01 new_tstdb2_arc_7kqbaf6v
-rw-r----- 1 oracle oinstall 3339714560 Jul 5 21:05 new_tstdb2_df_7iqbaesp
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:05 new_tstdb2_df_7jqbaets
-rw-r----- 1 oracle oinstall 220084224 Jul 5 21:06 tstdb2_arc_7hqbabqb
-rw-r----- 1 oracle oinstall 2356092928 Jul 5 21:09 tstdb2_df_7fqbaaj4
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:09 tstdb2_df_7gqbaak7
rman target sys/773946@tstdb1 auxiliary sys/asdf3_14@dupdb3
RMAN> duplicate database to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
Starting Duplicate Db at 20150705 21:05:49
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=332 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/05/2015 21:05:50
RMAN-05501: aborting duplication of target database
RMAN-06457: UNTIL SCN (12723363829157) is ahead of last SCN in archived logs (12723363829128)
上述提示SCN 12723363829157所在的日志没有归档出来
SQL> select FIRST_CHANGE#,NEXT_CHANGE#,sequence# from v$log where 12723363829157 between FIRST_CHANGE# and NEXT_CHANGE#;
FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
---------------- ---------------- ----------------
12723363829128 281474976710655 5
alter system switch logfile;
RMAN> backup archivelog sequence 5 format '/oradata06/dupuse/incartest/new_tstdb2_arc_%u';
piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u tag=TAG20150705T211125 comment=NONE
传输到dup db的同名目录下
scp /oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u oracle@10.10.149.110:/oradata06/dupuse/incartest/
至此dup db的/oradata06/dupuse/incartest/目录下有
-rw-r----- 1 oracle oinstall 436224 Jul 5 21:01 new_tstdb2_arc_7kqbaf6v
-rw-r----- 1 oracle oinstall 3339714560 Jul 5 21:05 new_tstdb2_df_7iqbaesp
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:05 new_tstdb2_df_7jqbaets
-rw-r----- 1 oracle oinstall 220084224 Jul 5 21:06 tstdb2_arc_7hqbabqb
-rw-r----- 1 oracle oinstall 2356092928 Jul 5 21:09 tstdb2_df_7fqbaaj4
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:09 tstdb2_df_7gqbaak7
-rw-r----- 1 oracle oinstall 5720064 Jul 5 21:20 new_tstdb2_arc_7lqbag7u
###再次进行duplicate
RMAN> duplicate database to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
Starting Duplicate Db at 20150705 21:21:46
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=332 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
contents of Memory Script:
{
set until scn 12723363829157;
sql clone "alter system set db_name =
''TSTDB1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUPDB3'' 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 = ''TSTDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUPDB3'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
Starting restore at 20150705 21:22:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=266 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 /oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/testccccc/control01.ctl
output file name=/oradata06/testccccc/control02.ctl
Finished restore at 20150705 21:22:13
database mounted
contents of Memory Script:
{
set until scn 12723363829157;
set newname for datafile 1 to
"/oradata06/testccccc/system01.dbf";
set newname for datafile 2 to
"/oradata06/testccccc/sysaux01.dbf";
set newname for datafile 3 to
"/oradata06/testccccc/undotbs01.dbf";
set newname for datafile 4 to
"/oradata06/testccccc/users01.dbf";
set newname for datafile 5 to
"/oradata06/testccccc/ts0422_1.dbf";
set newname for datafile 6 to
"/oradata06/testccccc/xdbts1.dbf";
set newname for datafile 7 to
"/oradata06/testccccc/ts0212.dbf";
set newname for datafile 8 to
"/oradata06/testccccc/ts0212_1.dbf";
set newname for datafile 10 to
"/oradata06/testccccc/undotbs3.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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20150705 21:22:18
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 /oradata06/testccccc/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/testccccc/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/testccccc/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata06/testccccc/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata06/testccccc/ts0422_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata06/testccccc/xdbts1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata06/testccccc/ts0212.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata06/testccccc/ts0212_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata06/testccccc/undotbs3.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 20150705 21:23:04
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=28 STAMP=884294909 file name=/oradata06/testccccc/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=29 STAMP=884294909 file name=/oradata06/testccccc/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=30 STAMP=884294909 file name=/oradata06/testccccc/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=31 STAMP=884294909 file name=/oradata06/testccccc/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=32 STAMP=884294909 file name=/oradata06/testccccc/ts0422_1.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=33 STAMP=884294909 file name=/oradata06/testccccc/xdbts1.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=34 STAMP=884294909 file name=/oradata06/testccccc/ts0212.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=35 STAMP=884294909 file name=/oradata06/testccccc/ts0212_1.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=36 STAMP=884294909 file name=/oradata06/testccccc/undotbs3.dbf
contents of Memory Script:
{
set until scn 12723363829157;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20150705 21:23:04
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=1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_arc_7kqbaf6v
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7kqbaf6v tag=TAG20150705T205350
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=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_1_884292447.dbf thread=1 sequence=1
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_1_884292447.dbf RECID=326 STAMP=884294912
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_2_884292447.dbf thread=1 sequence=2
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_2_884292447.dbf RECID=329 STAMP=884294912
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_3_884292447.dbf thread=1 sequence=3
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_3_884292447.dbf RECID=328 STAMP=884294912
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_4_884292447.dbf thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_4_884292447.dbf RECID=327 STAMP=884294912
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=5
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u tag=TAG20150705T211125
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=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_5_884292447.dbf thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_5_884292447.dbf RECID=330 STAMP=884294914
media recovery complete, elapsed time: 00:00:00
Finished recover at 20150705 21:23:10
Oracle instance started
Total System Global Area 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPDB3'' 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
sql statement: alter system set db_name = ''DUPDB3'' 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 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB3" RESETLOGS ARCHIVELOG
MAXLOGFILES 100
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 ( '/oradata06/testccccc/redo01a.log', '/oradata06/testccccc/redo01b.log' ) SIZE 128 M REUSE,
GROUP 2 ( '/oradata06/testccccc/redo02a.log', '/oradata06/testccccc/redo02b.log' ) SIZE 128 M REUSE,
GROUP 3 ( '/oradata06/testccccc/redo03a.log', '/oradata06/testccccc/redo03b.log' ) SIZE 128 M REUSE
DATAFILE
'/oradata06/testccccc/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata06/testccccc/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/oradata06/testccccc/sysaux01.dbf",
"/oradata06/testccccc/undotbs01.dbf",
"/oradata06/testccccc/users01.dbf",
"/oradata06/testccccc/ts0422_1.dbf",
"/oradata06/testccccc/xdbts1.dbf",
"/oradata06/testccccc/ts0212.dbf",
"/oradata06/testccccc/ts0212_1.dbf",
"/oradata06/testccccc/undotbs3.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata06/testccccc/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/sysaux01.dbf RECID=1 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/undotbs01.dbf RECID=2 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/users01.dbf RECID=3 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/ts0422_1.dbf RECID=4 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/xdbts1.dbf RECID=5 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/ts0212.dbf RECID=6 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/ts0212_1.dbf RECID=7 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/undotbs3.dbf RECID=8 STAMP=884294933
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=884294933 file name=/oradata06/testccccc/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=884294933 file name=/oradata06/testccccc/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=884294933 file name=/oradata06/testccccc/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=884294933 file name=/oradata06/testccccc/ts0422_1.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=884294933 file name=/oradata06/testccccc/xdbts1.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=884294933 file name=/oradata06/testccccc/ts0212.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=884294933 file name=/oradata06/testccccc/ts0212_1.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=8 STAMP=884294933 file name=/oradata06/testccccc/undotbs3.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data(PRIMARY KEY) columns
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20150705 21:23:35
确认表t0704_11、t0704_33存在、表t0704_22不存在
SQL> select count(*) from t0704_11;
COUNT(*)
----------
21
SQL> select count(*) from t0704_33;
COUNT(*)
----------
21
SQL> select count(*) from t0704_22;
select count(*) from t0704_22
*
ERROR at line 1:
ORA-00942: table or view does not exist
***连接到dupdb3,检查一下各文件的路径
col name format a50
set linesize 120 numwidth 16
SQL> select file#,name,checkpoint_change#,resetlogs_change#,status from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# RESETLOGS_CHANGE# STATUS
---------------- -------------------------------------------------- ------------------ ----------------- -------
1 /oradata06/testaaaaa/system01.dbf 12723363831251 12723363828514 ONLINE
2 /oradata06/testaaaaa/sysaux01.dbf 12723363831251 12723363828514 ONLINE
3 /oradata06/testaaaaa/undotbs01.dbf 12723363831251 12723363828514 ONLINE
4 /oradata06/testaaaaa/users01.dbf 12723363831251 12723363828514 ONLINE
5 /oradata06/testaaaaa/ts0422_1.dbf 12723363831251 12723363828514 ONLINE
6 /oradata06/testaaaaa/xdbts1.dbf 12723363831251 12723363828514 ONLINE
7 /oradata06/testaaaaa/ts0212.dbf 12723363831251 12723363828514 ONLINE
8 /oradata06/testaaaaa/ts0212_1.dbf 12723363831251 12723363828514 ONLINE
10 /oradata06/testaaaaa/undotbs3.dbf 12723363831251 12723363828514 ONLINE
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/oradata06/testaaaaa/redo03a.log
/oradata06/testaaaaa/redo03b.log
/oradata06/testaaaaa/redo02a.log
/oradata06/testaaaaa/redo02b.log
/oradata06/testaaaaa/redo01a.log
/oradata06/testaaaaa/redo01b.log
// duplicate时使用incarnation参数在仅连接target进行duplicate的情况下,指定duplicate到非当前incarnation下的某个SCN,但结果还是只能恢复到当前的incarnation
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
3 3 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
2 2 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
4 4 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
5 5 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
6 6 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
7 7 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
8 8 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
9 9 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
10 10 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
11 11 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
12 12 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
13 13 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
14 14 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
15 15 TSTDB1 2030654775 CURRENT 12723363828514 20150705 20:47:27
###重新启动dupdb3到nomount状态
export ORACLE_SID=dupdb3
sqlplus '/as sysdba'
shutdown abort
startup nomount
###执行duplicate发现还是从当前的incarnation进行的恢复
RMAN> duplicate database tstdb1 incarnation 13 to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
。。。
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 /oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
。。。
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 /oradata06/testccccc/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/testccccc/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/testccccc/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata06/testccccc/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata06/testccccc/ts0422_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata06/testccccc/xdbts1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata06/testccccc/ts0212.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata06/testccccc/ts0212_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata06/testccccc/undotbs3.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp
。。。
###结果还是恢复到了current incarnation
SQL> select count(*) from t0704_11;
COUNT(*)
----------
21
SQL> select count(*) from t0704_33;
COUNT(*)
----------
21
SQL> select count(*) from t0704_22;
select count(*) from t0704_22
*
ERROR at line 1:
ORA-00942: table or view does not exist
// duplicate时使用incarnation参数在仅连接catalog的情况下,指定duplicate到非当前的incarnation,结果报ORA-01547错误
###我们要恢复到的incarnation key是91140
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
40258 40259 SHZW 617151977 CURRENT 1 20120920 14:52:25
91139 91164 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
91139 91165 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
91139 91166 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
91139 91167 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
91139 91168 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
91139 91169 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
91139 91170 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
91139 91171 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
91139 91172 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
91139 91173 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
91139 91174 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
91139 91175 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
91139 91769 UNKNOWN 2030654775 ORPHAN 12723362562754 20150625 10:08:09
91139 91140 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
91139 92114 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
91139 92839 TSTDB1 2030654775 CURRENT 12723363828514 20150705 20:47:27
77120 77121 TSTDB2 2720835966 CURRENT 1 20150302 14:15:26
###重新启动dupdb3到nomount状态
export ORACLE_SID=dupdb3
sqlplus '/as sysdba'
startup force nomount
###仅连接catalog
rman catalog rman/773946@tstdb2 auxiliary sys/asdf3_14@dupdb3
RMAN> duplicate database tstdb1 incarnation 91140 to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
。。。。
Starting restore at 20150705 21:52:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=266 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 /oradata06/dupuse/incartest/tstdb2_df_7gqbaak7
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/tstdb2_df_7gqbaak7 tag=TAG20150705T193458
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/testccccc/control01.ctl
output file name=/oradata06/testccccc/control02.ctl
Finished restore at 20150705 21:52:12
。。。
Starting restore at 20150705 21:52:17
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 /oradata06/testccccc/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/testccccc/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/testccccc/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata06/testccccc/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata06/testccccc/ts0422_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata06/testccccc/xdbts1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata06/testccccc/ts0212.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata06/testccccc/ts0212_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata06/testccccc/undotbs3.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 20150705 21:53:13
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oradata06/testccccc/system01.dbf'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/05/2015 21:53:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 146 and starting SCN of 12723363827569 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 145 and starting SCN of 12723363827565 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 144 and starting SCN of 12723363827562 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 143 and starting SCN of 12723363827133 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 142 and starting SCN of 12723363824749 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 141 and starting SCN of 12723363828256 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 140 and starting SCN of 12723363828251 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 139 and starting SCN of 12723363828248 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 138 and starting SCN of 12723363826594 found to restore
上面的输出可以看到controlfile从old incarnation进行了恢复,但datafile还是从new incarnation的备份里恢复的,这才最终导致了ORA-01190错误
###连接dupdb3,可以看出controlfile的checkpoint_change#
***以下信息来自datafile header
SQL> select checkpoint_change#,resetlogs_change#,resetlogs_time from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ----------------- -----------------
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
9 rows selected.
***以下信息均来自controlfile
SQL> select resetlogs_change#,resetlogs_time from v$database;
RESETLOGS_CHANGE# RESETLOGS_TIME
----------------- -----------------
12723362562754 20150625 10:44:56
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
// 多incarnation环境搭建
假设数据库当前的incarnation key为A,在SCN1时刻创建t0704_11表,在SCN2时刻又创建了t0704_22表,在SCN3时刻drop掉t0704_11、t0704_22表;
发现t0704_11、t0704_22表被删除后,进行了incomplete recovery,recover到SCN1点后open resetlogs,生成新的incarnation key B,立即对数据库进行备份,需要确保备份完成的SCN值要小于SCN2,创建t0704_33表,确保t0704_33表创建时的SCN也要小于SCN2,等待current SCN值大于SCN2值之后,drop掉t0704_33表;
在catalog的情况下执行backup-based duplicate,分别尝试将db恢复到incarnation A的SCN2时刻、incarnation B的SCN2时刻
###tstdb1源库生成一个最新备份
rman target / catalog rman/773946@tstdb2
backup database include current controlfile format '/oradata06/dupuse/incartest/tstdb2_df_%u';
datafile备份:piece handle=/oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4 tag=TAG20150705T193458 comment=NONE
controlfile备份:piece handle=/oradata06/dupuse/incartest/tstdb2_df_7gqbaak7 tag=TAG20150705T193458 comment=NONE
create table t0704_11 tablespace ts0422_1 as select * from all_users;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
SYS@tstdb1-SQL> set numwidth 16
SYS@tstdb1-SQL> select current_scn from v$database;
CURRENT_SCN
----------------
12723363828513
create table t0704_22 tablespace ts0422_1 as select * from all_users;
SYS@tstdb1-SQL> select count(*) from t0704_22;
COUNT(*)
----------------
21
SYS@tstdb1-SQL> select count(*) from t0704_11;
COUNT(*)
----------------
21
---等待足够长时间,以给incarnation B留出一个backup database和建表的时间,确保backup和建表的操作执行完成后SCN仍然小于SCN2
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829157
drop table t0704_11;
drop table t0704_22;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select current_scn from v$database;
CURRENT_SCN
----------------
12723363830194
RMAN> list backup of database completed after '20150705 19:30:00';
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
92402 Full 2.19G DISK 00:00:27 20150705 19:35:27
BP Key: 92406 Status: AVAILABLE Compressed: NO Tag: TAG20150705T193458
Piece Name: /oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4
List of Datafiles in backup set 92402
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/system01.dbf
2 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/users01.dbf
5 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/ts0422_1.dbf
6 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/ts0212_1.dbf
10 Full 12723363827456 20150705 19:35:00 /oradata06/testaaaaa/undotbs3.dbf
***列出recover时需要用到的archivelog
SQL> select sequence# from v$archived_log where next_change#>12723363827456 and resetlogs_change#=12723362562754;
SEQUENCE#
----------------
143
144
145
146
147
148
backup archivelog sequence between 143 and 148 format '/oradata06/dupuse/incartest/tstdb2_arc_%u';
Archivelog备份:piece handle=/oradata06/dupuse/incartest/tstdb2_arc_7hqbabqb tag=TAG20150705T195554 comment=NONE
***当前的incarnation key是91140
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
91139 91140 TSTDB1 2030654775 CURRENT 12723362562754 20150625 10:44:56
###tstdb1 open resetlogs打开,形成一个新的incarnation,dbid和原先保持一致
shutdown immediate;
sqlplus '/as sysdba'
startup nomount
rman target / catalog rman/773946@tstdb2
***恢复到SCN1:12723363828513时刻
MAN> restore controlfile until scn 12723363828513;
Starting restore at 20150705 20:27:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=463 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /oradata06/dupuse/incartest/tstdb2_df_7gqbaak7
channel ORA_DISK_1: piece handle=/oradata06/dupuse/incartest/tstdb2_df_7gqbaak7 tag=TAG20150705T193458
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/testaaaaa/control01.ctl
output file name=/oradata06/testaaaaa/control02.ctl
Finished restore at 20150705 20:27:15
RMAN> restore database until scn 12723363828513;
Starting restore at 20150705 20:27:58
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata06/testaaaaa/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata06/testaaaaa/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata06/testaaaaa/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata06/testaaaaa/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata06/testaaaaa/ts0422_1.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata06/testaaaaa/xdbts1.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oradata06/testaaaaa/ts0212.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oradata06/testaaaaa/ts0212_1.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradata06/testaaaaa/undotbs3.dbf
channel ORA_DISK_1: reading from backup piece /oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4
channel ORA_DISK_1: piece handle=/oradata06/dupuse/incartest/tstdb2_df_7fqbaaj4 tag=TAG20150705T193458
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 20150705 20:29:23
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
***recover database until scn1
run
{
allocate channel d1 device type disk;
recover database until scn 12723363828513;
}
allocated channel: d1
channel d1: SID=530 device type=DISK
Starting recover at 20150705 20:31:23
Starting implicit crosscheck backup at 20150705 20:31:23
Crosschecked 27 objects
Finished implicit crosscheck backup at 20150705 20:31:24
Starting implicit crosscheck copy at 20150705 20:31:24
Finished implicit crosscheck copy at 20150705 20:31:25
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_136_1kf2sHC1D_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_137_1kf2sO192_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_139_1kf3KCnue_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_138_1kf3KCpeO_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_141_1kf3mjXkA_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_140_1kf3mjWms_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_143_1kfdtvzAq_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_142_1kfdtvoMB_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_145_1kfe76afR_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_144_1kfe76Z1y_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_145_1kfhlkmug_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_146_1kfhlktc4_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_144_1kfhll3pW_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_137_1kg8TVNAW_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_136_1kg8TVKkE_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_139_1kg94IPtL_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_138_1kg94IR0M_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_140_1kg94bcrC_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_141_1kg9H_x6L_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_142_1kg9H_yM2_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kg9P1Q8u_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kg9dt_9p_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kg9e0CM__.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kg9eO9mN_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_147_1kg9eYJKr_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_148_1kgAnGKwI_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgAnHJWT_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_147_1kgAnHPIC_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_1_1kgAuFrWR_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_2_1kgB5juPx_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_3_1kgCOa0fM_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_137_1kgqPPjZS_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_136_1kgqPPhRZ_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_139_1kgqxkv-j_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_138_1kgqxkyR0_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_140_1kgqy5EYZ_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_141_1kgqyIGQx_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kgvP1hFx_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kgvP1jiB_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kgvP7FF__.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgwBp9Zh_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_147_1kgwBpJKh_.arc
File Name: /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_148_1kgwBpX-C_.arc
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_04/o1_mf_s_884155780_1ke-6nyJC_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_04/o1_mf_s_884160047_1kf2575JA_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_04/o1_mf_s_884161722_1kf3e-Yih_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_05/o1_mf_s_884282538_1kgqA82V-_.bkp
File Name: /oradata06/fra/TSTDB1/autobackup/2015_07_05/o1_mf_s_884283494_1kgr386uR_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_07_05/o1_mf_nnsnf_TAG20150705T190710_1kgtoFq4d_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T104507_1kV7Y7e_d_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T163249_1kVQzYYJt_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_nnndf_TAG20150627T160630_1kWfzM_ge_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_ncsnf_TAG20150627T160630_1kWf_X2lE_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_nnndf_TAG20150627T163324_1kWhTZ3zY_.bkp
File Name: /oradata06/fra/TSTDB1/backupset/2015_06_27/o1_mf_ncsnf_TAG20150627T163324_1kWhVjGiv_.bkp
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/05/2015 20:31:26
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog
***调查上述错误的原因,连接target查看database incarnation,发现自动回到了最新的incarnation
rman target /
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
3 3 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
2 2 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
4 4 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
5 5 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
6 6 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
7 7 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
8 8 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
9 9 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
10 10 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
11 11 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
12 12 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
13 13 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
14 14 TSTDB1 2030654775 CURRENT 12723363819873 20150705 05:41:37
在recover的过程中RMAN自动将FRA里的备份信息都Catalog进控制文件,导致数据库切换到了最新的incarnation,对于这个问题MOS上有个文档说明了这个问题
此时我们要做的是手工将incarnation切到我们所需要的版本上
RMAN> reset database to incarnation 13;
database reset to incarnation 13
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
13 13 TSTDB1 2030654775 CURRENT 12723362562754 20150625 10:44:56
14 14 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
###再次进行recover
run
{
allocate channel d1 device type disk;
recover database until scn 12723363828513;
}
allocated channel: d1
channel d1: SID=463 device type=DISK
Starting recover at 20150705 20:46:12
starting media recovery
archived log for thread 1 with sequence 138 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_138_1kgqxkyR0_.arc
archived log for thread 1 with sequence 139 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_139_1kgqxkv-j_.arc
archived log for thread 1 with sequence 140 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_140_1kgqy5EYZ_.arc
archived log for thread 1 with sequence 141 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_141_1kgqyIGQx_.arc
archived log for thread 1 with sequence 142 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_04/o1_mf_1_142_1kfdtvoMB_.arc
archived log for thread 1 with sequence 143 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kgvP1hFx_.arc
archived log for thread 1 with sequence 144 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kgvP1jiB_.arc
archived log for thread 1 with sequence 145 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kgvP7FF__.arc
archived log for thread 1 with sequence 146 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgwBp9Zh_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_143_1kgvP1hFx_.arc thread=1 sequence=143
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_144_1kgvP1jiB_.arc thread=1 sequence=144
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_145_1kgvP7FF__.arc thread=1 sequence=145
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_07_05/o1_mf_1_146_1kgwBp9Zh_.arc thread=1 sequence=146
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150705 20:46:16
released channel: d1
***recover后检查datafile header的checkpoing_change#=SCN1+1
col name format a50
set linesize 150 pagesize 80 numwidth 16
SQL> select checkpoint_change#,file#,name,status from v$datafile_header;
CHECKPOINT_CHANGE# FILE# NAME STATUS
------------------ ---------------- -------------------------------------------------- -------
12723363828513 1 /oradata06/testaaaaa/system01.dbf ONLINE
12723363828513 2 /oradata06/testaaaaa/sysaux01.dbf ONLINE
12723363828513 3 /oradata06/testaaaaa/undotbs01.dbf ONLINE
12723363828513 4 /oradata06/testaaaaa/users01.dbf ONLINE
12723363828513 5 /oradata06/testaaaaa/ts0422_1.dbf ONLINE
12723363828513 6 /oradata06/testaaaaa/xdbts1.dbf ONLINE
12723363828513 7 /oradata06/testaaaaa/ts0212.dbf ONLINE
12723363828513 8 /oradata06/testaaaaa/ts0212_1.dbf ONLINE
12723363828513 10 /oradata06/testaaaaa/undotbs3.dbf ONLINE
alter database open resetlogs;
**确认当前scn远小于SCN2:12723363829157 再进行下面的备份
select current_scn from v$database;
CURRENT_SCN
----------------
12723363828778
backup database include current controlfile format '/oradata06/dupuse/incartest/new_tstdb2_df_%u'
datafile备份:piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp tag=TAG20150705T204823 comment=NONE
controlfile备份:piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets tag=TAG20150705T204823 comment=NONE
**备份完后确认当前SCN亦小于SCN2:12723363829157
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829016
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
40258 40259 SHZW 617151977 CURRENT 1 20120920 14:52:25
91139 91164 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
91139 91165 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
91139 91166 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
91139 91167 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
91139 91168 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
91139 91169 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
91139 91170 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
91139 91171 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
91139 91172 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
91139 91173 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
91139 91174 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
91139 91175 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
91139 91769 UNKNOWN 2030654775 ORPHAN 12723362562754 20150625 10:08:09
91139 91140 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
91139 92114 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
91139 92839 TSTDB1 2030654775 CURRENT 12723363828514 20150705 20:47:27
77120 77121 TSTDB2 2720835966 CURRENT 1 20150302 14:15:26
***是否恢复到SCN1:12723363828512的检查
SQL> select count(*) from t0704_11;
COUNT(*)
----------------
21
SQL> select count(*) from t0704_22;
select count(*) from t0704_22
*
ERROR at line 1:
ORA-00942: table or view does not exist
***确认当前SCN小于等于SCN2:12723363829157,再创建表t0704_33
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829042
create table t0704_33 tablespace ts0422_1 as select * from all_users;
SQL> select count(*) from t0704_33;
COUNT(*)
----------------
21
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
***确认t0704_33表存在时刻的SCN小于SCN2: 12723363829157
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829113
***由于是新的incarnation所以archivelog从1开始编号,能确定备份范围为1~4
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
backup archivelog sequence between 1 and 4 format '/oradata06/dupuse/incartest/new_tstdb2_arc_%u';
归档备份:piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7kqbaf6v tag=TAG20150705T205350 comment=NONE
select current_scn from v$database;
CURRENT_SCN
----------------
12723363829329 =SCN2:12723363829157后再执行下面的步骤
***确保下列查询有返回,否则执行alter system switch logfile将需要recover的日志归档出来
select sequence#,first_change#,next_change# from v$archived_log where 12723363829157 between first_change# and next_change#;
// duplicate时不加incarnation参数,仅连接target进行duplicate,能够正常恢复到当前incarnation的指定SCN
###先把/oradata06/dupuse/incartest/目录下的备份都传输到dup db的同名目录下
oracle@jq570322b:/oradata06/dupuse/incartest>ls -rlt
total 11611568
-rw-r----- 1 oracle oinstall 2356092928 Jul 05 19:35 tstdb2_df_7fqbaaj4
-rw-r----- 1 oracle oinstall 14385152 Jul 05 19:35 tstdb2_df_7gqbaak7
-rw-r----- 1 oracle oinstall 220084224 Jul 05 19:55 tstdb2_arc_7hqbabqb
-rw-r----- 1 oracle oinstall 3339714560 Jul 05 20:48 new_tstdb2_df_7iqbaesp
-rw-r----- 1 oracle oinstall 14385152 Jul 05 20:49 new_tstdb2_df_7jqbaets
-rw-r----- 1 oracle oinstall 436224 Jul 05 20:53 new_tstdb2_arc_7kqbaf6v
scp /oradata06/dupuse/incartest/* oracle@10.10.149.110:/oradata06/dupuse/incartest/
###准备dup db3实例参数,启动dupdb3实例
db_name=dupdb3
control_files='/oradata06/testccccc/control01.ctl','/oradata06/testccccc/control02.ctl'
log_file_name_convert='/oradata06/testaaaaa/','/oradata06/testccccc/'
export ORACLE_SID=dupdb3
sqlplus '/as sysdba'
startup nomount
###执行duplicate ...
dupdb目录下的所有备份文件如下:
oracle@qcp570717a:/oracle>ls -rlt /oradata06/dupuse/incartest/
total 11638688
-rw-r----- 1 oracle oinstall 436224 Jul 5 21:01 new_tstdb2_arc_7kqbaf6v
-rw-r----- 1 oracle oinstall 3339714560 Jul 5 21:05 new_tstdb2_df_7iqbaesp
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:05 new_tstdb2_df_7jqbaets
-rw-r----- 1 oracle oinstall 220084224 Jul 5 21:06 tstdb2_arc_7hqbabqb
-rw-r----- 1 oracle oinstall 2356092928 Jul 5 21:09 tstdb2_df_7fqbaaj4
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:09 tstdb2_df_7gqbaak7
rman target sys/773946@tstdb1 auxiliary sys/asdf3_14@dupdb3
RMAN> duplicate database to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
Starting Duplicate Db at 20150705 21:05:49
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=332 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/05/2015 21:05:50
RMAN-05501: aborting duplication of target database
RMAN-06457: UNTIL SCN (12723363829157) is ahead of last SCN in archived logs (12723363829128)
上述提示SCN 12723363829157所在的日志没有归档出来
SQL> select FIRST_CHANGE#,NEXT_CHANGE#,sequence# from v$log where 12723363829157 between FIRST_CHANGE# and NEXT_CHANGE#;
FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
---------------- ---------------- ----------------
12723363829128 281474976710655 5
alter system switch logfile;
RMAN> backup archivelog sequence 5 format '/oradata06/dupuse/incartest/new_tstdb2_arc_%u';
piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u tag=TAG20150705T211125 comment=NONE
传输到dup db的同名目录下
scp /oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u oracle@10.10.149.110:/oradata06/dupuse/incartest/
至此dup db的/oradata06/dupuse/incartest/目录下有
-rw-r----- 1 oracle oinstall 436224 Jul 5 21:01 new_tstdb2_arc_7kqbaf6v
-rw-r----- 1 oracle oinstall 3339714560 Jul 5 21:05 new_tstdb2_df_7iqbaesp
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:05 new_tstdb2_df_7jqbaets
-rw-r----- 1 oracle oinstall 220084224 Jul 5 21:06 tstdb2_arc_7hqbabqb
-rw-r----- 1 oracle oinstall 2356092928 Jul 5 21:09 tstdb2_df_7fqbaaj4
-rw-r----- 1 oracle oinstall 14385152 Jul 5 21:09 tstdb2_df_7gqbaak7
-rw-r----- 1 oracle oinstall 5720064 Jul 5 21:20 new_tstdb2_arc_7lqbag7u
###再次进行duplicate
RMAN> duplicate database to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
Starting Duplicate Db at 20150705 21:21:46
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=332 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
contents of Memory Script:
{
set until scn 12723363829157;
sql clone "alter system set db_name =
''TSTDB1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUPDB3'' 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 = ''TSTDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUPDB3'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
Starting restore at 20150705 21:22:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=266 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 /oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/testccccc/control01.ctl
output file name=/oradata06/testccccc/control02.ctl
Finished restore at 20150705 21:22:13
database mounted
contents of Memory Script:
{
set until scn 12723363829157;
set newname for datafile 1 to
"/oradata06/testccccc/system01.dbf";
set newname for datafile 2 to
"/oradata06/testccccc/sysaux01.dbf";
set newname for datafile 3 to
"/oradata06/testccccc/undotbs01.dbf";
set newname for datafile 4 to
"/oradata06/testccccc/users01.dbf";
set newname for datafile 5 to
"/oradata06/testccccc/ts0422_1.dbf";
set newname for datafile 6 to
"/oradata06/testccccc/xdbts1.dbf";
set newname for datafile 7 to
"/oradata06/testccccc/ts0212.dbf";
set newname for datafile 8 to
"/oradata06/testccccc/ts0212_1.dbf";
set newname for datafile 10 to
"/oradata06/testccccc/undotbs3.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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20150705 21:22:18
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 /oradata06/testccccc/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/testccccc/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/testccccc/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata06/testccccc/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata06/testccccc/ts0422_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata06/testccccc/xdbts1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata06/testccccc/ts0212.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata06/testccccc/ts0212_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata06/testccccc/undotbs3.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 20150705 21:23:04
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=28 STAMP=884294909 file name=/oradata06/testccccc/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=29 STAMP=884294909 file name=/oradata06/testccccc/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=30 STAMP=884294909 file name=/oradata06/testccccc/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=31 STAMP=884294909 file name=/oradata06/testccccc/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=32 STAMP=884294909 file name=/oradata06/testccccc/ts0422_1.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=33 STAMP=884294909 file name=/oradata06/testccccc/xdbts1.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=34 STAMP=884294909 file name=/oradata06/testccccc/ts0212.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=35 STAMP=884294909 file name=/oradata06/testccccc/ts0212_1.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=36 STAMP=884294909 file name=/oradata06/testccccc/undotbs3.dbf
contents of Memory Script:
{
set until scn 12723363829157;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20150705 21:23:04
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=1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_arc_7kqbaf6v
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7kqbaf6v tag=TAG20150705T205350
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=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_1_884292447.dbf thread=1 sequence=1
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_1_884292447.dbf RECID=326 STAMP=884294912
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_2_884292447.dbf thread=1 sequence=2
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_2_884292447.dbf RECID=329 STAMP=884294912
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_3_884292447.dbf thread=1 sequence=3
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_3_884292447.dbf RECID=328 STAMP=884294912
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_4_884292447.dbf thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_4_884292447.dbf RECID=327 STAMP=884294912
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=5
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_arc_7lqbag7u tag=TAG20150705T211125
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=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_5_884292447.dbf thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_5_884292447.dbf RECID=330 STAMP=884294914
media recovery complete, elapsed time: 00:00:00
Finished recover at 20150705 21:23:10
Oracle instance started
Total System Global Area 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPDB3'' 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
sql statement: alter system set db_name = ''DUPDB3'' 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 329895936 bytes
Fixed Size 2221160 bytes
Variable Size 255855512 bytes
Database Buffers 67108864 bytes
Redo Buffers 4710400 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB3" RESETLOGS ARCHIVELOG
MAXLOGFILES 100
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 ( '/oradata06/testccccc/redo01a.log', '/oradata06/testccccc/redo01b.log' ) SIZE 128 M REUSE,
GROUP 2 ( '/oradata06/testccccc/redo02a.log', '/oradata06/testccccc/redo02b.log' ) SIZE 128 M REUSE,
GROUP 3 ( '/oradata06/testccccc/redo03a.log', '/oradata06/testccccc/redo03b.log' ) SIZE 128 M REUSE
DATAFILE
'/oradata06/testccccc/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata06/testccccc/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/oradata06/testccccc/sysaux01.dbf",
"/oradata06/testccccc/undotbs01.dbf",
"/oradata06/testccccc/users01.dbf",
"/oradata06/testccccc/ts0422_1.dbf",
"/oradata06/testccccc/xdbts1.dbf",
"/oradata06/testccccc/ts0212.dbf",
"/oradata06/testccccc/ts0212_1.dbf",
"/oradata06/testccccc/undotbs3.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata06/testccccc/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/sysaux01.dbf RECID=1 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/undotbs01.dbf RECID=2 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/users01.dbf RECID=3 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/ts0422_1.dbf RECID=4 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/xdbts1.dbf RECID=5 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/ts0212.dbf RECID=6 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/ts0212_1.dbf RECID=7 STAMP=884294933
cataloged datafile copy
datafile copy file name=/oradata06/testccccc/undotbs3.dbf RECID=8 STAMP=884294933
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=884294933 file name=/oradata06/testccccc/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=884294933 file name=/oradata06/testccccc/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=884294933 file name=/oradata06/testccccc/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=884294933 file name=/oradata06/testccccc/ts0422_1.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=884294933 file name=/oradata06/testccccc/xdbts1.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=884294933 file name=/oradata06/testccccc/ts0212.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=884294933 file name=/oradata06/testccccc/ts0212_1.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=8 STAMP=884294933 file name=/oradata06/testccccc/undotbs3.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data(PRIMARY KEY) columns
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20150705 21:23:35
确认表t0704_11、t0704_33存在、表t0704_22不存在
SQL> select count(*) from t0704_11;
COUNT(*)
----------
21
SQL> select count(*) from t0704_33;
COUNT(*)
----------
21
SQL> select count(*) from t0704_22;
select count(*) from t0704_22
*
ERROR at line 1:
ORA-00942: table or view does not exist
***连接到dupdb3,检查一下各文件的路径
col name format a50
set linesize 120 numwidth 16
SQL> select file#,name,checkpoint_change#,resetlogs_change#,status from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# RESETLOGS_CHANGE# STATUS
---------------- -------------------------------------------------- ------------------ ----------------- -------
1 /oradata06/testaaaaa/system01.dbf 12723363831251 12723363828514 ONLINE
2 /oradata06/testaaaaa/sysaux01.dbf 12723363831251 12723363828514 ONLINE
3 /oradata06/testaaaaa/undotbs01.dbf 12723363831251 12723363828514 ONLINE
4 /oradata06/testaaaaa/users01.dbf 12723363831251 12723363828514 ONLINE
5 /oradata06/testaaaaa/ts0422_1.dbf 12723363831251 12723363828514 ONLINE
6 /oradata06/testaaaaa/xdbts1.dbf 12723363831251 12723363828514 ONLINE
7 /oradata06/testaaaaa/ts0212.dbf 12723363831251 12723363828514 ONLINE
8 /oradata06/testaaaaa/ts0212_1.dbf 12723363831251 12723363828514 ONLINE
10 /oradata06/testaaaaa/undotbs3.dbf 12723363831251 12723363828514 ONLINE
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/oradata06/testaaaaa/redo03a.log
/oradata06/testaaaaa/redo03b.log
/oradata06/testaaaaa/redo02a.log
/oradata06/testaaaaa/redo02b.log
/oradata06/testaaaaa/redo01a.log
/oradata06/testaaaaa/redo01b.log
// duplicate时使用incarnation参数在仅连接target进行duplicate的情况下,指定duplicate到非当前incarnation下的某个SCN,但结果还是只能恢复到当前的incarnation
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
3 3 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
2 2 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
4 4 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
5 5 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
6 6 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
7 7 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
8 8 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
9 9 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
10 10 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
11 11 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
12 12 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
13 13 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
14 14 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
15 15 TSTDB1 2030654775 CURRENT 12723363828514 20150705 20:47:27
###重新启动dupdb3到nomount状态
export ORACLE_SID=dupdb3
sqlplus '/as sysdba'
shutdown abort
startup nomount
###执行duplicate发现还是从当前的incarnation进行的恢复
RMAN> duplicate database tstdb1 incarnation 13 to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
。。。
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 /oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7jqbaets tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
。。。
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 /oradata06/testccccc/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/testccccc/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/testccccc/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata06/testccccc/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata06/testccccc/ts0422_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata06/testccccc/xdbts1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata06/testccccc/ts0212.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata06/testccccc/ts0212_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata06/testccccc/undotbs3.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp
。。。
###结果还是恢复到了current incarnation
SQL> select count(*) from t0704_11;
COUNT(*)
----------
21
SQL> select count(*) from t0704_33;
COUNT(*)
----------
21
SQL> select count(*) from t0704_22;
select count(*) from t0704_22
*
ERROR at line 1:
ORA-00942: table or view does not exist
// duplicate时使用incarnation参数在仅连接catalog的情况下,指定duplicate到非当前的incarnation,结果报ORA-01547错误
###我们要恢复到的incarnation key是91140
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
40258 40259 SHZW 617151977 CURRENT 1 20120920 14:52:25
91139 91164 TSTDB1 2030654775 PARENT 12723361876275 20150501 22:23:18
91139 91165 TSTDB1 2030654775 PARENT 12723361982201 20150502 17:26:40
91139 91166 TSTDB1 2030654775 ORPHAN 12723361982602 20150502 16:51:47
91139 91167 TSTDB1 2030654775 PARENT 12723361984663 20150621 08:02:54
91139 91168 TSTDB1 2030654775 ORPHAN 12723362036923 20150621 13:35:30
91139 91169 TSTDB1 2030654775 PARENT 12723362039102 20150621 16:03:46
91139 91170 TSTDB1 2030654775 PARENT 12723362040689 20150621 16:08:49
91139 91171 TSTDB1 2030654775 PARENT 12723362054987 20150622 19:55:12
91139 91172 TSTDB1 2030654775 PARENT 12723362144134 20150623 10:23:34
91139 91173 TSTDB1 2030654775 PARENT 12723362147001 20150623 10:40:47
91139 91174 TSTDB1 2030654775 PARENT 12723362149501 20150623 10:57:09
91139 91175 TSTDB1 2030654775 PARENT 12723362559613 20150625 09:36:10
91139 91769 UNKNOWN 2030654775 ORPHAN 12723362562754 20150625 10:08:09
91139 91140 TSTDB1 2030654775 PARENT 12723362562754 20150625 10:44:56
91139 92114 TSTDB1 2030654775 ORPHAN 12723363819873 20150705 05:41:37
91139 92839 TSTDB1 2030654775 CURRENT 12723363828514 20150705 20:47:27
77120 77121 TSTDB2 2720835966 CURRENT 1 20150302 14:15:26
###重新启动dupdb3到nomount状态
export ORACLE_SID=dupdb3
sqlplus '/as sysdba'
startup force nomount
###仅连接catalog
rman catalog rman/773946@tstdb2 auxiliary sys/asdf3_14@dupdb3
RMAN> duplicate database tstdb1 incarnation 91140 to dupdb3 until scn 12723363829157 db_file_name_convert='/oradata06/testaaaaa','/oradata06/testccccc';
。。。。
Starting restore at 20150705 21:52:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=266 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 /oradata06/dupuse/incartest/tstdb2_df_7gqbaak7
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/tstdb2_df_7gqbaak7 tag=TAG20150705T193458
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/testccccc/control01.ctl
output file name=/oradata06/testccccc/control02.ctl
Finished restore at 20150705 21:52:12
。。。
Starting restore at 20150705 21:52:17
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 /oradata06/testccccc/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/testccccc/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/testccccc/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata06/testccccc/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata06/testccccc/ts0422_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata06/testccccc/xdbts1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata06/testccccc/ts0212.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata06/testccccc/ts0212_1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata06/testccccc/undotbs3.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp
channel ORA_AUX_DISK_1: piece handle=/oradata06/dupuse/incartest/new_tstdb2_df_7iqbaesp tag=TAG20150705T204823
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 20150705 21:53:13
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oradata06/testccccc/system01.dbf'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/05/2015 21:53:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 146 and starting SCN of 12723363827569 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 145 and starting SCN of 12723363827565 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 144 and starting SCN of 12723363827562 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 143 and starting SCN of 12723363827133 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 142 and starting SCN of 12723363824749 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 141 and starting SCN of 12723363828256 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 140 and starting SCN of 12723363828251 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 139 and starting SCN of 12723363828248 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 138 and starting SCN of 12723363826594 found to restore
上面的输出可以看到controlfile从old incarnation进行了恢复,但datafile还是从new incarnation的备份里恢复的,这才最终导致了ORA-01190错误
###连接dupdb3,可以看出controlfile的checkpoint_change#
***以下信息来自datafile header
SQL> select checkpoint_change#,resetlogs_change#,resetlogs_time from v$datafile_header;
CHECKPOINT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ----------------- -----------------
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
12723363828839 12723363828514 20150705 20:47:27
9 rows selected.
***以下信息均来自controlfile
SQL> select resetlogs_change#,resetlogs_time from v$database;
RESETLOGS_CHANGE# RESETLOGS_TIME
----------------- -----------------
12723362562754 20150625 10:44:56
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
12723363827456
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1723923/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/53956/viewspace-1723923/