====Eine Oracle External Table zum Aufruf von Linux Befehlen ge(miss)brauchen==== **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 => [[dba:oracle_external_table|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 => [[dba:oracle_scheduler_12c_external_scripts|Mit dem Oracle 12c Scheduler die Crontab ersetzen - Skripte über die Datenbank Job Steuerung im Betriebsystem aufrufen]] ---- === Vorüberlegungen === 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. ==== Umsetzung ==== === Directory in der DB anlegen und Rechte setzen === 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; ==Problem KUP-04087: no execute access to directory object == Execute Rechte nicht vergessen! ---- === Script auf OS Seite anlegen === 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. === Test 1 - External Table anlegen um das Ergebnis zeilenweise einzulesen === 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 [[dba:oracle_sqlldr_faq|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. ==SQL*Loder Parameter recnum== 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 == Create Script == 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 / ==Probem KUP-01005: syntax error: found "logfile": expecting one of: "column" == 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) ) ==Probem KUP-01005: syntax error: found "badfile": expecting one of: "disable_directory_link_check, logfile, nologfile, preprocessor == 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 will ich sortieren?== 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. ---- === Script Ausführung integrieren === 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 / ---- ---- ==== Sicherheitsüberlegung === 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. D.h. in unseren Script könne wir auf alle Rechte zugreifen die auch der DB Owner hat .-), den Rest überlasse ich Ihrer Phantasie. ---- ==== Quellen ==== Oracle: * https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-external-tables-concepts.html#GUID-44323E01-7D72-45EC-915A-99E596769D9E * https://docs.oracle.com/en/database/oracle/oracle-database/18/sutil/oracle-sql-loader-field-list-contents.html#GUID-9D1D0548-E76F-4FFC-B4E9-E69BEF55FE0B