LLI Wiki — My Digital Knowledge Base

ORA-19809 when creating an archived log

Check the fast recovery area usage, enlarge it, and free space by backing up and deleting archived logs with RMAN.

ORA-19809 is raised when the database cannot create an archived redo log because the fast recovery area (FRA) has reached its size limit. The alert log shows the cause, and the usage of the FRA shows how full it is.

Check the usage of the recovery area

SET LINES 100
COL name FORMAT a60
SELECT name,
       FLOOR(space_limit / 1024 / 1024) "Size MB",
       CEIL(space_used / 1024 / 1024) "Used MB"
FROM v$recovery_file_dest
ORDER BY name;

Check the configured size, the archive destinations and the archive log state:

SHOW PARAMETER db_recovery_file_dest;
ARCHIVE LOG LIST;

The error itself is written to the alert log, in the diagnostic destination (for example $ORACLE_BASE/diag/rdbms/<db_unique_name>/<SID>/alert/log.xml).

Option 1: enlarge the recovery area

Increase the size limit (600G is the value of the original case), then archive the pending logs:

ALTER SYSTEM SET db_recovery_file_dest_size=600G;
ARCHIVE LOG ALL;
ALTER SYSTEM SWITCH LOGFILE;

Option 2: free space by removing archived logs

Use this when the area cannot be enlarged. Mount the database, then run RMAN:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
rman target /
RMAN> CATALOG RECOVERY AREA;
RMAN> LIST ARCHIVELOG ALL;
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

BACKUP ARCHIVELOG ALL DELETE INPUT backs up the archived logs and deletes each copy after it is backed up.

Then open the database:

ALTER DATABASE OPEN;

Notes

Mastodon LLIMastodon SocialFosstodon