一步一脚印

今天登陆数据库时报错：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 2008ORACLE 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

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

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