=====Mit dem Oracle 12c Scheduler die Crontab ersetzen - Skripte über die Datenbank Job Steuerung im Betriebsystem aufrufen====

Erste Version : ** 2018/02/05**

<note warning>Ab min 18c wurde die Sicherheit verstärkt! So einfach funktioniert es nicht mehr!</note>

Siehe dazu auch:https://svenweller.wordpress.com/2019/03/28/18c-scheduler-external-script-with-error-ora-27369-job-of-type-executable-failed-with-exit-code-no-child-processes/

**Aufgabe:** 

Die ETL Jobs auf einem Linux System sollen direkt aus der DB aufgerufen werden, die Linux crontab soll dazu nicht mehr verwendet werden. Die Skripte müssen dazu aber auch unter einen speziellen OS User auf dem System ausgeführt werden.

Mehr Details zum Scheduler siehe [[dba:oracle_scheduler|Der Oracle Job Scheduler 10g/11g/12c]]


=== Vorbereitung ===


User für die Job Steuerung anlegen und die notwendigen Rechte vergeben:

<code sql>
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;


-- grant the main rights CREATE JOB, CREATE CREDENTIAL, and CREATE EXTERNAL JOB


grant create job, create credential, create external job to job_control;


</code>


ETL Linux User im OS der DB anlegen:

<code bash>

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

</code>

ETL Test Script mit Parameter Übergabe anlegen:

<code bash>

su - job_control
cd /srv/job_control/

vi run_etl_load.sh


#!/bin/sh

echo "--Info Script was called with parameter $1 - log to file"     > /tmp/etl_job.log
echo "--Info Script was called with parameter $1 - log to std_out"

:wq

# Rechte nicht vergessen!
chmod 760 run_etl_load.sh


</code>

----

=== Die OS Credentials für den OS User in der DB hinterlegen===

Connect als job_control

<code sql>
sqlplus job_control

-- Credentials anlegen
begin
  DBMS_CREDENTIAL.CREATE_CREDENTIAL('OS_USER_JOB_CONTROL','job_control','my_secret_pwd');
end;
/

-- DD abfragen

SELECT  credential_name
       ,username
   FROM   user_credentials
ORDER BY credential_name
/


</code>

----

===Einen Job definieren===


Job (in diesem Fall einen einmaligen Job) anlegen mit:

<code plsql>

-- Einmal Job anlegen
-- 

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
  job_name            => 'ETL_JOB',
  job_type            => 'EXECUTABLE',
  job_action          => '/srv/job_control/run_etl_load.sh',
  number_of_arguments => 1,
  enabled             => false,
  auto_drop           => true,
  credential_name     => 'OS_USER_JOB_CONTROL');
  
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('ETL_JOB',1,'20180205 JOB');
 
 DBMS_SCHEDULER.ENABLE('ETL_JOB');

 END;
/

</code>

Testen über die View "user_scheduler_job_run_details ", ob es auch geklappt hat:

<code sql>

SELECT job_name
      , status
      , error#
      , actual_start_date
      , additional_info
 FROM user_scheduler_job_run_details 
WHERE job_name='ETL_JOB';

 Job                        status         Nbr Date
-------------------------- ---------- ------- -------
ADDITIONAL_INFO
-----------------------------------------------------------

ETL_JOB                    SUCCEEDED        0 05-FEB-18 01.58.16.290682 PM EUR
                                              OPE/VIENNA
EXTERNAL_LOG_ID="job_92830_2616",
USERNAME="job_control"


</code>

Auf OS Seite den erzeugen Logfile überprüfen (ob der Paramter auch wirklich richtig übergeben wurde):
<code bash>

cat /tmp/etl_job.log
Script was called with parameter 20180205 JOB

</code>


Testen wir mal was uns das Script in die Datenbank zurück gegeben hat (über die Spalten OUTPUT und BINARY_OUTPUT)  :

<code sql>

select OUTPUT from user_scheduler_job_run_details WHERE job_name = 'ETL_JOB';;

OUTPUT
--------

--Info Script was called with parameter 20180205 JOB- log to std_out

-- bei mehr als 4000 Zeichen

select utl_raw.cast_to_varchar2(BINARY_OUTPUT) from user_scheduler_job_run_details WHERE job_name = 'ETL_JOB';

</code>

D.h. wir können Standard Out abfangen und hier wieder Details des Jobs zurück and die DB reichen.

=== Fehler verhalten - Password anpassen ===

Was passiert wenn das Password des OS Users geändert wurde?

Neues Passwort gesetzt, Job erneut angelegt=>

<code sql>

select additional_info
  FROM user_scheduler_job_run_details
WHERE job_name='ETL_JOB'
/


ORA-27369: job of type EXECUTABLE failed with exit code: 7 !@#--!@#7#@!--#@!
STANDARD_ERROR="Launching external job failed: Invalid username or password"

</code>

Password neu setzen:
<code sql>
begin
DBMS_CREDENTIAL.UPDATE_CREDENTIAL (
    credential_name  => 'OS_USER_JOB_CONTROL'
   ,attribute        => 'PASSWORD'
   ,value            => 'hugo'
   );
end;
/
   
</code>

Job erneut ausführen, OK!



----

==== OS Jobs unter Windows aufrufen =====


