日志损坏解决一则

今天登陆数据库时报错：ORA-00257 archiver error. Connect internal only, until freed。

第一反映是归档出问题了，于是ssh到服务器上，用sys登陆数据库，查看归档情况：
SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      location=/data/oradata/member/
                                                 archivelog
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_min_succeed_dest         integer     1

SQL> select * fro v$log; 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
         1          1        155  524288000          1 NO  CURRENT             5567185305 09-1月 -08
         2          1        135  524288000          1 NO  INACTIVE            5557439250 26-11月-07
         3          1        154  524288000          1 NO  INACTIVE            5566792959 07-1月 -08

归档参数正常。
        
查看归档路径是否存在：
[oracle@member archivelog]$ ls -l /data/oradata/member/archivelog/
total 10687260
-rw-r-----    1 oracle   oinstall      512 Jan  9 15:09 1_135.dbf
-rw-r-----    1 oracle   oinstall 524287488 Dec 11 12:03 1_136.dbf
-rw-r-----    1 oracle   oinstall 524287488 Dec 11 12:05 1_137.dbf
......
-rw-r-----    1 oracle   oinstall 17809408 Jan  9 15:13 1_156.dbf
-rw-r-----    1 oracle   oinstall 123608064 Jan  9 15:14 1_157.dbf
-rw-r-----    1 oracle   oinstall 524286976 Jan  9 15:26 1_158.dbf        

看来路径也没有问题。

查看归档路径所在磁盘分区是否满了：
[oracle@member bdump]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             9.9G  419M  9.0G   5% /
/dev/sda1              99M   37M   58M  39% /boot
/dev/sdb1             404G  310G   74G  81% /data
/dev/sda8              46G   24G   21G  54% /home
/dev/sda2             9.9G  7.4G  2.0G  79% /opt
none                  2.0G     0  2.0G   0% /dev/shm
/dev/sda5             9.9G  4.7G  4.7G  50% /usr
/dev/sda6             9.9G  913M  8.5G  10% /var

可以看到空间还是足够的。

再查看alert文件信息：
Wed Jan  9 14:58:11 2008
ORA-16038: log 2 sequence# 135 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/data/oradata/member/redo02.log'
Wed Jan  9 14:58:11 2008
Errors in file /opt/ora9/admin/member/bdump/member_arc1_23286.trc:
ORA-16038: log 2 sequence# 135 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/data/oradata/member/redo02.log'
Wed Jan  9 14:58:11 2008
ARC0: Failed to archive log 2 thread 1 sequence 135
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Jan  9 14:58:11 2008
ORACLE Instance member - Archival Error
ARCH: Connecting to console port...
Wed Jan  9 14:58:11 2008
ORA-16014: log 2 sequence# 135 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/data/oradata/member/redo02.log'
ARCH: Connecting to console port...
ARCH:
Wed Jan  9 14:58:11 2008
ORA-16014: log 2 sequence# 135 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/data/oradata/member/redo02.log'
Wed Jan  9 14:58:11 2008
Errors in file /opt/ora9/admin/member/bdump/member_arc0_23284.trc:
ORA-16014: log 2 sequence# 135 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/data/oradata/member/redo02.log'

从alert文件中看到发生错误的原因是不能对redo02进行归档，尝试手工归档：
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 2 sequence# 135 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/data/oradata/member/redo02.log'

这个基本可以确定是因为redo02损坏导致无法归档了。从上面的v$log信息可以知道日志组2非当前日志，故可以尝试清空日志：

SQL> alter database clear unarchived logfile group 2;

Database altered

SQL> alter system switch logfile;

System altered

此时数据库恢复正常。赶紧找系统工程师检查硬盘。

注意，清空完日志后必须对库做一次全备，因为清空日志时已经造成了归档日志出现断点，影响恢复了。