recover database skip [forever] tablespace XXX 的用法介紹
recovery過程中可以臨時將某個暫無法恢復出來的tablespace skip掉，先恢復其它部分，待recovery database完成並open database後，再recover這個被skip掉的表空間；recover database skip [forever] tablespace ... 可以支援以上功能
////////////////
// complete recovery場景下使用 skip tablespace XXX
////////////////
###列出database備份
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
76278 Full 2.00G DISK 00:00:26 20150623 05:56:05
BP Key: 76281 Status: AVAILABLE Compressed: NO Tag: TAG20150623T055537
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_23/o1_mf_nnndf_TAG20150623T055537_1kR5zK8Nx_.bkp
List of Datafiles in backup set 76278
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/system01.dbf
2 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/users01.dbf
5 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0329_1.dbf
6 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0212_1.dbf
9 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0422_1.dbf
10 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0611.dbf
RMAN> list backup of controlfile
2> ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
76277 Full 13.64M DISK 00:00:00 20150621 16:56:29
BP Key: 76280 Status: AVAILABLE Compressed: NO Tag: TAG20150621T165532
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_21/o1_mf_ncsnf_TAG20150621T165532_1kP9z0FUh_.bkp
Control File Included: Ckp SCN: 12723362045827 Ckp time: 20150621 16:56:29
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
76279 Full 13.64M DISK 00:00:01 20150623 05:56:15
BP Key: 76282 Status: AVAILABLE Compressed: NO Tag: TAG20150623T055537
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_23/o1_mf_ncsnf_TAG20150623T055537_1kR5_UAqO_.bkp
Control File Included: Ckp SCN: 12723362138489 Ckp time: 20150623 05:56:14
###當前database schema
RMAN> report schema;
Report of database schema for database with db_unique_name TSTDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1024 SYSTEM YES /oradata06/testaaaaa/system01.dbf
2 896 SYSAUX NO /oradata06/testaaaaa/sysaux01.dbf
3 2048 UNDOTBS YES /oradata06/testaaaaa/undotbs01.dbf
4 1024 USERS NO /oradata06/testaaaaa/users01.dbf
5 128 TS0329 NO /oradata06/testaaaaa/ts0329_1.dbf
6 500 XDBTS NO /oradata06/testaaaaa/xdbts1.dbf
7 128 TS0212 NO /oradata06/testaaaaa/ts0212.dbf
8 48 TS0212 NO /oradata06/testaaaaa/ts0212_1.dbf
9 1800 TS0422_1 NO /oradata06/testaaaaa/ts0422_1.dbf
10 128 TS0611_NEW NO /oradata06/testaaaaa/ts0611.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1024 TEMP 1024 /oradata06/testaaaaa/temp01.dbf
###關閉資料庫進行restore
shutdown immediate;
startup mount;
restore database;
###重新命名資料檔案，模擬datafile丟失的場景，recover database提示缺datafile 10
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
RMAN> recover database;
Starting recover at 20150623 09:24:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/23/2015 09:24:11
RMAN-06094: datafile 10 must be restored
select ts.ts#,ts.name from v$tablespace ts,v$datafile df where df.ts#=ts.ts# and df.file#=10;
TS# NAME
---------- ------------------------------
15 TS0611_NEW
RMAN> recover database skip tablespace TS0611_NEW;
Starting recover at 20150623 09:35:43
using channel ORA_DISK_1
Executing: alter database datafile 10 offline
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 09:35:47
col name format a50
set linesize 100 pagesize 40
select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/oradata06/testaaaaa/system01.dbf SYSTEM
/oradata06/testaaaaa/sysaux01.dbf ONLINE
/oradata06/testaaaaa/undotbs01.dbf ONLINE
/oradata06/testaaaaa/users01.dbf ONLINE
/oradata06/testaaaaa/ts0329_1.dbf ONLINE
/oradata06/testaaaaa/xdbts1.dbf ONLINE
/oradata06/testaaaaa/ts0212.dbf ONLINE
/oradata06/testaaaaa/ts0212_1.dbf ONLINE
/oradata06/testaaaaa/ts0422_1.dbf ONLINE
/oradata06/testaaaaa/ts0611.dbf OFFLINE
alter database open;
###挪回datafile 10, recover tablespace ts0611_new
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
recover tablespace ts0611_new;
alter tablespace ts0611_new online;
SYS@tstdb1-SQL> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/oradata06/testaaaaa/system01.dbf SYSTEM
/oradata06/testaaaaa/sysaux01.dbf ONLINE
/oradata06/testaaaaa/undotbs01.dbf ONLINE
/oradata06/testaaaaa/users01.dbf ONLINE
/oradata06/testaaaaa/ts0329_1.dbf ONLINE
/oradata06/testaaaaa/xdbts1.dbf ONLINE
/oradata06/testaaaaa/ts0212.dbf ONLINE
/oradata06/testaaaaa/ts0212_1.dbf ONLINE
/oradata06/testaaaaa/ts0422_1.dbf ONLINE
/oradata06/testaaaaa/ts0611.dbf ONLINE
////////////////
//complete recovery場景下使用 skip forever XXX
////////////////
shutdown immediate
rman target / catalog rman/773946@tstdb2
startup nomount
restore database;
alter database mount;
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
RMAN> recover database;
Starting recover at 20150623 09:54:34
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/23/2015 09:54:35
RMAN-06094: datafile 10 must be restored
###complete recovery的場景下使用skip forever
RMAN> recover database skip forever tablespace ts0611_new;
Starting recover at 20150623 10:02:19
using channel ORA_DISK_1
Executing: alter database datafile 10 offline drop
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 10:02:23
col name format a60
set linesize 120
select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362144126
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144126
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144126
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362144126
alter database open;
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362144129
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362144126
###挪回原位
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
SYS@tstdb1-SQL> alter tablespace ts0611_new online;
alter tablespace ts0611_new online
*
ERROR at line 1:
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/oradata06/testaaaaa/ts0611.dbf'
###skip forever的tablespace依然可以recover
RMAN> recover tablespace ts0611_new;
Starting recover at 20150623 10:06:11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 20150623 10:06:12
10 rows selected.
SYS@tstdb1-SQL> alter tablespace ts0611_new online;
Tablespace altered.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362144129
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362144824
10 rows selected.
////////////////
//incomplete recovery場景下使用 skip tablespace XXX，open db後還能繼續恢復這個表空間
////////////////
shutdown immediate
startup mount
restore database;
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/users01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362138466
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
---recover database until scn
RMAN> recover database until scn 12723362144133;
Starting recover at 20150623 10:17:25
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/23/2015 10:17:25
RMAN-06094: datafile 10 must be restored
---recover database until scn ... skip tablespace ...
RMAN> recover database until scn 12723362144133 skip tablespace ts0611_new;
Starting recover at 20150623 10:18:15
using channel ORA_DISK_1
Executing: alter database datafile 10 offline
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 10:18:19
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144133
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144133
OFFLINE 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362145858
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145858
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145858
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362145858
###開啟資料庫
alter database open resetlogs
col name format a60
set linesize 120 numwidth 16
select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362145858
select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145859
OFFLINE 0
###挪回datafile 10
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
###online tablespace，提示Datafile 10是resetlogs前的版本
SYS@tstdb1-SQL> alter tablespace ts0611_new online;
alter tablespace ts0611_new online
*
ERROR at line 1:
ORA-01190: control file or data file 10 is from before the last RESETLOGS
ORA-01110: data file 10: '/oradata06/testaaaaa/ts0611.dbf'
###recover tablespace ts0611_new依然成功
RMAN> recover tablespace ts0611_new;
Starting recover at 20150623 10:28:50
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log for thread 1 with sequence 7 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc thread=1 sequence=6
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150623 10:28:51
SYS@tstdb1-SQL> select name,status,resetlogs_change#,checkpoint_change# from v$datafile_header;
NAME STATUS RESETLOGS_CHANGE# CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ----------------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362144134 12723362146664
10 rows selected.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362146664
////////////////
//incomplete recovery場景下使用 skip forever tablespace XXX，open db後XXX表空間仍可以恢復
////////////////
shutdown immediate
startup mount
restore database;
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
SYS@tstdb1-SQL> select name,status,resetlogs_change#,checkpoint_change# from v$datafile_header;
NAME STATUS RESETLOGS_CHANGE# CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ----------------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/users01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362054987 12723362138466
ONLINE 0 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147430
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147430
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147430
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362147430
RMAN> recover database until scn 12723362147000 skip forever tablespace ts0611_new;
Starting recover at 20150623 10:40:21
using channel ORA_DISK_1
Executing: alter database datafile 10 offline drop
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log for thread 1 with sequence 7 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc thread=1 sequence=6
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 10:40:25
alter database open resetlogs;
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362138466
RMAN> recover tablespace ts0611_new;
Starting recover at 20150623 10:48:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log for thread 1 with sequence 7 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc
archived log for thread 1 with sequence 1 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_1_1kRLv2UWQ_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc thread=1 sequence=6
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc thread=1 sequence=7
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_1_1kRLv2UWQ_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 20150623 10:48:21
starting full resync of recovery catalog
full resync complete
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362147001
10 rows selected.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362147000
alter tablespace ts0611_new online
Tablespace altered.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362148075
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362148075
SYS@tstdb1-SQL> create table aaa(id number) tablespace ts0611_new;
Table created.
////////////////////////////////
// incomplete recovery場景下使用 skip forever tablespace XXX，open db後XXX表空間仍可以恢復並驗證XXX表空間的恢復點
////////////////////////////////
###新建表空間，進行備份
create tablespace ts0625_1 datafile '/oradata06/testaaaaa/ts0625_1.dbf' size 128m;
backup database plus archivelog;
archivelog備份在tag=TAG20150625T095036、TAG20150625T095110
datafile & controlfile備份在tag=TAG20150625T095039
###建立測試表，記錄before drop時間
create table t0625_2 tablespace ts0625_1 as select * from all_users;
SYS@tstdb1-SQL> select count(*) from t0625_2;
COUNT(*)
----------------
19
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select sysdate from dual;
SYSDATE
-----------------
20150625 09:52:50
drop table t0625_1;
select sysdate from dual;
SYSDATE
-----------------
20150625 09:55:00
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
shutdown immediate
startup mount;
###restore到before drop time
RMAN> restore database until time '20150625 09:52:50';
Starting restore at 20150625 10:01:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK
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 00009 to /oradata06/testaaaaa/ts0623.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradata06/testaaaaa/ts0624.dbf
channel ORA_DISK_1: restoring datafile 00011 to /oradata06/testaaaaa/ts0624_2.dbf
channel ORA_DISK_1: restoring datafile 00012 to /oradata06/testaaaaa/ts0625.dbf
channel ORA_DISK_1: restoring datafile 00013 to /oradata06/testaaaaa/ts0625_1.dbf
channel ORA_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_25/o1_mf_nnndf_TAG20150625T095039_1kTq1Y_mg_.bkp
channel ORA_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_25/o1_mf_nnndf_TAG20150625T095039_1kTq1Y_mg_.bkp tag=TAG20150625T095039
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20150625 10:02:19
###模擬ts0625_1.dbf檔案丟失
mv /oradata06/testaaaaa/ts0625_1.dbf /oradata06/testaaaaa/ts0625_1.dbf.old
col name format a40
set numwidth 16 linesize 140 pagesize 50
SYS@tstdb1-SQL> select name,status,resetlogs_change#,checkpoint_change# from v$datafile_header;
NAME STATUS RESETLOGS_CHANGE# CHECKPOINT_CHANGE#
---------------------------------------- ------- ----------------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/users01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362559613 12723362561576
ONLINE 0 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362563571
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362563571
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362563571
/oradata06/testaaaaa/users01.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362563571
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0625_1.dbf ONLINE 12723362563571
###必須recover database skip tablespace XXX才能保證recover database的順利進行
RMAN> recover database until time '20150625 09:52:50';
Starting recover at 20150625 10:06:39
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/25/2015 10:06:40
RMAN-06094: datafile 13 must be restored
RMAN> recover database skip forever tablespace ts0625_1 until time '20150625 09:52:50';
Starting recover at 20150625 10:07:12
using channel ORA_DISK_1
Executing: alter database datafile 13 offline drop
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150625 10:07:15
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562753
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362562753
OFFLINE 0
###open resetlogs
alter database open resetlogs;
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362562757
OFFLINE 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 0
###恢復datafile 12的至源路徑
mv /oradata06/testaaaaa/ts0625_1.dbf.old /oradata06/testaaaaa/ts0625_1.dbf
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 12723362561576
###PRIOR_RESETLOGS_CHANGE#=12723362559613，根據resetlogs_change#=12723362559613到v$archived_log查詢最大的sequence#
SYS@tstdb1-SQL> select max(sequence#) from v$archived_log where resetlogs_change#=12723362559613;
MAX(SEQUENCE#)
----------------
12
RMAN> recover tablespace ts0625_1;
Starting recover at 20150625 10:12:28
starting full resync of recovery catalog
full resync complete
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150625 10:12:31
starting full resync of recovery catalog
full resync complete
SYS@tstdb1-SQL> select name,status,checkpoint_change#,resetlogs_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------------------------------------- ------- ------------------ -----------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 12723362562754 12723362562754
---12723362562754包含在log sequence# 6裡
SYS@tstdb1-SQL> select sequence#,first_change#,next_change# from v$archived_log where resetlogs_change#=12723362559613 and 12723362562754 between first_change# and next_change#;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------------- ---------------- ----------------
6 12723362562746 12723362562767
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 12723362562753
SYS@tstdb1-SQL> select count(*) from t0625_2;
select count(*) from t0625_2
*
ERROR at line 1:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/oradata06/testaaaaa/ts0625_1.dbf'
alter tablespace ts0625_1 online;
###驗證表t0625_2存在
SYS@tstdb1-SQL> select count(*) from t0625_2;
COUNT(*)
----------------
19
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf ONLINE 12723362564357
12 rows selected.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf ONLINE 12723362564357
結論：skip tablespace /skip forever tablespace用在incomplete recovery/complete recovery的場景中都可以起到將延遲recover表空間的功能，先把db拉起來，再恢復這個表空間；incomplete recovery先把database recover到指定時間點open resetlogs，之後再執行recover tablespace XXX的效果相當於將該表空間從備份點恢復到open resetlogs後的當前最新的時間點。skip tablespace和skip forever tablespace作用上沒有明顯區別
////////////////
// complete recovery場景下使用 skip tablespace XXX
////////////////
###列出database備份
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
76278 Full 2.00G DISK 00:00:26 20150623 05:56:05
BP Key: 76281 Status: AVAILABLE Compressed: NO Tag: TAG20150623T055537
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_23/o1_mf_nnndf_TAG20150623T055537_1kR5zK8Nx_.bkp
List of Datafiles in backup set 76278
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/system01.dbf
2 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/users01.dbf
5 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0329_1.dbf
6 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0212_1.dbf
9 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0422_1.dbf
10 Full 12723362138466 20150623 05:55:39 /oradata06/testaaaaa/ts0611.dbf
RMAN> list backup of controlfile
2> ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
76277 Full 13.64M DISK 00:00:00 20150621 16:56:29
BP Key: 76280 Status: AVAILABLE Compressed: NO Tag: TAG20150621T165532
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_21/o1_mf_ncsnf_TAG20150621T165532_1kP9z0FUh_.bkp
Control File Included: Ckp SCN: 12723362045827 Ckp time: 20150621 16:56:29
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
76279 Full 13.64M DISK 00:00:01 20150623 05:56:15
BP Key: 76282 Status: AVAILABLE Compressed: NO Tag: TAG20150623T055537
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_23/o1_mf_ncsnf_TAG20150623T055537_1kR5_UAqO_.bkp
Control File Included: Ckp SCN: 12723362138489 Ckp time: 20150623 05:56:14
###當前database schema
RMAN> report schema;
Report of database schema for database with db_unique_name TSTDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1024 SYSTEM YES /oradata06/testaaaaa/system01.dbf
2 896 SYSAUX NO /oradata06/testaaaaa/sysaux01.dbf
3 2048 UNDOTBS YES /oradata06/testaaaaa/undotbs01.dbf
4 1024 USERS NO /oradata06/testaaaaa/users01.dbf
5 128 TS0329 NO /oradata06/testaaaaa/ts0329_1.dbf
6 500 XDBTS NO /oradata06/testaaaaa/xdbts1.dbf
7 128 TS0212 NO /oradata06/testaaaaa/ts0212.dbf
8 48 TS0212 NO /oradata06/testaaaaa/ts0212_1.dbf
9 1800 TS0422_1 NO /oradata06/testaaaaa/ts0422_1.dbf
10 128 TS0611_NEW NO /oradata06/testaaaaa/ts0611.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 1024 TEMP 1024 /oradata06/testaaaaa/temp01.dbf
###關閉資料庫進行restore
shutdown immediate;
startup mount;
restore database;
###重新命名資料檔案，模擬datafile丟失的場景，recover database提示缺datafile 10
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
RMAN> recover database;
Starting recover at 20150623 09:24:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/23/2015 09:24:11
RMAN-06094: datafile 10 must be restored
select ts.ts#,ts.name from v$tablespace ts,v$datafile df where df.ts#=ts.ts# and df.file#=10;
TS# NAME
---------- ------------------------------
15 TS0611_NEW
RMAN> recover database skip tablespace TS0611_NEW;
Starting recover at 20150623 09:35:43
using channel ORA_DISK_1
Executing: alter database datafile 10 offline
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 09:35:47
col name format a50
set linesize 100 pagesize 40
select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/oradata06/testaaaaa/system01.dbf SYSTEM
/oradata06/testaaaaa/sysaux01.dbf ONLINE
/oradata06/testaaaaa/undotbs01.dbf ONLINE
/oradata06/testaaaaa/users01.dbf ONLINE
/oradata06/testaaaaa/ts0329_1.dbf ONLINE
/oradata06/testaaaaa/xdbts1.dbf ONLINE
/oradata06/testaaaaa/ts0212.dbf ONLINE
/oradata06/testaaaaa/ts0212_1.dbf ONLINE
/oradata06/testaaaaa/ts0422_1.dbf ONLINE
/oradata06/testaaaaa/ts0611.dbf OFFLINE
alter database open;
###挪回datafile 10, recover tablespace ts0611_new
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
recover tablespace ts0611_new;
alter tablespace ts0611_new online;
SYS@tstdb1-SQL> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/oradata06/testaaaaa/system01.dbf SYSTEM
/oradata06/testaaaaa/sysaux01.dbf ONLINE
/oradata06/testaaaaa/undotbs01.dbf ONLINE
/oradata06/testaaaaa/users01.dbf ONLINE
/oradata06/testaaaaa/ts0329_1.dbf ONLINE
/oradata06/testaaaaa/xdbts1.dbf ONLINE
/oradata06/testaaaaa/ts0212.dbf ONLINE
/oradata06/testaaaaa/ts0212_1.dbf ONLINE
/oradata06/testaaaaa/ts0422_1.dbf ONLINE
/oradata06/testaaaaa/ts0611.dbf ONLINE
////////////////
//complete recovery場景下使用 skip forever XXX
////////////////
shutdown immediate
rman target / catalog rman/773946@tstdb2
startup nomount
restore database;
alter database mount;
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
RMAN> recover database;
Starting recover at 20150623 09:54:34
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/23/2015 09:54:35
RMAN-06094: datafile 10 must be restored
###complete recovery的場景下使用skip forever
RMAN> recover database skip forever tablespace ts0611_new;
Starting recover at 20150623 10:02:19
using channel ORA_DISK_1
Executing: alter database datafile 10 offline drop
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 10:02:23
col name format a60
set linesize 120
select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362144126
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144126
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144126
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144126
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362144126
alter database open;
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362144129
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362144126
###挪回原位
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
SYS@tstdb1-SQL> alter tablespace ts0611_new online;
alter tablespace ts0611_new online
*
ERROR at line 1:
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/oradata06/testaaaaa/ts0611.dbf'
###skip forever的tablespace依然可以recover
RMAN> recover tablespace ts0611_new;
Starting recover at 20150623 10:06:11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 20150623 10:06:12
10 rows selected.
SYS@tstdb1-SQL> alter tablespace ts0611_new online;
Tablespace altered.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362144129
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144129
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362144824
10 rows selected.
////////////////
//incomplete recovery場景下使用 skip tablespace XXX，open db後還能繼續恢復這個表空間
////////////////
shutdown immediate
startup mount
restore database;
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/users01.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362138466
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362138466
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
---recover database until scn
RMAN> recover database until scn 12723362144133;
Starting recover at 20150623 10:17:25
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/23/2015 10:17:25
RMAN-06094: datafile 10 must be restored
---recover database until scn ... skip tablespace ...
RMAN> recover database until scn 12723362144133 skip tablespace ts0611_new;
Starting recover at 20150623 10:18:15
using channel ORA_DISK_1
Executing: alter database datafile 10 offline
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 10:18:19
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144133
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144133
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144133
OFFLINE 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362145858
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145858
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145858
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145858
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362145858
###開啟資料庫
alter database open resetlogs
col name format a60
set linesize 120 numwidth 16
select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362145858
select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145859
OFFLINE 0
###挪回datafile 10
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
###online tablespace，提示Datafile 10是resetlogs前的版本
SYS@tstdb1-SQL> alter tablespace ts0611_new online;
alter tablespace ts0611_new online
*
ERROR at line 1:
ORA-01190: control file or data file 10 is from before the last RESETLOGS
ORA-01110: data file 10: '/oradata06/testaaaaa/ts0611.dbf'
###recover tablespace ts0611_new依然成功
RMAN> recover tablespace ts0611_new;
Starting recover at 20150623 10:28:50
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log for thread 1 with sequence 7 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc thread=1 sequence=6
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150623 10:28:51
SYS@tstdb1-SQL> select name,status,resetlogs_change#,checkpoint_change# from v$datafile_header;
NAME STATUS RESETLOGS_CHANGE# CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ----------------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362144134 12723362145859
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362144134 12723362146664
10 rows selected.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362145859
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/users01.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362145859
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362146664
////////////////
//incomplete recovery場景下使用 skip forever tablespace XXX，open db後XXX表空間仍可以恢復
////////////////
shutdown immediate
startup mount
restore database;
mv /oradata06/testaaaaa/ts0611.dbf /oradata06/testaaaaa/ts0611.dbf.bak
SYS@tstdb1-SQL> select name,status,resetlogs_change#,checkpoint_change# from v$datafile_header;
NAME STATUS RESETLOGS_CHANGE# CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ----------------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/users01.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362054987 12723362138466
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362054987 12723362138466
ONLINE 0 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147430
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147430
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147430
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147430
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362147430
RMAN> recover database until scn 12723362147000 skip forever tablespace ts0611_new;
Starting recover at 20150623 10:40:21
using channel ORA_DISK_1
Executing: alter database datafile 10 offline drop
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log for thread 1 with sequence 7 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc thread=1 sequence=6
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:02
Finished recover at 20150623 10:40:25
alter database open resetlogs;
mv /oradata06/testaaaaa/ts0611.dbf.bak /oradata06/testaaaaa/ts0611.dbf
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362138466
RMAN> recover tablespace ts0611_new;
Starting recover at 20150623 10:48:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc
archived log for thread 1 with sequence 7 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc
archived log for thread 1 with sequence 1 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_1_1kRLv2UWQ_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_2_1kR6taPTD_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_3_1kR6teZeM_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_4_1kR6tmGw7_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_5_1kRIdmC2Q_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_6_1kRIdovOA_.arc thread=1 sequence=6
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_7_1kRKxVWK7_.arc thread=1 sequence=7
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_23/o1_mf_1_1_1kRLv2UWQ_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 20150623 10:48:21
starting full resync of recovery catalog
full resync complete
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362147001
10 rows selected.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf OFFLINE 12723362147000
alter tablespace ts0611_new online
Tablespace altered.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362148075
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
------------------------------------------------------------ ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/users01.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0329_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362147004
/oradata06/testaaaaa/ts0611.dbf ONLINE 12723362148075
SYS@tstdb1-SQL> create table aaa(id number) tablespace ts0611_new;
Table created.
////////////////////////////////
// incomplete recovery場景下使用 skip forever tablespace XXX，open db後XXX表空間仍可以恢復並驗證XXX表空間的恢復點
////////////////////////////////
###新建表空間，進行備份
create tablespace ts0625_1 datafile '/oradata06/testaaaaa/ts0625_1.dbf' size 128m;
backup database plus archivelog;
archivelog備份在tag=TAG20150625T095036、TAG20150625T095110
datafile & controlfile備份在tag=TAG20150625T095039
###建立測試表，記錄before drop時間
create table t0625_2 tablespace ts0625_1 as select * from all_users;
SYS@tstdb1-SQL> select count(*) from t0625_2;
COUNT(*)
----------------
19
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select sysdate from dual;
SYSDATE
-----------------
20150625 09:52:50
drop table t0625_1;
select sysdate from dual;
SYSDATE
-----------------
20150625 09:55:00
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
shutdown immediate
startup mount;
###restore到before drop time
RMAN> restore database until time '20150625 09:52:50';
Starting restore at 20150625 10:01:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK
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 00009 to /oradata06/testaaaaa/ts0623.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradata06/testaaaaa/ts0624.dbf
channel ORA_DISK_1: restoring datafile 00011 to /oradata06/testaaaaa/ts0624_2.dbf
channel ORA_DISK_1: restoring datafile 00012 to /oradata06/testaaaaa/ts0625.dbf
channel ORA_DISK_1: restoring datafile 00013 to /oradata06/testaaaaa/ts0625_1.dbf
channel ORA_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_25/o1_mf_nnndf_TAG20150625T095039_1kTq1Y_mg_.bkp
channel ORA_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_25/o1_mf_nnndf_TAG20150625T095039_1kTq1Y_mg_.bkp tag=TAG20150625T095039
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20150625 10:02:19
###模擬ts0625_1.dbf檔案丟失
mv /oradata06/testaaaaa/ts0625_1.dbf /oradata06/testaaaaa/ts0625_1.dbf.old
col name format a40
set numwidth 16 linesize 140 pagesize 50
SYS@tstdb1-SQL> select name,status,resetlogs_change#,checkpoint_change# from v$datafile_header;
NAME STATUS RESETLOGS_CHANGE# CHECKPOINT_CHANGE#
---------------------------------------- ------- ----------------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/users01.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362559613 12723362561576
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362559613 12723362561576
ONLINE 0 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362563571
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362563571
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362563571
/oradata06/testaaaaa/users01.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362563571
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362563571
/oradata06/testaaaaa/ts0625_1.dbf ONLINE 12723362563571
###必須recover database skip tablespace XXX才能保證recover database的順利進行
RMAN> recover database until time '20150625 09:52:50';
Starting recover at 20150625 10:06:39
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/25/2015 10:06:40
RMAN-06094: datafile 13 must be restored
RMAN> recover database skip forever tablespace ts0625_1 until time '20150625 09:52:50';
Starting recover at 20150625 10:07:12
using channel ORA_DISK_1
Executing: alter database datafile 13 offline drop
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150625 10:07:15
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562753
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562753
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362562753
OFFLINE 0
###open resetlogs
alter database open resetlogs;
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362562757
OFFLINE 0
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 0
###恢復datafile 12的至源路徑
mv /oradata06/testaaaaa/ts0625_1.dbf.old /oradata06/testaaaaa/ts0625_1.dbf
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 12723362561576
###PRIOR_RESETLOGS_CHANGE#=12723362559613，根據resetlogs_change#=12723362559613到v$archived_log查詢最大的sequence#
SYS@tstdb1-SQL> select max(sequence#) from v$archived_log where resetlogs_change#=12723362559613;
MAX(SEQUENCE#)
----------------
12
RMAN> recover tablespace ts0625_1;
Starting recover at 20150625 10:12:28
starting full resync of recovery catalog
full resync complete
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc
archived log for thread 1 with sequence 3 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc
archived log for thread 1 with sequence 4 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc
archived log for thread 1 with sequence 5 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc
archived log for thread 1 with sequence 6 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_2_1kTq3AZHd_.arc thread=1 sequence=2
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_3_1kTq8e8FN_.arc thread=1 sequence=3
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_4_1kTq8obLf_.arc thread=1 sequence=4
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_5_1kTq8-6p7_.arc thread=1 sequence=5
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_25/o1_mf_1_6_1kTqAaKlg_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 20150625 10:12:31
starting full resync of recovery catalog
full resync complete
SYS@tstdb1-SQL> select name,status,checkpoint_change#,resetlogs_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------------------------------------- ------- ------------------ -----------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757 12723362562754
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 12723362562754 12723362562754
---12723362562754包含在log sequence# 6裡
SYS@tstdb1-SQL> select sequence#,first_change#,next_change# from v$archived_log where resetlogs_change#=12723362559613 and 12723362562754 between first_change# and next_change#;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------------- ---------------- ----------------
6 12723362562746 12723362562767
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf OFFLINE 12723362562753
SYS@tstdb1-SQL> select count(*) from t0625_2;
select count(*) from t0625_2
*
ERROR at line 1:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/oradata06/testaaaaa/ts0625_1.dbf'
alter tablespace ts0625_1 online;
###驗證表t0625_2存在
SYS@tstdb1-SQL> select count(*) from t0625_2;
COUNT(*)
----------------
19
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf SYSTEM 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf ONLINE 12723362564357
12 rows selected.
SYS@tstdb1-SQL> select name,status,checkpoint_change# from v$datafile_header;
NAME STATUS CHECKPOINT_CHANGE#
---------------------------------------- ------- ------------------
/oradata06/testaaaaa/system01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/sysaux01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/undotbs01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/users01.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0422_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/xdbts1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0212_1.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0623.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0624_2.dbf ONLINE 12723362562757
/oradata06/testaaaaa/ts0625_1.dbf ONLINE 12723362564357
結論：skip tablespace /skip forever tablespace用在incomplete recovery/complete recovery的場景中都可以起到將延遲recover表空間的功能，先把db拉起來，再恢復這個表空間；incomplete recovery先把database recover到指定時間點open resetlogs，之後再執行recover tablespace XXX的效果相當於將該表空間從備份點恢復到open resetlogs後的當前最新的時間點。skip tablespace和skip forever tablespace作用上沒有明顯區別
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1726049/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- recover database delete archivelogs skip tablespace temp;報錯DatabasedeleteHive
- 冷備手工完全恢復（recover database，recover tablespace，recover datafile）Database
- recover database until cancel和 recover database區別Database
- Recover_DatabaseDatabase
- rman recover databaseDatabase
- tablespace point in time recover (tpitr)
- recover database using backup controlfile與 recover database 的區別Database
- recover database until timeDatabase
- alter database recover to logical standby xxx 很長時間，為什麼Database
- ORL Faces Database介紹Database
- mysqldump中skip-tz-utc引數介紹MySql
- 水煮oracle30----tablespace建立autoextend介紹Oracle
- javascript的this用法簡單介紹JavaScript
- spam和saint的用法介紹AI
- css url()用法介紹CSS
- getElementsByClassName()方法用法介紹
- css vm用法介紹CSS
- getCurrentPosition用法介紹
- Error querying database. XXXXXXXXXXXXX, No database selected。ErrorDatabase
- 關於Oracle Database Vault介紹OracleDatabase
- js lastIndexOf()函式的用法介紹JSASTIndex函式
- js的returnValue屬性用法介紹JS
- python BeautifulSoup用法介紹Python
- MySQL 5.7 NOT EXISTS用法介紹MySql
- jQuery css()方法用法介紹jQueryCSS
- javascript中加號(+)用法介紹JavaScript
- jQuery(html,[ownerDocument])用法介紹jQueryHTML
- replaceChild()函式用法介紹函式
- Object.isSealed()用法介紹Object
- require.js用法介紹UIJS
- recover database的四條語句區別 .Database
- 12c-RECOVER PLUGGABLE DATABASEDatabase
- Django model update的各種用法介紹Django
- angularJS的router用法簡單介紹AngularJS
- jQuery的triggerHandler()方法用法介紹jQuery
- javascript的分號(;)用法簡單介紹JavaScript
- js中getBoundingClientRect()的用法介紹JSGCclient
- 自定義元件 : TypeArray的用法與介紹元件