在归档模式下删除非系统文件的恢复 - 数据库编程

7G -rw-r----- 1 oracle oinstall 51M Sep 5 10:13 test02.dbf -rw-r----- 1 oracle oinstall 301M Sep 5 10:13 test01.dbf -rw-r----- 1 oracle oinstall 201M Sep 16 16:56 temp01.dbf -rw-r----- 1 oracle oinstall 51M Sep 18 13:49 redo02.log -rw-r----- 1 oracle oinstall 51M Sep 18 13:51 redo03.log -rw-r----- 1 oracle oinstall 51M Sep 18 13:51 zlm01.dbf -rw-r----- 1 oracle oinstall 31M Sep 18 13:51 users01.dbf -rw-r----- 1 oracle oinstall 166M Sep 18 13:51 undotbs01.dbf -rw-r----- 1 oracle oinstall 561M Sep 18 13:51 system01.dbf -rw-r----- 1 oracle oinstall 271M Sep 18 13:51 sysaux01.dbf -rw-r----- 1 oracle oinstall 51M Sep 18 13:51 redo01.log -rw-r----- 1 oracle oinstall 101M Sep 18 13:51 example01.dbf -rw-r----- 1 oracle oinstall 7.2M Sep 18 13:52 control03.ctl -rw-r----- 1 oracle oinstall 7.2M Sep 18 13:52 control02.ctl -rw-r----- 1 oracle oinstall 7.2M Sep 18 13:52 control01.ctl [oracle@ora10g ora10g]$ rm -f zlm01.dbf [oracle@ora10g ora10g]$ 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'
可以看到，此时是无法open数据库的，因为数据库文件物理上已经不存在，而在控制文件中是有记录的，这里提示的是“cannot identify/lock data file 6”，而当如果仅仅是物理上存在，数据文件头中的信息与控制文件中记录的数据文件头信息不一致时，会提示xxx文件需要恢复
--手动创建一个datafile 6 SQL> alter database create datafile 6;

Database altered.
注意，此时仅仅是创建了一个不一致的datafile 6而已，也可以通过RMAN的restore datafile 6;命令来实现，作用是一样的
--恢复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: {=suggested | filename | AUTO | CANCEL} auto --此处输入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

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$ 5 C_COBJ#
尽管online日志没有了，但由于归档日志从头至尾都没有删除过，很快地数据库完成了介质恢复，顺利地open了，测试表数据也未丢失
测试二：更改表内容后的归档日志全部丢失

--删除测试表中第5条记录并提交 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以及

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