=== OS User Account hinterlegen, inkl. Domain! ====


Ist keine Domain hinterlegt, kommt es zu folgenden Fehler: "ORA-27369: Job vom Typ EXECUTABLE nicht erfolgreich mit Exit-Code: <fc #ff0000>The storage control block address is invalid.</fc>" - Sehr aussagekräftig .... 
=> Scheduled Job fails with ORA-27369 with exit code: The storage control blocks were destroyed (Doc ID 1674713.1)

<code plsql>

-- Credentials anlegen
BEGIN
  DBMS_CREDENTIAL.CREATE_CREDENTIAL('OS_USER_JOB_CONTROL','gpipperr','xxxxxxxxx');
END;
/

-- Domain zuordnen
BEGIN
 DBMS_CREDENTIAL.UPDATE_CREDENTIAL (
       credential_name   => 'OS_USER_JOB_CONTROL'
    ,  attribute         => 'WINDOWS_DOMAIN'
    ,  value             => 'SATURN'
   );
END;
/

--  select Credential
SELECT credential_name
       ,username
       ,windows_domain
       ,enabled
FROM   user_credentials
ORDER BY credential_name
/


</code>

Tip: Welche Domain verwende ich überhaupt? CMD öffnen und mit "set user" abfragen.


CMD Script anlegen (darauf achten das der ausgewählte User die entsprechenden Rechte besitzt!)

info.cmd:
<code cmd>
echo "--Info Script was called with parameter %1 - log to file"     > C:\temp\etl_job.log
echo "--Info Script was called with parameter %1 - log to std_out"
</code>


Über einen Job das Script aufrufen:

<code plsql>

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
  job_name            => 'ETL_JOB',
  job_type            => 'EXECUTABLE',
  job_action          => 'C:\oracle\dbScript\info.cmd',
  number_of_arguments => 1,
  enabled             => FALSE,
  auto_drop           => TRUE,
  credential_name     => 'OS_USER_JOB_CONTROL');
 
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('ETL_JOB',1,'20191709 JOB');
 
 DBMS_SCHEDULER.ENABLE('ETL_JOB');
 
 END;
/

-- Prüfen

SELECT job_name
      , STATUS
      , error#
      , actual_start_date
      , additional_info
 FROM user_scheduler_job_run_details 
WHERE job_name='ETL_JOB'
order by actual_start_date
/

</code>


Testen wir mal was uns das Script in die Datenbank zurück gegeben hat (über die Spalten OUTPUT und BINARY_OUTPUT)  :

<code sql>

select ERRORS
   ,  OUTPUT
   ,  BINARY_ERRORS
   ,  BINARY_OUTPUT
 from user_scheduler_job_run_details 
WHERE job_name = 'ETL_JOB';


OUTPUT
--------

--Info Script was called with parameter 20180205 JOB- log to std_out

-- bei mehr als 4000 Zeichen

select utl_raw.cast_to_varchar2(BINARY_OUTPUT) from user_scheduler_job_run_details WHERE job_name = 'ETL_JOB';

</code>

D.h. wir können Standard Out abfangen und hier wieder Details des Jobs zurück and die DB reichen.


Klappt das auch für einen Powerschell Aufruf?

So würde der Aufruf eines PowerShell Scripts über einen Job normalerweise ausschauen:
<code powershell>
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -NoLogo -NoProfile -NonInteractive -File C:\oracle\dbScript\info.ps1
</code>

<code plsql>
BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
  job_name            => 'ETL_JOB2',
  job_type            => 'EXECUTABLE',
  job_action          => '%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe',
  number_of_arguments => 1,
  enabled             => FALSE,
  auto_drop           => TRUE,
  credential_name     => 'OS_USER_JOB_CONTROL2');
 
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('ETL_JOB2',1,'-ExecutionPolicy Bypass -NoLogo -NoProfile -NonInteractive -File C:\oracle\dbScript\info.ps1 parameter');
 
 DBMS_SCHEDULER.ENABLE('ETL_JOB2');
 
 END;
/


select ERRORS
   ,  OUTPUT
 from user_scheduler_job_run_details 
WHERE job_name = 'ETL_JOB2';

</code>

Bei mir aber bisher nur der Standard Default Fehler "ORA-27369: Job vom Typ EXECUTABLE nicht erfolgreich mit Exit-Code: The storage control block address is invalid."

Suche noch ob es eine Lösung für den direkten Aufruf der Powershell gibt, ansonsten das in eine CMD packen und von dort aufrufen.




----

==== Wo steht aber nun das Passwort in der DB? ====


siehe Tabelle =>  **SYS scheduler$_credential**

siehe Hinweise in diesen Foliensatz: 

https://www.doag.org/formes/pubfiles/4200237/2012-K-null-Alexander_Kornbrust-STREAM-KEYNOTE_SECURITY__Best_of_Oracle_Security_2012-Praesentation.pdf für 11g, in 12c anscheinend gefixt bzw. anders gelöst.






----

==== Quellen ====

  * Oracle Documentation - External Jobs => https://docs.oracle.com/database/121/ADMIN/scheduse.htm#ADMIN12384
  * Oracle Documentation - DBMS_CREDENTIAL => https://docs.oracle.com/database/121/ARPLS/d_credential.htm#ARPLS73733