{"id":3707,"date":"2021-10-04T08:00:00","date_gmt":"2021-10-04T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3707"},"modified":"2023-11-02T17:28:00","modified_gmt":"2023-11-02T14:28:00","slug":"pos-mporoyme-na-lamvanoyme-email-kathe-fora","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-lamvanoyme-email-kathe-fora\/","title":{"rendered":"How can we get email whenever we have blocking session in Oracle database"},"content":{"rendered":"

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<\/strong>.<\/p>\n\n\n\n

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<\/strong> the session of the blocker<\/strong>.<\/p>\n\n\n\n

Why is it important to know blocking sessions<\/h2>\n\n\n\n

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<\/strong> (cut).<\/p>\n\n\n\n

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.<\/strong><\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

<\/p>\n\n\n\n

In previous article<\/a> we had seen how we can set one up Linux servers<\/strong> (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.<\/p>\n\n\n\n

Create an SMTP relay with Google's gmail<\/h2>\n\n\n\n

If we have our own relay<\/strong> on the exchange server with configured smtp that will send the emails, we don't need this step.<\/p>\n\n\n\n

But now we will see what we do in case we want to send the emails through Gmail.<\/p>\n\n\n\n

First we need to connect to gmail account security<\/a>.<\/p>\n\n\n\n

There we should activate 2 factor authentication and then create an application password.<\/p>\n\n\n\n

\"\"
01<\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

After getting the code from gmail, go to our server and add to the mail configuration our email along with the code we got:<\/p>\n\n\n\n

[oracle@dp-gr ~]$ sudo vi \/etc\/mail.rc<\/code><\/pre>\n\n\n\n
account gmail {\n    set smtp-use-starttls\n    set ssl-verify=ignore\n    set smtp-auth=login\n    set smtp=smtp:\/\/smtp.gmail.com:587\n    set from=\"info@dataplatform.gr\"\n    set smtp-auth-user=dataplatform.gr@gmail.com\n    set smtp-auth-password=\"edw_vazoume_to_app_password\"\n    set ssl-verify=ignore\n    set nss-config-dir=\/etc\/pki\/nssdb\n}<\/pre>\n\n\n\n
We are ready to test that we can send email:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ echo \"Test\" | \/usr\/bin\/mail -A gmail -s \"dokimastiko\" ToEmailMas@outlook.com<\/code><\/pre>\n\n\n\n
How will we find the session that creates the blocking<\/h2>\n\n\n\n
We create the following script that outputs a warning message with information about the session being blocked and about the session of the blocker:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ vi sessions_blocking.sql<\/code><\/pre>\n\n\n\n
set echo off\nset linesize 150\nset pagesize 0\nset feedback off\nselect 'Warning: Blocking Sessions on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance||'!!! '\nFROM gv$session\nwhere \nrownum = 1\nand seconds_in_wait > 600;\n\nSELECT 'Blocked Session: ' \n   || ' LOGON: ' || to_char(h.LOGON_TIME,'YYYY\/MM\/DD HH:MI:SS') \n   || ' ---- SID: ' || h.SID\n   || ' ---- SERIAL: ' || h.SERIAL#\n   || ' ---- PROCESS: ' || p.SPID\n   || ' ---- USERNAME: ' || u.username\n   || ' ---- OSUSER: ' || h.osuser\n   || ' ---- MACHINE: ' || h.machine\n   || ' ---- PROGRAM: ' || h.program\n   || ' ---- MODULE: ' || h.module\n   || ' ---- BLOCKING SESSION STATUS: ' || h.blocking_session_status\n   || ' ---- BLOCKING INSTANCE: ' || h.blocking_instance\n   || ' ---- BLOCKING SESSION: ' || h.blocking_session\n   || ' ---- BLOCKING TIME: ' || h.seconds_in_wait\n   || ' ---- BLOCKING OBJECT: ' || o.owner||'.'||o.object_name\n   || ' ----                  SQL TEXT: ' || s.sql_text\n   || '                                                                                                                              '\n   || 'Blocker''s Session: ' \n   || ' LOGON: ' || to_char(h2.LOGON_TIME,'YYYY\/MM\/DD HH:MI:SS') \n   || ' ---- SID: ' || h2.SID\n   || ' ---- SERIAL: ' || h2.SERIAL#\n   || ' ---- PROCESS: ' || p2.SPID\n   || ' ---- STATUS: ' || h2.status\n   || ' ---- USERNAME: ' || u.username\n   || ' ---- OSUSER: ' || h2.osuser\n   || ' ---- MACHINE: ' || h2.machine\n   || ' ---- PROGRAM: ' || h2.program\n   || ' ---- MODULE: ' || h2.module\n   || ' ----                  SQL TEXT: ' || s2.sql_text\n   FROM\n   gv$session h\nINNER JOIN gv$session h2 on (h.blocking_instance=h2.inst_id and  h.blocking_session=h2.sid)\nLEFT 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 \nLEFT 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 \nLEFT JOIN DBA_USERS u ON h.USER# = u.USER_ID\nLEFT JOIN gv$process  p ON p.ADDR = h.PADDR and p.inst_id = h.inst_id \nLEFT JOIN gv$process  p2 ON p2.ADDR = h2.PADDR and p2.inst_id = h2.inst_id \nLEFT JOIN dba_objects o ON o.object_id = h.row_wait_obj#\nWHERE  1=1\nand h.seconds_in_wait > 600\nand h.blocking_session_status = 'VALID';\nSELECT \n   'Kill Commands: '\n   || 'KILL FROM DB: \" alter system kill session '''||sid||', '||h.serial#||', @'||h.inst_id||''' immediate; \"'\n   ||' ---- KILL FROM OS: \" kill -9 '||p.spid || ' \"'\nFROM\n   gv$session h\nLEFT 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 \nLEFT JOIN gv$process  p ON p.ADDR = h.PADDR and p.inst_id = h.inst_id \nWHERE  1=1\nand h.SID = (select blocking_session from gv$session where seconds_in_wait > 600 and blocking_session_status = 'VALID' and rownum=1);\nexit;\n\n<\/pre>\n\n\n\n
*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.<\/em><\/p>\n\n\n\n
How do we send an email when there is a blocking session for over the limit we set<\/h2>\n\n\n\n
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 \u201cOracle DB Alert ORCL\u201d<\/em> in the emails we have defined with a warning and the information of the blocking sessions as text.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
So we create the executable with the following code:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ vi check_blocking_sessions.sh<\/code><\/pre>\n\n\n\n
#!\/bin\/bash\nsqlplus -S \"\/as sysdba\" @\/home\/oracle\/sessions_blocking.sql > \/home\/oracle\/sessions_blocking.log\ncnt=$(wc -l <\/home\/oracle\/sessions_blocking.log)\necho $cnt\nif [ $cnt -ge \"4\" ]\nthen\n#       \/usr\/bin\/uuencode alert_$ORACLE_SID.log alert_$ORACLE_SID.log | \\\n        \/usr\/bin\/mail -A gmail -s \"Oracle DB Blocking on ORCL\" \\\n                -r info@dataplatform.gr \\\n                dataplatform.gr@gmail.com \\\n         < \/home\/oracle\/sessions_blocking.log\n\n\n\nfi<\/pre>\n\n\n\n
We can alternatively run it remotely on another machine with the SYS user via ezconnect<\/a><\/strong>:<\/p>\n\n\n\n
#!\/bin\/bash\nsqlplus -S \"sys\/Kwdikos@hostname:1521\/ORCL as sysdba\" @\/home\/oracle\/sessions_blocking.sql > \/home\/oracle\/sessions_blocking.log\ncnt=$(wc -l <\/home\/oracle\/sessions_blocking.log)\necho $cnt\nif [ $cnt -ge \"4\" ]\nthen\n#       \/usr\/bin\/uuencode alert_$ORACLE_SID.log alert_$ORACLE_SID.log | \\\n        \/usr\/bin\/mail -A gmail -s \"Oracle DB Alert ORCL\" \\\n                -r info@dataplatform.gr \\\n                dataplatform.gr@gmail.com \\\n         < \/home\/oracle\/sessions_blocking.log\n\n\n\nfi<\/pre>\n\n\n\n
We don't forget to have given execute permission before executing it:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ chmod +x check_blocking_sessions.sh\n[oracle@dp-gr ~]$ .\/check_blocking_sessions.sh<\/code><\/pre>\n\n\n\n
How do we schedule it to check every 5 minutes for Alerts<\/h2>\n\n\n\n
Since our executable is ready, we can add a line to the crontab in which we will set it to run every 5 minutes:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ crontab -e<\/code><\/pre>\n\n\n\n
#crontab\n0,5,10,15,20,25,30,35,40,45,50,55 * * * * . ~\/.bash_profile && (\/home\/opc\/check_blocking_sessions.sh >> \/dev\/null 2>&1)<\/pre>\n\n\n\n
The result<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
This email will contain a message of the type:<\/p>\n\n\n\n
Warning: Blocking Session's on DBNAME@HOSTNAME!!!\nBlocked Session:  LOGON: 2022\/11\/15 01:12:07 ---- SID: 2021 ---- SERIAL: 28919 ---- PROCESS: 21889176 ---- USERNAME: SYS ---- OSUSER: user --\n-- MACHINE: SMATZOURANIS ---- PROGRAM: plsqldev.exe ---- MODULE: PL\/SQL Developer ---- BLOCKING SESSION STATUS: VALID ---- BLOCKING INSTA\nNCE: 1 ---- BLOCKING SESSION: 1341 ---- BLOCKING TIME: 3126 ---- BLOCKING OBJECT: SYS.TEST ---- \t\t SQL TEXT: update sys.test set name='t\nest10' where name='test2'\nBlocker's Session:  LOGON: 2022\/11\/15 01:11:34 ---- SID: 1341 ---- SERIAL: 31387 ---- PROCESS: 41550040 ---- STATUS: INACTIVE ---- USERNAME: SYS ---\n- OSUSER: user ---- MACHINE: SMATZOURANIS ---- PROGRAM: plsqldev.exe ---- MODULE: PL\/SQL Developer ----\t\t      SQL TEXT\n:\n\nKill Commands: KILL FROM DB: \" alter system kill session '1341, 31387, @1' immediate; \" ---- KILL FROM OS: \" kill -9 41550040 \"<\/code><\/pre>\n\n\n\n
With the following information about the blocking session, as well as about the blocker's session:<\/p>\n\n\n\n