日志损坏，已解决！

今天登陆时报错：

ORA-16014: log 1 sequence# 58 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test/redo01a.rdo'
ORA-00312: online log 1 thread 1: '/u02/oracle/oradata/test/redo01b.rdo'

归档有问题，查看归档情况：
SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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

SQL> select * from v$log;

GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1         58   52428800          2 NO INACTIVE         25-M
         3          1         60   52428800          2 NO CURRENT          26-M
         2          1         59   52428800          2 NO INACTIVE         25-M

归档参数正常。

查看归档路径是否存在：
dlp-> ls -l /u01/app/oracle/flash_recovery_area/TEST/archivelog
total 32
drwxr-x--- 2 oracle oinstall 4096 May 18 14:31 2010_05_17
drwxr-x--- 2 oracle oinstall 4096 May 18 17:23 2010_05_18
drwxr-x--- 2 oracle oinstall 4096 May 19 09:38 2010_05_19
drwxr-x--- 2 oracle oinstall 4096 May 20 16:07 2010_05_20
drwxr-x--- 2 oracle oinstall 4096 May 23 15:55 2010_05_23
drwxr-x--- 2 oracle oinstall 4096 May 24 07:30 2010_05_24
drwxr-x--- 2 oracle oinstall 4096 May 25 23:55 2010_05_25
drwxr-x--- 2 oracle oinstall 4096 May 26 01:46 2010_05_26  

看来路径也没有问题。

查看归档路径所在磁盘分区是否满了：
dlp-> df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/sda1             4.8G 2.9G 1.6G 65% /
tmpfs                 246M     0 246M   0% /dev/shm
/dev/sdb1             4.0G 3.8G 356K 100% /u01
/dev/sdc1             4.0G 2.0G 1.9G 52% /mysql
/dev/sdd1             2.0G 1.4G 511M 74% /u02

可以看到空间不足。

再查看alert文件信息：
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. This is most likely the result of a crash
during file creation.
*************************************************************
Wed May 26 01:24:32 2010
Errors in file /u01/app/oracle/admin/test/bdump/test_arc0_3256.trc:
ORA-19504: failed to create file "/u01/app/oracle/flash_recovery_area/TEST/archivelog/2010_05_26/o1_mf_1_58_%u_.arc"
ORA-27044: unable to write the header block of file
Linux Error: 28: No space left on device
Additional information: 3
ARC0: Error 19504 Creating archive log file to '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2010_05_26/o1_mf_1_58_0_.arc'
ARC0: Failed to archive thread 1 sequence 58 (19504)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed May 26 01:24:32 2010
ORACLE Instance test - Archival Error
Wed May 26 01:24:32 2010
ORA-16038: log 1 sequence# 58 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test/redo01a.rdo'
ORA-00312: online log 1 thread 1: '/u02/oracle/oradata/test/redo01b.rdo'
We

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

SQL> alter database clear unarchived logfile group 2;

Database altered

SQL> alter system switch logfile;

System altered

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

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

===============

Archive process error: ORA-16014: 日志 1 的序列号 269 未归档, 没有可用的目的地
ORA-00312: 联机日志 1 线程 1: 'C:\ORADATA\TEST\REDO01.LOG'
.

Archive process error: ORACLE Instance test - Archival Error

Archive process error: ORA-16038: 日志 3 序列号 271 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 3 线程 1: 'C:\ORADATA\TEST\REDO03.LOG'
.

Archive process error: ORACLE Instance test - Archival Error
.

Archive process error: ORA-16014: 日志 1 的序列号 269 未归档, 没有可用的目的地
ORA-00312: 联机日志 1 线程 1: 'C:\ORADATA\TEST\REDO01.LOG'
.

Archive process error: ORACLE Instance test - Archival Error