Vergleich der Oracle SQL Funktionen ADD_MONTHS vs INTERVAL

Oracle SQL ADD_MONTHS vs. INVERVAL Funktionen

ADD_MONTHS:

Die Funktion ADD_MONTHS fügt einem Datum eine definierte Anzahl von Monaten hinzu.

Die Syntax sieht folgendermaßen aus:

ADD_MONTHS (input_date, number_months)

Um diese Operation durchführen zu können, werden also zwei verpflichtende Parameter benötigt:

Input_date: Datum, zu welchem n Monate dazugerechnet werden

Number_months: Anzahl der Monate vom Datentyp Integer, die zum angegebenen Datum dazugerechnet werden.

Der Rückgabewert dieser Funktion ist immer ein DATE.

SELECT ADD_MONTHS (‚17-04-2020‘, 2) AS ADD_MONTHS_RESULT
FROM DUAL

 

Ist das angegebene Datum der letzte Tag des Monats und das resultierende Datum hat weniger oder mehr Tage, so ist das Ergebnis der letzte Tag des Monats.

SELECT ADD_MONTHS (‚31-03-2020‘, 1) AS ADD_MONTHS_RESULT
FROM DUAL

Natürlich kann man vom angegebenen Datum auch Monate abziehen, indem man ein „–“ vor den Monaten platziert.

SELECT ADD_MONTHS (‚28-02-2020‘, -1) AS ADD_MONTHS_RESULT
FROM DUAL

Spannend wird das Thema bei Schaltjahren.
2020 ist ein Schaltjahr, somit hat der Februar 29 anstatt 28 Tage.

SELECT ADD_MONTHS (‚28-02-2020‘, 1) AS ADD_MONTHS_RESULT
FROM DUAL

Nimmt man jedoch den 29. Februar, so bekommt man nicht den 29. März als Resultat, sondern den 31. März.

SELECT ADD_MONTHS (‚29-02-2020‘, 1) AS ADD_MONTHS_RESULT 
FROM DUAL

INTERVAL YEAR TO MONTH:

Dies ist ein spezieller Datentyp, welcher es ermöglicht, Intervalle von Jahren und Monaten zu speichern.

Die Syntax hat folgendes Format:

INTERVAL ‚year[-month]‘ [YEAR[(precision)])] [TO MONTH]

Defaultmäßig wird precision auf 2 gesetzt, sofern nichts anderes angegeben wurde. Das heißt, es können nur 99 Jahre und 11 Monate gespeichert werden.

INTERVAL '120-3' YEAR(3) TO MONTH:

Intervall von 120 Jahren und 3 Monaten.

In diesem Fall muss precision angegeben werden, da die angegebenen Jahre dreistellig sind.

INTERVAL '105' YEAR(3)

Intervall von 105 Jahren.

INTERVAL '500' MONTH(3)

Intervall von 500 Monaten.

In diesem Fall muss precision angegeben werden, da die angegebenen Monate dreistellig sind.

Bei Monatsangaben muss man sehr genau sein.

Anders als bei ADD_MONTHS rechnet INTERVAL nicht automatisch auf den letzten Tag des Monats.

Folgendes Statement gibt einen „ORA-01839: Datum für angegebenen Monat nicht gültig“ Fehler zurück:

SELECT TO_DATE (‚31-01-2020‘) + INTERVAL ‚1‘ MONTH AS RESULT
FROM DUAL

Auch bei Schaltjahren muss man sehr genau sein.

Dieses Statement gibt einen „ORA-01839: Datum für angegebenen Monat nicht gültig“ Fehler zurück:

SELECT TO_DATE (‚29-02-2020‘) + INTERVAL ‚3‘ YEAR AS RESULT 
FROM DUAL

Korrekt wäre:

SELECT TO_DATE (‚29-02-2020‘) + INTERVAL ‚4‘ YEAR AS RESULT FROM DUAL


 

Oracle Session Killer

Oracle Standard Edition – Simple Session/SQL Killer

In diesem kurzen Blog Post zeigen wir, wie man mittels einer einfachen Prozedur „long running Sessions“ killen kann.

Der Nachteil dieser Lösung ist, dass die komplette Session gekillt wird.

