getestet mit Oracle 12c unter Linux 7
Aufgabe:
In einer APEX Admin Oberfläche soll die Crontab der DB Linux Maschine darstellt werden, da auf der Maschine auch ein paar ETL Jobs in Bash realisiert wurden und überwacht werden müssen.
Idee:
Mit der Prprocessor Anweisung einer External Table ein Script starten, das Ergebnis ein eine Datei pipen und diese per External Table zeilenweise in die DB laden.
Dabei sollen die Zeilen auch in der richtige Reihenfolge in der DB angezeigt werden.
Hier dazu eine ähnliche Lösung um CSV Dateien aus Hadoop zu importieren ⇒ CSV Dateien in Oracle als external Table lesen und importieren - Daten aus Hadoop lesen
Alternativ wäre natürlich auch eine Oracle Scheduler Lösung denkbar ⇒ Mit dem Oracle 12c Scheduler die Crontab ersetzen - Skripte über die Datenbank Job Steuerung im Betriebsystem aufrufen
Die External Loader Funktionalität wird unter dem User, dem die Datenbank läuft, ausgeführt.
Soll zum Beispiel die Crontab eines anderen Users ausgelesen werden, haben wir hier ein Rechte Problem!
Das lässt sich nur über einen sudo Befehl „sudo crontab -l -u myetluser“ lösen!
D.h. der DB User muss zuvor die notwendigen Sudo Rechte erhalten.
Darauf achten das der Oracle DB Owner auch die notwendige Gruppe hat oder die Verzeichnisse die notwendigen Rechte damit der Oracle DB Owner überhaupt die Verzeichnisse lesen kann!
Verzeichniss anlegen:
mkdir -p /srv/elt/db_command/ chmod 777 /srv/elt/db_command/
Directory Objekt in der DB als sys anlegen:
CREATE directory DB_COMMAND AS '/srv/elt/db_command/'; GRANT READ,WRITE,EXECUTE ON directory DB_COMMAND TO DWH_ADMIN;
Execute Rechte nicht vergessen!
Ein einfaches Script erstellen
vi readCrontab.sh #!/bin/sh #set runtime export PATH=$PATH:/bin:/sbin/:/usr/bin # falls genug Rechte! # alternativ /bin/sudo /bin/crontab -l -u myUser > readCrontab.dat # /bin/crontab -l > readCrontab.dat /bin/chmod 666 readCrontab.dat chmod 777 readCrontab.sh
Testen und dabei die readCrontab.dat erzeugen.
Im ersten Schritt lesen wir nun die Dat Datei ein, wie diese erzeugt wird klären wir später, wie gesagt der DB Owner braucht erstmal dazu eine paar Rechte, wenn es nicht seine eigene Crontab ist!.
Wie kann eine fortlaufende Nummer in einem External Table Import erzeugt werden?
Mit dem SQL*Loader kann das mit sequence(count) oder auch mit einer COLUMN EXPRESSION „parameter_id_seq.nextval“ erfolgen, beides funktioniert aber mir mit der SQL Loader Anweisung in der External table Definition nicht.
Die Lösung für das Problem ist der SQL*LDR Parameter „recnum“, siehe in der Doku ⇒ https://docs.oracle.com/database/121/SUTIL/GUID-1F722706-2A6A-4F60-AC91-D1466958BA55.htm#SUTIL1239
DROP TABLE SHOW_LINUX_CRONTAB; CREATE TABLE SHOW_LINUX_CRONTAB( line_no NUMBER(11) , line_value VARCHAR2(4000) ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY DWH_OP_COMMAND ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE LOGFILE 'DWH_OP_COMMAND':'readCronTab.log' BADFILE 'DWH_OP_COMMAND':'readCronTab.bad' NODISCARDFILE SKIP 0 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL ( line_no RECNUM , line_value CHAR(4000) ) ) LOCATION (DWH_OP_COMMAND:'readCronTab.dat') ) REJECT LIMIT 0 NOPARALLEL NOMONITORING /
alle FIELD Anweisungen in eine Zeile hintereinander schreiben!!
So ist es richtig!
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL ( line_no RECNUM , line_value CHAR(4000) )
Das funkioniert nicht:
LOGFILE DWH_OP_COMMAND:'readCronTab.log' BADFILE DWH_OP_COMMAND:'readCronTab.bad'
es fehlen die ' !
so ist es richtig:
LOGFILE 'DWH_OP_COMMAND':'readCronTab.log' BADFILE 'DWH_OP_COMMAND':'readCronTab.bad'
Warum ich die Line Number brauche? Ich zeige das ja später in APEX in einem Classic Report an, eigentlich sollten dann da ja die Daten so auftauchen wie sie eingelesen wurden, aber die Daten waren sortiert bzw. durcheinander.
Daher die Line_no eingeführt, und damit das Problem sicher gelöst.
Nachdem wir nun eine Datei aus dem OS erfolgreich einlesen können fehlt nur noch der Schritt die einzulesende Datei im gleichen Moment mit unseren Script zu erzeugen.
Dazu steht mit der „PREPROCESSOR“ ein mächtiges Werkzeug zur Verfügung um Daten VOR dem einlesen zu entpacken oder zu konvertieren.
Für jede Datei, die in der „LOCATION“ Description beschrieben wird, wird das Script in der „PREPROCESSOR“ aufgerufen. Der Dateiname wird als %1% bzw. %1 übergeben.
Eigentlich dient das dazu zum Beispiele diese Datei zu entpacken, wir rufen etwas auf und erzeugen die gleiche Datei wieder.
Die Anweisung gehöhrt in den ACCESS PARAMETERS () Bereich:
PREPROCESSOR DWH_OP_COMMAND:'readCrontab.sh'
Die komplette Lösung sieht damit so aus:
DROP TABLE SHOW_LINUX_CRONTAB; CREATE TABLE SHOW_LINUX_CRONTAB( line_no NUMBER(11) , line_value VARCHAR2(4000) ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY DWH_OP_COMMAND ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE PREPROCESSOR DWH_OP_COMMAND:'readCrontab.sh' LOGFILE 'DWH_OP_COMMAND':'readCronTab.log' BADFILE 'DWH_OP_COMMAND':'readCronTab.bad' NODISCARDFILE SKIP 0 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL ( line_no RECNUM , line_value CHAR(4000) ) ) LOCATION (DWH_OP_COMMAND:'readCronTab.dat') ) REJECT LIMIT 0 NOPARALLEL NOMONITORING /
Von der Security Seite ist das Feature brand gefährlich!
Wir können ja jetzt jedes Script, das wir erstellen, in der DB Owner Umgebung ausführen lassen.
Oracle: