日志文件损坏恢复（实验）

来源：互联网发布：mac软件删不掉编辑：程序博客网时间：2024/04/28 07:42

一，单个日志成员文件损坏（某个日志组下有多个成员文件，单个成员文件损坏）

二、数据库正常关闭，发现某一个组下面所有成员文件都损坏了。

1、丢失的组不是CURRENT 组的所有成员文件。

2、丢失的组是CURRENT组所成员文件

三、数据库非正常关闭，发现某一个日志组下的成员文件都损坏了（非正常关闭来不及写脏数据）

查看当前日志组成员：SQL> select GROUP#,STATUS,MEMBER from v$logfile order by 1;    GROUP# STATUS  MEMBER---------- ------- ----------------------------------------         1         /oracle/oradata/testdb/redo01c.log         1         /oracle/oradata/testdb/redo01a.log         1         /oracle/oradata/testdb/redo01b.log         2         /oracle/oradata/testdb/redo02a.log         2         /oracle/oradata/testdb/redo02c.log         2         /oracle/oradata/testdb/redo02b.log         3         /oracle/oradata/testdb/redo03c.log         3         /oracle/oradata/testdb/redo03b.log         3         /oracle/oradata/testdb/redo03d.log         3         /oracle/oradata/testdb/redo03a.log10 rows selected.  删除一个日志成员（故障点） [oracle@oracledb testdb]$ rm -rf redo03a.log  发现丢失日志文件以后，在v$logfile中没有立即标示出INVALID，此时重启数据库。 在丢失日志成员文件情况下，数据库能够正常重启。此时才显示出INVALID。  SQL> select GROUP#,STATUS,MEMBER from v$logfile order by 1;    GROUP# STATUS  MEMBER---------- ------- ----------------------------------------         1         /oracle/oradata/testdb/redo01c.log         1         /oracle/oradata/testdb/redo01a.log         1         /oracle/oradata/testdb/redo01b.log         2         /oracle/oradata/testdb/redo02a.log         2         /oracle/oradata/testdb/redo02c.log         2         /oracle/oradata/testdb/redo02b.log         3         /oracle/oradata/testdb/redo03c.log         3         /oracle/oradata/testdb/redo03b.log         3         /oracle/oradata/testdb/redo03d.log         3 INVALID /oracle/oradata/testdb/redo03a.log进行恢复:（1）先删除数据库中对应的成员文件记录：SQL> alter database drop logfile member '/oracle/oradata/testdb/redo03a.log';Database altered.（2）在重新添加新的成员日志文件：SQL> alter database add logfile member '/oracle/oradata/testdb/redo03a.log' to group 3;Database altered.此时恢复完毕，可以正常使用了。

二、数据库正常关闭，发现某一个组下面所有成员文件都损坏了。

1、丢失的组不是CURRENT 组的所有成员文件。

查看前置环境：SQL> select group#,status from v$log;    GROUP# STATUS---------- ----------------         1 INACTIVE         2 CURRENT         3 INACTIVE已知group 2 是current正常关库：SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.删除日志group 1所有成员文件（故障点）：[oracle@oracledb testdb]$ rm redo01*尝试打开实例：SQL> startupORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started.Total System Global Area 1058779136 bytesFixed Size                  1304616 bytesVariable Size             704645080 bytesDatabase Buffers          348127232 bytesRedo Buffers                4702208 bytesDatabase mounted.ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01a.log'ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01b.log'ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01c.log'只能打开到mount阶段，并且报错。错误恢复：此时数据库已经处于mount状态。查看日志组信息，发现并非current组所有成员丢失。SQL> select group#,status from v$log;    GROUP# STATUS---------- ----------------         1 INACTIVE         3 INACTIVE         2 CURRENT（1）用clear日志组的方式，清理日志组。SQL> alter database clear logfile group 1;Database altered.清理的过程中，oracle实例自动重建了刚刚丢失的所有日志文件。（2）查看，恢复的所有日志文件（被重建）[oracle@oracledb testdb]$ ls -a |grep 'redo01'  redo01a.logredo01b.logredo01c.log此时数据就能打开了：SQL> alter database open;Database altered.

