1、模拟数据文件offline drop;
alter database datafile 5 offline drop;
2、手工切换检查点,使检查点更新。
alter system checkpoint;
3、将redo中的日志覆盖。
alter system switch logfile; --切换几次
4、查看检查点情况。
select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
(to_char(CHECKPOINT_TIME,'yyyy')-1988)*12*31*24*3600+
(to_char(CHECKPOINT_TIME,'mm')-1)*31*24*3600
+(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
+to_char(CHECKPOINT_TIME,'hh24')*3600
+to_char(CHECKPOINT_TIME,'mi')*60
+to_char(CHECKPOINT_TIME,'ss') CHECKPOINT_TIME_scn
from v$datafile order by 1;
FILE# CHECKPOINT_TIME_FILE CHECKPOINT_TIME_SCN
---------- -------------------------------------- -------------------
1 2013-12-07 21:29:34 833578174
2 2013-12-07 21:29:34 833578174
3 2013-12-07 21:29:34 833578174
4 2013-12-07 21:29:34 833578174
5 2013-12-07 21:28:19 833578099
5、SQL> alter database datafile 5 online; --尝试onlin需recover
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/opt/oracle/oradata/ORCL/test.dbf'
6、recover datafile 5;失败,日志已被覆盖。
7、用bbed查看4号文件的scn。
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00107969
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x31af64be
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000040
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
修改datafile 5 的scn
modify /x 00107969 offset 484
modify /x 31af64be offset 492
sum apply
datafile 5修改后
p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00107969
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x31af64be
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000025
ub4 kcrbabno @504 0x00000340
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
SQL> select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
(to_char(CHECKPOINT_TIME,'yyyy')-1988)*12*31*24*3600+
(to_char(CHECKPOINT_TIME,'mm')-1)*31*24*3600
2 3 4 +(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
+to_char(CHECKPOINT_TIME,'hh24')*3600
5 6 +to_char(CHECKPOINT_TIME,'mi')*60
+to_char(CHECKPOINT_TIME,'ss') CHECKPOINT_TIME_scn
7 from v$datafile order by 1; 8
FILE# CHECKPOINT_TIME_FILE CHECKPOINT_TIME_SCN
---------- -------------------------------------- -------------------
1 2013-12-07 21:29:34 833578174
2 2013-12-07 21:29:34 833578174
3 2013-12-07 21:29:34 833578174
4 2013-12-07 21:29:34 833578174
5 2013-12-07 21:28:19 833578099
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/opt/oracle/oradata/ORCL/test.dbf'
SQL> recover datafile 5;
Media recovery complete.
SQL> select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
2 (to_char(CHECKPOINT_TIME,'yyyy')-1988)*12*31*24*3600+
3 (to_char(CHECKPOINT_TIME,'mm')-1)*31*24*3600
4 +(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
5 +to_char(CHECKPOINT_TIME,'hh24')*3600
6 +to_char(CHECKPOINT_TIME,'mi')*60
7 +to_char(CHECKPOINT_TIME,'ss') CHECKPOINT_TIME_scn
8 from v$datafile order by 1;
FILE# CHECKPOINT_TIME_FILE CHECKPOINT_TIME_SCN
---------- -------------------------------------- -------------------
1 2013-12-07 21:29:34 833578174
2 2013-12-07 21:29:34 833578174
3 2013-12-07 21:29:34 833578174
4 2013-12-07 21:29:34 833578174
5 2013-12-07 21:28:19 833578099
SQL> alter database datafile 5 online;
Database altered.
SQL> select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
2 (to_char(CHECKPOINT_TIME,'yyyy')-1988)*12*31*24*3600+
3 (to_char(CHECKPOINT_TIME,'mm')-1)*31*24*3600
4 +(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
5 +to_char(CHECKPOINT_TIME,'hh24')*3600
6 +to_char(CHECKPOINT_TIME,'mi')*60
7 +to_char(CHECKPOINT_TIME,'ss') CHECKPOINT_TIME_scn
from v$datafile order by 1; 8
FILE# CHECKPOINT_TIME_FILE CHECKPOINT_TIME_SCN
---------- -------------------------------------- -------------------
1 2013-12-07 21:29:34 833578174
2 2013-12-07 21:29:34 833578174
3 2013-12-07 21:29:34 833578174
4 2013-12-07 21:29:34 833578174
5 2013-12-07 21:34:18 833578458
SQL> alter system checkpoint;
System altered.
SQL> select file#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
(to_char(CHECKPOINT_TIME,'yyyy')-1988)*12*31*24*3600+
(to_char(CHECKPOINT_TIME,'mm')-1)*31*24*3600
2 3 4 +(to_char(CHECKPOINT_TIME,'dd')-1)*24*3600
+to_char(CHECKPOINT_TIME,'hh24')*3600
5 6 +to_char(CHECKPOINT_TIME,'mi')*60
7 +to_char(CHECKPOINT_TIME,'ss') CHECKPOINT_TIME_scn
8 from v$datafile order by 1;
FILE# CHECKPOINT_TIME_FILE CHECKPOINT_TIME_SCN
---------- -------------------------------------- -------------------
1 2013-12-07 21:34:35 833578475
2 2013-12-07 21:34:35 833578475
3 2013-12-07 21:34:35 833578475
4 2013-12-07 21:34:35 833578475
5 2013-12-07 21:34:35 833578475
已成功offline。