Automatic Cleanup of Flashback Logs when FRA is Full | New feature in Oracle Database Monitoring Script "dbalarm"

 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:

DBA Bundle V7.5 is now available!

 The DBA Bundle release of May 2023 is now available, it's packed with new and updated scripts along with multiple bug fixes.

Download it from here: https://www.dropbox.com/s/le5pxctq0w6v49u/DBA_BUNDLE7.tar?dl=0

Once you click on the link, first close this Window:

Then, click on the Download button at the most upper right side of the page:

 In case you are new to the DBA Bundle, this link will go you through its major features:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

DISCLAIMER: THIS BUNDLE IS AN OPEN SOURCE PROJECT, IT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS". TO BE USED BY SOMEONE KNOWS HOW TO MANAGE ORACLE DBs AKA: DBAs :-)

Shell Script to Kill All Processes having Specific Pattern/String in Their Name or Description

 What this script for?

This script kills all running processes having a string/pattern "provided by the user" in their name or description.

This script works only on Linux environment, it won't work on Unix.

When you need such script?

Sometimes when I shut down an Oracle database it goes hung in the middle, in many cases it worth to kill all the connected sessions to the DB from OS to release this hung in many cases, so I kill all the processes having this pattern in their name, suppose that my instance name is ORCL: oracleORCL (LOCAL=NO)

This script helps me to safely and easily do this job. This is just an example of usage, but it can kill any process, not only Oracle ones.

Before going to "How to Use" section, please read the following disclaimer carefully:

THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
THE AUTHOR WILL NOT BE HELD RESPONSIBLE TO ANY PERSON OR ENTITY WITH RESPECT TO ANY DATA LOSS OR DAMAGES CAUSED BY THIS SCRIPT.

This script is like any powerful Linux command, you suppose to really know what you are doing, make sure you read the prompted messages and outputs carefully.
Also, it's expected that you already practiced/tested this script on a test environment before using it against production.

How to use:

1- You need to provide the full or partial name of the processes you want to kill.
2- The user you are running this script with should be the owner of the processes to be killed.

# sh KILL_ALL_PROCESSES_MATCHING_STRING_LINUX.sh

Read the Disclaimer and decide whether you want to continue or not, then provide the full or partial name of the processes you want to kill:

 

 The script will list you all the processes to be killed along with their total number. [You must review them all carefully before proceeding to the next step, to make sure the script will kill exactly the processes you want]

 

Once you are 100% sure to kill the listed processes, then go ahead and confirm the killing of those processes:

The script will give you 5 seconds in case you want to change your mind, simply use Ctrl+c to terminate it or leave it to run.

Once the killing is complete, the script will check if any other processes are left having the same pattern:

In case there are processes still left, the script will list them to you, you may need to run the script again to kill them, or you may need to execute the script with the right OS user which owns these processes.

You can download the script from this link:

https://www.dropbox.com/s/60ob48dctotmsx1/KILL_ALL_PROCESSES_MATCHING_STRING_LINUX.sh?dl=0

Or just copy the below content to a file with .sh extension to the machine you want to execute it against: