Oracle在归档模式下删除非系统文件的恢复(2)

SQL> delete from zlm.test1 where id=5;

 

1 row deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from zlm.test1;

 

        ID OBJECT_NAME

---------- ---------------

         1 ICOL$

         2 I_USER1

         3 CON$

         4 UNDO$

 

SQL> select group#,status,archived from v$log;

 

    GROUP# STATUS           ARC

---------- ---------------- ---

         1 CURRENT          NO

         2 INACTIVE         YES

         3 INACTIVE         YES

 

--同样的,切3次归档,把online日志刷到归档去

SQL> alter system archive log current;

 

System altered.

 

SQL> alter system archive log current;

 

System altered.

 

SQL> alter system archive log current;

 

System altered.

 

SQL> select group#,status,archived from v$log;

 

    GROUP# STATUS           ARC

---------- ---------------- ---

         1 CURRENT          NO    --此处online日志已经被刷新

         2 ACTIVE           YES

         3 ACTIVE           YES

 

--关闭数据库,在os级别删除datafile 6以及新增的归档日志文件

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@ora10g ~]$ cd $ORACLE_BASE/oradata/ora10g

[oracle@ora10g ora10g]$ rm -f zlm01.dbf 

[oracle@ora10g ora10g]$ cd $ORACLE_BASE/flash_recovery_area/ORA10G/archivelog/2014_09_18

[oracle@ora10g 2014_09_18]$ ll -lrth

total 9.5M

-rw-r----- 1 oracle oinstall 2.4M Sep 18 10:10 o1_mf_1_172_b1nhskdd_.arc

-rw-r----- 1 oracle oinstall 469K Sep 18 10:14 o1_mf_1_173_b1nj0wxp_.arc

-rw-r----- 1 oracle oinstall 6.1M Sep 18 13:49 o1_mf_1_174_b1nwmrpv_.arc

-rw-r----- 1 oracle oinstall 1.0K Sep 18 13:49 o1_mf_1_175_b1nwmzo4_.arc

-rw-r----- 1 oracle oinstall 2.5K Sep 18 13:49 o1_mf_1_176_b1nwn43r_.arc

-rw-r----- 1 oracle oinstall  37K Sep 18 13:51 o1_mf_1_177_b1nwpwxb_.arc

-rw-r----- 1 oracle oinstall 477K Sep 18 14:01 o1_mf_1_178_b1nx9ry9_.arc

-rw-r----- 1 oracle oinstall 1.0K Sep 18 14:01 o1_mf_1_179_b1nx9y1k_.arc

-rw-r----- 1 oracle oinstall 7.0K Sep 18 14:01 o1_mf_1_180_b1nxb6q1_.arc

 

这里14:01生成的3个归档日志,是我在删除测试表数据库后归档current online日志生成的

 

--为了方便恢复,移走这3个归档日志(未真正删除)

[oracle@ora10g 2014_09_18]$ mv *178* ../

[oracle@ora10g 2014_09_18]$ mv *179* ../

[oracle@ora10g 2014_09_18]$ mv *180* ../

[oracle@ora10g 2014_09_18]$ ll -lrth

total 9.0M

-rw-r----- 1 oracle oinstall 2.4M Sep 18 10:10 o1_mf_1_172_b1nhskdd_.arc

-rw-r----- 1 oracle oinstall 469K Sep 18 10:14 o1_mf_1_173_b1nj0wxp_.arc

-rw-r----- 1 oracle oinstall 6.1M Sep 18 13:49 o1_mf_1_174_b1nwmrpv_.arc

-rw-r----- 1 oracle oinstall 1.0K Sep 18 13:49 o1_mf_1_175_b1nwmzo4_.arc

-rw-r----- 1 oracle oinstall 2.5K Sep 18 13:49 o1_mf_1_176_b1nwn43r_.arc

-rw-r----- 1 oracle oinstall  37K Sep 18 13:51 o1_mf_1_177_b1nwpwxb_.arc

[oracle@ora10g 2014_09_18]$ exit

exit

 

--启动数据库

SQL> startup

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              88082000 bytes

Database Buffers          192937984 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'

 

--再次创建数据文件datafile 6

SQL> alter database create datafile 6;

 

Database altered.

 

--对数据文件datafile 6进行介质恢复

SQL> recover datafile 6;

ORA-00279: change 983806 generated at 09/18/2014 13:47:22 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_%u_.arc

ORA-00280: change 983806 for thread 1 is in sequence #174

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 983923 generated at 09/18/2014 13:49:44 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_%u_.arc

ORA-00280: change 983923 for thread 1 is in sequence #175

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_b1nwmrpv_.arc' no longer needed

for this recovery

 

 

ORA-00279: change 983927 generated at 09/18/2014 13:49:51 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_176_%u_.arc

ORA-00280: change 983927 for thread 1 is in sequence #176

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_b1nwmzo4_.arc' no longer needed

for this recovery

 

 

ORA-00279: change 983931 generated at 09/18/2014 13:49:56 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_177_%u_.arc

ORA-00280: change 983931 for thread 1 is in sequence #177

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_176_b1nwn43r_.arc' no longer needed

for this recovery

 

 

ORA-00279: change 983974 generated at 09/18/2014 13:51:24 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_178_%u_.arc

ORA-00280: change 983974 for thread 1 is in sequence #178

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_177_b1nwpwxb_.arc' no longer needed

for this recovery

 

 

ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_178_b1nx9ry9_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

当执行auto后,第一个建议的归档位置是174,然后到175、176、177,都没有问题,一直到178,提示文件无法找到,由于178、179、180这3个归档日志被移走了,模拟被删除的情况,数据库无法自动获取到这3个归档日志,也就无法把datafile 6前推到数据库正常关闭前的一致性状态,这个时候想要恢复,就只能通过BBED工具来修改数据文件头信息来实现了,数据库自身以无法完成这个任务,如果这个数据文件对整个数据库而言并不是非常重要,那么可以先offline该文件,然后一致性打开数据库,当然,这个数据文件中的数据也就丢失了

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/890dfb7716b5753121347e27a8856394.html