Erste Version 08/2018 - 10g, 11g, 12c, 18c, 19c

Der Oracle Jobs Scheduler in der Datenbank ist sehr mächtig und kann sehr umfangreich konfiguriert werden.

Mit dem Scheduler lassen sich komplexe Job Ketten aufbauen und komplexe Zeitpläne realisieren, das Filesystem überwachen etc.

Leider ist daher eine einfache Definition eines Standard Job im Umkehrschluss oft etwas kompliziert, da viele Parameter definiert werden können und/oder müssen.

Verstärkend kommt hinzu, das einzelne Elemente bei der Definition eines Jobs wiederum optional sind und mehrere Weg damit zum gleichen Ziel führen.

Siehe auch:

• PDF ⇒ DOAG 2021 Datenbank mit Exaday - Job Steuerung in der Datenbank mit dem Oracle Scheduler - Montag 17.Mai 2021 14:00 bis 14:40 Online
• PDF ⇒ APEX Connect 2021 - APEX 20.2 Automations Feature und der Oracle Scheduler im Einsatz - Mittwoch, 5. Mai 2021 , 14:00 - 14:40 Online

Übersicht über die Abhängigkeiten bei einer Definition eines Jobs:

Die einzelnen Elemente eines Jobs:

• Job
  • Definiert den eigentlichen Job
  • Anlegen mit „DBMS_SCHEDULER.create_job“
  • Gehört einen Anwender/Schema der Datenbank
  • Kann die weiteren Elemente des Schedulers verwenden oder die Eigenschaften der Elemente werden direkt im Job hinterlegt
  • Besitzt immer ein Job Klasse, die Resource Gruppe und den DB Service definiert
  • Eimal Jobs löschen sich selbst wenn „Auto Drop“ = true
  • Ein Job kann eine Priority besitzen ( 1 bis 5 )
• Program
  • Definiert WAS in einem Job ausgeführt werden soll
  • Dieses „Program“ kann von beliebig vielen Jobs verwendet werden
  • Anlegen mit „DBMS_SCHEDULER.create_program“
  • Gehört einen Anwender/Schema der Datenbank
  • Kann ein Externes Programm, ein PL/SQL Block oder eine Stored Procdure sein
  • Programm Argumente
    • Für diesen Aufruf können Argumente optional definiert werden
    • Anlegen mit „DBMS_SCHEDULER.define_program_argument“
• Chain
  • Liste von Program oder Scheduler vom Type Event die nacheinander abgearbeitet werden
  • Diese „Chain“ kann von beliebig vielen Jobs verwendet werden
  • Können wiederum verschachtelt werden um komplexe Ketten abzubilden
  • Gehört einen Anwender/Schema der Datenbank
• Job Class
  • Jeder Job besitze immer eine Job Klasse - meist den Default „DEFAULT_JOB_CLASS“
  • Definiert die für den laufenden Job verwendete Resource Consumer Group
    • Ist eine Resource Consumer Group UND ein Service definiert, wird die Resource Consumer Group über diese Einstellung auf der Job Class festgelegt und NICHT mehr über dem Mapping auf dem Service verändert!
  • Definiert den verwendeten DB Service - Ist dieser Service down oder nicht vorhanden wird KEIN Fehler geworfen ⇒ der Job mit der Klasse läuft einfach nicht!
  • Definiert den Log Level
• Schedule
  • definiert die zeitliche Abarbeitung/Ausführung - Dieser Plan kann von beliebig vielen Jobs verwendet werden
  • Anlegen mit „DBMS_SCHEDULER.create_program“
  • Gehört einen Anwender/Schema der Datenbank
  • Dieser „Schedule“ kann von beliebig vielen Jobs verwendet werden
  • Zeitzone Angabe von erheblicher Bedeutung
  • Verschiedene Typen von Zeitplänen
    • Standard - Startzeit wählen, Intervall einstellen , bei Bedarf Endzeit einstellen
    • Event - AQ Event abholen und dann erst starten
    • Calender - Calender definieren
