How can we get email whenever we have blocking session in Oracle database

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

In an Oracle database, every time a session updates the elements of a table, it becomes locked to ensure that no other session will be able to modify the same table at the same time. But when another session tries to modify the already locked table that is being modified by another session then the second session will wait (blocked) until the first one finishes and the first one that essentially delays the second one (the blocked one) is called blocking session.

In this article we will see a way so that when we have a session creating blocking in Oracle for more than a time limit, it will send a warning email that contains the information about the session being blocked, the session of the blocker as well as the command to do kill the session of the blocker.

Why is it important to know blocking sessions

When a session creates blocking, it may delay many other sessions that in turn want to modify the same data. This will lead to very long delays in our database. Knowing this session we can do it kill (cut).

But somewhere here we should mention that in Oracle sessions that want not to modify, but to read data do not create blocking as they read the information from the undo tablespace and not directly from the table itself.

The footsteps

In previous article we had seen how we can set one up Linux servers (Oracle Linux based on RHEL specifically) so that we receive an email whenever an error occurs in the Alert log of the database instance. Accordingly, now we will see the steps to receive emails when there is a blocking session after a certain time limit.

Create an SMTP relay with Google's gmail

If we have our own relay on the exchange server with configured smtp that will send the emails, we don't need this step.

But now we will see what we do in case we want to send the emails through Gmail.

First we need to connect to gmail account security.

There we should activate 2 factor authentication and then create an application password.

After getting the code from gmail, go to our server and add to the mail configuration our email along with the code we got:

[oracle@dp-gr ~]$ sudo vi /etc/mail.rc

account gmail {
    set smtp-use-starttls
    set ssl-verify=ignore
    set smtp-auth=login
    set smtp=smtp://smtp.gmail.com:587
    set from="info@dataplatform.gr"
    set smtp-auth-user=dataplatform.gr@gmail.com
    set smtp-auth-password="edw_vazoume_to_app_password"
    set ssl-verify=ignore
    set nss-config-dir=/etc/pki/nssdb
}

We are ready to test that we can send email:

[oracle@dp-gr ~]$ echo "Test" | /usr/bin/mail -A gmail -s "dokimastiko" ToEmailMas@outlook.com

How will we find the session that creates the blocking

We create the following script that outputs a warning message with information about the session being blocked and about the session of the blocker:

[oracle@dp-gr ~]$ vi sessions_blocking.sql

set echo off
set linesize 150
set pagesize 0
set feedback off
select 'Warning: Blocking Sessions on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance||'!!! '
FROM gv$session
where 
rownum = 1
and seconds_in_wait > 600;

SELECT 'Blocked Session: ' 
   || ' LOGON: ' || to_char(h.LOGON_TIME,'YYYY/MM/DD HH:MI:SS') 
   || ' ---- SID: ' || h.SID
   || ' ---- SERIAL: ' || h.SERIAL#
   || ' ---- PROCESS: ' || p.SPID
   || ' ---- USERNAME: ' || u.username
   || ' ---- OSUSER: ' || h.osuser
   || ' ---- MACHINE: ' || h.machine
   || ' ---- PROGRAM: ' || h.program
   || ' ---- MODULE: ' || h.module
   || ' ---- BLOCKING SESSION STATUS: ' || h.blocking_session_status
   || ' ---- BLOCKING INSTANCE: ' || h.blocking_instance
   || ' ---- BLOCKING SESSION: ' || h.blocking_session
   || ' ---- BLOCKING TIME: ' || h.seconds_in_wait
   || ' ---- BLOCKING OBJECT: ' || o.owner||'.'||o.object_name
   || ' ----                  SQL TEXT: ' || s.sql_text
   || '                                                                                                                              '
   || 'Blocker''s Session: ' 
   || ' LOGON: ' || to_char(h2.LOGON_TIME,'YYYY/MM/DD HH:MI:SS') 
   || ' ---- SID: ' || h2.SID
   || ' ---- SERIAL: ' || h2.SERIAL#
   || ' ---- PROCESS: ' || p2.SPID
   || ' ---- STATUS: ' || h2.status
   || ' ---- USERNAME: ' || u.username
   || ' ---- OSUSER: ' || h2.osuser
   || ' ---- MACHINE: ' || h2.machine
   || ' ---- PROGRAM: ' || h2.program
   || ' ---- MODULE: ' || h2.module
   || ' ----                  SQL TEXT: ' || s2.sql_text
   FROM
   gv$session h
INNER JOIN gv$session h2 on (h.blocking_instance=h2.inst_id and  h.blocking_session=h2.sid)
LEFT JOIN gv$SQLAREA s ON h.sql_hash_value = s.hash_value and h.sql_ADDRESS = s.ADDRESS and h.inst_id = s.inst_id 
LEFT JOIN gv$SQLAREA s2 ON h2.sql_hash_value = s2.hash_value and h2.sql_ADDRESS = s2.ADDRESS and h2.inst_id = s2.inst_id 
LEFT JOIN DBA_USERS u ON h.USER# = u.USER_ID
LEFT JOIN gv$process  p ON p.ADDR = h.PADDR and p.inst_id = h.inst_id 
LEFT JOIN gv$process  p2 ON p2.ADDR = h2.PADDR and p2.inst_id = h2.inst_id 
LEFT JOIN dba_objects o ON o.object_id = h.row_wait_obj#
WHERE  1=1
and h.seconds_in_wait > 600
and h.blocking_session_status = 'VALID';
SELECT 
   'Kill Commands: '
   || 'KILL FROM DB: " alter system kill session '''||sid||', '||h.serial#||', @'||h.inst_id||''' immediate; "'
   ||' ---- KILL FROM OS: " kill -9 '||p.spid || ' "'
FROM
   gv$session h
LEFT JOIN gv$SQLAREA s ON h.sql_hash_value = s.hash_value and h.sql_ADDRESS = s.ADDRESS and h.inst_id = s.inst_id 
LEFT JOIN gv$process  p ON p.ADDR = h.PADDR and p.inst_id = h.inst_id 
WHERE  1=1
and h.SID = (select blocking_session from gv$session where seconds_in_wait > 600 and blocking_session_status = 'VALID' and rownum=1);
exit;

*In the fields on line 9, 50 and 62, we define the threshold in seconds from which we want it to send the blocking sessions. 600.

How do we send an email when there is a blocking session for over the limit we set

Now we will create an executable that will run the above query, exporting the results to a text file. In case the file is more than 3 lines then it will send an email with the title “Oracle DB Alert ORCL” in the emails we have defined with a warning and the information of the blocking sessions as text.

The reason that the process only completes if the file contains more than 3 lines is so that it does not send an email if there is no alert and the text file is empty.

So we create the executable with the following code:

[oracle@dp-gr ~]$ vi check_blocking_sessions.sh

#!/bin/bash
sqlplus -S "/as sysdba" @/home/oracle/sessions_blocking.sql > /home/oracle/sessions_blocking.log
cnt=$(wc -l </home/oracle/sessions_blocking.log)
echo $cnt
if [ $cnt -ge "4" ]
then
#       /usr/bin/uuencode alert_$ORACLE_SID.log alert_$ORACLE_SID.log | \
        /usr/bin/mail -A gmail -s "Oracle DB Blocking on ORCL" \
                -r info@dataplatform.gr \
                dataplatform.gr@gmail.com \
         < /home/oracle/sessions_blocking.log



fi

We can alternatively run it remotely on another machine with the SYS user via ezconnect:

#!/bin/bash
sqlplus -S "sys/Kwdikos@hostname:1521/ORCL as sysdba" @/home/oracle/sessions_blocking.sql > /home/oracle/sessions_blocking.log
cnt=$(wc -l </home/oracle/sessions_blocking.log)
echo $cnt
if [ $cnt -ge "4" ]
then
#       /usr/bin/uuencode alert_$ORACLE_SID.log alert_$ORACLE_SID.log | \
        /usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
                -r info@dataplatform.gr \
                dataplatform.gr@gmail.com \
         < /home/oracle/sessions_blocking.log



fi

We don't forget to have given execute permission before executing it:

[oracle@dp-gr ~]$ chmod +x check_blocking_sessions.sh
[oracle@dp-gr ~]$ ./check_blocking_sessions.sh

How do we schedule it to check every 5 minutes for Alerts

Since our executable is ready, we can add a line to the crontab in which we will set it to run every 5 minutes:

[oracle@dp-gr ~]$ crontab -e

#crontab
0,5,10,15,20,25,30,35,40,45,50,55 * * * * . ~/.bash_profile && (/home/opc/check_blocking_sessions.sh >> /dev/null 2>&1)

The result

Now we will see that whenever a blocking exceeds the threshold we have set (for example I had set one second) we will receive an email.

This email will contain a message of the type:

Warning: Blocking Session's on DBNAME@HOSTNAME!!!
Blocked Session:  LOGON: 2022/11/15 01:12:07 ---- SID: 2021 ---- SERIAL: 28919 ---- PROCESS: 21889176 ---- USERNAME: SYS ---- OSUSER: user --
-- MACHINE: SMATZOURANIS ---- PROGRAM: plsqldev.exe ---- MODULE: PL/SQL Developer ---- BLOCKING SESSION STATUS: VALID ---- BLOCKING INSTA
NCE: 1 ---- BLOCKING SESSION: 1341 ---- BLOCKING TIME: 3126 ---- BLOCKING OBJECT: SYS.TEST ---- 		 SQL TEXT: update sys.test set name='t
est10' where name='test2'
Blocker's Session:  LOGON: 2022/11/15 01:11:34 ---- SID: 1341 ---- SERIAL: 31387 ---- PROCESS: 41550040 ---- STATUS: INACTIVE ---- USERNAME: SYS ---
- OSUSER: user ---- MACHINE: SMATZOURANIS ---- PROGRAM: plsqldev.exe ---- MODULE: PL/SQL Developer ----		      SQL TEXT
:

Kill Commands: KILL FROM DB: " alter system kill session '1341, 31387, @1' immediate; " ---- KILL FROM OS: " kill -9 41550040 "

With the following information about the blocking session, as well as about the blocker's session:

• Logon time
• SID
• Serial
• Process
• Username
• OS user
• Machine
• Program
• Module
• Blocking status
• Blocking sessions
• Blocking time
• Blocking object
• SQL text
• Kill blocker commands

Sources:

• V$SESSION