Über unsMediaKontaktImpressum
Dr. Kurt Franke 10. März 2015

Batchverarbeitungen in Oracle-Datenbanken: Möglichkeiten zur Prozesskontrolle

Datenbank-Batchverarbeitungen laufen unabhängig von aktuell bestehenden Verbindungen in der Datenbank im Hintergrund ab. Es ist sogar so, dass die Prozesskontrolle selbst in irgendeiner Form im Hintergrund läuft, wobei die von ihr asynchron angestarteten Batchjobs auch im Bezug auf die Prozesskontrolle im Hintergrund laufen. Zur Erzeugung der Sessions/Prozesse für die Batchjobs bietet sich in heutigen Zeiten die Verwendung von dbms_scheduler mit seinen vielfältigen Möglichkeiten an. Das ist jedoch nicht zwingend – jede Methodik, die das asynchrone Starten einer Session mit Vorgabe der darin auszuführenden Verarbeitung erlaubt, ist dafür geeignet.

Komplett innerhalb einer Datenbank laufende Batchjobs benötigen eine Möglichkeit, mit Fehler abgebrochene von noch laufenden Batchverarbeitungen zu unterscheiden. Erfolgreich beendete Verarbeitungen werden durch einen bei Beginn zugeordneten und am Ende abgeschlossenen Eintrag in einer Logtabelle erkannt, ebenso Verarbeitungen, die mit einem normalen Fehler abgebrochen sind und ihren Eintrag in der Logtabelle noch abschließen konnten – hier natürlich mit einem Fehlerstatus. Bei Auftreten von Oracle-Exceptions, die die zugehörige Datenbank-Session terminieren (z. B. ORA-04030, ORA-07445), ist dies jedoch nicht mehr möglich. Für solche Fälle wird ein Mechanismus zur Erkennung benötigt, ob für als laufend gekennzeichnete Verarbeitungseinträge die zugehörigen Prozesse/Sessions noch existieren. Im Folgenden werden 2 verschiedene Möglichkeiten für ein Handling zur Prozesskontrolle von Batchjobs beschrieben.

Die erste Variante verwendet die Views V$SESSION / V$PROCESS bzw. Sub-Views darauf, die auf die zur gehandelten Batch-Applikation gehörenden Sessions/Prozesse limitiert sind. In der Statustabelle für die Batchverarbeitungen sind geeignete Columns integriert, um directe Joins mit diesen V$-Views zu ermöglichen und somit eine Verknüpfung der Session-Information mit der Verarbeitungsinformation bereitzustellen. Die zweite Variante verwendet einen ganz anderen Ansatz, der davon ausgeht, dass ein Zugriff auf die Views V$SESSION / V$PROCESS bzw. Sub-Views nicht zur Verfügung steht. Man braucht dazu einen Object-Typ, dessen Eindeutigkeit bei der Erzeugung sichergestellt ist, der die Lebensdauer einer Session hat und der bei Session-Beendigung automatisch bereinigt wird. Einen solchen Object-Typ bietet die Verwendung von Locks, die über das dbms_lock-Package mit Duration = SESSION erzeugt werden. Zur sicheren Zuordnung eines Locks zu einer Verarbeitung muss für jede Verarbeitung inklusive ihrer klassifizierenden Parameter ein eindeutiger Lockname definiert werden, wodurch dann die Existenz/Nichtexistenz eines solchen Locks äquivalent zur Existenz/Nichtexistenz einer laufenden Verarbeitung ist.

Verwendung von V$SESSION etc.

Die Verwendung von einigen V$-Views ermöglicht eine umfassende Überwachung der Sessions / Prozesse einer Datenbank-Applikation. Es können dabei grundsätzlich alle existierenden Sessions einer Datenbank-Batchverarbeitung identifiziert werden. Voraussetzung dafür ist, dass Zugriffsrechte auf die benötigten V$-Views erteilt werden.

Wegen der Wiederverwendung von Session-ID’s (sid) durch den Datenbank-Kernel ist diese für sich alleine keine eindeutige Erkennungsgröße. Die serial#, die oft zusammen mit der sid benötigt wird, muss im Verlauf einer Session nicht konstant sein, sondern kann sich unter bestimmten Bedingungen ändern. Sie ist damit zusammen mit der sid nicht als Erkennungsgröße geeignet. Es werden Werte benötigt, die sich im Laufe einer Session nicht ändern. Dazu gehören die logon_time aus v$session und die spid aus v$process. In der spid ist entweder die Operatingsystem-Process-Id des Serverprozesses hinterlegt (DB unter Unix), oder die Thread-ID des einzigen Datenbank-Prozesses einer Instanz (DB unter Windows). Es werden also die Views V$SESSION und V$PROCESS benötigt, die über v$process.addr = v$session.paddr gejoined werden. Zur Einschränkung auf die jeweils eigene Session wird einfach noch V$MYSTAT über v$session.sid = v$mystat.sid AND v$mystat.rownum = 1 hinzugejoined.

Für eine verbesserte Übersicht kann auch noch das Package dbms_application_info zum Setzen des module-Wertes in V$SESSION eingesetzt werden. Als module-Wert wird ein die Application und die gerade laufende Verarbeitung bezeichnender Text verwendet. Der module-Wert kann dann als weitere Einschränkung bei der Auflistung der zur Applikation gehörigen Sessions / ProZesse verwendet werden.

Wenn eine existierende interaktive Session mehrfach hintereinander für verschiedene Verarbeitungen genutzt wird, wie es insbesondere bei Tests der Prozesskontrolle vorkommt, um den betreffenden Ablauf im Vordergrund zu haben, muss sichergestellt sein, dass ein solcher module-Wert am Ende einer Verarbeitung wieder auf den Vorgängerwert zurückgesetzt wird, um Probleme bei der Erkennung der zur Applikation gehörigen Sessions / Prozesse zu vermeiden. Es muss ein Exceptionhandler verwendet werden, der sicherstellt, dass das Wiederherstellen des module-Wertes auch im Fehlerfalle erfolgt. Insbesondere bietet die Verwendung dieses Verfahrens auch die Möglichkeit, eigene Views zu generieren, die Joins von V$SESSION und V$PROCESS mit applikationseigenen Status-Tabellen durchführen, um so auch eine applikationsspezifische Sicht auf die verarbeitenden Sessions / Prozesse bereitzustellen. Voraussetzung dafür ist, dass die für den Join benötigten Informationen (sid etc.) auch in den jeweiligen Status-Tabellen hinterlegt werden. Dies eröffnet den DBA’s und auch den Applikations-Administratoren besonders einfache Möglichkeiten zum Erhalt notwendiger Informationen.

Am Beispiel eines Ausschnitts aus einer integierten Abhängigkeits- und Job-Steuerung werden im Folgenden die Möglichkeiten skizziert, die eine Verwendung von V$SESSION etc. bei der Prozess-Kontrolle bereitstellt. Jeder Job wird dort mit allen seinen Attributen in der Tabelle FOLLOWUP_JOB_DESC beschrieben. Dies geschieht bei der Definition eines Jobs oder bei späteren Modifikationen. Bei der Initialisierung eines Job-Laufes wird unter anderem auch ein Runlog-Eintrag erzeugt, der zum Speichern spezifischer Attribute eines Job-Laufes dient, bevor die festgelegte Startmethode aufgerufen wird:


INSERT INTO followup_job_runlog
 (job_name,period_start_date,period_end_date,run_id,state,itime)
 VALUES(job_nam,period_start,period_end,job_run_id,job_init_state,sysdate)
;

Die ausführende Session erhält als Parameter den Job-Namen, sowie das Begin-Datum und Ende-Datum der zu verabeitenden Daten und die job_run_id und kann damit eindeutig den auszuführende Job-Lauf identifizieren. Sie trägt dann als eine ihrer ersten Aktionen die eigene SID und OS_PID ein:


