众所周知，我们的核心生产数据库通常都是在归档模式下运行的，更不用说还配置dg环境的了。开启归档，并保证所有归档不丢失，就能保证我们对数据库所做的任何修改不会丢失，归档日志可谓是恢复的根本，如果丢失归档，那么即使rman功能再强大，也无法对丢失的数
众所周知，我们的核心生产数据库通常都是在归档模式下运行的，更不用说还配置dg环境的了。开启归档，并保证所有归档不丢失，就能保证我们对数据库所做的任何修改不会丢失，归档日志可谓是恢复的根本，如果丢失归档，那么即使rman功能再强大，也无法对丢失的数据进行恢复。所以我们通常配置的rman策略就是全备+归档+控制文件自动备份。这里的归档不是指数据库创建以来生成的归档(那量也太大了)，而是当进行rman非一致性备份时新产生的那部分归档日志，用来保证数据库可以前推到一致性状态，这样才能顺利open数据库。以下的测试只是想说明归档日志对恢复数据的重要性，并没有用到rman来进行恢复。
测试一：归档日志健全未丢失
--连接到oracle，确保是运行在归档模式下[oracle@ora10g ~]$ sqlplus / as sysdba
sql*plus: release 10.2.0.1.0 - production on 8 13:46:53 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
connected to:oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options
sql> archive log listdatabase log mode archive mode --归档模式automatic archival enabledarchive destination use_db_recovery_file_destoldest online log sequence 172next log sequence to archive 174current log sequence 174sql> set lin 130 pages 130sql> col name for a45sql> select file#,name from v$datafile;
file# name---------- --------------------------------------------- 1 /u01/app/oracle/oradata/ora10g/system01.dbf 2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf 3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf 4 /u01/app/oracle/oradata/ora10g/users01.dbf 5 /u01/app/oracle/oradata/ora10g/example01.dbf
--创建测试表空间、用户、表sql> create tablespace zlm_test datafile '/u01/app/oracle/oradata/ora10g/zlm01.dbf' size 50m;
tablespace created.
sql> create user zlm identified by zlm default tablespace zlm_test;
user created.
sql> grant connect,resource to zlm; --赋权限
grant succeeded.
sql> select file#,name from v$datafile;
file# name---------- --------------------------------------------- 1 /u01/app/oracle/oradata/ora10g/system01.dbf 2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf 3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf 4 /u01/app/oracle/oradata/ora10g/users01.dbf 5 /u01/app/oracle/oradata/ora10g/example01.dbf 6 /u01/app/oracle/oradata/ora10g/zlm01.dbf --新增了6号文件作为测试表存放的物理介质
6 rows selected.
sql> create table zlm.test1 as select rownum as id,object_name from dba_objects where rownumtable created.
sql> col object_name for a15
sql> select * from zlm.test1;
id object_name---------- --------------- 1 icol$ 2 i_user1 3 con$ 4 undo$ 5 c_cobj#
--查看当前online日志文件状态sql> select group#,status,archived from v$log;
group# status arc---------- ---------------- --- 1 inactive yes 2 inactive yes 3 current no --当前日志组为3，未归档
--归档当前日志（多次）sql> alter system archive log current;
system altered.
sql> alter system archive log current;
system altered.
sql> alter system archive log current;
system altered.
这里进行了3次归档当前日志文件的操作，目的是使online日志被刷新，强制其归档，写到归档日志中去，因为我们要测试的是归档，否则恢复文件时，会自动去online日志中查找，即便是非归档模式，只要online日志还未被刷新，依旧是可以恢复的
sql> select group#,status,archived from v$log;
group# status arc---------- ---------------- --- 1 inactive yes 2 inactive yes 3 current no --虽然看起来和刚才上一步一致，但此时其实已经把第3组online日志刷新掉了
--保险起见，再归档一次（可选）sql> alter system archive log current;
system altered.
sql> select group#,status,archived from v$log;
group# status arc---------- ---------------- --- 1 current no 2 inactive yes 3 active yes --现在新一轮的第3组的日志也已经归档了
--一致性关闭数据库，在os级别删除测试文件datafile 6sql> shutdown immediate
database closed.database dismounted.oracle instance shut down.sql> ![oracle@ora10g ~]$ cd $oracle_base/oradata/ora10g[oracle@ora10g ora10g]$ ll -lrthtotal 1.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]$ exitexit
--重启数据库sql> startuporacle instance started.
total system global area 285212672 bytesfixed size 1218992 bytesvariable size 88082000 bytesdatabase buffers 192937984 bytesredo buffers 2973696 bytesdatabase mounted.ora-01157: cannot identify/lock data file 6 - see dbwr trace fileora-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
可以看到，此时是无法open数据库的，因为数据库文件物理上已经不存在，而在控制文件中是有记录的，这里提示的是“cannot identify/lock data file 6”，而当如果仅仅是物理上存在，数据文件头中的信息与控制文件中记录的数据文件头信息不一致时，会提示xxx文件需要恢复
--手动创建一个datafile 6sql> alter database create datafile 6;
database altered.
注意，此时仅仅是创建了一个不一致的datafile 6而已，也可以通过rman的restore datafile 6;命令来实现，作用是一样的
--恢复datafile 6sql> recover datafile 6;ora-00279: change 983806 generated at 09/18/2014 13:47:22 needed for thread 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_174_%u_.arcora-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 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_175_%u_.arcora-00280: change 983923 for thread 1 is in sequence #175ora-00278: log file '/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_174_b1nwmrpv_.arc' no longer neededfor 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以及新增的归档日志文件sql> shutdown immediatedatabase 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 -lrthtotal 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 -lrthtotal 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]$ exitexit
--启动数据库sql> startuporacle instance started.
total system global area 285212672 bytesfixed size 1218992 bytesvariable size 88082000 bytesdatabase buffers 192937984 bytesredo buffers 2973696 bytesdatabase mounted.ora-01157: cannot identify/lock data file 6 - see dbwr trace fileora-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
--再次创建数据文件datafile 6sql> 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 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_174_%u_.arcora-00280: change 983806 for thread 1 is in sequence #174
specify log: {=suggested | filename | auto | cancel}autoora-00279: change 983923 generated at 09/18/2014 13:49:44 needed for thread 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_175_%u_.arcora-00280: change 983923 for thread 1 is in sequence #175ora-00278: log file '/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_174_b1nwmrpv_.arc' no longer neededfor this recovery
ora-00279: change 983927 generated at 09/18/2014 13:49:51 needed for thread 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_176_%u_.arcora-00280: change 983927 for thread 1 is in sequence #176ora-00278: log file '/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_175_b1nwmzo4_.arc' no longer neededfor this recovery
ora-00279: change 983931 generated at 09/18/2014 13:49:56 needed for thread 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_177_%u_.arcora-00280: change 983931 for thread 1 is in sequence #177ora-00278: log file '/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_176_b1nwn43r_.arc' no longer neededfor this recovery
ora-00279: change 983974 generated at 09/18/2014 13:51:24 needed for thread 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_178_%u_.arcora-00280: change 983974 for thread 1 is in sequence #178ora-00278: log file '/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_177_b1nwpwxb_.arc' no longer neededfor 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 statuslinux error: 2: no such file or directoryadditional information: 3
当执行auto后，第一个建议的归档位置是174，然后到175、176、177，都没有问题，一直到178，提示文件无法找到，由于178、179、180这3个归档日志被移走了，模拟被删除的情况，数据库无法自动获取到这3个归档日志，也就无法把datafile 6前推到数据库正常关闭前的一致性状态，这个时候想要恢复，就只能通过bbed工具来修改数据文件头信息来实现了，数据库自身以无法完成这个任务，如果这个数据文件对整个数据库而言并不是非常重要，那么可以先offline该文件，然后一致性打开数据库，当然，这个数据文件中的数据也就丢失了
--使datafile 6 offlinesql> 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 timeora-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 -lrthtotal 516kdrwxr-x--- 2 oracle oinstall 4.0k sep 12 10:33 2014_09_12drwxr-x--- 2 oracle oinstall 4.0k sep 15 17:19 2014_09_15drwxr-x--- 2 oracle oinstall 4.0k sep 17 12:30 2014_09_16drwxr-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_.arcdrwxr-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 -lrthtotal 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]$ exitexit
sql> startup mount
oracle instance started.
total system global area 285212672 bytesfixed size 1218992 bytesvariable size 88082000 bytesdatabase buffers 192937984 bytesredo buffers 2973696 bytesdatabase 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 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_178_%u_.arcora-00280: change 983974 for thread 1 is in sequence #178
specify log: {=suggested | filename | auto | cancel}autolog 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 1ora-00289: suggestion : /u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_174_%u_.arcora-00280: change 983806 for thread 1 is in sequence #174
specify log: {=suggested | filename | auto | cancel}/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_174_%u_.arc
specify log: {=suggested | filename | auto | cancel}/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_175_%u_.arc
specify log: {=suggested | filename | auto | cancel}/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_176_%u_.arc
specify log: {=suggested | filename | auto | cancel}/u01/app/oracle/flash_recovery_area/ora10g/archivelog/2014_09_18/o1_mf_1_177_%u_.arc
specify log: {=suggested | filename | auto | cancel}/u01/app/oracle/flash_recovery_area/ora10g/archivelog/o1_mf_1_178_%u_.arc --注意区别，是mv后的新路径......以此类推，这样也是可以完成recover的，只不过麻烦一些，但前提是，这些物件还存在！
总结：鉴于归档日志对于数据库的恢复非常重要，因此对归档日志的备份也要重视起来。可以这么说，归档日志就是对online日志的备份，对于那些写入数据文件的脏数据，和不一致数据而言，都是要通过归档日志来前滚到一致性状态的，只有当数据库的所有数据文件与关闭数据库时是一致的，才可以无需备份归档日志文件。