Thursday, April 7, 2011

Automatic Archiving Stop when Archive Destination Disk is Full

The database is running in archive mode with automatic archiving is turned on . log Archive destination is in FRA (flash Recovery Area) .The DB_RECOVERY_FILE_DEST_SIZE  was set to be 2G. Once when i startup the database it goes at mount stage and throw the following error .
ORA-1034   : Oracle not available 
ORA-16014 : log 3 sequence# xx  not archived


Then i check my alert logfile and find the space related issue in fra .i.e  Automatic Archiving gets Stop when there is no space in disk. I fire the following command to resolve the issue .

SQL> startup
ORACLE instance started.
Total System Global Area       313860096 bytes
Fixed Size                               1332892 bytes
Variable Size                           281020772 bytes
Database Buffers                     25165824 bytes
Redo Buffers                           6340608 bytes
Database mounted.
ORA-1034     : Oracle not available 


SQL>alter system set log_archive_dest_1='location=<< new location>>'  ;      
( Here we may increase the fra size or change the archive destination , as  i have changed the archive destination )

SQL> alter system  archive  log  all  to  '<< new destination >>'  ;
SQL> shut immediate ( or sometimes shut abort if hangs)
SQL> startup 


Explanation : 
Once the archive destination becomes full the location also becomes invalid. Normally Oracle does not do a recheck to see if space has been made available.

1) Using the command  
SQL> alter system archive log all to '<< new location >>' ; 
The   Above  command  gives  Oracle a valid  location  for  the  archive  logs.  Even after  using this the archive  log  destination  parameter  is  still  invalid  and  automatic  achive  does  not  work. We  can  also use  this  to allow  to  do  a  Shutdown  immediate  instead  of   Shutdown  abort. 


2.)  Shutdown  and restart  of  the database  resets  the  archive  log  destination  parameter  to  be  valid . Do not  forget  to  make disk  space available  before  starting  the  database. 


3.) Use  the  REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter  to determine whether and when  ARCn  attempts  to  re-archive to a failed destination following an error. REOPEN applies to all errors,  not  just  OPEN  errors. REOPEN=n  sets  the  minimum  number  of  seconds  before  ARCn should  try  to  reopen  a  failed  destination. The  default  value for  n is 300 seconds. A value of 0 is the same  as  turning  off  the  REOPEN  option, in  other  words,  ARCn  will  not  attempt  to  archive  after   a failure. If  we change   the  archive  destination  then  there  is  no  need  of  specifying  repoen  option  . 


ENJOY     :-)



2 comments:

siva said...

Dear Neeraj,

All your posts are really helpful. I have one question about this, Is the database down if the Archive location full?

Thanks,
Siva

NEERAJ VISHEN said...

Hi Shiva ,

Thanks for your comments .

If there is no space on archive location, then database get hung . It will not get down .

Have good time .

Enjoy :)