UPDATE followup_job_runlog rl
 SET (sid,os_pid) = (SELECT s.sid, p.spid
                      FROM v$process p, v$session s
                      WHERE p.addr = s.paddr (+)
                        AND s.sid = (SELECT sid
                                      FROM v$mystat
                                      WHERE rownum = 1
                                    )
                    )
 WHERE rl.job_name = :job_nam
   AND rl.period_start_date = :period_start
   AND rl.run_id = :job_run_id
;

Es wird auch die Bezeichnung der Job-Steuerung zusammen mit dem ausgeführten Job via dbms_application_info als Module in V$SESSION eingetragen.


BEGIN
 dbms_application_info.read_module(saved_module_name,saved_action_name);
 appl_info_read := TRUE;
 dbms_application_info.set_module(FOLLOWUP: ' || jobnam, '');
END;
BEGIN
 --=====================================
 -- hier findet die Verarbeitung statt
 --=====================================
IF appl_info_read THEN
  dbms_application_info.set_module(saved_module_name);
 END IF;
EXCEPTION WHEN OTHERS THEN
 IF appl_info_read THEN
  dbms_application_info.set_module(saved_module_name);
 END IF;
END;

Ab diesem Zeitpunkt erscheint ein Job-Lauf in den Überwachungsviews. Von allen  Überwachungsviews der integrierten Abhängigkeits- und Job-Steuerung sind für die Betrachtung hier 2 interessant: followup_job_running und followup_job_broken. Diese dienen der externen Überwachung der Jobs – die tool-interne Überwachung benötigt dazu keine Views. Sie stellt über die direkt verwalteten Daten sicher, dass nach erfolgreicher Beendigung abhängige Jobs aus dem Job-Netz nach Erfüllung aller weiteren Abhängigkeiten gestartet werden, während bei einem Abbruch bei den direkt abhängigen Jobs die NICHT-Erfüllung dieser einen Abhängigkeit dazu führt, dass diese nicht gestartet werden. Die beiden Views sind folgendermaßen definiert:


CREATE OR REPLACE FORCE VIEW followup_job_running
AS
  SELECT r.job_name,
         j.job_type,
         j.period_type,
         j.run_user,
         j.start_methode,
         r.period_start_date,
         r.period_end_date,
         r.run_id,
         r.state,
         r.itime,
         r.stime,
         r.sid,
         r.os_pid,
         r.async_pid,
         r.dbms_job_number
   FROM followup_job_desc j,
        followup_job_runlog r
   WHERE j.job_name = r.job_name
     AND j.state = 'R'
     AND r.state = 'R'
     AND j.run_id = r.run_id
     AND (r.sid,r.os_pid,r.job_name) IN
           (SELECT s.sid,
                   p.spid,
                   substr(s.module, instr(s.module,': ') + 2)
             FROM v$process p, v$session s
             WHERE p.addr = s.paddr (+)
               AND s.sid = r.sid
           )
;

CREATE OR REPLACE FORCE VIEW followup_job_broken
AS
  SELECT j.job_name,
         j.job_type,
         j.period_type,
         j.run_user,
         j.start_methode,
         r.period_start_date,
         r.period_end_date,
         j.run_id,
         j.state,
         r.state runlog_state,
         r.finished,
         r.itime,
         r.stime,
         r.ftime,
         r.sid,
         r.os_pid,
         r.async_pid,
         r.dbms_job_number,
         r.ora_errno,
         r.ora_errstr
   FROM followup_job_desc j,
        followup_job_runlog r
   WHERE j.job_name = r.job_name (+)
     AND j.is_old = 'N'
     AND j.state != 'Q'
     AND j.run_id = r.run_id (+)
     AND r.itime (+) < sysdate - (5/(24*3600))
     AND (r.sid,r.os_pid,r.job_name) NOT IN
           (SELECT s.sid,
                   p.spid,
                   substr(s.module, instr(s.module,': ') + 2)
             FROM v$process p, v$session s
             WHERE p.addr = s.paddr (+)
               AND s.sid = r.sid
           )
;

Ein Job-Lauf erscheint dann zuerst in followup_job_running, sogar noch bevor die eigentliche Datenverarbeitung beginnt. Die Verarbeitungen müssen so erstellt werden, dass jede nicht abschließend handelbare Exception nach oben durchgereicht wird. Dort wird die Exception gehandelt und der Verarbeitungsstatus B (broken) zusammen mit Errorcode und Meldung in followup_job_runlog eingetragen sowie der Module-Inhalt in V$SESSION wieder auf den ursprünglichen Wert zurückgesetzt. Nach allen notwendigen Aufräumarbeiten wird dann die extra für die Verarbeitung bereitgestellte Session beendet.

Die Abhängigkeitsteuerung stellt sicher, dass sich Fehler einer Verarbeitung nicht durch das ganze nachfolgende Job-Netz ausbreiten, in dem das Anstarten der Nachfolge-Verarbeitungen unterbleibt. In einem solchen Falle ist ein Administrator gefragt, um das Problem zu identifizieren und zu beheben. Der erste Schritt dabei ist die Sichtung von followup_job_broken, wo jeder abgebrochene Job erscheint und auch gleich der Fehler, der letztendlich zum Abbruch geführt hat. Das ist eine einfach und schnell zugängliche Ausgangsbasis, um weitere Problem-Analysen auf Basis von Detail-Logs der Verarbeitung und/oder Oracle-Views oder gar der angelieferten Daten aus anderen System durchzuführen.

Grundsätzlich kann festgestellt werden, dass dieses Verfahren komplett ohne den Einsatz von Verarbeitungslocks für die Prozesskontrolle auskommt. Zur Sicherstellung, dass eine bestimmte Verarbeitung nicht gleichzeitig mehrfach läuft, wird ein anderer Mechanismus verwendet. Dies kann z. B. folgendermaßen durchgeführt werden: Vor Beginn der Überprüfung wird ein Exclusivlock auf die Statustabelle der Jobs angefordert. Nach Erhalt wird geprüft, ob es für die aktuell zu startende Verarbeitung bereits einen aktiven Batchjob gibt – wenn nicht, wird einer erzeugt. Nun wird der Exclusivlock wieder freigegegeben. Zusammen mit der Sicherstellung der Eindeutigkeit jedes Jobs über einen Primary Key auf den Job-Namen verhindert dies das mehrfache gleichzeitige Laufen jedes Jobs.

Eingrenzung der Views auf Applikations-Prozesse

Wenn eine Applikation nicht Zugriff auf Informationen über alle Datenbank-Sessions erhalten soll, bietet sich die Möglichkeit, in einem priviligierten Schema – z. B. SYSTEM – eingeschränkte Views anzulegen, in denen nur Sessions / Prozesse zu finden sind, die zu einer Applikation gehören. Dies kann beispielsweise über eine Einschränkung des Session-Usernames erfolgen, von dem man üblicherweise diejenigen kennt, die zu einer Datenbank-Applikation gehören. In diesem Falle werden Zugriffsrechte an die Applikations-Schemata nur für diese eingeschränkten Views erteilt.

Durch die Verwendung solcher eingeschränkter Views wird die Methodik der Prozesskontrolle nicht verändert, sondern nur die Information selbst auf erlaubte Prozesse limitiert. Ein derartiges Verfahren kann grundsätzlich für mehrere verschiedene Datenbank-Batch-Applikationen in einer einzigen Datenbank erfolgen.

Sobald jedoch die Verarbeitungen in mehreren Schemata zusammen mit einem solchen Mechanismus verwaltet werden sollen, werden dazu schon einige ANY-Privilegien erforderlich, die in Definer-Rights Stored Procedures benötigt werden und deshalb ohne Verwendung von Rollen direkt granted sein müssen. Das ist mehr, als selbst das Schema SYSTEM aufzuweisen hat. Bei solchen Rechten scheint dann auch das Select-Privileg auf einige wenige V$-Views als vernachlässigbar, und man wird dafür sinnvollerweise gleich den direkten Zugriff auf die V$-Views verwenden.

Verwendung von Locks mit Session-Duration und wohldefinierten Namen

Ein ganz anderer Ansatz ist die Verwendung von Locks mit der Lebensdauer SESSION, die über einen wohldefinierten Namen einer bestimmten Verarbeitung zugeordnet sind. Dafür wird nur das Ausführungsrecht auf das Package DBMS_LOCK benötigt, um solche Locks zu erzeugen und abfragen zu können. Dieses Verfahren eröffnet jedoch grundsätzlich keine direkte Möglichkeit, die zu einer Datenbank-Applikation gehörigen Sessions / Prozesse aufzulisten. Es ist ohne zusätzliche Rechte nur möglich, die Existenz eines solchen Locks zu prüfen und daraus auf die Existenz einer aktiven Verarbeitung zu schließen. Erst mit Zugriffs-Rechten auf DBA_LOCK und DBMS_LOCK_ALLOCATED kann die SID der betreffenden Session herausgefunden werden und weiter über V$SESSION und V$PROCESS zusätzliche Information, wobei man bei Erteilung von solchen Rechten dann auch alternativ die Rechte auf einige V$-View erteilen und mit dem oben beschriebenen Verfahren arbeiten kann.

Was dieses Verfahren jedoch bereitstellt, ist eine Möglichkeit zur Erkennung, ob eine Verarbeitung gerade aktiv ist, erfolgreich beendet wurde, mit Fehler abgebrochen ist oder gar die zugehörige Verarbeitungs-Session überhaupt nicht mehr existiert (nach ORA-00600 etc.). Damit können gleichzeitige Mehrfachläufe sicher verhindert werden. Über geeignete Abhängigkeits-Definitionen kann auch verhindert werden, dass gleichzeitig verschiedenartige Verarbeitungen laufen, die die gleichen Daten manipulieren. Somit kann grundsätzlich ein korrekter automatisierter Ablauf sichergestellt werden.

Programmatisch muss dazu sichergestellt werden, dass eine Verarbeitung nur dann startet, wenn sie zuvor den zugehörigen Lock (mit dem zugehörigen Locknamen) erhalten hat. Als abschließende Aktion gibt eine Verarbeitung den allokierten Lock frei, nachdem sie die applikatonseigene Statustabelle aktualisiert hat. Ein zeitgesteuerter kurzperiodischer Datenbank-Job prüft in der Statustabelle als gerade laufend gekennzeichnete Jobs auf die Existenz des zugehörigen Locks – wenn ein solcher nicht existiert, wird der betreffende Job-Eintrag in der Statustabelle auf einen speziellen Abbruch-Status aktualisiert, der als Kennzeichnung für eine Session ohne Abschlussmeldung dient.

Durch die zeitnahe Aktualisierung der Jobstatus in der Statustabelle kann diese Statustabelle selbst mit allen als RUNNING gekennzeichneten Einträgen als Liste der aktiven Verarbeitungen betrachtet werden und somit als Analogon einer Session-/Prozess-Liste dienen. Man muss sich jedoch dabei stets bewusst sein, dass diese Liste gerade bezüglich verschwundener Sessions/Prozesse nicht unbedingt aktuell ist. Insbesondere kann so der Eindruck entstehen, dass manche Verarbeitungen in einem Überlappungsbereich gleichzeitig laufen, obwohl eine der Verarbeitungen einen Abbruch mit Verschwinden der Session hatte und der Status nur noch nicht aktualisiert wurde. Vermeiden kann man dies, wenn jede zu startende Verarbeitung nach Erhalt des Verarbeitungslocks zunächst einen Exclusiv-Tablelock auf die Statustabelle anfordert, nach dessen Erhalt zunächst alle als RUNNING markierten Jobs via Locktest auf eine zugehörige Session überprüft und ggf. auf Verschwunden-Status setzt, bevor der eigene Statuseintrag auf RUNNING gesetzt und mit der Verarbeitung begonnen wird.

Das alleinige Verwenden von Verarbeitungslocks bietet jedoch noch keine Möglichkeit, den Einträgen in der Statustabelle die Sessions/Prozesse der Datenbank zuzuordnen. Auch wenn diese Möglichkeit für die Applikation selbst wegen nicht ausreichenden Rechten nicht besteht, kann über eine solche Möglichkeit dem DBA doch die Arbeit erleichtert werden, wenn z. B. eine bestimmte Verarbeitung abgebrochen werden muss. Eine solche Erleichterung bietet z. B. eine SID-Column in der Statustabelle, die beim Erzeugen eines neuen Eintrags über SYS_CONTEXT('USERENV','SID') befüllt wird. Man muss sich hier jedoch bewusst sein, dass diese SID wegen der Wiederverwendung nur für Verarbeitungen im Status RUNNING gültig ist. Auch die Verwendung von dbms_application_info.set_module() erleichtert die Zuordnung – allein oder zusammen mit einer SID in der Statustabelle.

Nicht zuletzt erlaubt auch die Abfrage der existierenden Verarbeitungslocks die Bestimmung der SID einer Verarbeitung, sofern ausreichende Privilegien zur Verfügung stehen. Dies ist über ein Statement wie das folgende möglich:


   SELECT la.name,
         l.session_id AS hold_by_session_id,
         l.blocking_others,
         la.expiration
   FROM dbms_lock_allocated la,
        dba_lock l
   WHERE la.expiration > sysdate
     AND la.name LIKE 'PROCESS?_CONTROL.%' ESCAPE '?'
     AND l.lock_type = 'PL/SQL User Lock'
     AND l.mode_held = 'Exclusive'
     AND ltrim(rtrim(to_char(la.lockid,'999999999999'))) = l.lock_id1
  ;

Insbesondere der Prefix des Locknamens und der Lockmode müssen dabei an die Applikation angepasst sein – wenn auch GUI-Zugriffe gehandelt werden, sind dafür Shared Locks für mehrere gleichzeitige GUI-Zugriffe gut geeignet. Da solche Locks nur existieren, solange die erzeugende Session existiert, kann ein Join mit V$SESSION allein mit der SID als Joinbedingung erfolgen und liefert damit dem DBA eine Verknüpfung von Session-Attributen mit den Verarbeitungen.

Zusammenfassung

Es wurden zwei unterschiedlich ansetzende Verfahren zur Prozesskontrolle vorgestellt. Beide ermöglichen einer Datenbank-Batch-Applikation die Überwachung und Steuerung von durchzuführenden Verarbeitungen, die von einer zentralen Steuer-Session bei Bedarf gestartet werden. Dabei muss eine solche zentrale Steuer-Session nicht kontinuierlich existieren, weil die Steuer-Information jederzeit abgefragt werden kann.

Die Variante mit direktem Zugriff auf V$SESSION / V$PROCESS bzw. auf Sub-Views mit Einschränkung auf Applikations-Sessions/-Prozesse erlaubt auch dem Applikations-Administrator ohne DBA-Rechte Zugriff auf Session-Attribute der aktiven Batch-Verarbeitungen. Auch für die Implementierung der Steuerung ergeben sich einfachere und mehr Möglichkeiten. Sie setzt jedoch die Erteilung von Zugriffsrechten auf diese V$-Views entweder an das/die Applikations-Schema/-ta oder an ein zentrales Schema zur Erstellung von Einschränkungsviews voraus.

Die Variante mit Einsatz von Verarbeitungslocks mit Session-Duration und wohldefinierten Namen benötigt wesentlich geringerere Rechte – Ausführungsrechte aus das Package DBMS_LOCK sind hier ausreichend. Dafür werden DBA-Rechte benötigt, um die Sessions mit ihren Attributen zu identifizieren, welche gerade aktive Verarbeitungen durchführen. Die Implementierung der Prozesskontrolle und Steuerung ist in dieser Variante etwas anspruchsvoller.

Autor

Dr. Kurt Franke

Schwerpunkt Oracle Datenbanken, vor allem performante Verarbeitung großer Datenmengen, Steuerung und Überwachung in Batchjobs. Nun bei Cellent Fincance Solutions in Stuttgart datenbankseitige Entwicklung im Bereich Financial Crime...
>> Weiterlesen
botMessage_toctoc_comments_9210