I've introduced a new feature in dbalarm script to clear all flashback logs whenever the Flash Recovery Area (FRA) hits its defined threshold "FRATHRESHOLD" inside the script. This is the first optional corrective action I include in dbalarm monitoring script. I may add more optional corrective actions in the future if I feel it was easy understood and utilized by DBAs.
I feel that since Oracle 10g, Oracle has a chronic problem with cleaning up FLASHBACK Logs FLBs whenever FRA becomes full, this is why I'm targeting the cleanup of FLBs in this feature.
How this feature works:
When the defined threshold for FRA "FRATHRESHOLD" is reached, the code will check if parameter "FLASHBACK_RESET" inside the script is set to Y, then it will check if FLASHBACK feature is turned ON, if it's ON, it will execute the following commands which will turn Flashback Database feature to OFF then ON to clean up all FLASHBACK logs in FRA:
ALTER DATABASE FLASHBACK OFF;
ALTER DATABASE FLASHBACK ON;
Then it will check again if the script has managed to successfully turn FLASHBACK feature back ON, if the script fails, it will send a Warning Email to the user to notify that the script has failed to turn ON flashback feature, including the commands log in the Email body.
Note: You must consider that resetting the FLASHBACK Database feature will delete all
Flashback Logs, and hence this will undermine the ability of flashing back the database to a point in time older than the reset activity.
If the database is a PHYSICAL STANDBY, and the Recovery is running, then extra commands will be executed to maintain a proper reset of Flashback feature on a PHYSICAL STANDBY database:
ALTER DATABASE FLASHBACK OFF;
RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE FLASHBACK ON;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT;
How to Use this Feature:
First, if you are not familiar with dbalarm script, I strongly recommend you to go through this article first to have an idea of how it works:
http://dba-tips.blogspot.com/2014/02/database-monitoring-script-for-ora-and.html
Download the script:
https://www.dropbox.com/s/a8p5q454dw01u53/dbalarm.sh?dl=0
Second, Set parameter FLASHBACK_RESET=Y at line# 132
Note: This feature is disabled by default, you have to enable it manually to avail it.
Facts you must know:
When FRA is full, Oracle will start creating archivelogs under $ORACLE_HOME/dbs which in most cases doesn't have sufficient space to accommodate Archivelogs!
This script works only on Linux environments, it's well tested on Oracle & Red Hat Linux 6 & 7 but not tested on other flavors and hence you have to test it well in a test environment before using it on production.
DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
GitHub version:
