Saturday, April 21, 2018

Script To Automatic Kill of Long Running Sessions Connecting From Specific Program | Oracle On Linux

In the previous post, I've shared a script reports long running sessions/queries. In this post I'll explain another simple script that kills long running sessions connecting from specific programs e.g. SQL Developer

The script can be tuned to target any specific programs, but I'm mainly defaulting the script to target SQL Developer, as it's widely used by the developers, moreover, it doesn't maintain disconnecting the running session properly when the user close the session's window, keeping the "supposed to be closed" session query running in the database!

Download link:
https://www.dropbox.com/s/ke01v10bu0bat84/kill_long_running_queries.sh?dl=0

How to use:

The script is designed to be scheduled in the crontab to run every let's say 5 minutes to kill specific sessions based on the killing criteria which you can identify by editing the following parameters:

export ORACLE_SID=orcl
You have to provide the name of the instance you want to run the script against e.g. orcl

MAIL_LIST="youremail@yourcompany.com"
Provide your email to get a detailed report after each successful session kill.

export MODULE_NAME="'SQL Developer'"
You have to provide the module name that appears in v$session which you are targeting its sessions, in order to provide more than one module you have to enclose each module name between single quote putting comma "," in between.
e.g. To include SQL Developer and Toad sessions in the killing criteria you have to provide the values to MODULE_NAME parameter like the following:
export MODULE_NAME="'SQL Developer','Toad'"

To get an idea of module names that are connecting to your database run this statement:
select distinct(module_name) from gv$active_session_history;

export DURATION="2.5"
Duration parameter is the session's elapsed active time in hours which if reached the session will be a candidate for kill. It's set to 2 hours and half as a default value. of course you have to change it to whatever fits your requirements.

export REPORT_ONLY="N"
This parameter if set to "N" to enable the real kill of the candidate session, if it's set to "Y" it will not do the actual kill but will ONLY report the candidate session to your email. This parameter is helpful during testing phase, you've to test this script first by setting this parameter to "Y", it will keep reporting to you the candidate sessions (to be killed) without killing them, once you're confident with the script accuracy, set it to"N" to start killing the candidate sessions.

Ultimately, although I'm not keen to share such scripts that may cause problems if they are not accurately configured , I'm sharing this script in the hope that you will use it wisely, and in order to do so, you have to test and test and test such scripts before you deploy them on production.
By your use of this script you acknowledge that you use it at your own risk.

Github version:



Shell Script To Monitor Long Running Queries In Oracle

Monitoring long-running queries is part of the early warning mechanism for the performance
issues on the database before it gets exaggerated


If you're running Oracle on Linux, then this script is for you.
Download link:
https://www.dropbox.com/s/yh6k3r7q3p876h0/report_long_runing_queries.sh?dl=0

The script is easy to use, it will run against all instances on the server reporting any active sessions/query for more than 60 minutes (the default), you can adjust this duration by changing the following parameter under THRESHOLDS section to whatever number of minutes you want:

EXEC_TIME_IN_MINUTES=60        # Report Sessions running longer than N minutes (default is 60 min).

Also, you can control the report generation if the number of long active sessions have reached a specific count. By adjusting the following parameter:

LONG_RUN_SESS_COUNT=0        # The count of long-running sessions, report will not appear unless reached (0 report all long running sessions).

Lastly, you must provide your email address in order to receive a detailed Email report of long-running queries/active sessions, by editing below template in navy color:

MAIL_LIST="youremail@yourcompany.com"

You can schedule the script in the crontab to run every 5/10 minutes.

In the upcoming post, I'll share another script to automatically kill long active sessions connected by specific programs like SQL Developer or Toad. Let's make the life of our beloved developers easy as they always make our life easy :-)

Monitor Long Running Queries | Long Active Sessions Script (GitHub version):

Thursday, April 5, 2018

DBA Bundle V4.2 Is Now Available

Fixed reported bugs inside (dbdailychk.sh, dbalarm.sh, export_data.sh, report_long_runing_queries.sh) scripts.

New Features:
Added Goldengate log monitoring feature to dbalarm.sh script [Disabled by default].
Added new aliases:
 - raclog to open the alert logfile for Clusterware/Oracle Restart.
 - asmlert to open the alert logfile for ASM instance.

To download DBA Bundle V4.2:
https://www.dropbox.com/s/xn0tf2pfeq04koi/DBA_BUNDLE4.tar?dl=0

To visit the main article that explains in details all bundle features:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html