Archive full www.oracle.com

What to do when the archive directory is full.

Archive full: do NOT panic!

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

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

When the archive log directory is full, the database comes to a stillstand and most likely users start calling and/or screaming; what you must NOT do is, of course, remove any archived redo log, or you might desperately be in troubles someday.

Stay calm because, as serious as the consequences for the end users may be, in reality the database is almost always perfectly alright. The problem is that the online redologs are full and before overwriting them Oracle has to take a copy and have it transfered onto the archive destination by the archiver process.

So, resist to the temptation of deleting anything.
  1. Connect internally (sysdba) and find out where the archive directory is with the command archive log list: 
    
    $ sqlplus '/ as sysdba'

    SQL> archive log list;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /oradmp/archive/PRODDB
    Oldest online log sequence     162434
    Next log sequence to archive   162436
    Current log sequence           162436
  2. From the operating system, make sure that this is the problem indeed (the mount point is full) and not something else: 
    $ df -k /oradmp
    Filesystem             kbytes   used    avail capacity  Mounted on
    /dev/vx/dsk/cspdg/oradmp  11796416 11796000 416    100%    /oradmp
  3. Staying in sqlplus, start archiving to some mount point that has room :
    alter system archive log start to '/var/oracle/archive_SOS';

  4. At this point the database should come back to life and you can run an archive backup to free some space.

What can I do if in the hurry I moved the archived redo logs?

It is not the end of the world; the problem is that if you start an rman archivelog backup it will fail because of the missing logs.

channel t1: starting piece 1 at 29-APR-04
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 04/29/2004 18:01:05
ORA-19505: failed to identify file "/oradmp/archive/PRODDB/PRODDB_1_161663.arc"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Recovery Manager complete.
This message probably means that you moved the /oradmp/archive/PRODDB/PRODDB_1_161663.arc somewhere else during the intervention.


In that case you should tell rman to check which archived logs are still available on disk; the command is change archivelog all crosscheck; 

$ rman catalog "rman@REPOS" target "sys"

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

target database Password: 
connected to target database: PRODDB (DBID=4131138365)
connected to recovery catalog database

RMAN> change archivelog all crosscheck;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=204 devtype=DISK
validation failed for archived log
archive log filename=/orarman/archive/PRODDB/PRODDB_1_161194.arc recid=160131 stamp=524251670
validation failed for archived log
archive log filename=/orarman/archive/PRODDB/PRODDB_1_161195.arc recid=160132 stamp=524258814
validation failed for archived log
archive log filename=/orarman/archive/PRODDB/PRODDB_1_161196.arc recid=160133 stamp=524270978
... ... ...
After this check, the archive log backup will run without errors. You can now copy back the archived redo logs to the archive directory, execute 'change archivelog all crosscheck' another time, take the backup etc. till all the log files have been sent to the backup unit.

A short script to avoid the 'archive directoty full' error

It is therefore better to avoid getting an ORA-00257 archiver error. A monitoring event can be set in the Oracle Enterprise Manager, but here is a simple UNIX script that checks the capacity of the archive directory mount point and switches the database if a threshold is reached. A flag file avoids switching again. 

sqlplus -s /nolog<<EOF_SQL 
  connect / as sysdba
  spool /tmp/archiveDestoracle.log
  archive log list
  spool off
  exit
EOF_SQL
# extract the archive destination 
archDest=`cat /tmp/archiveDestoracle.log | grep 'Archive destination' | awk '{ print $3 }'`
cap=`df -k $archDest|awk '{ print $5 }' | sed "s/%//g" | grep -v capacity `
freeK=`df -k $archDest|awk '{ print $4 }'`
echo "free $freeK"
if [ $cap -gt 90 -a ! -f /tmp/already_switched ]; then
  echo "The archive directory is $cap full" 
  echo "The archive directory is $cap full" > /tmp/arcoracle.log
  mailx -s "Archive almost full" dba@mycompany.com < /tmp/arcoracle.log
  ### rm /tmp/arcoracle.log
  sqlplus '/as sysdba' << EOF_SWITCH
  SELECT SYSDATE FROM DUAL;
  alter system archive log start to '/var/oracle/archive_SOS';
  exit
EOF_SWITCH
  touch /tmp/already_switched
fi

Date format in the rman output

The date format in the outputs produced by rman is set by the NLS_DATE_FORMAT in the UNIX environment:

export NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss'
[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]
Rate this article ...
Very poor Poor Average Good Very good