2、丢失的组是CURRENT组所成员文件

查看前置环境：SQL> select group#,status from v$log;    GROUP# STATUS---------- ----------------         1 UNUSED         2 CURRENT         3 INACTIVE发现group 2是CURRENT组正常关闭数据库：SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.删除CURRENT组下所有成员文件：（故障点）[oracle@oracledb testdb]$ rm -rf redo02*[oracle@oracledb testdb]$ ls -a | grep 'redo02'打开数据库,提示错误。SQL> startup;ORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started.Total System Global Area 1058779136 bytesFixed Size                  1304616 bytesVariable Size             704645080 bytesDatabase Buffers          348127232 bytesRedo Buffers                4702208 bytesDatabase mounted.ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/oracle/oradata/testdb/redo02a.log'ORA-00312: online log 2 thread 1: '/oracle/oradata/testdb/redo02b.log'ORA-00312: online log 2 thread 1: '/oracle/oradata/testdb/redo02c.log'查看发现丢失的Group 2 是CURRENT组SQL> select group#,status from v$log;    GROUP# STATUS---------- ----------------         1 UNUSED         3 INACTIVE         2 CURRENT错误恢复：（1）尝试，直接clear group 2（出错）。SQL> alter database clear logfile group 2;alter database clear logfile group 2*ERROR at line 1:ORA-00350: log 2 of instance test (thread 1) needs to be archivedORA-00312: online log 2 thread 1: '/oracle/oradata/testdb/redo02a.log'ORA-00312: online log 2 thread 1: '/oracle/oradata/testdb/redo02b.log'ORA-00312: online log 2 thread 1: '/oracle/oradata/testdb/redo02c.log'（2）采用关键字UNARCHIVED（指明，不进行归档操作）的clear操作。SQL> alter database clear unarchived logfile group 2;Database altered.发现所有日志文件被重建了：[oracle@oracledb testdb]$ ls -a | grep 'redo02'redo02a.logredo02b.logredo02c.log现在可以打开数据库了。SQL> alter database open;Database altered.

三、数据库非正常关闭，发现某一个日志组下的成员文件都损坏了（非正常关闭来不及写脏数据）

1、丢失的是Active状态下日志组所有成员文件

制作脏数据：SQL> select * from scott.emp where empno=7788;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20SQL> update scott.emp set sal=5000 where empno=7788;1 row updated.SQL> commit;Commit complete.SQL> select * from scott.emp where empno=7788;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7788 SCOTT      ANALYST         7566 19-APR-87       5000                    20切换日志组：SQL> select group#,status from v$log;    GROUP# STATUS---------- ----------------         1 CURRENT         2 UNUSED         3 INACTIVESQL> alter system switch logfile;System altered.SQL> select group#,status from v$log;    GROUP# STATUS---------- ----------------         1 ACTIVE         2 CURRENT         3 INACTIVE发现此时，Group 1是Active状态。异常方式关闭数据库：SQL> shutdown abort;ORACLE instance shut down.把日志组1 （ACTIVE）下的所有成员文件删除；（故障点）[oracle@oracledb testdb]$ rm -rf redo01*[oracle@oracledb testdb]$ ls -a | grep redo01[oracle@oracledb testdb]$ 启动数据库，报错。SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started.Total System Global Area 1058779136 bytesFixed Size                  1304616 bytesVariable Size             704645080 bytesDatabase Buffers          348127232 bytesRedo Buffers                4702208 bytesDatabase mounted.ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01c.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01b.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01a.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3尝试，用clear方式清除Group 1，出错，表示需要实例恢复。（出错）SQL> alter database clear logfile group 1  2  ;alter database clear logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01a.log'ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01b.log'ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01c.log'尝试，用archived 方式clear Group 1，出错，表示需要实例恢复。（出错）

