Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:oracle_external_table_execute_script

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 ⇒ 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


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 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 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)       
 ) 	

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
     ( 	PREPROCESSOR DWH_OP_COMMAND:'readCrontab.sh'
        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
 /


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

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
dba/oracle_external_table_execute_script.txt · Zuletzt geändert: 2018/03/21 22:13 von Gunther Pippèrr