ORA-19809
We usually watch the space usage of log archive destination very closely because the database will be suspended if the space is full. Particularly, if the log archive destination is USE_DB_RECOVERY_FILE_DEST, then you must watch the usage of Fast Recovery Area (FRA) instead to prevent ORA-19809.
When you meet the following error, you hit the space limit of FRA.
ORA-16038: log 2 sequence# 230348 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'd:/oracle/oradata/dbname/redo/redo02.log'
If you have already shutdown the database, the error prevents you from startup, it could be severe.
Let's see the content of this error:
Description
ORA-19809: limit exceeded for recovery files
Cause
The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded.
Action
There are five possible solutions:
- Take frequent backup of recovery area using RMAN.
- Consider changing RMAN retention policy.
- Consider changing RMAN archived log deletion policy.
- Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
- Delete files from recovery area using RMAN.
Then check current initialization parameters:
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string d:\oracle\flash_recovery_area\dbname
db_recovery_file_dest_size big integer 10G
Solutions
Several ways that can solve ORA-19809 are listed in the following:
1. Resize FRA
Resize FRA to a larger value if disks still have more space available for FRA. Assuming that your database cannot be opened normally.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=200G SCOPE=SPILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Actually, starting up the database to MOUNT or NOMOUNT is no difference. Just make sure that the value does not exceed the overall disk space for FRA.
If your database is still online, then use SCOPE=BOTH
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=200G SCOPE=BOTH;
2. Delete Expired Archived Logs
Delete expired archive logs if there have no more space for archived logs. Assuming a lot of expired archive logs are available to be deleted.
[oracle@test ~]$ rman target /
...
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
Adding NOPROMPT right after DELETE makes RMAN to directly delete all archived logs without user's confirmation.
If the space is still full, you need a stronger medication like the next one.
3. Delete All Archived Logs
Delete all archive logs, no matter what.
RMAN> DELETE ARCHIVELOG ALL;
Or do it directly without asking.
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
Then make the archived log list consistent.
RMAN> CROSSCHECK ARCHIVELOG ALL;
4. Switch FRA to Another Location
Change log archive destination to another location
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=e:\oracle\flash_recovery_area2\dbname SCOPE=SPFILE;
Don't forget to bounce the database service in order to apply the new change.
5. Change Destination of Archived Logs
Again, you have to restart the database to make it work.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=e:\oracle\dbname\archivelog SCOPE=SPFILE;
Preventive Actions
Here are preventive actions that you can consider to take to prevent ORA-19809.
1. Larger FRA or Destination
Set a reasonable size for FRA in advance. Just like we talked about in solution #1, #4 and #5.
2. Smaller Recovery Window
Impose a smaller recovery window on the retention period, if the current recovery windows is significantly big. For example, we change the recovery window from 60 days into 7 days.
RMAN> SHOW ALL;
...
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Then we delete some obsolete backups like the following command according to the new policy immediately, or put it into your backup scripts.
RMAN> DELETE OBSOLETE;
Consequently, there should have fewer backups left in FRA.
3. Fewer Backup of Archived Logs
Change archived log deletion policy. Assuming the current setting is to backup 2 times, we set to 1 time or NONE.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO SBT;
RMAN> DELETE OBSOLETE;
4. Apply NOLOGGING
Set tables NOLOGGING temporarily before importing or bulk data loading.
SQL> ALTER TABLE tablename1 NOLOGGING;
SQL> ALTER TABLE tablename2 NOLOGGING;
...
I hope this post can help you to solve ORA-19809.
Thanks Very Helpful. !!!
You’re welcome!
Great actionable info! Exactly what is needed in moments on need!!
It’s my pleasure!
Thank you, that’s great stuff and very well presented too.
My pleasure!
yse,
my issue resolved
thank you
My pleasure!
Thank you it fixed my problem.
My pleasure!