Fast Recovery Area

How to Resolve ORA-19809 Limit Exceeded for Recovery Files

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:

  1. Take frequent backup of recovery area using RMAN.
  2. Consider changing RMAN retention policy.
  3. Consider changing RMAN archived log deletion policy.
  4. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
  5. 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 to a larger value if disks still have more space available for FRA. Assuming that your database cannot be opened normally.
  2. 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;
  3. 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.
  4. [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.

  5. Delete all archive logs, no matter what.
  6. 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;
  7. Change log archive destination to another location
  8. 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.

  9. Change the destination of archived logs, if there’s no more room left for FRA.
  10. 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. Set a reasonable size for FRA in advance.
  2. Just like we talked about in solution #1.

  3. Set tables NOLOGGING temporarily before importing or bulk data loading.
  4. SQL> ALTER TABLE tablename1 NOLOGGING;
    SQL> ALTER TABLE tablename2 NOLOGGING;
    ...

  5. 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.
  6. 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.

  7. Change archived log deletion policy. Assuming the current setting is to backup 2 times, we set to 1 time or NONE.
  8. RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO SBT;
    RMAN> DELETE OBSOLETE;

I hope this post can help you to solve ORA-19809.

4 thoughts on “How to Resolve ORA-19809 Limit Exceeded for Recovery Files

Leave a Reply

Your email address will not be published. Required fields are marked *