Gunther Oracle APEX und Datenbank Wiki

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:

• 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