ORA-19809, Failed RMAN Backups and Flashback

Flashback Recovery Area is a common gotcha in many locations as time goes by, so I thought it might be worthwhile to review, since for me, it’s all new.  My last two shops, due to design, had no use for it…

Flashback Recovery Area, (FRA) is an intriguing feature and should not be confused as the same as the flashback feature- The ability to flashback to a given time or flashback a table when something has gone wrong has benefits over recovery that is easy for anyone who’s had the to perform either task can appertain.   Per Andy Colvin, as of vervion 11.2, FRA now stands for Flash Recovery Area to eliminate some of the confusion.   As I become more familiar with the FRA, I see a few areas of improvement that I hope Oracle will continue to work on and I will continue to add to my knowledge on.

With more complexity comes more challenges and knowledge of configuration. FRA is no different. It has three main parameters, making it a simple feature to set up:

• db_flashback_retention_target          Sets retention target time, in minutes.

• db_recovery_file_dest                          The location of the archive logs used by FRA

• db_recovery_file_dest_size                The max space allocated to everything residing in the FRA destination, helps deter from overwhelming a disk location or diskgroup.

You manually set the location for the FRA and the destination max size that you wish to allocate to the FRA.

The first parameter only pertains to the retention time for flashback logs, no other FRA data retention.

Oracle sets a standard default of 1440 minutes retention target, (1 day) If you think about how different each database usage is, this could be quite a lot of archive log retention and disk allocation for some.

The db_recovery_file_dest_size parameter is there to help with this. This is the max amount of space you have set aside for this the database. This will deter the database from over- allocating and filling up a disk or diskgroup. In a RAC environment, this is set at the instance level, so you must set it for each node but the values must match for the entire RAC environment- no different parameter values per node.

It's good to know what is contained in your FRA location. Keep in mind that its not just archive logs:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
 ------------ ------------------ ------------------------- ---------------
 CONTROLFILE      0   0    0
 ONLINELOG        0   0    0
 ARCHIVELOG   21.06  12  172
 BACKUPPIECE  29.52   0    6
 IMAGECOPY        0   0    0
 FLASHBACKLOG  9.02  22   28

When you are posed with the challenge of space limitations, it’s good to know how much space is currently being used by the FRA.  In one case, the database with too high a FRA retention, it was a development database and the FRA setting for retention had been brought over from production during a cloning process.

This resulted in the following:

SQL> select  name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used  / 1024 / 1024) "Used MB"
from    v$recovery_file_dest
order by name ;

NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
/<dir>/FRA                                                         153600      128475

NAME                                                     TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     10880

SQL> alter system set db_flashback_retention_target=1440;

System altered.

With the FRA retention time put back to the default, the space usage was more in line with a development database, which often has less extra allocation of space than a production one.  With a clone, all three parameter settings must be set to the correct values involving FRA.

Now, here is the challenge, if you up against an ORA-19809 error in RMAN and impacted due to an FRA retention size limit for archive logs, as in the pictured example:

…you can’t just backup the archive logs with a purge option, as follows:

rman target /
 run {
 allocate channel chan1 type disk;
 backup archivelog all delete input format '//arch_%d_%u_%s';
 release channel chan1;
 }

This will fail because performing a backup of these archive logs before purging them takes FRA space and your error states you are at your limit…

You have a couple choices to address this-

you can add more space to FRA and then perform the backup, re-adjusting the parameter post the clean up:

alter system set db_recovery_file_dest_size=scope=both;

or you can just purge everything that is expired..

rman target /
 crosscheck archivelog all;
 delete expired archivelog all; 

If RMAN is the culprit.  Verify that your backups are using FRA space and that they are the reason for your issue.  For a backup that is being written to the FRA location, the standard practice for an RMAN backup is to:

1.  Perform the full backup

2. Purge the previous backup

In the example below, you will note that the backuppiece is utilizing 51% of the FRA space.  If it were to attempt to perform a second backup, THEN purge, it will hit into the FRA limitation FIRST, resulting in an error.

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                51.1                         0              87
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

Then you may have the choice of adjusting or changing the way you do backups if you do not wish to add to the FRA  space allocated, (db_recovery_file_dest_size.)

Let’s say the customer is using image copies and they agree to go to standard RMAN compressed backups, which will save considerable space.

You may look at the retention time and decide it is high, (assume its a development environment and there is no need to keep a full week of backups, which was one of the issues above that was taking up so much space.

rman target/
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;

Another customer ran into the issue with a failed standby “catch up” of archive logs.  It had ran out of physical space on the primary for archiving and once released, hit the ceiling on max on db_recovery_file_dest_size.  The size max had to be escalated until we could successfully get the system going again and then the standby had to match the primary.

What to remember as you are setting up FRA:

1. Set a retention time that makes sense for the database usage and the space you are able to allocate towards FRA.
2. When sizing the db_recovery_file_dest_size, keep in mind that more than archive logs are using the space allocated.  You must have enough space, plus adequate growth, for archive logs, backups, and/or controlfiles, online logs and image copies, depending on your configuration.  Use the scripts to see what you are using and size appropriately.
3. Have proper monitoring of the FRA usage area so that you can pro-actively address issues.

The more I use it, the more I find value and challenges with it.  If a backup is incorrectly or falls back to the default settings to backup to the FRA location, (the GUI in EM12c can easily set this up, even though the DBA may think they have configured otherwise…)  If you fill up the FRA max size, the archiver hangs, which is a service outage for many companies that in today’s business is not an option.  If the FRA is set to an ASM diskgroup that has limits, just as with disk, the FRA destination size may still be hindered as you attempt to work around a challenging issue.