Πώς μπορούμε να λαμβάνουμε email κάθε φορά που έχουμε blocking session σε βάση δεδομένων της Oracle

Πώς μπορούμε να λαμβάνουμε email κάθε φορά που έχουμε blocking session σε βάση δεδομένων της Oracle
Πώς μπορούμε να λαμβάνουμε email κάθε φορά που έχουμε blocking session σε βάση δεδομένων της Oracle

Σε μία βάση δεδομένων της Oracle κάθε φορά που κάποιο session ενημερώνει τα στοιχεία ενός πίνακα αυτός γίνεται locked (κλειδώνει) ώστε να είναι σίγουρο ότι κάνενα άλλο session δεν θα μπορεί να τροποποιήσει τον ίδιο πίνακα ταυτόχρονα. Όταν όμως κάποιο άλλο session προσπαθήσει να τροποποιήσει τον ήδη locked πίνακα που τροποποιεί κάποιο άλλο session τότε το δεύτερο session θα περιμένει (blocked) μέχρι να τελειώσει το πρώτο και το πρώτο που ουσιαστηκά καθυστερεί το δεύτερο (το blocked) ονομάζεται blocking session.

Σε αυτό το άρθρο θα δούμε έναν τρόπο ώστε όταν θα έχουμε κάποιο session να δημιουργεί blocking στην Oracle για πάνω από ένα χρονικό όριο, να στέλνει ένα warning email που να περιέχει τις πληροφορίες για το session που γίνεται block, το session του blocker καθώς και την εντολή για να κάνουμε kill το session του blocker.

Γιατί ειναι σημαντικό να γνωρίζουμε τα blocking sessions

Όταν κάποιο session δημιουργεί blocking, ενδέχεται να καθυστερεί πολλά άλλα sessions που θέλουν και αυτά με την σειρά τους να τροποποιήσουν τα ίδια δεδομένα. Έτσι θα οδηγηθούμε σε πολύ μεγάλες καθυστερήσεις στη βάση δεδομένων μας. Γνωρίζοντας το session αυτό μπορούμε να το κάνουμε kill (κόψουμε).

Κάπου εδώ όμως θα πρέπει να αναφέρουμε ότι στην Oracle τα sessions που θέλουν όχι να τροποποιήσουν, αλλά να διαβάσουν δεδομένα δεν δημιουργούν blocking καθώς την πληροφορία την διαβάζει από το undo tablespace και όχι απευθείας από τον ίδιο τον πίνακα.

Τα βήματα

Σε προηγούμενο άρθρο είχαμε δει πως μπορούμε να ρυθμίσουμε έναν Linux server (Oracle Linux βασισμένο σε RHEL συγκεκριμένα) ώστε να λαμβάνουμε email κάθε φορά που εμφανίζεται ένα error στο Alert log του instance της βάσης δεδομένων. Αντίστοιχα τώρα θα δούμε τα βήματα για να λαμβάνουμε email όταν υπάρχει blocking session μετά από κάποιο χρονικό όριο.

Δημιουργία SMTP relay με gmail της Google

Αν έχουμε δικό μας relay στον exchange server με ρυθμισμένο το smtp που θα στέλνει τα emails, αυτό το βήμα δεν το χρειαζόμαστε.

Θα δούμε τώρα όμως τι κάνουμε σε περίπτωση που θέλουμε να στέλνουμε τα emails μέσω Gmail.

Για αρχή θα πρέπει να συνδεθούμε στο gmail account security.

Εκεί θα πρέπει να ενεργοποιήσουμε το 2 factor authentication και έπειτα να δημιουργήσουμε application password.

Πώς μπορούμε να λαμβάνουμε email κάθε φορά που έχουμε blocking session σε βάση δεδομένων της Oracle
01

Αφού πάρουμε τον κωδικό από το gmail, πάμε στον server μας και προσθέτουμε στο mail configuration το email μας μαζί με τον κωδικό που πήραμε:

[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
}

Είμαστε έτοιμοι να κάνουμε δοκιμή ότι μπορούμε να στείλουμε email:

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

Πώς θα βρίσκουμε τo session που δημιουργεί το blocking

Φτιάχνουμε το παρακάτω script που εξάγει ένα warning μύνημα με πληροφορίες για τo session που γίνεται block και για το session του 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;

*Στα πεδία στην 9, 50 και 62 γραμμή ορίζουμε το όριο(threshold) σε δευτερόλεπτα από το οποίο θέλουμε να στέλνει τα blocking session.. Αν θα θέλαμε να μας εμφανίζει μόνο όποιο blocking υπάρχει για πάνω από 10 λεπτά θα αλλάζαμε τις 2 τιμές αυτές σε 600.

Πώς στέλνουμε με email όταν υπάρχει blocking session για πάνω από το όριο που θέσαμε

Τώρα θα δημιουργήσουμε ένα εκτελέσιμο το οποίο θα τρέχει το παραπάνω query εξάγοντας τα αποτελέσματα σε ένα text file. Σε περίπτωση που το αρχείο είναι πάνω από 3 γραμμές τότε θα στείλει email με τον τίτλο “Oracle DB Alert ORCL” στα email που έχουμε ορίσει έχοντας ως κείμενο ένα warning και τις πληροφοριές των blocking sessions.

Ο λόγος που η διαδικασία ολοκληρώνεται μόνο αν το αρχείο περιέχει πάνω από 3 γραμμές είναι ώστε να μην στέλνει email στη περίπτωση που δεν υπάρχει κάποιο alert και το text file είναι κενό.

Οπότε δημιουργούμε το εκτελέσιμο με τον παρακάτω κώδικα:

[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

Μπορούμε εναλλακτικά να το τρέχουμε απομακρυσμένα σε άλλο μηχάνημα με τον χρήστη SYS μέσω 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

Δεν ξεχνάμε να έχουμε δώσει δικαίωμα execute πριν την εκτέλεση του:

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

Πώς το κάνουμε schedule να γίνεται έλεγχος κάθε 5 λεπτά για Alerts

Αφού και το εκτελέσιμό μας είναι έτοιμο μπορούμε να προσθέσουμε μια γραμμή στο crontab στην οποία θα ορίσουμε να τρέχει και αυτή κάθε 5 λεπτά:

[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)

Το αποτέλεσμα

Τώρα θα δούμε ότι όποτε ξεπερνάει κάποιο blocking το threshold που έχουμε ορίσει (για το παράδειγμα είχα βάλει το ένα δευτερόλεπτο) θα μας έρχεται email.

Το email αυτό θα περιέχει ένα μύνημα του τύπου:

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 "

Με τις παρακάτω πληροφορίες για το blocking session, καθώς και για το session του blocker:

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

Πώς μπορούμε να λαμβάνουμε email κάθε φορά που έχουμε blocking session σε βάση δεδομένων της Oracle
02

Πηγές:

Μοιράσου το

Αφήστε μία απάντηση