Translate

Search This Blog

ORA-16038: log 1 sequence# 2901 cannot be archived - ORA-19809: limit exceeded for recovery files logfileswitch(archiving needed)

ORA-16038: log 1 sequence# 36792 cannot be archived.
ORA-19809: limit exceeded for recovery files

Your DML statements ,even, session logon hangs and you get above error besides getting below error in alert log file as well. Further you see wait event logfileswitch(archiving needed) which implies sesion is waiting for log file switch to succeed which in turn is waiting for older logfiles to be archived.

SID,SEQ#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE,WAIT_TIME_MICRO,TIME_REMAINING_MICRO,TIME_SINCE_LAST_WAIT_MICRO

49,47571,logfileswitch(archivingneeded),,0,00,,0,00,,0,00,3290255840,2,Configuration,0,84,WAITING,84329776,-1,0

If this wait event is seen continuously then it means archiver process is not able to write archive redo logfiles due below reasons

1. mount point or directory pointed by log_archive_destination_n is full in disk space

2. There is flash recovery area being used for archiving, there may be enough space in directory/mount point on which flash recovery area lies but flash recovery area is filled upto space limit defined by instance init parameter db_recovery_file_dest_size   

 select *  from v$recovery_file_dest

NAME,           SPACE_LIMIT,SPACE_USED,   SPACE_RECLAIMABLE,NUMBER_OF_FILES
C:\flash_recovery_area,107374182400,107216960512,1081344,1147

107374182400=100GB

Here you can see flash recovery area has only free space=SPACE_LIMIT-SPACE_USED=149MB while the redo log file size is bigger(200MB) than this free space 149MB so redo log could not be archived.

Remedy 1:

 SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
db_recovery_file_dest                string      C:\flash_recovery_area
db_recovery_file_dest_size           big integer 100G

If there is enough free space in flash reocvery area location pointed by flash recovery area db_recovery_file_dest  then increase space limit(db_recovery_file_dest) which is set to 100GB in above: 
 
alter systemset db_recovery_file_dest_size=400g [

Remedy 2:

-delete older archive logs which are not needed according to backup policy

RMAN> delete archivelog until time 'sysdate-7'

-delete expired archive logs (archive log fiels deleted physically from OS command and not from RMAN.First, mark such archived logs to be expired in RMAN.

RMAN> crosscheck archivelog all
OR
RMAN>change archive log all validate;

then delete marked expired archive logs

RMAN>delete expired archivelog all;

Now archiving will succeed.

Follow by Email