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
DELETE ARCHIVELOG ALLremoves every archived log known to RMAN. Run it only when the logs are backed up and no longer needed for recovery.- The original text ends with
ALTER DATABASE OPEN RESETLOGS. RESETLOGS is only needed after an incomplete recovery or a restore, so this article usesALTER DATABASE OPEN. - The original also changed
log_archive_destand made a copy of the archived logs to another location. Those values were redacted in the source and are not reproduced.