SQL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01a.log'ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01b.log'ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo01c.log'故障恢复：<span style="color:#ff0000;">（修改参数文件，添加隐含参数“_allow_resetlogs_corruption=true”）</span>（1）创建参数文件（关闭状态下可以执行）SQL> create pfile from spfile;File created.（2）修改参数文件，添加隐含参数[oracle@oracledb testdb]$ cd $ORACLE_HOME/dbs[oracle@oracledb dbs]$ vi inittest.ora参数文件添加一行：_allow_resetlogs_corruption=true（3）从参数文件强制启动到mount状态SQL> startup mount pfile=$ORACLE_HOME/dbs/inittest.ora force;ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecatedORACLE instance started.Total System Global Area 1058779136 bytesFixed Size                  1304616 bytesVariable Size             704645080 bytesDatabase Buffers          348127232 bytesRedo Buffers                4702208 bytesDatabase mounted.查看参数_allow_resetlogs_corruptionSQL> show parameter _allow_resetlogs_corruption;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------_allow_resetlogs_corruption          boolean     TRUE（4）用 resetlogs方式打开数据库，出错：（出错）SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01139: RESETLOGS option only valid after an incomplete database recovery（5）假意做不完全恢复。SQL> recover database until cancel;     --假意做不完全恢复。ORA-00279: change 2028843 generated at 06/09/2014 18:57:18 needed for thread 1ORA-00289: suggestion : /oracle/oradata/archive2/archive_9c5a9c8e_1_845771110_39.logORA-00280: change 2028843 for thread 1 is in sequence #39Specify log: {<RET>=suggested | filename | AUTO | CANCEL}autoORA-00279: change 2029584 generated at 06/09/2014 19:05:28 needed for thread 1ORA-00289: suggestion : /oracle/oradata/archive2/archive_9c5a9c8e_1_845771110_40.logORA-00280: change 2029584 for thread 1 is in sequence #40ORA-00278: log file '/oracle/oradata/archive2/archive_9c5a9c8e_1_845771110_39.log' no longer needed for this recoveryORA-00308: cannot open archived log '/oracle/oradata/archive2/archive_9c5a9c8e_1_845771110_40.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3（6）再尝试resetlogs打开库：<span style="color:#33cc00;">（居然打开了）</span>SQL> alter database open resetlogs;Database altered.如果没有打开，如下；SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00600: internal error code, arguments: [2662], [0], [354496], [0],[354513], [4194432], [], [], [], [], [], []Process ID: 5083Session ID: 1 Serial number: 5退出会话，重新用pfile 来启动数据库SQL> startup pfile=$ORACLE_HOME/dbs/inittest.ora;ORACLE instance started.Total System Global Area 1071333376 bytesFixed Size                  1341312 bytesVariable Size             620759168 bytesDatabase Buffers          444596224 bytesRedo Buffers                4636672 bytesDatabase mounted.Database opened.（7）日志组序列重置了。SQL> select sequence#,group#,status from v$log; SEQUENCE#     GROUP# STATUS---------- ---------- ----------------         1          1 CURRENT         0          2 UNUSED         0          3 UNUSED检查脏数据是否有丢失，居然没有丢失

SQL> select * from scott.emp where empno=7788;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7788 SCOTT      ANALYST         7566 19-APR-87       5000                    20  并且重建了已经丢失的Group1

SQL> select * from v$logfile order by 1;    GROUP# STATUS  TYPE    MEMBER                                   IS_---------- ------- ------- ---------------------------------------- ---         1         ONLINE  /oracle/oradata/testdb/redo01c.log       NO         1         ONLINE  /oracle/oradata/testdb/redo01a.log       NO         1         ONLINE  /oracle/oradata/testdb/redo01b.log       NO         2         ONLINE  /oracle/oradata/testdb/redo02a.log       NO         2         ONLINE  /oracle/oradata/testdb/redo02c.log       NO         2         ONLINE  /oracle/oradata/testdb/redo02b.log       NO         3         ONLINE  /oracle/oradata/testdb/redo03d.log       NO         3         ONLINE  /oracle/oradata/testdb/redo03c.log       NO         3         ONLINE  /oracle/oradata/testdb/redo03a.log       NO         3         ONLINE  /oracle/oradata/testdb/redo03b.log       NO10 rows selected.

注意：resetlogs 打开数据库，以及unarchived 清除日志组后需要立即重新备份数据库。

0 0