ORA-00257 Error: Archive Log on disk space using 100% error resolution

Phenomenon:

The operation cannot be completed while a DML operation is in progress. There is no response when exiting Sqlplus---------stuck and unable to exit normally.

Log on to the host of the database and use Sqlplus to log in to the DBA user, prompting for an error.

[Oracle@oel-01 ~]$ Sqlplus Bys/bys

Sql*plus:release 11.2.0.1.0 Production on Sun June 21 17:55:09 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:

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

First, view log prompts a redo log cannot be archived.

[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 June 21 18:01:18 2013

Arch:archival stopped, error occurred. would 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 '

Second, check the use of hard disk, found that Oracle_home is the archive directory used already 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

Third, here I use Rman to delete the archive log

[oracle@oel-01 ~]$ Rman Target/

Rman> crosscheck Archivelog All;

rman> Delete expired Archivelog all;

Delete the archive log before today

rman> Delete archivelog until time ' sysdate-1 ';

can also be used

rman> Delete Archivelog all; Delete all Archives

Four, to view the disk space, has been released a part.

[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

Five, at this time using DBA user login Sqlplus still error, using SYSDBA user login, switch the current log file.

[Oracle@oel-01 ~]$ Sqlplus Bys/bys

Sql*plus:release 11.2.0.1.0 Production on Sun June 21 18:03:48 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:

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

Log in using SYSDBA

sys@ bys001>alter system switch logfile; ----may take a long time.

System altered.

sys@ bys001>select group#,status,archived from V$log;

group# STATUS ARC

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

1 Current NO

2 INACTIVE YES

3 ACTIVE NO

The log may be as follows:

[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 added for thread 1 sequence 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 added for thread 1 sequence 113 ID 0xebe3b9d9 dest 1:

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

Six, now the database restore normal, you can log in and operate

sys@ Bys001>conn Bys/bys

Connected.

bys@ Bys001>exit

[Oracle@oel-01 ~]$ Sqlplus Bys/bys

bys@ bys001>truncate table test1;

Table truncated.

Finally, the archive log is backed up and deleted.

Use the Backup archive script as follows:

[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

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/