--使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日志的备份,对于那些写入数据文件的脏数据,和不一致数据而言,都是要通过归档日志来前滚到一致性状态的,只有当数据库的所有数据文件与关闭数据库时是一致的,才可以无需备份归档日志文件。