=====Mit dem Oracle Scheduler 12c/18c/19c das Filesystem überwachen und bei Bedarf einen Job starten=====
**Aufgabe:**
Sobald Dateien in einem Ordner auf der Datenbank Maschine auftauchen, soll ein Job zur Verarbeitung der Daten gestartet werden.
Der DB Job soll erkennen ob neue Daten vorliegen und dann über das Betriebssystem verarbeiten/in die DB laden.
Sehe dazu => [[dba:oracle_scheduler_12c_external_scripts|Mit dem Oracle 12c / 19c Scheduler die Crontab ersetzen - Skripte über die Datenbank Job Steuerung im Betriebssystem aufrufen]]
Mehr Details zum Scheduler siehe => [[dba:oracle_scheduler | Der Oracle Job Scheduler ab 10g ]]
----
==== Architektur ====
In der Datenbank ist per Default ein Job "FILE_WATCHER" angelegt, der alle 10 Minuten alle definierten "Datei Überwachungen" prüft.
----
==== Vorbereitung DB und OS User ====
===DB User===
User für den Import der Daten anlegen und die notwendigen Rechte vergeben:
sqlplus / as sysdba
-- Create the Job User
create user job_control identified by job$100;
grant connect,resource to job_control;
grant unlimited tablespace to job_control;
=== OS User ===
Linux User für den Datentransfer im OS der DB anlegen:
groupadd -g 1100 etl
useradd -u 1102 -g etl job_control
passwd job_control
# Create directory for the ETL scripts
mkdir /srv/job_control
chown job_control:etl /srv/job_control
=== Die OS Credentials für den OS User in der Datenbank hinterlegen ===
sqlplus / as sysdba
-- Credentials anlegen
BEGIN
DBMS_SCHEDULER.CREATE_CREDENTIAL(
'FILE_WATCH_CRED'
, 'job_control'
, 'oracle');
END;
/
-- Rechte an dem Credential an Job Control User vergeben
GRANT EXECUTE ON FILE_WATCH_CRED to job_control;
-- DD abfragen
column credential_name format a32
column username format a32
SELECT credential_name
,username
FROM user_credentials
ORDER BY credential_name
/
----
====File Watcher Objekt in der DB definieren====
BEGIN
DBMS_SCHEDULER.CREATE_FILE_WATCHER(
FILE_WATCHER_NAME => 'IMPORT_FILE_WATCHER'
, DIRECTORY_PATH => '/srv/job_control'
, FILE_NAME => 'import*.csv'
, CREDENTIAL_NAME => 'FILE_WATCH_CRED'
, DESTINATION => NULL
, ENABLED => FALSE
);
END;
/
--bei Fehlern entfernen und neu anlegen
begin
DBMS_SCHEDULER.DROP_FILE_WATCHER (
file_watcher_name => 'IMPORT_FILE_WATCHER'
);
end;
/
-- rechte an den DB User vergeben
GRANT EXECUTE ON IMPORT_FILE_WATCHER to job_control;
Was haben wir angelegt:
SET LINESIZE 100
COLUMN file_watcher_name FORMAT A20
COLUMN destination FORMAT A15
COLUMN directory_path FORMAT A15
COLUMN file_name FORMAT A10
COLUMN credential_name FORMAT A20
SELECT file_watcher_name
, destination
, directory_path
, file_name
, credential_name
FROM dba_scheduler_file_watchers
;
----
====Überwachungs-Interval bei Bedarf anpassen====
Per Default läuft der File Watcher Job in der Datenbank alle 10 Minuten.
Anpassen auf z.B. 2 Minuten:
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE'
, 'REPEAT_INTERVAL'
,'FREQ=MINUTELY;INTERVAL=2');
END;
/
----
==== Eigentlichen Job für die Verarbeitung unter dem User JOB_CONTROL anlegen ====
Im ersten Schritt sollen in diesem Demo nur die Meta Daten der Datei in eine Log Tabelle geschrieben werden.
Log Tabelle:
CREATE TABLE CSV_IMPORT_FILE_WATCH_LOG (
message VARCHAR2(4000)
);
PL/SQL Code Block um die Meta Daten auszuwerten:
CREATE OR REPLACE PROCEDURE CSV_INTERFACE_FILE_IMPORT (p_file_watch_result SYS.SCHEDULER_FILEWATCHER_RESULT)
AS
v_message CSV_IMPORT_FILE_WATCH_LOG.message%TYPE;
BEGIN
v_message := 'Pfad :' || p_file_watch_result.directory_path
||'-Name :'|| p_file_watch_result.actual_file_name
||'-Size :'|| p_file_watch_result.file_size
||'-Date :'|| p_file_watch_result.file_timestamp
||'-Epoch :'|| p_file_watch_result.ts_ms_from_epoch;
INSERT INTO CSV_IMPORT_FILE_WATCH_LOG (message)
VALUES (v_message);
COMMIT;
END;
/
Oracle Scheduler Programm Definition:
BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'CSV_INTERFACE_FILE_IMPORT_PROG',
program_type => 'STORED_PROCEDURE',
program_action => 'CSV_INTERFACE_FILE_IMPORT',
number_of_arguments => 1,
enabled => FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.define_metadata_argument(
program_name => 'CSV_INTERFACE_FILE_IMPORT_PROG',
metadata_attribute => 'event_message',
argument_position => 1);
END;
/
Den eigentlichen Job anlegen:
begin
DBMS_SCHEDULER.drop_job('job_control.CSV_INTERFACE_FILE_IMPORT_JOB');
end;
/
BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'job_control.CSV_INTERFACE_FILE_IMPORT_JOB',
program_name => 'job_control.CSV_INTERFACE_FILE_IMPORT_PROG',
event_condition => NULL,
queue_spec => 'IMPORT_FILE_WATCHER',
auto_drop => FALSE,
enabled => FALSE);
END;
/
Problem: **ORA-24085: operation failed, queue IMPORT_FILE_WATCHER is invalid**
ERROR at line 1:
ORA-24085: operation failed, queue IMPORT_FILE_WATCHER is invalid
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_SCHEDULER", line 536
ORA-06512: at line 2
=>** Lösung : als SYS den Job anlegen**, hier fehlt noch ein Recht, aber nur welches? Direkt auf die AD Queue? Seltsam, bin noch am suchen.
Falls auch auf neue Parallel erstellte Dateien zu reagieren:
BEGIN
DBMS_SCHEDULER.set_attribute('CSV_INTERFACE_FILE_IMPORT_JOB','PARALLEL_INSTANCES',TRUE);
END;
/
Alles aktivieren:
EXEC DBMS_SCHEDULER.enable('IMPORT_FILE_WATCHER');
EXEC DBMS_SCHEDULER.enable('CSV_INTERFACE_FILE_IMPORT_PROG');
EXEC DBMS_SCHEDULER.enable('CSV_INTERFACE_FILE_IMPORT_JOB');
----
====Debug====
Manueller Aufruf der Routine hinter dem File Watcher und Auslesen des Tracefiles:
oradebug setmypid
oradebug tracefile_name
alter system set events '27401 trace name context forever, level 262144';
begin
dbms_ischedfw.file_watch_job;
end;
/
wie:
vi /opt/oracle/diag/rdbms/gpidb/GPIDB/trace/GPIDB_ora_51129.trc
..
file_watcher:: 2021-05-16 21:52:10.679: Directory: /srv/job_control, ArrivalTime: 1621194686000, SteadyStDur: 30000
file_watcher:: 2021-05-16 21:52:10.680: LastChkTime for directory /srv/job_control changed to 1621194686000
file_watcher:: 2021-05-16 21:52:10.682: All checking done
file_watcher:: 2021-05-16 21:52:10.683: Setting up results information
file_watcher:: 2021-05-16 21:52:10.684: Directory: /srv/job_control
file_watcher:: 2021-05-16 21:52:10.685: Result file name: import23.csv
file_watcher:: 2021-05-16 21:52:10.687: Request pattern: import*.csv, Regex: import.*.csv
file_watcher:: 2021-05-16 21:52:10.688: Match found, adding to list of matching requests
file_watcher:: 2021-05-16 21:52:10.690: File watching done
file_watcher:: 2021-05-16 21:52:10.709: Updating history
file_watcher:: 2021-05-16 21:52:10.710: Iteration 1
file_watcher:: 2021-05-16 21:52:10.710: Updating history entry
file_watcher:: 2021-05-16 21:52:10.710: Dir Path: /srv/job_control
file_watcher:: 2021-05-16 21:52:10.710: Last Chk Time: 16-MAY-21 07.51.26.000000 PM +00:00
file_watcher:: 2021-05-16 21:52:10.710: Processing results
file_watcher:: 2021-05-16 21:52:10.711: Iteration 1
file_watcher:: 2021-05-16 21:52:10.711: Dir Path: /srv/job_control
file_watcher:: 2021-05-16 21:52:10.711: File Name: import23.csv
file_watcher:: 2021-05-16 21:52:10.711: File Size: 22258
file_watcher:: 2021-05-16 21:52:10.711: File Tstamp: 16-MAY-21 07.51.26.000000 PM +00:00
file_watcher:: 2021-05-16 21:52:10.711: Matching Requests:
file_watcher:: 2021-05-16 21:52:10.711: Request: SYS.IMPORT_FILE_WATCHER
SCHED 05-16 21:52:10.713 1 00 51129 ora 0(sjssuWriteJssuFlags):Using Flags 9
..
----
==== Quellen ====
Oracle Dokumentation:
* https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/scheduling-jobs-with-oracle-scheduler.html
* https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html
Blogs:
* https://aychin.wordpress.com/2014/08/21/oracle-file_watcher-and-its-troubleshooting/
* https://oracle-base.com/articles/11g/scheduler-enhancements-11gr2
* https://oracle-base.com/blog/2019/08/29/q-is-the-create-job-privilege-required-in-19c-a-no/