Damit diese Lösung aber so generisch wie möglich ist, werden nur jene Spalten selektiert die ausreichen, um eine Identifikation des USER/SQL sicherzustellen.

Es ist natürlich möglich, auch weitere Spalten hinzuzugeben und die Tabellen Definition dafür anzupassen.

Diese Killer Prozedur eignet sich für Oracle Standard Edition Datenbanken, wo kein Resource Manager funktioniert.

Ab Version 18c hat Oracle eine „CANCEL SQL“ eingeführt, siehe https://oracle-base.com/articles/18c/alter-system-cancel-sql-18c

Wenn Sie eine höhere Version verwenden, kann diese Prozedur einfach umgeschrieben werden, in dem Sie folgendes Statement …

stmt := 'alter system kill session '''|| d.sid ||','||d.serial#||',@'||d.inst_id||''' immediate';

auf…

stmt := 'alter system cancel sql '''|| d.sid ||','||d.serial#||',@'||d.inst_id||''' ';

…ändern.

PLSQL Code

drop table sys.dbmonitor_tab purge ;
create table sys.dbmonitor_tab (datum timestamp, inst_id number, sid number, serial# number, username varchar2(50), sql_id varchar2(30), sql_text varchar2(50)) tablespace users ;

create or replace procedure sys.dbmonitor(timeout IN number) IS 
-- query everything what is NOT a background process, bg processes dont have a username :)
cursor get_data is select s.inst_id, s.sid, s.serial#, s.username, s.sql_id, substr(sql.sql_text,1,50) as txt from gv$session s, gv$sqlarea sql where s.sql_id=sql.sql_id and s.inst_id=sql.inst_id and 
status='ACTIVE' and round(last_call_et / 60,2) > timeout and username is not null ;
d get_data%rowtype;
stmt varchar2(4000) ;
begin
for d in get_data
loop 
-- audit the query
insert into sys.dbmonitor_tab values (systimestamp, d.inst_id, d.sid, d.serial#, d.username, d.sql_id, d.txt) ;
commit ;

stmt := 'alter system kill session '''|| d.sid ||','||d.serial#||',@'||d.inst_id||''' immediate';
-- kill it!
execute immediate stmt ;
end loop;

exception
when others then
NULL ;
end; 
/

Test Szenario

Session 1: (läuft normalerweise sehr lange):

SQL> select count(*) from dba_objects,dba_objects,dba_objects ;

Session 2: (kill session die länger als 1 Minute laufen):

SQL> exec dbmonitor(1)

PL/SQL procedure successfully completed.

SQL> select * from sys.dbmonitor_tab ;

DATUM INST_ID SID SERIAL# USERNAME SQL_ID SQL_TEXT
---------------------------------------- ---------- ---------- ---------- ---------- ------------------------------ --------------------------------------------------
15-MAY-20 01.39.37.429586 PM 1 85 60201 SYS d3s7r4a3xa0su select count(*) from dba_objects,dba_objects,dba_o
15-MAY-20 01.43.00.013776 PM 1 85 59874 SYS d3s7r4a3xa0su select count(*) from dba_objects,dba_objects,dba_o

Session 1 bekommt folgende Meldung angezeigt:

SQL> select count(*) from dba_objects,dba_objects,dba_objects ;
select count(*) from dba_objects,dba_objects,dba_objects
*
ERROR at line 1:
ORA-00028: your session has been killed

Wenn Oracle 18c oder eine höhere Versionen implementiert ist, bekommt die Session folgenden Error angezeigt und wird nicht gekillt.

Der Benutzer kann in diesem Fall weiterarbeiten.

SQL> select count(*) from dba_objects,dba_objects,dba_objects ;
select count(*) from dba_objects,dba_objects,dba_objects
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Um diese Prozedur autonom zu machen, können wir in der Datenbank einen Scheduler Job erstellen, der alle 5 Minute gestartet wird und jene Sessions killt, die länger als 5 Minuten laufen:

begin
dbms_scheduler.create_job(
job_name=>'SYS.SESSION_KILLER',
job_type=>'PLSQL_BLOCK',
job_action=>'begin dbmonitor(5);end;',
repeat_interval=>'FREQ=MINUTELY;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55',
enabled=>true);
end;
/
Dbvisit switchover under the hood

Dbvisit switchover under the hood

Dbvisit Standby is a complete physical standby solution for Oracle Standard Edition databases, where we can’t use Oracle Data Guard due to licensing restrictions.

Executing switchover in Dbvisit configuration is quite a straightforward process – just click on “Perform graceful switchover” and select the configuration where we want to execute the switchover.

Before performing switchover, ensure that daemons are turned off and log gap is 0.

DBVisit main screen

Dbvisit main screen

DBVisit switchover screen

Dbvisit switchover screen

But what’s happening on the databases during the switchover process?

Dbvisit provides us only very high-level information.

DBVisit switchover status

Dbvisit switchover status

 

But what’s going on during each step?

Let’s have a look: 

1.      Running pre-checks

In this step, network connectivity, database availability and running processes are checked.

2.      Pre processing

Nologging operations, log gap and standby consistency are checked.

3.      Processing primary

Database is restarted in restricted session mode, current log is archived and standby redolog files are created.

Then the database is restarted, control file is backed up by Dbvisit and the database is shut down.

4.      Processing standby, converting standby

The control file backup created in the previous step and all the missing redo logs are transferred to the standby location.

The standby DB is restarted with that control file to mount exclusive mode and the data files are renamed in the control file if necessary.

Online log files are dropped on the standby and unarchived online log files from the primary DB are transferred to recover the standby database. TEMP files are deleted from the control file.

Afterwards, the database is opened mode as the new primary DB – role transition was done by transferring the control file from the primary DB, therefore there’s no need to convert it. Now when the database is open, the missing online log files are recreated.

Converting standby is complete.

5.      Converting primary

Database is started to mount state, role switch is executed, online logs are deleted on OS and the database is restarted again to mount state. After the restart, the database role is PHYSICAL STANDBY.

6.      Completing

Missing temp files in the new primary are recreated.

7.      Synchronizing

All the new archived logs from the new primary are being transferred to the new standby.

8.      Post processing

Dbvisit configuration is updated and variables are reversed between primary and standby.

Oracle Text Troubleshooting

Oracle Text Index – Concept and troubleshooting its related issues

With Oracle text indexes (or Domain index), we can index text documents and search it based on contents using text patterns with specialized text query operators.

Oracle Text index is different from the traditional B-Tree or Bitmap indexes. They have several components communicates internally.

In a query application, the table must contain the text or pointers to where the text is stored. Text is usually a collection of documents but can also be small text.

Oracle Text index differs from the traditional B-Tree or Bitmap. In an Oracle Text index, the text data is not directly indexed rather, the text data is split into a set of tokens (these splits stored in database internal tables) and tokens are indexed.

Oracle Text Index objects

Oracle Text index has four tables: $I, $K, $N and $R tables.

The $I table contains the data which is being indexed, all the tokens (words) generated from the text document is stored in this table. The tokens in this table are indexed by a B-Tree index with name format DR${index_name}$X.

The $K table maps the internal DOCID values to external ROWID values (fetching a DOCID when we know the ROWID value) .

The $R table maps the ROWID values to DOCID values, (fetching a ROWID when we know the DOCID value). The entries from this table are indexed by a B-Tree index with name format DRC${index_name}$R.

The $N table contains a list of deleted DOCID values, which are cleaned up by the index optimization process.

Oracle Text Health Check

 Oracle Text Status and Version:

  1. A: Status of all CTXSYS objects status :
SELECT * FROM dba_objects
WHERE status !='VALID' AND OWNER = 'CTXSYS' 
ORDER BY object_type,     object_name;

B: The query for health check of the index.
The idx_docid_count is Number of documents indexed. The number of idx_docid_count should be the same or close to the number of rows of base table. The domidx_status  is domain index status.

SELECT c.idx_owner,c.idx_name,c.idx_text_name,c.idx_type,
c.idx_docid_count, i.status,i.domidx_status
FROM ctxsys.ctx_indexes c, dba_indexes i
WHERE c.idx_owner = ‘OWNER’
AND c.idx_name = ‘INDEX_NAME' and c.idx_name=i.index_name
ORDER BY 2,3;

C: Compilation errors of invalid Text-related objects:

SELECT owner, name, type, line, position, text
  FROM dba_errors
 WHERE owner = 'CTXSYS'
      OR (owner = 'SYS' AND (name like 'CTX_%' or name like 'DRI%'))
  ORDER BY owner, name, sequence;

  SELECT * FROM ctxsys.ctx_index_errors
   ORDER BY err_timestamp DESC, err_index_owner, err_index_name;

D: Extract the DDL of the existing index :

SELECT CTX_REPORT.CREATE_INDEX_SCRIPT('SCHEMA.INDEX_NAME') FROM DUAL;
  1. Validating the Index integrity:

A: Validate $K against the base table(there should be no rows selected for a valid INDEX) :

select  *
from dr$INDEX_NAME$k k
where not exists (select 1
from TABLE_NAME t
where k.textkey = t.rowid);

The keys on $K  table should be match with the base table rowids.

B: Validate $R against $K(there should be no rows selected for a valid INDEX) :

select  *
from table(ctx_diag.decode_r('dr$INDEX_NAME$R')) r
where not exists (select 1
from dr$INDEX_NAME$k k
where r.textkey = k.textkey);

C: validate $R (find duplicates) (there should be no rows selected for a valid INDEX) :

column docids for a40

select  textkey, listagg(docid, ', ') within group (order by docid) docids
from table(ctx_diag.decode_r('dr$INDEX_NAME$R'))
group by textkey
having count(*) > 1;

The above queries for validating the TEXT index should have no return values therefore the index would be consistence.

Types of Oracle Text Indexes

CONTEXT

Use this index to build a text retrieval application when your text consists of large coherent documents.

You can index documents of different formats such as MS Word, HTML or plain text.

You can customize the index in a variety of ways.

This index type requires CTX_DDL.SYNC_INDEX after DML on base table.

Note! Transactional CONTEXT Indexes: The new TRANSACTIONAL parameter to CREATE INDEX and ALTER INDEX enables changes to a base table to be immediately queryable.

CTXCAT

Use this index type for better mixed query performance.

Typically, with this index type, you index small documents or text fragments.
Other columns in the base table, such as item names, prices, and descriptions can be included in the index to improve mixed query performance.

This index is larger and takes longer to build than a CONTEXT index.

The size of a CTXCAT index is related to the total amount of text to be indexed, the number of indexes in the index set, and the number of columns indexed.
Consider your queries and your resources before adding indexes to the index set.

This index type is transactional, automatically updating itself after DML to base table.

No CTX_DDL.SYNC_INDEX is necessary.

CTXRULE

Use CTXRULE index to build a document classification or routing application.

This index is created on a table of queries, where the queries define the classification or routing criteria. 

ALTER INDEX Sync Methods

 MANUAL:

No automatic synchronization. This is the default. You must manually synchronize the index with CTX_DDL.SYNC_INDEX.

EVERY:

Automatically synchronize the index at a regular interval specified by the value of interval-string.

ON COMMIT:

Synchronize the index immediately after a commit.   

TRANSACTIONAL:

Specify that documents can be searched immediately after they are inserted or updated.

If a text index is created with TRANSACTIONAL enabled, then, in addition to processing the synchronized rowids already in the index, the CONTAINS operator will process unsynchronized rowids as well.

To turn on TRANSACTIONAL index property:

ALTER INDEX myidx REBUILD PARAMETERS('replace metadata transactional');
                To turn off TRANSACTIONAL index property:
ALTER INDEX myidx REBUILD PARAMETERS('replace metadata nontransactional');

Oracle Text Index preferences

DATASTORE:

  1.  DIRECT_DATASTORE: Indicates that the data is stored internally in text columns of a database table.
  2. MULTI_COLUMN_DATASTORE: Indicates that the data is stored in text table in more than one column. Columns are concatenated (joined) to create a virtual document and each concatenated row is indexed as a single document
  3. DETAIL_DATASTORE: Indicates that the data is stored internally in a text column.
  4. NESTED_DATASTORE: Indicates that the data is stored in nested tables
  5. FILE_DATASTORE: Indicates that the data is stored in Operating System files. This type of data source is supported only for CONTEXT index.
  6. URL_DATASTORE: Indicates that the data is stored over Internet.
  7. USER_DATASTORE: Indicates that the documents would be synthesized at index time by a user defined stored procedure

FILTER:

In this phase the text stream can be converted to format that is recognized by the Oracle text processing engine. 

SECTIONER:

 The task of the sectioner is to divide the incoming text stream into multiple sections based on the internal document structures (HTML or XML).

 LEXER:

This property determines the language associated with the incoming document.

 How to SYNC_INDEX after DML on base table

The following script is useful to synchronize the index with the base table when using context type of text index:

export ORACLE_SID=SID_NAME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib32:/usr/lib
sqlplus "/as sysdba" << EOF
exec ctx_ddl.sync_index(idx_name =>'SCHEMA.INXEX’);
exit;
EOF

Oracle 19c DBMS_JOB Konvertierung mit Tücken

Unlängst machte ich bei einer Datenbank-Migration Bekanntschaft mit einer der vielen Verhaltensänderungen, wie sie bei Versionswechsel immer wieder vorkommen.

Der Kunde hatte sich entschieden seine bereits ans Herz gewachsene Oracle 11.2.0.4er Datenbank auf aktuellen Stand zu bringen und sich folgerichtig für die Oracle 19c Version entschieden, welche ja eine „Long Term Support“ Version ist.

Da die Applikationslogik bereits zuvor in einzelne Schemas aufgeteilt war, wählte ich als Migrationsmethode Datapump Export/Import.

DBMS_JOB konvertiert

Nach dem ersten Test-Import und der für Export/Import obligatorischen Analyse der Importfehler stellte ich mit Überraschung fest, dass die vorhandenen Datenbank Jobs, welche zuvor als klassische DBMS_JOB angelegt waren, nun automatisch in neue Scheduler-Jobs „konvertiert“ wurden.

Dieses Verhalten der automatischen Konvertierung kannte ich bisher nur bei einem klassischen Upgrade der Datenbank und es wird auch erst seit Version 19c so gehandhabt.

Das nun auch automatisch neue Scheduler-Jobs bei einem Datapump Import angelegt wurden, überraschte mich erstmal, war aber bei näherer Betrachtung durchaus logisch.

Wie inzwischen allgemein bekannt sein sollte, ist das bereits in die Jahre gekommene DBMS_JOB Package zur Verwaltung von klassischen Datenbank Jobs bereits seit Version 12.2.0.1 „deprecated“, was in der Praxis bedeutet, dass es zwar noch existiert, jedoch in zukünftigen Versionen jederzeit „desupported“ bzw. auch entfernt werden könnte.

Oracle empfiehlt daher komplett auf den „Scheduler“ (DBMS_SCHEDULER) umzusteigen, was in jedem Fall sinnvoll ist, bietet dieser doch wesentlich mehr Funktionalität.

Mit der Version 19c geht Oracle noch einen Schritt weiter und baut eine automatische Konvertierung für klassischen Jobs ein. Das bedeutet, dass das alte DBMS_JOB API noch wie gewohnt verwendet werden kann, Oracle jedoch im Hintergrund automatisch aus jedem neuen DBMS_JOB in Wirklichkeit einen Scheduler Job erzeugt. Das Package DBMS_JOB dient dabei nur mehr als Wrapper um die Rückwärtskompatibilität zu gewährleisten.

Unter der Haube werkt somit nur mehr der Scheduler (das Mapping zwischen „alten“ und „neuen“ Jobs findet man übrigens in der neuen Dictionary Tabelle: SCHEDULER$_DBMSJOB_MAP).

Damit ist auch das Verhalten während meines Datapump Imports erklärt: Da hier die Jobs ebenfalls neu angelegt wurden, erhalte ich auch sofort die dazugehörenden Scheduler Jobs.

SQL> select job,schema_user,what from dba_jobs where job=844;

JOB SCHEMA_USER WHAT
------------- ----------- -------------------------------------
844 DWH dbms_mview.refresh('"DWH"."MVIEW1"');


SQL> select job_name,owner,job_action from dba_scheduler_jobs 
where job_name like 'DBMS_JOB$_844';

JOB_NAME OWNER JOB_ACTION
------------- ---------- -------------------------------------
DBMS_JOB$_844 DWH dbms_mview.refresh('"DWH"."MVIEW1"');

Das Mapping zwischen „alten“ und „neuen“ Jobs findet man übrigens in der neuen Dictionary Tabelle: SCHEDULER$_DBMSJOB_MAP):

SQL> select * from SCHEDULER$_DBMSJOB_MAP where dbms_job_number=844;

DBMS_JOB_NUMBER JOB_OWNER JOB_NAME
--------------- --------- --------------
844 DWH DBMS_JOB$_844

Allgemeine DBMS_JOB Verhaltensänderungen

Trotz Automatismus dürfen folgende Änderungen nicht unerwähnt bleiben:

Berechtigungen

Da es sich nun immer um Scheduler Jobs handelt (auch wenn diese mittels DBMS_JOB angelegt werden) und damit um Datenbankobjekte, muss der User zwingend die Berechtigung zum Anlegen dieser Objekte über das „CREATE JOB“ Privileg erhalten, um weiterhin Jobs über DBMS_JOB anzulegen und zu verwalten.

Transaktionsverhalten

Verwendet man in einer früheren Version als 19c DBMS_JOB zur Verwaltung der Jobs, muss immer explizit ein „commit“ verwendet werden, um die jeweilige Transaktion abzuschließen.

Ab Oracle 19c ist dies nun nicht mehr notwendig – der Scheduler Job wird implizit angelegt.

Trotzdem bleibt die Transaktionsfunktionalität bei der Verwendung von DBMS_JOB erhalten.

Stolperstein NLS-Einstellungen

Nachdem die Jobs während meines Datapump Imports automatisch in Scheduler Jobs konvertiert wurden, war hier für mich als DBA eigentlich nichts weiter zu tun.

Die Freude währte allerdings nur kurz, denn der Kunde machte mich umgehend auf ein seltsames Phänomen aufmerksam: Einige Spalten in seinen Materialized Views hätten plötzlich seltsame Werte bekommen!

Zahlen, die in VARCHAR2 Feldern als Strings gespeichert waren, hatten nun wie von Geisterhand 6 Nullen als Nachkommastelle bekommen.

Meine erste Reaktion war natürlich sofort das Konstrukt „Zahlen in Strings zu speichern“ in Frage zu stellen, allerdings hatte dieser Umstand leider wie so oft eine historische Berechtigung und mit dem eigentlichen Problem gar nichts zu tun.

Lediglich die Auswirkungen eines ganz anderen Problems kamen aber genau an dieser Stelle glücklicherweise an die Oberfläche: Die Jobs, welche den Refresh dieser Materialized Views durchführten, waren genau jene Jobs welche beim Import automatisch konvertiert wurden.

Analyse

Nach einer kurzen Analyse war klar: Bei der Neuanlage der ursprünglichen Jobs während des Imports wurden offensichtlich geänderte NLS-Settings verwendet!

Durch die besondere Konstellation von mehreren verschachtelten Views, MViews und DB-Links führten diese NLS-Settings (konkret die Einstellung des Parameters NLS_NUMERIC_CHARACTERS) dazu, dass einige Zahlen nun in den MViews falsch dargestellt wurden.

Damit war das Problem erkannt und nach einigen Tests schnell behoben.
Das NLS-Environment für einen bestehenden Scheduler-Job lässt sich übrigens auch im Nachhinein einfach ändern (man muss also nicht den kompletten Job neu erstellen):

begin
sys.dbms_scheduler.set_attribute('"USER1"."DBMS_JOB$_1185"','NLS_ENV',
'NLS_LANGUAGE=''GERMAN'' NLS_TERRITORY=''GERMANY'' NLS_CURRENCY=''€'
'NLS_ISO_CURRENCY=''GERMANY'' NLS_NUMERIC_CHARACTERS='',.'
' NLS_DATE_FORMAT=''DD.MM.RR'' NLS_DATE_LANGUAGE=''GERMAN'
' NLS_SORT=''GERMAN''');
end;
/

Fazit:

  • DBMS_JOBs werden ab Oracle 19c automatisch in Scheduler Jobs „konvertiert“ (sowohl beim Upgrade wie auch beim Import) – das alte DBMS_JOB Interface bleibt vorerst vollständig erhalten.
  • Besser vor einem Upgrade alle DBMS_JOBs überprüfen und kontrolliert durch DBMS_SCHEDULER Jobs ersetzen.
  • Besonders beim Import vorab die NLS-Settings von Jobs überprüfen.

Mein persönlicher Tipp an dieser Stelle:

Auch wenn gute Automatismen vorhanden sind, sollte man sich immer schon im Vorfeld eines geplanten Versionswechsels mit bekannten Verhaltensänderungen auseinandersetzen und diese auch bereits vorab gelöst haben.

Dann gibt es auch keine unliebsamen Überraschungen mit unbekannten „Side-Effects“.

Kill Blocking Job entfernt Blocking Sessions

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.

OAUTH2 Authentifizierung für ORDS REST Services

OAUTH2 Authentifizierung für ORDS REST Services

Oracle bietet über den ORDS (Oracle REST Data Services) die Möglichkeit REST Services in der Datenbank zu erstellen.

Um diese auch entsprechend zu schützen kann man den Zugriff darauf mittels einer OAUTH2 Authentifizierung einschränken. Die Authentifizierung und Autorisierung arbeiten mit Benutzern, Rollen und Berechtigungen (Privileges).

Der Aufbau ist dabei wie folgt:

Authentifizierung und Autorisierung

Abbildung 1: Rollen

Das bedeutet, ein Benutzer kann mehrere Rollen besitzen, welche ihm wiederum jeweils mehrere Berechtigungen zuweisen. Eine Berechtigung wiederum gilt für 1 oder mehrere Module (=Services) und darin eingebettete Ressourcen. Die Einschränkung auf Ressourcen wird allerdings nicht über eine Verknüpfung festgelegt, sondern einen Textfilter, welcher mit Regular Expressions arbeitet.

Die einzelnen Elemente können folgendermaßen angelegt werden:

Rolle

  • Per PL/SQL mit einem Aufruf der Prozedur create_role im Package ords.
  • Über das entsprechende REST Service Interface in APEX

Abbildung 1: Rollen

Berechtigung

  • Per PL/SQL mit einem Aufruf der Prozedur create_privilege im Package ords. Hier wird gibt es zwei Aufrufe, entweder wird nur 1 Rolle übergeben oder ein Array an Rollen.
  • Über das entsprechende REST Service Interface in APEX

Abbildung 2: Berechtigungen

Abbildung 3: Berechtigungen Detail

Berechtigungen Details

Zuweisungen der Berechtigung zum Modul werden mittels der Prozedur set_module_privilege im Package ords erstellt oder wie in Abbildung 3 zu sehen über das REST Service Interface in APEX.

Zuweisungen der Berechtigung zur jeweiligen Ressource werden mittels der Prozedur create_privilege_mapping im Package ords erstellt oder wie in Abbildung 3 zu sehen über das REST Service Interface in APEX.

 

ACHTUNG!!!

Es scheint hier einen Bug zu geben wenn ein Universal-Model verwendet wird – sprich „/*“.

Dieser wird dann nicht modulmäßig eingeschränkt und wirkt sich dann auch auf alle APEX Applikationen aus, welche dann potentiell nicht mehr aufgerufen werden können.

Benutzer

Benutzer können AUSSCHLIESSLICH per PL/SQL mittels der Prozedur create_client im Package oauth erzeugt werden!

Die Rolle muss dann ebenfalls per PL/SQL zugewiesen werden mittels der Funktion grant_client_role im Package oauth.

Die Tatsache, dass die Benutzer nicht über das Interface angelegt werden können, muss unbedingt berücksichtig werden.

Einsatz der angelegten Objekte

Um das Service nun zu nutzen muss eine Authentifizierung durchgeführt werden. Beim Anlegen des OAUTH Clients wird ein Benutzername übergeben. Mit diesem können dann die benötigten Benutzerdaten aus der Tabelle user_ords_clients geholt werden z.B. mit folgendem Statement:

SELECT client_id, client_secret FROM user_ords_clients WHERE name = 'NAME_DES_USERS';

Die client_id und das client_secret werden dann wiederum benötigt um den OAUTH Zugriffstoken zu erhalten. Dazu wird standardmäßig ein eigenes Webservice bereitgestellt, welches mit einer speziellen URL erreicht werden kann. Die URL setzt sich aus dem Basispfad zusammen und wird am Ende mit oauth/token ergänzt – z.B. https://www.myserver.at/ords/apex/oauth/token

Dieser URL werden client_id und client_secret als HTTP-Authentifizierung mitgegeben (User:Passwort), im Payload wird „grant_type=client_credentials“ mitgegeben. Als Antwort erhält man ein JSON mit folgenden Attributen:

  • access_token: der Token selbst
  • token_type: ist immer „bearer“ – muss mit angegeben werden mit dem Token
  • expires_in: Gültigkeitsdauer in Sekunden – standardmäßig 1 Stunde (dieser Wert kann nicht individuell je Service angepasst werden, nur global für alle Services dieser ORDS Instanz)

Beim Aufruf der eigentlichen Services welches durch die OAUTH Authentifizierung geschützt wird, wird dann im Header des Requests folgendes mitgegeben:

„Authorization: Bearer [OAUTH Token]“

OAUTH Token ist der vorhin mittels Webservice generierte Token. Falls der Token nicht mehr gilt (sprich die Gültigkeit abgelaufen ist oder der Token schlicht falsch ist) – gibt das Service einen HTTP 403 Forbidden Fehler zurück.

Die SQL WITH clause (oder Subquery Refactoring)

Die SQL WITH clause (oder Subquery Refactoring) wurde mit der Oracle 9i Release 2 Database eingeführt. Deren Benutzung ist seither Standard, da sie deutliche Vorteile bietet.

Mit der WITH clause wird eine temporäre Tabelle/View erstellt, auf die im Nachhinein zugegriffen werden kann.

Dadurch spart man sich bei komplexen SQL-Statments jede Menge an Code und macht ihn in der Regel auch lesbarer. Die temporäre Tabelle/View wird nur solang innerhalb eines SQL Statements verwendet, bis die Ausführung beendet ist.

Als Beispiel nehmen wir eine Employee und Department Tabelle.

Employees:

Departments:

 

Wir wollen nun alle Employees anzeigen, deren Standort sich in New York verbindet.

Standardmäßig benutzen wir ein Subselect da sich die Location in der Departments Tabelle befindet.

SELECT *
FROM emp e
WHERE e.DEPTNO in (SELECT d.DEPTNO from dept d where d.loc = 'NEW YORK');

Jetzt benutzen wir die WITH clause und erhalten das gleiche Ergebnis.

With w_dept_ny as
(SELECT /*+ materialize */ d.deptno
FROM dept d
WHERE d.loc = 'NEW YORK')
SELECT *
FROM emp e
join w_dept_ny d on e.deptno = d.deptno;

Seit Oracle 12c ist es möglich PL/SQL Functions und Procedures in eine WITH claus zu packen.

Dazu wird es noch einen gesonderten Blog Eintrag geben.

 

 

 

 

Business Breakfast 2020 auf Herbst verschoben

Aufgrund der anhaltenden Corona-Virus Krise haben wir uns entschlossen, unsere alljährlich im Frühjahr stattfindende Business Breakfast Veranstaltungreihe abzusagen.

Wir hoffen, dass sich die Lage bald entspannt und wir die Veranstaltungsreihe im Herbst nachholen können.

 

 

wir haben eine neue Adresse

Wir haben uns verpflanzt. Schon wieder.

Nachdem wir erst vor vier Jahren in unser doppelt so großes Büro gezogen sind,
ist es jetzt wieder so weit uns zu verdoppeln.

Wir haben uns daher erneut verpflanzt und schlagen im Ares Tower unsere Wurzeln,
um weiter wachsen zu können.

Unsere neue Adresse:

DBConcepts GmbH
Ares Tower, Donau-City Straße 11
1220 Wien

Wir freuen uns von Ihnen zu hören!