Job-Verwaltung mit dem Oracle Scheduler

Job-Verwaltung mit dem Oracle Datenbank Scheduler

von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG (ergänzt Juni 2013)

Datenbank-Jobs in der Datenbank zu verwalten, ist schon seit jeher mit dem Package DBMS_JOB in der Datenbank möglich. Mit Oracle Database 10g ist nun das Package DBMS_SCHEDULER als Ersatz für DBMS_JOB eingeführt worden. Wie DBMS_JOB steht DBMS_SCHEDULER ohne zusätzliche Installation in allen Editionen der Datenbank zur Verfügung. DBMS_SCHEDULER Package bietet allerdings mehr Funktionalität und wird im Gegensatz zum Package DBMS_JOB in jedem Release weiterentwickelt.
Folgende Beispiele listen wichtige Merkmale und Unterschiede zu DBMS_JOB auf:

Verwendbar ohne zusätzliche Installation oder Setzen von Initialisierungsparameter

Unterstützung verschiedener Jobtypen wie z.B. Stored Procedures, anonyme PL/SQL Blöcke oder andere externe ausführbare Programme

Einsetzbar mit PL/SQL Paket API oder graphisch mit Enterprise Manager Console

Mögliche Verkettung von Jobs

Flexibles Job-Scheduling über Zeit-, Event- und Job-Abhängigkeiten

Setzen von Job-Prioritäten durch Integration mit Datenbank Ressource Manager

Der folgende Tipp erklärt zu Beginn einige Grundlagen des Datenbank Schedulers (kurz Scheduler) und konzentriert sich im zweiten Teil auf weiterführende Einsatzmöglichkeiten.

Einige Grundlagen

Im ersten Beispiel wird ein Job, der mit DBMS_JOB implementiert ist, auf DBMS_SCHEDULER umgestellt. Folgendes Beispiel soll dazu als Basis dienen:

execute DBMS_JOB.SUBMIT(job=>:jobno, 
                        what=>'begin insert into log_tab values (sysdate, ''Start Job 1''); end;', 
                        next_date=>SYSDATE,
                        interval=>'SYSDATE+1');

Um DBMS_SCHEDULER zu nutzen, müssen zuerst einige Security-Einstellungen vorgenommen werden. Falls eigene Jobs erzeugt werden sollen, eignen sich die Systemprivilegien CREATE JOB und CREATE EXTERNAL JOB. Falls zusätzlich noch spezielle Scheduler Ressourcen wie Jobklassen und Windows administriert werden sollen, ist das Systemprivileg MANAGE SCHEDULER erforderlich. Die Rolle SCHEDULER_ADMIN enthält zur Vereinfachung alle diese Systemprivilegien.
Das obige Programm sieht mit DBMS_SCHEDULER dann folgendermassen aus:

execute DBMS_SCHEDULER.CREATE_JOB (job_name => 'job1', 
                              job_type => 'PLSQL_BLOCK', 
                              job_action => 'begin insert into log_tab values (sysdate, ''Start Job 1''); end;', 
                              start_date => SYSDATE, 
                              repeat_interval => 'FREQ = DAILY; INTERVAL = 1',
                              auto_drop => FALSE, -- Default ist TRUE
                              enabled => TRUE)    -- Default ist FALSE

Damit der Job automatisch startet und nicht separat eingeschaltet werden muss, wird der Parameter ENABLED auf TRUE gesetzt. Bei dem Wert FALSE ist zusätzlich ein Einschalten erforderlich, das mit folgendem Kommando erfolgen kann:

execute DBMS_SCHEDULER.ENABLE('job1');

Möchte man die Scheduler Jobs graphisch im Enterprise Manager generieren, findet sich ein Einstieg unter "Server=>Oracle Scheduler" in 11g bzw. unter "Administration=>Oracle Scheduler" in 10g. Der folgende Screenshot zeigt den Einstieg in der Enterprise Manager Control Console 11g:

Für eine größere Ansicht auf das Bild klicken.

Intern verwendet die Datenbank selbst diese Datenbank Scheduler Jobs. So sind alle Advisory Jobs wie z.B. Segment Space Advisor, SQL Tuning Advisor, die "Automated Maintenance Tasks" in 11g oder die "Manage Optimizer Statistics Tasks" in 10g mit DBMS_SCHEDULER implementiert.
Hinweis: Verwechseln Sie nicht die Datenbank Scheduler Jobs mit dem eigenen Enterprise Manager Job-System, das mit Enterprise Manager Mitteln selbstdefinierte oder vordefinierte Jobs wie Patching, Cloning und Backup mit Enterprise Manager Mitteln für die Targets zur Verfügung stellt. Diese sind unter dem "Job" Link auf fast jeder Enterprise Manager Seite zu finden (siehe folgenden Screenshot).

Für eine größere Ansicht auf das Bild klicken.

Zusätzlich können alle beteiligten Komponenten im Scheduler separat definiert werden. Die folgende Liste gibt einen Überblick über die Komponenten und gibt jeweils ein kurze Beschreibung:

Program bestimmt den Namen, die Aktion, den Programmtyp usw.

Schedule bestimmt wann und wie häufig ein Job ausgeführt wird.

Job kombiniert die Aktion und das Schedule.

Job class definiert gleiche Attribute, Service-Nutzung, Ressource-Allokation, Prioritäten usw. Der Default ist 'DEFAULT_JOB_CLASS'.

Window bzw. Window group definiert feste Zeitintervalle für die Ressourcenallokation mit dem Datenbank Resource Manager.

Das Erzeugen der einzelnen Komponenten wird jeweils durch eine entsprechende Prozedur wie CREATE_PROGRAM, CREATE_JOB_CLASS, CREATE_WINDOW oder CREATE_SCHEDULE unterstützt. Um zum Beispiel ein eigenes Schedule zu definieren, wird die Prozedur CREATE_SCHEDULE und eine spezielle Kalendersyntax genutzt.
So bedeutet zum Beispiel:

Jede Stunde: FREQ=HOURLY; INTERVAL=1

Alle 2 Wochen: FREQ=WEEKLY; INTERVAL=2

Alle 30 Tage: FREQ=DAILY; INTERVAL=30

Alle 5 Minuten: FREQ=MINUTELY; INTERVAL=5

Jede Sekunde: FREQ=SECONDLY

6:23 a.m. jeden Dienstag: FREQ=WEEKLY; BYDAY=TUE; BYHOUR=6; BYMINUTE=23

Weitere Beispiele zur Anwendung der Kalendersyntax findet sich im Handbuch Oracle Database Administrator's Guide 11g Release 1 (11.1). Folgendes Schedule startet dann am 10. Juli 2009 um 22:00 und wiederholt sich jede Woche um die gleiche Zeit.

execute DBMS_SCHEDULER.CREATE_SCHEDULE(repeat_interval => 'FREQ=WEEKLY;BYHOUR=22:00',
                       start_date => to_timestamp_tz('2009-07-10 Europe/Berlin', 'YYYY-MM-DD TZR'),
                       schedule_name => 'WOCHENSCHEDULE');

Das nächste Beispiel soll einen RMAN Job ausführen, um alle nicht mehr notwendigen Archive Log Dateien jede Woche um 22.00 zu löschen. Dazu wird das oben definiert Schedule WOCHENSCHEDULE verwendet. Im ersten Schritt wird nun das RMAN Programm definiert.

# Einfaches RMAN Shell-Skript
# Hinzufuegen von Environment Variablen und Pfad für das RMAN Kommando
#!/bin/bash
export ORACLE_SID=lp11g
export ORACLE_HOME=/orabase/product/11.1.0
/orabase/product/11.1.0/bin/rman<<EOI
connect target
crosscheck archivelog all;
delete noprompt archivelog all;
EOI

Hinweis: Das Hinzufügen der Environmentvariablen bzw. die genaue Pfadangabe ist beim Aufruf von externen Programmen notwendig, damit der Scheduler in der Lage ist das Programm zu finden und korrekt auszuführen. Im zweiten Schritt wird dann der Scheduler Job wie folgt aufgesetzt:

execute DBMS_SCHEDULER.CREATE_JOB(job_name => 'JOB_DELARCH',
                                  job_type => 'EXECUTABLE',
                                  job_action => '/export/home/oracle/us/del_arch.rm',
                                  schedule_name => 'WOCHENSCHEDULE',
                                  job_class => 'DEFAULT_JOB_CLASS',
                                  auto_drop => FALSE,
                                  enabled => TRUE)

Das Monitoring des Jobs erfolgt entweder im Enterprise Manager oder über die Data Dictionary Views wie z.B. DBA_SCHEDULER_JOBS für allgemeine Job-Informationen, DBA_SCHEDULER_SCHEDULES für die Schedules oder DBA_SCHEDULER_JOB_RUN_DETAILS für Informationen und Fehler der einzelnen Job-Ausführungen. Folgendes Beispiel zeigt Informationen zum RMAN Job JOB_DELARCH, der mit abgewandeltem Schedule alle 5 Minuten startet.

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='dd.mm.yyyy hh24:mi';
SELECT last_start_date, next_run_date, state, job_type, job_action 
FROM dba_scheduler_jobs WHERE job_name='JOB_DELARCH'

LAST_START_DATE  NEXT_RUN_DATE    STATE           JOB_TYPE
---------------- ---------------- --------------- ----------------
JOB_ACTION
--------------------------------------------------------------------------------
14.07.2009 11:10 14.07.2009 11:15 SCHEDULED       EXECUTABLE
/export/home/oracle/us/del_arch.rm

Um fehlerhaften Jobs auf die Spur zu kommen, gibt eine weitere Abfrage auf DBA_SCHEDULER_JOB_RUN_DETAILS hilfreiche Auskünfte. Ohne korrektes Setzen des RMAN Pfad beziehungsweise der Environment Variablen im RMAN Skript werden folgende Fehler generiert.

SELECT actual_start_date,  additional_info 
FROM dba_scheduler_job_run_details 
WHERE job_name='JOB_DELARCH' AND status='FAILED' ORDER BY 1 desc

ACTUAL_START_DAT
----------------
ADDITIONAL_INFO
------------------------------------------------------------------------------------
13.07.2009 11:35
ORA-27369: job of type EXECUTABLE failed with exit code: Network is down
STANDARD_ERROR="/export/home/oracle/us/del_arch.rm: line 5: rman: command not found"

10.07.2009 15:40
ORA-27369: job of type EXECUTABLE failed with exit code: 255
STANDARD_ERROR="execve: Exec format error"

Komplexere Anwendungen

Es ist auch möglich, komplexere Anwendungen mit dem Scheduler Jobsystem zu entwickeln. So kann zum Beispiel das Schedule wie oben schon erwähnt auch über Eventabhängigkeit wie "File Arrival" gesteuert werden. Benachrichtigungen nach dem Eintritt eines bestimmten Jobstatus sind ebenso möglich. Ab Oracle 10g Release 2 können auch Jobabhängigkeiten, die sogenannten Jobverkettungen, und Jobpriorisierungen vorgenommen werden. Da die Fülle der Möglichkeiten den Rahmen des Tipps sprengen würde, konzentriert sich dieser Tipp auf Jobverkettungen und die Möglichkeiten der Benachrichtigung.

Jobs in Abhängigkeiten von vorangegangen Jobausführungen zu entwickeln, basiert generell auf den Prozeduren DEFINE_CHAIN, DEFINE_STEP und DEFINE_STEP_RULE. Zuerst wird eine Chain, danach werden die einzelnen beteiligten Programme und im letzten Schritt die Regeln, nach denen die Programme ausgeführt werden sollen, definiert.
Folgendes Beipiel zeigt eine einfache Jobverkettung: Es wird mit Programm A gestartet. Falls Programm A erfolgreich ist, startet Programm B, ansonsten startet Programm C.

execute DBMS_SCHEDULER.CREATE_CHAIN(chain_name => 'my_chain'); 
execute DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain','step1','A');
execute DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain','step2','B');
execute DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain','step3','C');

execute DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain','TRUE','start step1'); 
execute DBMS_SCHEDULER.DEFINE_CHAIN_RULE( 'my_chain', 'step1 SUCCEEDED', 'start step2'); 
execute DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( 'my_chain', 'step1 COMPLETED AND step1 NOT SUCCEEDED', 'start step3'); 
execute DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( 'my_chain', 'step2 COMPLETED OR step3 COMPLETED', 'END');

Dies ist nur ein einfaches Beispiel einer Jobverkettung. Als Regeln können im zweiten Argument von DEFINE_CHAIN_RULE zum Beispiel auch Fehlercodes abgefragt werden. Zusätzlich können die einzelnen Schritte auch Eigenschaften wie 'PAUSE', 'RESTART_ON_RECOVERY' bzw. 'SKIP' erhalten, um den Ablauf der Chain noch weiter zu beeinflussen. Im Enterprise Manager wird das Ergebnis dann unter anderem als Topology sichtbar.

Für eine größere Ansicht auf das Bild klicken.

