非归档数据文件offline drop处理

来源：互联网发布：three.js vr全景漫游编辑：程序博客网时间：2024/05/16 06:02

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;

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

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。

0 0