Πώς μπορούμε να λαμβάνουμε email κάθε φορά που έχουμε blocking session σε βάση δεδομένων της Oracle
Πιστοποιήσεις:
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
- Πώς κάνουμε μαζικά detach / attach βάσεις δεδομένων στον SQL Server - 2 Φεβρουάριος 2024
Σε μία βάση δεδομένων της 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.
Αφού πάρουμε τον κωδικό από το gmail, πάμε στον server μας και προσθέτουμε στο mail configuration το email μας μαζί με τον κωδικό που πήραμε:
[oracle@dp-gr ~]$ sudo vi /etc/mail.rcaccount 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.sqlset 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
Hi, Thanks for sharing that script !