Flash Recovery Area Usage

Job details

Name:	Flash Recovery Area Usage
Platform:	Oracle
Category:	Capacity
Description:	Checks space usage in the flash recovery area.
Long description:	Checks space usage in the flash recovery area.
Version:	2.8
Default schedule:	10 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & eleven_and_above = ‘1′ & _priv_read_v_flash_recovery_area_usage = ‘1′]

Parameters

Name	Default value	Description
warning threshold	80	Warning threshold for flash recovery area usage (percentage).
alarm threshold	95	Alarm threshold for flash recovery area usage (percentage).
warning threshold (GB)	5	Minimum of free space (in GB) for the flash recovery area. If the amount of free space falls below this parameter value, and at the same time the amount of free space in percent falls below the “warning threshold” parameter value, the alert will return a WARNING.
alarm threshold (GB)	2	Minimum of free space (in GB) for the flash recovery area. If the amount of free space falls below this parameter value, and at the same time the amount of free space in percent falls below the “alarm threshold” parameter value, the alert will return an ALARM.
history threshold	30	The number of days the statistics will be kept in the history table.
reclaimable space as free space	NO	If sett to “YES“ the reclaimable space usage of Flash Recovery Area will be calculated as free space.

Job Summary

Purpose: This monitoring job is designed to monitor the Flash Recovery Area (FRA) usage in Oracle databases.
Why: This job is essential to ensure that the Flash Recovery Area does not reach full capacity, which is crucial for database recovery operations. Overfilling the FRA can lead to severe issues like inability to perform backup or archive processes that can result in data loss or database unavailability.
Manual checking: You can check the Flash Recovery Area usage manually using the following SQL command:

SELECT PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE, NUMBER_OF_FILES FROM V$FLASH_RECOVERY_AREA_USAGE;

Job Details

“Name:” Flash Recovery Area Usage
“Version:” 2.8
“Company:” dbwatch.com
“Default Schedule:” Every hour
“Description:” Checks space usage in the flash recovery area for Oracle databases.

Key Parameters

“Warning Threshold (%):” When usage exceeds this percentage, a warning is issued.
“Alarm Threshold (%):” Exceeding this percentage triggers an alarm indicating critical usage levels.
“Warning Threshold (GB):” Specifies the warning level for free GB in the FRA.
“Alarm Threshold (GB):” Specifies the critical threshold for free GB below which an alarm is issued.
“History Threshold (days):” Defines how long statistics are retained in the history table.
“Reclaimable Space Considered Free:” Determines whether reclaimable space should be counted as free space.

Functional Dependencies

This job has a self-dependency for checking historical values against current readings.
It depends on a history table designed to store historical FRA usage data.

Dependency	Description
FLASH_REC_AREA_check_#instance~id#	Self-check for monitoring FRA usage.
flash_rec_usage_histr_#instance~id#	Historical data storage.

Implementation Details

This job involves:

Creating a history table for FRA usage.
A stored procedure named FLASH_REC_AREA_check_#instance~id# that:
Checks the FRA percentage usage.

Reporting

Report Title: Flash Recovery Area Usage
The report includes:
Percent space used by each file type.

File Type	% Space Used	Reclaimable	Number of Files
File type data	% of space used	% that can be reclaimed	Total files count

Recovery Recommendations

Manual Cleanup Advice: This section of the report provides guidance on using RMAN for freeing up space from the flash recovery area by deleting unnecessary or expired files.

RMAN Step-by-step commands:
DELETE OBSOLETE;

This dbWatch job ensures that the Flash Recovery Area is under constant surveillance, preventing potential issues that could impair the database’s ability to recover from failures effectively.