Oracle: How to tell DB about manually freed space in FRA

If a database is configured to use a Flash Recovery Area (FRA), all archive-logs and RMAN-Backups go there. The FRA’s disk-space is limited to a user-defined value.
Usually a DBA’s RMAN-Backupjob cleans up all obsolete files in the FRA. This can easily be done by issuing a “DELETE OBSOLETE” in RMAN. Related to the defined RETENTION POLICY this command would delete all files no longer needed. If no manual deletes of files in the FRA on OS-Level are done, the database correctly keeps track of the remaining space in there.

SQL> SELECT name,
  ROUND(space_limit/1024/1024/1024,0) limit_gb,
  ROUND(space_used /1024/1024/1024,0) used_gb,
  ROUND(space_used /space_limit,4)*100 AS pct_used
FROM V$RECOVERY_FILE_DEST;

NAME                                    LIMIT_GB    USED_GB   PCT_USED
------------------------------------- ---------- ---------- ----------
/usr/people/oracle/fast_recovery_area         50         43      85.09

SQL>

This query gives a more detailed overview about the storage-distribution:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      63.52                         0             379
BACKUP PIECE                      21.56                         0               4
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL>

If a database is configured in archive-log-mode and to use a FRA, but without a RMAN-Based backup with cleanup, the FRA will continually be filled with archive-logs until no FRA-Space is left. This would bring the database to a halt. All active sessions are on hold showing the wait-events “log file switch (archiving needed)” or “free buffer waits”.

Now if we, in “panic”, delete a bunch of old archive-logs in the FRA using OS-Commands (like “rm”) to free space, the database would not notice this. Even a “CROSSCHECK ARCHIVELOG ALL” would not release the DB from it’s hold-state. It would just mark the deleted files as “EXPIRED” in the RMAN-Catalog. To make the database to recognize the delete, we must “delete” the files using RMAN once more:

RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

In general there is no need to manually delete files in the FRA on OS-Level, as we could have achieved our goal completely from within RMAN. This command for example would delete all archive-logs older than seven days – in the RMAN-Catalog and on disk:

RMAN> DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';

The above procedure, of course, can also be used to handle the (manual) delete of backup-sets.

More info about the RMAN’s DELETE-Command can be found here:

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/rcmsynta016.htm#i81315

But there is one thing left: If we delete our archive-logs in the FRA with RMAN, only the files are deleted. And as Oracle creates every day a new directory for that days archive-logs named “YYYY-MM-DD” , we would still have a huge set of empty directories left. To clean up that too we could execute this (on Linux) after our RMAN-Backup/Cleanup has run:

find /apps/oracle/fast_recovery_area/DEMODB/ -mindepth 1 -empty -type d -exec rm -r {} \;

This command would pass all empty directories found under the given path to “rm” and hence delete them.

Edit 2012-05-02:
I got a note that, according to Oracle-Support, all empty directories in the FRA older than seven days would get deleted automatically after backup of control-file. I verified this successfully. But mind that “older than seven days” here means “remained unchanged since seven days”. In plain: If you delete all files from your archive-log-folder named “2012_05_02”, this folder would get deleted on 2012-05-09 (seven days later).

See MOS-Document: “Flash Recovery Area Housekeeping of empty directories” [ID 305970.1]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: