Sunday, December 12, 2010

Archiver Hung in Oracle database - ORA-16038, ORA-19504, ORA-00257

Archiver Hung in Oracle database

Steps to delete archive log file not needed for recovery (older than the last backup of the database)

Summary
1. check how much space is used by archiver
2. check the last good backup of the database
3. delete archive log files older than last good backup
4. crosscheck archive log


Steps

1. check how much space is used by archiver
  Sql>  select count(*),sum(blocks*block_size) from v$archived_log where    backup_count=0 and deleted='NO';
   Sql> select * from v$flash_recovery_area_usage;

2. check the last good backup of the database
 
   set pages 999 lines 120
   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   select
       INPUT_TYPE, STATUS,
       to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
       to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
       elapsed_seconds/3600                   hrs,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;
  
  

   3. delete archive log files older than last good backup

rman target / nocatalog
 allocate channel for maintenance device type disk;
 crosscheck archivelog all;
 delete noprompt archivelog until time 'sysdate - 1';
 delete noprompt expired archivelog all;
 delete noprompt obsolete device type disk;  

4. crosscheck archive log

 crosscheck archivelog all;
 release channel;
 exit;


 Hope this helps! Rupam