Möchte man Event-basierendes Scheduling implementieren z.B. ein "File Arrival" oder eine Benachrichtigung nach Eintritt eines bestimmten Jobstatus, muss man dies nicht selbst programmieren, sondern kann entsprechende PL/SQL Programmpakete auf OTN im Bereich Scheduler nutzen. Im folgenden Beispiel wird damit eine Jobbenachrichtigung implementiert. Dazu laden wir uns die nötige zip Datei bestehend aus 3 Dateien (README und 2 SQL Skripte) von OTN und führen das SQL Skript job_notification.sql als SYS aus. Da intern zur Implementierung das Paket UTL_SMTP verwendet wird, muss der Aufruf den "outgoing mailserver" und den "port" enthalten.

start job_notification.sql <outgoing mailserver> <port>

Nun stehen die zwei Prozeduren ADD_JOB_EMAIL_NOTIFICATION bzw. REMOVE_JOB_EMAIL_NOTIFCATION zur Anwendung zur Verfügung.

PROCEDURE add_job_email_notification
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB_NAME                       VARCHAR2                IN
 RECIPIENT_ADDRESS              VARCHAR2                IN
 EVENTS                         VARCHAR2                IN     DEFAULT
 SENDER_ADDRESS                 VARCHAR2                IN     DEFAULT
 SUBJECT_PREFIX                 VARCHAR2                IN     DEFAULT
 EMAIL_SERVER_HOST              VARCHAR2                IN     DEFAULT
 EMAIL_SERVER_PORT              BINARY_INTEGER          IN     DEFAULT

Mögliche Events, die verwendet werden können, sind dabei: 'JOB_STARTED','JOB_SUCCEEDED', 'JOB_FAILED', 'JOB_BROKEN', 'JOB_COMPLETED','JOB_STOPPED', 'JOB_SCH_LIM_REACHED', 'JOB_DISABLED','JOB_CHAIN_STALLED', 'JOB_OVER_MAX_DUR'. Eine einfache Anwendung für den Fall, dass der Job JOB_DELARCH nicht erfolgreich ist, sieht dann folgendermassen aus:

execute add_job_email_notification('JOB_DELARCH','ulrike.schwinn@oracle.com','JOB_FAILED')

Konsultiert man die Data Dictionary View DBA_SCHEDULER_JOBS, ist zusätzlich ein neuer Job JOB_DELARC_EMAILER erzeugt worden:

SELECT job_name, next_run_date, enabled, state, comments
FROM user_scheduler_jobs WHERE job_name LIKE '%ARC%'

JOB_NAME                       NEXT_RUN_DATE    ENABL STATE
------------------------------ ---------------- ----- ---------------
COMMENTS
--------------------------------------------------------------------------------
JOB_DELARCH                    14.07.2009 15:10 TRUE  SCHEDULED


JOB_DELARC_EMAILER                              TRUE  SCHEDULED
Auto-generated job to send email alerts for job "SYS"."JOB_DELARCH"

Die Benachrichtigung bei fehlenden Environment Variablen im Skript erfolgt dann prompt mit folgendem Inhalt:

Job: "SYS"."JOB_DELARCH"
Event: JOB_FAILED
Date: 14-JUL-09 03.15.02 PM EUROPE/BERLIN
Log ID: 13503
Error code: 27369
Error message: 
ORA-27369: job of type EXECUTABLE failed with exit code: Not owner

Möchte man die Emailbenachrichtigungen löschen, die zu diesem Job gehören, erreicht man dies über folgende Ausführung:

execute remove_job_email_notification('JOB_DELARCH')

Ebenso einfach lassen sich Jobs erzeugen, die "Ausschau halten", ob eine bestimmte Datei in ein Directory kopiert bzw. dort angekommen ist - das sogenannte "File Watch" oder "File Arrival" Programm. Dazu werden wie im Fall der Benachrichtigung Dateien von OTN kopiert und installiert.

Alle diese Vorgänge lassen sich auch im Enterprise Manager durchführen oder monitoren. Der Benachrichtigungsjob zum Beispiel wird im Enterprise Manager dann folgendermassen dargestellt:

Für eine größere Ansicht auf das Bild klicken.

Weitere Funktionen wie Jobpriorisierungen mit dem Resource Manager oder Erweiterungen im nächsten Datenbankrelease werden in einer der kommenden Ausgaben thematisiert.

Zurück zur Community-Seite