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
