ORA-00257-归档日志所在磁盘空间使用100%出错解决

现象：

正在进行DML操作时，操作一直无法完成。退出SQLPLUS时一直无响应--卡着不动无法正常退出。

登陆数据库所在主机，使用SQLPLUS登陆DBA用户时，提示归档出错。

[oracle@oel-01 ~]$ sqlplus bys/bys

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 21 17:55:09 2013

ERROR:

ORA-00257: archiver error. Connect internal only, until freed.

一、查看日志提示一个REDO日志不能归档。

[oracle@oel-01 ~]$ tail alert_bys001.log

ORA-19502: write error on file "", block number (block size=)

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/bys001/redo03.log'

Sun Jul 21 18:01:18 2013

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance bys001 - Archival Error

ORA-16014: log 3 sequence# 219 not archived, no available destinations

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/bys001/redo03.log'

Errors in file /u01/app/oracle/diag/rdbms/bys001/bys001/trace/bys001_arc1_6050.trc:

ORA-16014: log 3 sequence# 219 not archived, no available destinations

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/bys001/redo03.log'

二、查看硬盘使用情况，发现ORACLE_HOME 即归档文件所在目录使用已经100%

[oracle@oel-01 ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 19G 18G 3.8M 100% /

/dev/sda1 99M 21M 74M 22% /boot

tmpfs 3.0G 529M 2.5G 18% /dev/shm

/dev/sda5 4.6G 2.6G 1.9G 58% /backup

三、这里我使用RMAN来删除归档日志

[oracle@oel-01 ~]$ rman target /

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

删除今天之前的归档日志

RMAN> delete archivelog until time 'sysdate-1' ;

也可以用

RMAN> delete archivelog all; 删除所有归档

四、查看磁盘空间，已经释放出来了一部分。

[oracle@oel-01 ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 19G 18G 588M 97% /

/dev/sda1 99M 21M 74M 22% /boot

tmpfs 3.0G 529M 2.5G 18% /dev/shm

/dev/sda5 4.6G 1.8G 2.6G 42% /backu

五、此时使用DBA用户登陆SQLPLUS依然出错，使用SYSDBA用户登陆，切换当前日志文件。

[oracle@oel-01 ~]$ sqlplus bys/bys

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 21 18:03:48 2013

ERROR:

ORA-00257: archiver error. Connect internal only, until freed

使用SYSDBA登陆

SYS@ bys001>alter system switch logfile; ----可能会需要较长时间。

System altered.

SYS@ bys001>select group#,status,archived from v$log;

GROUP# STATUS ARC

---------- ---------------- ---

1 CURRENT NO

2 INACTIVE YES

3 ACTIVE NO

日志可能如下：

[oracle@bys001 ~]$ cat alert_bys1.log

Sat Oct 05 12:44:41 2013

Suspending MMON action 'metrics monitoring' for 82800 seconds

Sat Oct 05 12:45:10 2013

Archiver process freed from errors. No longer stopped

Sat Oct 05 12:45:14 2013

Archived Log entry 106 added for thread 1 sequence 111 ID 0xebe3b9d9 dest 1:

krse_arc_driver_core: Successful archiving of previously failed ORL

Sat Oct 05 12:45:14 2013

Thread 1 advanced to log sequence 114 (LGWR switch)

Current log# 3 seq# 114 mem# 0: /u01/oradata/bys1/redo03.log

Sat Oct 05 12:45:14 2013

AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail

Sat Oct 05 12:45:21 2013

Archived Log entry 107 added for thread 1 sequence 113 ID 0xebe3b9d9 dest 1:

Archived Log entry 108 added for thread 1 sequence 112 ID 0xebe3b9d9 dest 1:

六、现在数据库恢复正常，可以登陆并操作

SYS@ bys001>conn bys/bys

Connected.

BYS@ bys001>exit

[oracle@oel-01 ~]$ sqlplus bys/bys

BYS@ bys001>truncate table test1;

Table truncated.

最后要对归档日志进行备份和删除。

使用备份归档脚本如下：

[oracle@oel-01 ~]$ cat archback.sh

#!/bin/sh

#su - oracle

source /home/oracle/.bash_profile

##########

/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman log /home/oracle/rman-arch`date +%Y%m%d-%H%M`.log <<EOF

connect target /;

run{

backup archivelog all delete input

format '/backup/archlog/arch_%d_%T_%s';

}

exit