• Window
  • Ein definiertes Zeitfenster, ist das Ende des Zeitfenster erreicht, kann je nach Bedarf der Job abgebrochen werden
  • Anlegen mit „DBMS_SCHEDULER.create_window“
  • Für ein Fenster kann ein Ressource Plan gewählt werden
  • Wird während eines offenen Zeitfensters die DB gestoppt, kann es passieren das diese Fenster nicht mehr geschlossen wird und damit keine Job mit diesem Fenster mehr startet!
  • Nur ein Window kann zur gleiche Zeit in der DB aktiv sein, ⇒ setzt den aktive Resourcen Plan für den Resource Manager!
    • Ist das Fenster aktiv wird der globale Resource Plan für die gesamte DB auf den Plan für diese Fenster gesetzt und der init.ora Parameter „resource_manager_plan“ wird vom Fenster gesetzt (zum Beispiel auf „SCHEDULER[0x196257]:GPI_LOW_LOAD“)
  • Über den dann aktiven Ressource Plan und der Consumer Group + Service Angabe in der Job Class wird der Job auf das Fenster gebunden
• Window Group
  • Organisationsgruppe für alle Windows
  • Eine Gruppe kann ein- und ausgeschaltet werden
• Global Attributes
  • Globale Attribute für die gesamte Job Umgebung wie die aktuelle Zeitzone

• DEFAULT_TIMEZONE - Zeitzone für den Job
• EMAIL_SENDER
• EMAIL_SERVER
• EMAIL_SERVER_CREDENTIAL
• EMAIL_SERVER_ENCRYPTION
• EVENT_EXPIRY_TIME
• FILE_WATCHER_COUNT
• LAST_OBSERVED_EVENT
• LOG_HISTORY
• MAX_JOB_SLAVE_PROCESSES

Zeitzone überwachen:

SELECT DBMS_SCHEDULER.STIME FROM DUAL;
 
STIME
----------------------------------------
10.12.14 19:28:51,178000000 +01:00

Zeitzone setzen:

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute(
     attribute => 'default_timezone'
   , VALUE     => 'EUROPE/BERLIN');
END;
/
 
SELECT DBMS_SCHEDULER.STIME FROM DUAL;
 
STIME
------------------------------------------------------
10.12.14 19:31:04,039000000 EUROPE/BERLIN

Für das Überwachen kann dieses Script hilfreich sein: http://orapowershell.codeplex.com/SourceControl/latest#sql/jobs_sheduler.sql

Übersicht über die DBA Views bgzl. dem DBMS Scheduler:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   'DEFAULT_JOB_CLASS', 'logging_level', DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
END;

Global:

BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');
END;
/

Auf einer Job Klasse:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('DEFAULT_JOB_CLASS','log_history','130');
END;

Alles:

BEGIN
DBMS_SCHEDULER.PURGE_LOG();
END;
/

Nur alles was älter ist als 10 Tage und nur vom JOB LOG:

EXEC DBMS_SCHEDULER.PURGE_LOG(log_history => 10, which_log => 'JOB_LOG');

Fein Granuar:

EXEC DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'CLEAN_SQL_ERROR_LOG_TABLE, DEFAULT_JOB_CLASS');                      

Einfachste Methode um einen Scheduler Job anzulegen:

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => 'CLEAN_SQL_ERROR_LOG_TABLE'
    , job_type        => 'PLSQL_BLOCK'
    , job_action      => 'BEGIN system.deleteOraErrorTrigTab(15); END;'
    , start_date      => SYSTIMESTAMP
    , repeat_interval => 'freq=daily; byhour=13; byminute=0'
    , end_date        => NULL
    , enabled         => TRUE
    , comments        => 'Job to clean all lean Error Log older then xx days');
END;
/

Ab 11g R1 kann ein Job „Lightweight“ angelegt werden, der Vorteil ist das weniger im Data Dictionary an Objekten angelegt werden muss.

Das erleichert das Anlegen, wenn viele einmalige Jobs angelegt werden müssen (Parameter job_style ⇒ 'LIGHTWEIGHT' !).

Allerdings muss dazu zuvor ein „Program“ definiert werden.

BEGIN
  DBMS_SCHEDULER.create_program (
      program_name        => 'CLEAN_SQL_ERROR_LOG_TABLE_PROG'
    , program_type        => 'STORED_PROCEDURE'
    , program_action      => 'system.deleteOraErrorTrigTab'
    , number_of_arguments => 1
    , enabled             => FALSE
    , comments            => 'Prog to clean Error Log older then xx days');
END;
/
 
BEGIN
  DBMS_SCHEDULER.define_program_argument (
      program_name      => 'CLEAN_SQL_ERROR_LOG_TABLE_PROG'
    , argument_name     => 'p_keepdays'
    , argument_position => 1
    , argument_type     => 'NUMBER'
    , default_value     => '15');
END;
/
BEGIN
  DBMS_SCHEDULER.enable (name => 'CLEAN_SQL_ERROR_LOG_TABLE_PROG');
END;
/
 
BEGIN
  DBMS_SCHEDULER.create_job (
     job_name         => 'CLEAN_SQL_ERRTABLE_LIGHT'
    , program_name    => 'CLEAN_SQL_ERROR_LOG_TABLE_PROG'
            , job_style       => 'LIGHTWEIGHT'
    , comments        =>  'Job to clean all lean Error Log older then xx days'
            , enabled         => true);
END;
/
 
-- nur im Job Log steht ein Eintrag!
 
select * 
  from dba_scheduler_job_log
where job_name = 'CLEAN_SQL_ERRTABLE_LIGHT'
;

-- programm von vorherigen Beispiel Lightwight Job wird weiterverwendet
 
select *  
  from dba_scheduler_programs 
 where program_name = 'CLEAN_SQL_ERROR_LOG_TABLE_PROG'
/ 
 
 
-------------------------------------------------------------------------
-- Create Oracle Scheduler Time Plan
BEGIN
  DBMS_SCHEDULER.create_schedule (
     schedule_name   => 'DBA_TIMEPLAN'
    , start_date      => SYSTIMESTAMP
    , repeat_interval => 'freq=daily; byhour=13; byminute=0'
    , end_date        => NULL
    , comments        => 'Job time plan for DB Maintenance');
END;
/
 
select * 
  from dba_scheduler_schedules 
 where schedule_name = 'DBA_TIMEPLAN'
/ 
 
 
-------------------------------------------------------------------------
-- Create Scheduler Job
BEGIN
  DBMS_SCHEDULER.create_job (
     job_name         => 'CLEAN_SQL_ERROR_LOG_TABLE'
    , program_name    => 'CLEAN_SQL_ERROR_LOG_TABLE_PROG'
    , schedule_name   => 'DBA_TIMEPLAN'   
    , comments        =>  'Job to clean all lean Error Log older then xx days'
    , enabled         => false);
 
  DBMS_SCHEDULER.set_job_argument_value( job_name => 'CLEAN_SQL_ERROR_LOG_TABLE'
	                               , argument_name => 'p_keepdays'												
                                       , argument_value => '10'
	);         
 
 
  DBMS_SCHEDULER.enable (name => 'CLEAN_SQL_ERROR_LOG_TABLE');
 
END;
/ 
 
select * from dba_scheduler_jobs 
where job_name = 'CLEAN_SQL_ERROR_LOG_TABLE'
/

Alles wieder entfernen:

begin
  DBMS_SCHEDULER.drop_job      (job_name       => 'CLEAN_SQL_ERROR_LOG_TABLE');
  DBMS_SCHEDULER.drop_schedule (schedule_name  => 'DBA_TIMEPLAN');
  DBMS_SCHEDULER.drop_program  (program_name   => 'CLEAN_SQL_ERROR_LOG_TABLE_PROG');
end;
/  

• Einen Service im Cluster anlegen der NUR auf der gewünschten Instance läuft
• Eine Job Klasse anlegen die diesen Service verwendet
• Dem Job diese Job Klasse zuordnen

Wenn „Instance Stickiness“ auf „TRUE“ dann versucht die DB den Job auf der Instance wieder zu starten, auf der zu letzt der Job lief, ABER nur wenn diese Instance auch die Instance mit dem aktuelle geringsten Last ist! D.h. es kann sich in einem System mit stark wechselnder Last nicht darauf verlassen werden das der Job wirklich jedesmal auf der selben Instance startet!

# stetzen
begin  
  dbms_scheduler.set_attribute( name => 'GPI.DO_MY_JOB' 
                               , attribute=>'INSTANCE_ID'
                               , value=> 3
  );  
end;  
/ 
 
#prüfen mit:
 
set serveroutput on
 
declare
 v_out varchar2(255);
begin
  dbms_scheduler.get_attribute( name     => 'GPI.DO_MY_JOB' 
                              , attribute=>'INSTANCE_ID'
                              , value=> v_out
   );  
 
  dbms_output.put_line('-- Info   INSTANCE_ID Attribute::'||v_out);
end; 
/ 
 
#wieder auf null stetzen:
 
begin
  dbms_scheduler.set_attribute_null ( name       => 'GPI.DO_MY_JOB' 
                                   ,attribute  => 'INSTANCE_ID'
  );
end;
/

Bei DBMS_SCHEDULER.create_schedule kann über ein Zeitintervall angegeben werden.

Die Syntax ist recht komplex ⇒http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72273 ( nach repeat_interval suchen), für 12c siehe https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/scheduling-jobs-with-oracle-scheduler.html#GUID-10B1E444-8330-4EC9-85F8-9428D749F7D5

Beispiele:

Der Calender Ausdruck kann mit DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING getestet werden.

DECLARE	
	v_next_run_date TIMESTAMP;
	v_start_date    TIMESTAMP:=systimestamp;  
        v_return_date_after TIMESTAMP
BEGIN
  FOR i IN 1 ..10
  loop
  DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(  
		  calendar_string   => 'FREQ=MINUTELY; INTERVAL=15'
		, start_date        => v_start_date
		, return_date_after => v_return_date_after 
		, next_run_date     => v_next_run_date);
 
	DBMS_OUTPUT.PUT_LINE('-- Info actual date :: '||to_char(v_start_date,'dd.mm.yyyy hh24:mi')||
	                     '   --> next_run_date:: '||to_char(v_next_run_date,'dd.mm.yyyy hh24:mi')
	);
         v_return_date_after := v_next_run_date;
  END loop;
END;
/
 
-- Info actual date :: 16.01.2015 19:19   --> next_run_date:: 16.01.2015 19:34
 
-- als Funktion
 
CREATE OR REPLACE FUNCTION getNextRunDate(p_calendar_string      varchar2
                                         ,p_start_date           TIMESTAMP WITH TIME ZONE DEFAULT systimestamp
                                         ,p_return_date_after    TIMESTAMP WITH TIME ZONE DEFAULT systimestamp)
   RETURN DATE
IS
   v_next_run_date   TIMESTAMP WITH TIME ZONE;
BEGIN
   dbms_scheduler.EVALUATE_CALENDAR_STRING(calendar_string     => p_calendar_string
                                          ,start_date          => p_start_date
                                          ,return_date_after   => p_return_date_after
                                          ,next_run_date       => v_next_run_date);
 
   RETURN v_next_run_date;
END;
/
 
-- abfragen über alle Jobs wann die Jobs das nächste mal und das übernächste mal laufen würden:
 
SELECT js.job_name
      , JS.REPEAT_INTERVAL
      , js.next_run_date
      , getNextRunDate(JS.REPEAT_INTERVAL,js.next_run_date,js.next_run_date) AS over_next_run_date
      , js.state||' - '||js.job_action  AS job_info     
    FROM dba_scheduler_jobs js
       , dba_objects o
   WHERE     js.owner = o.owner(+)
         AND js.job_name = o.OBJECT_NAME(+)
         AND js.owner != 'SYS'
/         

Nachträglich ändern:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
     name       => 'CHECK_LONG_RUNNING_TIMEPLAN'
   , attribute  => 'repeat_interval'
   , VALUE      =>  'FREQ=MINUTELY; INTERVAL=15');
END;
/
 
 
SELECT * 
  FROM dba_scheduler_schedules 
 WHERE schedule_name = 'DBA_TIMEPLAN'
/ 

Ein Scheduler Window legt den aktiven Resource Plan für den Zeitraum, in dem das Fenster „offen“ ist, fest.

D.h. ein per init.ora definiert globaler Resourceplan wird von diesem Scheduler Window überschrieben!

Vorteil:

• Damit lassen sich mehrere Resouce Pläne zeitlich organisieren

Nachteil:

• Der eigentlich gültige Plan kann überschrieben werden, ohne das man sich dessen so einfach bewust ist!

siehe auch ⇒ Den Oracle Resource Manager ab 11g verwenden

Über den Ressource Plan im Windows und der Consumer Group + Service Angabe in der Job Class wird dann der Job auf das Window gebunden und entsprechend in der Gruppe des Resource Plans priorisiert.

Übersicht:

Siehe Statement dazu im Orginal Doku ⇒ Job Classes

Das der Scheduler den Plan gesetzt hat, kann am init.ora Parameter „resource_manager_plan“ erkannt werden, hier steht dann ein „SCHEDULER[0x196257]:GPI_LOW_LOAD“ im aktuellen Wert!

Die Bedeutung des 0x196257 ist leider nuklar, gibt kein DB Objekt mit der ID, und die ID in den Log Tabellen ist es auch nicht. Am suchen…

Übersicht über die beteiligten Views:

Zu welchen Plan ist ein Job Fenster zugeordnet:

COLUMN job_class_name    format a25
COLUMN job_class_service format a25
COLUMN resource_plan     format a28
COLUMN consumer_group    format a25
COLUMN job_window        format a20
 
 
SELECT jc.job_class_name
     , jc.service AS job_class_service 
     , cg.consumer_group
     , pd.plan     AS resource_plan
     , sw.window_name AS job_window
  FROM dba_scheduler_job_classes jc  
      , dba_rsrc_consumer_groups cg
      , dba_rsrc_plan_directives pd
      , dba_scheduler_windows sw
      , DBA_RSRC_GROUP_MAPPINGS gm
  WHERE jc.resource_consumer_group = cg.consumer_group                    
    AND cg.consumer_group=pd.group_or_subplan (+) 
    AND sw.resource_plan(+)=pd.group_or_subplan 
 ORDER BY 1
/

siehe auch Datenbank Auto Statistik Job überwachen - Fehlerursache bei veralteten Statistiken

Problem:

ORA-27486: Nicht ausreichende Berechtigungen
ORA-06512: IN "SYS.DBMS_ISCHED", Zeile 168
ORA-06512: IN "SYS.DBMS_SCHEDULER", Zeile 288
ORA-06512: IN Zeile 2

Lösung:

GRANT CREATE job TO <SCHEMA>;

Eine Job Klasse muss den entsprechenden User „gegranted“ werden!

sys.dbms_scheduler.create_job( 
...
job_class => 'MY_ADMIN_CLASS'
...
 
...
 
ORA-27476: "MY_ADMIN_CLASS" does NOT exist CHECK the log/trace file FOR more Details
 
 
GRANT EXECUTE  ON SYS.MY_ADMIN_CLASS TO GPI;

Über den <Schema Owner>.<Job Name> kann mit der Routine dbms_scheduler.drop_job ein Job entfernt werden, mit force⇒true wird der Job sofort gestoppt und entfernt, falls false erst nach dem nächsten Lauf.

Beispiel:

BEGIN
 dbms_scheduler.drop_job (job_name => 'BGJOBUSER.SYMON', force=>TRUE);
END;
/

Evlt. sind es einfach zuviele Einträge im Log, siehe ⇒ How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG (Doc ID 443364.1)

Hierzu auch folgende Anmerkungen beachten ⇒ DBMS_SCHEDULER Job Log Lösch-Verhalten - Löschen optimieren

Hier ein SQL um alle Jobs mit Ihren Laufzeiten und den letzten Fehler aus den Log zu überwachen:

WITH jobs AS (
SELECT  js.owner
       ,  js.job_name
       ,  decode (js.state, 'SHUD', 'SCHEDULED', 'DIS' , 'DISABLED', 'RUN', 'RUNNING',   js.state) AS state
       ,  js.run_count
       ,  js.failure_count
       ,  to_char (js.last_start_date, 'dd.mm hh24:mi') AS last_start_date
       ,  to_char (js.next_run_date, 'dd.mm hh24:mi')   AS next_run_date
       , LAST_RUN_DURATION
    FROM dba_scheduler_jobs js
)
, 
last_log_date AS (
SELECT owner
    , job_name 
    , MAX(log_date) AS log_date
 FROM dba_scheduler_job_log 
GROUP BY  owner
    , job_name
    )
, last_logs AS (
  SELECT l.* 
    FROM dba_scheduler_job_log l  
INNER JOIN last_log_date ld 
  ON (    ld.owner=l.owner 
      AND  ld.job_name=l.job_name
      AND ld.log_date=l.log_date
    )
 
)
 
SELECT j.OWNER
    , j.JOB_NAME
    , j.STATE
    , j.RUN_COUNT
    , j.FAILURE_COUNT
    , round( EXTRACT( SECOND FROM j.LAST_RUN_DURATION ) 
          + EXTRACT( MINUTE FROM j.LAST_RUN_DURATION ) * 60 
          + EXTRACT( HOUR FROM j.LAST_RUN_DURATION ) * 60 * 60 
          + EXTRACT( DAY FROM j.LAST_RUN_DURATION ) * 60 * 60 * 24
            ,2) AS LAST_RUN_DURATION_SEC
    , j.LAST_START_DATE
    , j.NEXT_RUN_DATE
    , to_char(l.LOG_DATE, 'dd.mm hh24:mi') AS last_log_date
    , nvl(l.STATUS,'NO_LOG_FOR_THIS_JOB') AS  last_log_status
  FROM jobs j 
 LEFT JOIN last_logs l
   ON  (j.owner=l.owner AND  j.job_name=l.job_name)
WHERE 1=1
 -- only active  Jobs
 AND state != 'DISABLED'
ORDER BY j.owner,j.job_name
;

Oracle:

• http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm
• http://docs.oracle.com/cd/B19306_01/server.102/b14231/schedover.htm#i1106396
• http://docs.oracle.com/cd/E11882_01/server.112/e25494/schedadmin.htm#ADMIN035

Netz:

• http://www.held-informatik.de/tipps-tricks/plsql-packages/steuerung-von-datenbank-jobs.html
• http://oracle-base.com/articles/10g/scheduler-10g.php#windows
• https://www.eehelp.com/question/syntax-of-calendar-dbms-scheduler-repeat-interval/
• http://www.snapdba.com/2014/10/dbms_scheduler-jobs-running-an-hour-late-dstbst-issues/#.WyE8yO6FPmE
• https://otsteam.tistory.com/390

Bei 19c zu beachten ⇒ https://mikedietrichde.com/2020/05/21/dbms_job-one-off-patch-needed-for-oracle-19-3-0-19-7-0/