Kill-Blocker-Job erkennt und entfernt Blocking-Sessions
Der Kill-Blocker-Job dient dazu, um Blocking-Sessions in der Oracle Datenbank zu erkennen und eventuell existierende „Blocker“ zu identifizieren und per E-Mail zu melden.
Dabei kann festgelegt werden, in welchen Intervallen die Blocking-Sessions gemeldet werden und ob sie automatisch gekillt werden oder nicht.
In diesem Beispiel wurde definiert, dass alle Blocking-Sessions die länger als 30 Sekunden blockieren, automatisch identifiziert werden und per E-Mail an mehrere eMail Adressen gemeldet werden.
Weiters versucht der Kill-Blocker-Job die Blocker-Sessions automatisch zu killen.
1) Tablespace BLOCKMON erstellen
Für den neuen User BLOCKMON kann optional ein Tablespace erstellt werden. In unserem Fall erstellen wir einen neuen Tablespace.
create tablespace BLOCKMON datafile size 100M autoextend on maxsize 5G;
2) User BLOCKMON erstellen
create user BLOCKMON identified by PASSWORDDEFINIEREN default tablespace BLOCKMON;
Dem User BLOCKMON müssen folgende, notwendige Rechte vergeben werden:
GRANT CREATE SESSION TO BLOCKMON; GRANT SELECT ANY DICTIONARY TO BLOCKMON; GRANT CREATE PROCEDURE,CREATE SEQUENCE,CREATE VIEW,CREATE TABLE TO BLOCKMON; ALTER USER BLOCKMON QUOTA UNLIMITED ON USERS; GRANT "RESOURCE" TO BLOCKMON ; ALTER USER BLOCKMON DEFAULT ROLE "RESOURCE"; GRANT CREATE JOB TO BLOCKMON ; GRANT ALTER SYSTEM TO BLOCKMON ; GRANT UNLIMITED TABLESPACE TO BLOCKMON ;
3) Tabellen als User BLOCKMON erstellen
Als nächster Schritt müssen folgende Tabellen erstellt werden.
In diesen Tabellen werden die Details zu den Blockern gespeichert – wie zum Beispiel die Kill-Logdaten und Lock-Logdaten.
3-1- DDL für die Tabelle KILLLOG
CREATE TABLE "BLOCKMON"."KILLLOG" ( "SNAP" NUMBER, "TS" TIMESTAMP (6), "INST_ID" NUMBER, "SID" NUMBER, "STMT" VARCHAR2(4000 BYTE) ) TABLESPACE "BLOCKMON" ;
3-2- DDL für die Tabelle LOCKLOG
CREATE TABLE "BLOCKMON"."LOCKLOG" ( "INST_ID" NUMBER, "ADDR" RAW(8), "KADDR" RAW(8), "SID" NUMBER, "TYPE" VARCHAR2(2 BYTE), "ID1" NUMBER, "ID2" NUMBER, "LMODE" NUMBER, "REQUEST" NUMBER, "CTIME" NUMBER, "BLOCK" NUMBER, "SNAP" NUMBER, "TS" TIMESTAMP (6) ) TABLESPACE "BLOCKMON" ;
Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu Problem kommen, dass einige Spalten fehlen. Daher muss in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen werden und in den Tabellen die fehlenden Spalten ergänzt werden.
drop table locklog; create table locklog as (select s.*,1 snap,sysTIMESTAMP ts from gv$lock s where 1=0);
3-3- DDL für die Tabelle SESSIONLOG
CREATE TABLE "BLOCKMON"."SESSIONLOG" ( "INST_ID" NUMBER, "SADDR" RAW(8), "SID" NUMBER, "SERIAL#" NUMBER, "AUDSID" NUMBER, "PADDR" RAW(8), "USER#" NUMBER, "USERNAME" VARCHAR2(30 BYTE), "COMMAND" NUMBER, "OWNERID" NUMBER, "TADDR" VARCHAR2(16 BYTE), "LOCKWAIT" VARCHAR2(16 BYTE), "STATUS" VARCHAR2(8 BYTE), "SERVER" VARCHAR2(9 BYTE), "SCHEMA#" NUMBER, "SCHEMANAME" VARCHAR2(30 BYTE), "OSUSER" VARCHAR2(30 BYTE), "PROCESS" VARCHAR2(24 BYTE), "MACHINE" VARCHAR2(64 BYTE), "PORT" NUMBER, "TERMINAL" VARCHAR2(30 BYTE), "PROGRAM" VARCHAR2(48 BYTE), "TYPE" VARCHAR2(10 BYTE), "SQL_ADDRESS" RAW(8), "SQL_HASH_VALUE" NUMBER, "SQL_ID" VARCHAR2(13 BYTE), "SQL_CHILD_NUMBER" NUMBER, "SQL_EXEC_START" DATE, "SQL_EXEC_ID" NUMBER, "PREV_SQL_ADDR" RAW(8), "PREV_HASH_VALUE" NUMBER, "PREV_SQL_ID" VARCHAR2(13 BYTE), "PREV_CHILD_NUMBER" NUMBER, "PREV_EXEC_START" DATE, "PREV_EXEC_ID" NUMBER, "PLSQL_ENTRY_OBJECT_ID" NUMBER, "PLSQL_ENTRY_SUBPROGRAM_ID" NUMBER, "PLSQL_OBJECT_ID" NUMBER, "PLSQL_SUBPROGRAM_ID" NUMBER, "MODULE" VARCHAR2(64 BYTE), "MODULE_HASH" NUMBER, "ACTION" VARCHAR2(64 BYTE), "ACTION_HASH" NUMBER, "CLIENT_INFO" VARCHAR2(64 BYTE), "FIXED_TABLE_SEQUENCE" NUMBER, "ROW_WAIT_OBJ#" NUMBER, "ROW_WAIT_FILE#" NUMBER, "ROW_WAIT_BLOCK#" NUMBER, "ROW_WAIT_ROW#" NUMBER, "TOP_LEVEL_CALL#" NUMBER, "LOGON_TIME" DATE, "LAST_CALL_ET" NUMBER, "PDML_ENABLED" VARCHAR2(3 BYTE), "FAILOVER_TYPE" VARCHAR2(13 BYTE), "FAILOVER_METHOD" VARCHAR2(10 BYTE), "FAILED_OVER" VARCHAR2(3 BYTE), "RESOURCE_CONSUMER_GROUP" VARCHAR2(32 BYTE), "PDML_STATUS" VARCHAR2(8 BYTE), "PDDL_STATUS" VARCHAR2(8 BYTE), "PQ_STATUS" VARCHAR2(8 BYTE), "CURRENT_QUEUE_DURATION" NUMBER, "CLIENT_IDENTIFIER" VARCHAR2(64 BYTE), "BLOCKING_SESSION_STATUS" VARCHAR2(11 BYTE), "BLOCKING_INSTANCE" NUMBER, "BLOCKING_SESSION" NUMBER, "FINAL_BLOCKING_SESSION_STATUS" VARCHAR2(11 BYTE), "FINAL_BLOCKING_INSTANCE" NUMBER, "FINAL_BLOCKING_SESSION" NUMBER, "SEQ#" NUMBER, "EVENT#" NUMBER, "EVENT" VARCHAR2(64 BYTE), "P1TEXT" VARCHAR2(64 BYTE), "P1" NUMBER, "P1RAW" RAW(8), "P2TEXT" VARCHAR2(64 BYTE), "P2" NUMBER, "P2RAW" RAW(8), "P3TEXT" VARCHAR2(64 BYTE), "P3" NUMBER, "P3RAW" RAW(8), "WAIT_CLASS_ID" NUMBER, "WAIT_CLASS#" NUMBER, "WAIT_CLASS" VARCHAR2(64 BYTE), "WAIT_TIME" NUMBER, "SECONDS_IN_WAIT" NUMBER, "STATE" VARCHAR2(19 BYTE), "WAIT_TIME_MICRO" NUMBER, "TIME_REMAINING_MICRO" NUMBER, "TIME_SINCE_LAST_WAIT_MICRO" NUMBER, "SERVICE_NAME" VARCHAR2(64 BYTE), "SQL_TRACE" VARCHAR2(8 BYTE), "SQL_TRACE_WAITS" VARCHAR2(5 BYTE), "SQL_TRACE_BINDS" VARCHAR2(5 BYTE), "SQL_TRACE_PLAN_STATS" VARCHAR2(10 BYTE), "SESSION_EDITION_ID" NUMBER, "CREATOR_ADDR" RAW(8), "CREATOR_SERIAL#" NUMBER, "ECID" VARCHAR2(64 BYTE), "TS" TIMESTAMP (6), "SNAP" NUMBER, "INFO" VARCHAR2(100 BYTE) ) TABLESPACE "BLOCKMON" ;
Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu dem Problem kommen, dass einige Spalten fehlen. Daher muss hier in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen und in den Tabellen die fehlenden Spalten ergänzt werden.
drop table sessionlog;
create table sessionlog as (select s.*,sysTIMESTAMP ts,1 snap,cast('x' as VARCHAR2(100)) info from gv$session s where 1=0);
3-4- DDL für die Tabelle SQLLOG
CREATE TABLE "BLOCKMON"."SQLLOG"
( "INST_ID" NUMBER,
"SQL_TEXT" VARCHAR2(1000 BYTE),
"SQL_FULLTEXT" CLOB,
"SQL_ID" VARCHAR2(13 BYTE),
"SHARABLE_MEM" NUMBER,
"PERSISTENT_MEM" NUMBER,
"RUNTIME_MEM" NUMBER,
"SORTS" NUMBER,
"LOADED_VERSIONS" NUMBER,
"OPEN_VERSIONS" NUMBER,
"USERS_OPENING" NUMBER,
"FETCHES" NUMBER,
"EXECUTIONS" NUMBER,
"PX_SERVERS_EXECUTIONS" NUMBER,
"END_OF_FETCH_COUNT" NUMBER,
"USERS_EXECUTING" NUMBER,
"LOADS" NUMBER,
"FIRST_LOAD_TIME" VARCHAR2(19 BYTE),
"INVALIDATIONS" NUMBER,
"PARSE_CALLS" NUMBER,
"DISK_READS" NUMBER,
"DIRECT_WRITES" NUMBER,
"BUFFER_GETS" NUMBER,
"APPLICATION_WAIT_TIME" NUMBER,
"CONCURRENCY_WAIT_TIME" NUMBER,
"CLUSTER_WAIT_TIME" NUMBER,
"USER_IO_WAIT_TIME" NUMBER,
"PLSQL_EXEC_TIME" NUMBER,
"JAVA_EXEC_TIME" NUMBER,
"ROWS_PROCESSED" NUMBER,
"COMMAND_TYPE" NUMBER,
"OPTIMIZER_MODE" VARCHAR2(10 BYTE),
"OPTIMIZER_COST" NUMBER,
"OPTIMIZER_ENV" RAW(2000),
"OPTIMIZER_ENV_HASH_VALUE" NUMBER,
"PARSING_USER_ID" NUMBER,
"PARSING_SCHEMA_ID" NUMBER,
"PARSING_SCHEMA_NAME" VARCHAR2(30 BYTE),
"KEPT_VERSIONS" NUMBER,
"ADDRESS" RAW(8),
"TYPE_CHK_HEAP" RAW(8),
"HASH_VALUE" NUMBER,
"OLD_HASH_VALUE" NUMBER,
"PLAN_HASH_VALUE" NUMBER,
"CHILD_NUMBER" NUMBER,
"SERVICE" VARCHAR2(64 BYTE),
"SERVICE_HASH" NUMBER,
"MODULE" VARCHAR2(64 BYTE),
"MODULE_HASH" NUMBER,
"ACTION" VARCHAR2(64 BYTE),
"ACTION_HASH" NUMBER,
"SERIALIZABLE_ABORTS" NUMBER,
"OUTLINE_CATEGORY" VARCHAR2(64 BYTE),
"CPU_TIME" NUMBER,
"ELAPSED_TIME" NUMBER,
"OUTLINE_SID" NUMBER,
"CHILD_ADDRESS" RAW(8),
"SQLTYPE" NUMBER,
"REMOTE" VARCHAR2(1 BYTE),
"OBJECT_STATUS" VARCHAR2(19 BYTE),
"LITERAL_HASH_VALUE" NUMBER,
"LAST_LOAD_TIME" VARCHAR2(19 BYTE),
"IS_OBSOLETE" VARCHAR2(1 BYTE),
"IS_BIND_SENSITIVE" VARCHAR2(1 BYTE),
"IS_BIND_AWARE" VARCHAR2(1 BYTE),
"IS_SHAREABLE" VARCHAR2(1 BYTE),
"CHILD_LATCH" NUMBER,
"SQL_PROFILE" VARCHAR2(64 BYTE),
"SQL_PATCH" VARCHAR2(30 BYTE),
"SQL_PLAN_BASELINE" VARCHAR2(30 BYTE),
"PROGRAM_ID" NUMBER,
"PROGRAM_LINE#" NUMBER,
"EXACT_MATCHING_SIGNATURE" NUMBER,
"FORCE_MATCHING_SIGNATURE" NUMBER,
"LAST_ACTIVE_TIME" DATE,
"BIND_DATA" RAW(2000),
"TYPECHECK_MEM" NUMBER,
"IO_CELL_OFFLOAD_ELIGIBLE_BYTES" NUMBER,
"IO_INTERCONNECT_BYTES" NUMBER,
"PHYSICAL_READ_REQUESTS" NUMBER,
"PHYSICAL_READ_BYTES" NUMBER,
"PHYSICAL_WRITE_REQUESTS" NUMBER,
"PHYSICAL_WRITE_BYTES" NUMBER,
"OPTIMIZED_PHY_READ_REQUESTS" NUMBER,
"LOCKED_TOTAL" NUMBER,
"PINNED_TOTAL" NUMBER,
"IO_CELL_UNCOMPRESSED_BYTES" NUMBER,
"IO_CELL_OFFLOAD_RETURNED_BYTES" NUMBER,
"SNAP" NUMBER,
"TS" TIMESTAMP (6)
)
TABLESPACE "BLOCKMON"
LOB ("SQL_FULLTEXT") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu dem Problem kommen, dass einige Spalten fehlen. Daher muss hier in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen und in den Tabellen die fehlenden Spalten ergänzt werden.
drop table sqllog; create table sqllog as (select s.*,1 snap,sysTIMESTAMP ts from gv$sql s where 1=0);
3-5- DDL für die Tabelle SQLTEXTLOG
CREATE TABLE "BLOCKMON"."SQLTEXTLOG" ( "INST_ID" NUMBER, "ADDRESS" RAW(8), "HASH_VALUE" NUMBER, "SQL_ID" VARCHAR2(13 BYTE), "COMMAND_TYPE" NUMBER, "PIECE" NUMBER, "SQL_TEXT" VARCHAR2(64 BYTE), "SNAP" NUMBER ) TABLESPACE "BLOCKMON" ;
Hinweis: Bei einigen Datenbank-Releases kann es beim Insert zu dem Problem kommen, dass einige Spalten fehlen. Daher muss hier in so einem Fall die Struktur von den jeweiligen gv$ Views übernommen und in den Tabellen die fehlenden Spalten ergänzt werden.
drop table sqltextlog; create table sqltextlog as (select s.*,1 snap from gv$sqltext s where 1=0);
3-6- DDL für die Tabelle TEST
CREATE TABLE "BLOCKMON"."TEST" ( "N" NUMBER ) TABLESPACE "BLOCKMON" ;
4) Mehrere VIEWS als User BLOCKMON erstellen
4-1- DDL for View V_BLOCKED_SESSIONS
CREATE OR REPLACE FORCE VIEW "BLOCKMON"."V_BLOCKED_SESSIONS" ("TIMESTAMP", "SNAP", "INFO", "INST_ID", "SID", "USERNAME", "WAIT_CLASS",
"EVENT", "SECONDS_IN_WAIT", "SQL_ID", "SQL_TEXT") AS
select se.ts timestamp,se.snap,se.info,se.inst_id,se.sid,se.username,se.wait_class,se.event,se.seconds_in_wait,sq.sql_id,sq.sql_text
from sessionlog se,sqllog sq
where info='blocked'
and se.snap=sq.snap(+)
and se.sql_id=sq.sql_id(+);
4-2- DDL for View V_BLOCKING_DATA
CREATE OR REPLACE FORCE VIEW "BLOCKMON"."V_BLOCKING_DATA" ("SNAP", "INST_ID", "SID", "SQL_ID", "INFO", "WAIT_CLASS", "EVENT",
"SECONDS_IN_WAIT", "TIMESTAMP") AS
select se.snap,se.inst_id,se.sid,se.sql_id,se.info,se.wait_class,se.event,se.seconds_in_wait,se.ts timestamp from sessionlog se, killlog
ki,sqllog sq
where se.snap=ki.snap
and se.snap=sq.snap(+)
and se.inst_id=ki.inst_id
and se.sid=ki.sid
and se.sql_id=sq.sql_id(+);
4-3- DDL for View V_KILLED_SESSIONS
CREATE OR REPLACE FORCE VIEW "BLOCKMON"."V_KILLED_SESSIONS" ("TIMESTAMP", "SNAP", "INFO", "INST_ID", "SID", "USERNAME", "WAIT_CLASS",
"EVENT", "SECONDS_IN_WAIT", "SQL_ID", "SQL_TEXT") AS
select ki.ts timestamp,ki.snap,se.info,ki.inst_id,ki.sid,se.username,se.wait_class,se.event,se.seconds_in_wait,sq.sql_id,sq.sql_text
from killlog ki,sessionlog se,sqllog sq
where ki.snap=se.snap
and ki.inst_id=se.inst_id
and ki.sid=se.sid
and se.info='blocker'
and se.snap=sq.snap(+)
and se.sql_id=sq.sql_id(+);
5) SEQUENCES als User BLOCKMON erstellen
DDL for Sequence SEQ
CREATE SEQUENCE "BLOCKMON"."SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 607041 CACHE 20 NOORDER NOCYCLE ;
6) PROZEDUR WATCHDOG als User BLOCKMON erstellen
DDL for Procedure WATCHDOG
set define off;
create or replace procedure BLOCKMON.watchdog as
snapid number;
rsid number;
sqlid varchar2(100);
stm varchar2(4000);
msg varchar2(32000);
blkdmsg varchar2(32000);
blkdsql varchar2(32000);
crlf varchar2(100):=chr(13)||chr(10);
flag number;
begin
select seq.nextval into snapid from dual;
for s in (select * from gv$session where blocking_session is not null) loop
--session
insert into sessionlog (select s1.*,systimestamp,snapid,'blocked' from gv$session s1 where inst_id=s.inst_id and sid=s.sid);
insert into sessionlog (select s1.*,systimestamp,snapid,'blocker' from gv$session s1 where inst_id=s.blocking_instance and sid=s.blocking_session);
insert into sessionlog (select s1.*,systimestamp,snapid,'finalblocker' from gv$session s1 where inst_id=s.final_blocking_instance and sid=s.final_blocking_session);
--sql
insert into sqllog (select s1.*,snapid,systimestamp from gv$sql s1 where sql_id=(select sql_id from gv$session s1 where inst_id=s.inst_id and sid=s.sid));
insert into sqllog (select s1.*,snapid,systimestamp from gv$sql s1 where sql_id=(select sql_id from gv$session s1 where inst_id=s.blocking_instance and sid=s.blocking_session));
insert into sqllog (select s1.*,snapid,systimestamp from gv$sql s1 where sql_id=(select sql_id from gv$session s1 whereinst_id=s.final_blocking_instance and sid=s.final_blocking_session));
--lock
insert into locklog (select s1.*,snapid,systimestamp from gv$lock s1 where inst_id=s.inst_id and sid=s.sid);
insert into locklog (select s1.*,snapid,systimestamp from gv$lock s1 where inst_id=s.blocking_instance and sid=s.blocking_session);
insert into locklog (select s1.*,snapid,systimestamp from gv$lock s1 where inst_id=s.final_blocking_instance and sid=s.final_blocking_session);
--sqltext
insert into sqltextlog (select s1.*,snapid from gv$sqltext s1 where inst_id=s.inst_id and sql_id=s.sql_id);
blkdmsg:='instance:'||s.inst_id||crlf;
blkdmsg:=blkdmsg||'sid:'||s.sid||crlf;
blkdmsg:=blkdmsg||'username:'||s.username||crlf;
blkdmsg:=blkdmsg||'program:'||s.program||crlf;
blkdmsg:=blkdmsg||'module:'||s.module||crlf;
blkdmsg:=blkdmsg||'wait_class:'||s.wait_class||crlf;
blkdmsg:=blkdmsg||'evnt:'||s.event||crlf;
blkdmsg:=blkdmsg||'seconds_in_wait:'||s.seconds_in_wait||crlf;
blkdmsg:=blkdmsg||crlf;
flag:=0;
blkdsql:='';
for r in (select distinct piece,sql_text from sqltextlog where sql_id=s.sql_id order by piece) loop
blkdsql:=blkdsql||r.sql_text||crlf;
flag:=1;
end loop;
if flag=0 then
blkdsql:='--NONE--'||crlf;
end if;
--kill nur final blocker
if s.seconds_in_wait>=30 then
for b in (select * from gv$session where inst_id=s.final_blocking_instance and sid=s.final_blocking_session) loop
--sqltext
insert into sqltextlog (select s1.*,snapid from gv$sqltext s1 where inst_id=b.inst_id and sql_id=b.sql_id);
stm:='alter system kill session '''||b.sid||','||b.serial#||',@'||b.inst_id||'''';
insert into killlog values (snapid,systimestamp,b.inst_id,b.sid,stm);
msg:='will try to kill('||stm||')'||chr(10)||chr(13)||'(if this mail is not sent again, the kill has been successful)'||crlf;
msg:=msg||crlf;
msg:=msg||'(there will be one mail per blocked session, if there is a cascading block situation, the blocks will be handled sequencially)'||
crlf;
msg:=msg||crlf;
msg:=msg||'DETAILED INFORMATION:'||crlf;
msg:=msg||'---------------------'||crlf;
msg:=msg||crlf;
msg:=msg||'BLOCKER SESSION:'||crlf;
msg:=msg||'----------------'||crlf;
msg:=msg||'instance:'||b.inst_id||crlf;
msg:=msg||'sid:'||b.sid||crlf;
msg:=msg||'username:'||b.username||crlf;
msg:=msg||'program:'||b.program||crlf;
msg:=msg||'module:'||b.module||crlf;
msg:=msg||'wait_class:'||b.wait_class||crlf;
msg:=msg||'event:'||b.event||crlf;
msg:=msg||'seconds_in_wait:'||b.seconds_in_wait||crlf;
msg:=msg||crlf;
msg:=msg||'BLOCKED SESSION:'||crlf;
msg:=msg||'-------------------'||crlf;
msg:=msg||blkdmsg;
msg:=msg||crlf;
msg:=msg||'Current Statements:'||crlf;
msg:=msg||'-------------------'||crlf;
msg:=msg||'BLOCKER(normaly NONE):'||crlf;
flag:=0;
for r in (select distinct piece,sql_text from sqltextlog where sql_id=b.sql_id order by piece) loop
msg:=msg||r.sql_text||crlf;
flag:=1;
end loop;
if flag=0 then
msg:=msg||'--NONE--'||crlf;
end if;
msg:=msg||crlf;
msg:=msg||'BLOCKED:'||crlf;
msg:=msg||blkdsql||crlf;
msg:=msg||crlf;
msg:=msg||'historical data in schema BLOCKMON:'||crlf;
msg:=msg||'select * from blockmon.v_blocked_sessions where snap=<snap>'||crlf;
msg:=msg||'select * from blockmon.v_killed_sessions where snap=<snap>'||crlf;
msg:=msg||'Base Tables:'||crlf;
msg:=msg||'KILLLOG'||crlf;
msg:=msg||'LOCKLOG'||crlf;
msg:=msg||'SESSIONLOG'||crlf;
msg:=msg||'SQLLOG'||crlf;
msg:=msg||'SQLTEXTLOG'||crlf;
msg:=msg||crlf;
utl_mail.send(
sender => 'NAME@xyxyxy.com',
recipients => 'EMPFÄNGER@xyxyxy.com, EMPFÄNGER@xyxyxy.com ',
cc => 'EMPFÄNGER@xyxyxy.com ',
bcc => '',
subject => 'BLOCKER DETECTED (snap '||snapid||')',
message => msg,
mime_type => 'text/plain; charset=us-ascii',
priority => NULL);
begin
-- null;
execute immediate stm;
exception when others then null;
end;
dbms_output.put_Line(stm);
end loop;
end if;
end loop;
end;
/
Falls man vorher testen möchte ob die Blocking Sessions richtig gekillt werden, muss man den Eintrag „execute immediate stm;“ auskommentieren.
Dadurch werden die Blocker nicht gekillt, sondern nur E-Mails verschickt mit der Info welche Session gekillt werden sollten.
begin null; -- execute immediate stm; exception when others then null; end; dbms_output.put_Line(stm);
Wie schon oben bei den einzelnen Tabellen beschrieben, kann es bei einigen Datenbank-Releases beim Insert in den folgenden Tabellen zum Problem kommen, dass einige Spalten fehlen, die aber auf den entsprechenden Datenbank-Releases in den jeweiligen gv$ Views vorhanden sind:
sqllog, sessionlog, locklog, sqltextlog
Daher muss hier in solchen Fällen die Struktur von den jeweiligen gv$ Views übernommen- und in den Tabellen die fehlenden Spalten ergänzt werden.
Als BLOCKMON:
drop table locklog;
create table locklog as (select s.*,1 snap,sysTIMESTAMP ts from gv$lock s where 1=0);
drop table sqllog;
create table sqllog as (select s.*,1 snap,sysTIMESTAMP ts from gv$sql s where 1=0);
drop table sessionlog;
create table sessionlog as (select s.*,sysTIMESTAMP ts,1 snap,cast('x' as VARCHAR2(100)) info from gv$session s where 1=0);
drop table sqltextlog;
create table sqltextlog as (select s.*,1 snap from gv$sqltext s where 1=0);
7) ACL EINRICHTEN
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('netacl.xml',
'Allow usage to the UTL network packages', 'BLOCKMON', TRUE,
'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'BLOCKMON', TRUE,
'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('netacl.xml','*');
END;
/
8) Falls kein UTL_MAIL installiert -> UTL_MAIL installieren
select * from dba_objects where object_name='UTL_MAIL'; => kein UTL_MAIL installiert installieren des UTL_MAILS: @utlmail.sql @prvtmail.plb Rechte fehlen (BLOCKMON): grant execute on utl_mail to BLOCKMON;
9) SMTP EINRICHTEN
SQL> alter system set smtp_out_server='mail.xyxyxy.com'; System altered. SQL> show parameter smtp NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ smtp_out_server string mail.xyxyxy.com
10) TESTEN
Zum Testen kann eine Blocking-Session erstellt werden und getestet werden, ob der Kill-Blocker richtig eingerichtet wurde.
1- Blockingsessions erzeugen:
BLOCKMON User: insert into TEST values(1); commit; update TEST set n=2;
Nun nochmal mit einem anderen User.
update BLOCKMON.TEST set n=3; => Blockingsessions erzeugt!!! 3- Prozedur ausführen als BLOCKMON USER (am besten ohne kill): => nach 30 Sekunden: begin watchdog; end; /
Falls der Test erfolgreich verläuft, kann das Killen wieder aktiviert- und der Job dafür eingerichtet werden.
11) JOB FÜR BLOCKMON USER EINRICHTEN
Nun muss noch folgender Job zum Ausführen des WATCHDOGs eingerichtet werden:
begin dbms_scheduler.create_job( job_name=>'BLOCKMON.WATCHDOG_JOB', job_type=>'PLSQL_BLOCK', job_action=>'begin watchdog;end;', repeat_interval=>'freq=minutely', enabled=>true); end; /
12) Abschließend
Durch das Einrichten des WATCHDOG_JOB wird die Oracle Datenbank laufend aktiv auf blockierende Sessions überprüft und ein aufgespührter Blocker automatisch beendet.
In der WATCHDOG Procedure (Punkt 6) werden alle Einstellungen vorgenommen, um die Kill-Voraussetzungen für Blocking Sessions anzupassen.
DBConcepts GmbH
