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

--使datafile 6 offline

SQL> alter database datafile 6 offline;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select file#,name,status from v$datafile;

 

     FILE# NAME                                          STATUS

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

         1 /u01/app/Oracle/oradata/ora10g/system01.dbf   SYSTEM

         2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf  ONLINE

         3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf   ONLINE

         4 /u01/app/oracle/oradata/ora10g/users01.dbf    ONLINE

         5 /u01/app/oracle/oradata/ora10g/example01.dbf  ONLINE

         6 /u01/app/oracle/oradata/ora10g/zlm01.dbf      OFFLINE

 

6 rows selected.

 

SQL> select * from zlm.test1;

select * from zlm.test1

                  *

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

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

 

虽然打开了数据库,但测试数据表还是丢失了,丢失了归档,又没有备份过归档,那么丢数据库是在所难免得了,又一次证明了归档对数据恢复的重要性,由于刚才并未真正地删除归档,只是使了一个trick,那么就当我们之前对归档做了个手动备份,现在来恢复丢失的归档(mv回原归档路径)

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

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

[oracle@ora10g archivelog]$ ll -lrth

total 516K

drwxr-x--- 2 oracle oinstall 4.0K Sep 12 10:33 2014_09_12

drwxr-x--- 2 oracle oinstall 4.0K Sep 15 17:19 2014_09_15

drwxr-x--- 2 oracle oinstall 4.0K Sep 17 12:30 2014_09_16

drwxr-x--- 2 oracle oinstall 4.0K Sep 18 10:15 2014_09_17

-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

drwxr-x--- 2 oracle oinstall 4.0K Sep 18 14:25 2014_09_18

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

[oracle@ora10g archivelog]$ cd 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

[oracle@ora10g 2014_09_18]$ exit

exit

 

SQL> startup mount

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.

SQL> alter database datafile 6 online;

 

Database altered.

 

SQL> recover datafile 6;

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

 

 

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

auto

Log applied.

Media recovery complete.

SQL> alter database open;

 

Database altered.

 

SQL> select * from zlm.test1;

 

        ID OBJECT_NAME

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

         1 ICOL$

         2 I_USER1

         3 CON$

         4 UNDO$

 

当恢复了归档后,再次对datafile 6进行介质恢复,再open数据库以后,之前丢失的数据又回来了。

注意:当归档路径在OS上物理存在,只是默认位置不是FRA指定的路径,那么当执行recover 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}

/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_%u_.arc

 

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

/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_%u_.arc

 

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

/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_176_%u_.arc

 

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

/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_177_%u_.arc

 

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

/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/o1_mf_1_178_%u_.arc    --注意区别,是mv后的新路径

...

...

以此类推,这样也是可以完成recover的,只不过麻烦一些,但前提是,这些物件还存在!

 

总结:鉴于归档日志对于数据库的恢复非常重要,因此对归档日志的备份也要重视起来。可以这么说,归档日志就是对online日志的备份,对于那些写入数据文件的脏数据,和不一致数据而言,都是要通过归档日志来前滚到一致性状态的,只有当数据库的所有数据文件与关闭数据库时是一致的,才可以无需备份归档日志文件。

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

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