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

it2025-10-03  3

众所周知,我们的核心生产数据库通常都是在归档模式下执行的,更不用说还配置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 - Production With the Partitioning, OLAP and Data Mining options SQL> archive log list Database log mode              Archive Mode    --归档模式 Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     172 Next log sequence to archive   174 Current log sequence           174 SQL> set lin 130 pages 130 SQL> col name for a45 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 --创建測试表空间、用户、表 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 rownum<=5; Table 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 6 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> ! [oracle@ora10g ~]$ cd $ORACLE_BASE/oradata/ora10g [oracle@ora10g ora10g]$ ll -lrth total 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]$ 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: {<RET>=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以及新增的归档日志文件 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该文件,然后一致性打开数据库,当然,这个数据文件里的数据也就丢失了 --使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.cnblogs.com/bhlsheji/p/4020284.html

最新回复(0)