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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/53956/viewspace-1726049/