Oracle File Watcher

extjobo must be owned by oracle with the Oracle install group (usually oinstall) with a minimum of rwx (700) permissions for oracle. If you intend the File Watcher to run as a user other than oracle then permissions should be rwxr-xr-x (755)...

ls -l $ORACLE_HOME/bin/extjobo

jssu must be owned by root with the Oracle install group (usually oinstall) and must have the setuid bit set i.e chmod 4750 (-rwsr-x---)...

ls -l $ORACLE_HOME/bin/jssu

The oracle binary must have the setuid bit set for user and group i.e. chmod 6751 (-rwsr-s--x)...

ls -l $ORACLE_HOME/bin/oracle

Linux

Linux-PAM must be installed

yum info pam

Show File Watchers

@/u01/dba/dbs/showfilewatchers.sql

SET LINESIZE 220

SET PAGESIZE 40

COLUMN owner FORMAT a20

COLUMN file_watcher_name FORMAT a30

COLUMN directory_path FORMAT a50 WRAP

COLUMN file_name FORMAT a30 WRAP

COLUMN credential_owner FORMAT a20

COLUMN credential_name FORMAT a30

SELECT owner,

       file_watcher_name,

       enabled, directory_path,

       file_name,

       credential_owner,

       credential_name

  FROM dba_scheduler_file_watchers

File Watcher Poll Frequency

By default the File Watcher will check for new files every 10 minutes

Change

To change it to check every 5 minutes use...

BEGIN

  DBMS_SCHEDULER.set_attribute(

    'file_watcher_schedule',

    'repeat_interval',

    'freq=minutely; interval=5');

END;

Check

SELECT repeat_interval

FROM dba_scheduler_schedules

WHERE schedule_name = 'FILE_WATCHER_SCHEDULE';

Get File Watcher Password (11g)

or... Why you should always use a low privileged OS account for File Watcher jobs in 11g

SELECT u.name CREDENTIAL_OWNER,

       O.NAME CREDENTIAL_NAME,

       C.USERNAME,

       DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd

FROM SYS.SCHEDULER$_CREDENTIAL C,

     SYS.OBJ$ O,

     SYS.USER$ U

WHERE U.USER# = O.OWNER#

  AND C.OBJ#  = O.OBJ# ;

NOTE: DBMS_ISCHED can only be executed by a user with SYSDBA privileges.NOTE: This works in 11gR2 but not 12c+

Worked Example

In this example we want to populate a table (WATCHED_FILE_INFO) with information about a file whenever a file with a name matching a specific pattern (watch*.txt) is written to a specific filesystem location (/tmp).

CREATE TABLE myfilewatcher_tab (

   myfilename       VARCHAR2(30),

   myfilepath       VARCHAR2(30),

   myfiletime       TIMESTAMP);

To populate the table we will call this procedure...

CREATE OR REPLACE PROCEDURE myfilewatcher_proc (p_sfwr SYS.SCHEDULER_FILEWATCHER_RESULT) AS

BEGIN

  INSERT INTO myfilewatcher_tab (myfilename, myfilepath, myfiletime)

  VALUES (SUBSTR(p_sfwr.directory_path,1,30),

          SUBSTR(p_sfwr.actual_file_name,1,30),

          p_sfwr.file_timestamp);

  COMMIT;

END;

The File Watcher will need a credential that specifies an OS username and password with privileges to read the created file...

11g...

BEGIN

  DBMS_SCHEDULER.create_credential(

    credential_name => 'mycredential',

    username        => 'myosuser',

    password        => 'myospassword');

END;

12c...

Use: dbms_credential (to be documented)

To create the File Watcher use...

BEGIN

  DBMS_SCHEDULER.create_file_watcher(

    file_watcher_name => 'myfilewatcher',

    directory_path    => '/tmp',

    file_name         => 'watch*.txt',

    credential_name   => 'mycredential',

    destination       => NULL,

    enabled           => FALSE);

END;

Next we need a Scheduler Program to call our stored procedure...

BEGIN

  DBMS_SCHEDULER.create_program(

    program_name        => 'myfilewatcher_prog',

    program_type        => 'stored_procedure',

    program_action      => 'myfilewatcher_proc',

    number_of_arguments => 1,

    enabled             => FALSE);

END;

This defines the argument that the program will pass to the procedure...

BEGIN

  DBMS_SCHEDULER.define_metadata_argument(

    program_name       => 'myfilewatcher_prog',

    metadata_attribute => 'event_message',

    argument_position  => 1);

END;

Create a Scheduler Job...

BEGIN

  DBMS_SCHEDULER.create_job(

    job_name        => 'myfilewatcher_job',

    program_name    => 'myfilewatcher_prog',

    event_condition => NULL,

    queue_spec      => 'myfilewatcher',

    auto_drop       => FALSE,

    enabled         => FALSE);

END;

By default, the arrival of new files will be ignored if the job is already running. To make the job run for every file you will need to do this...

BEGIN

  DBMS_SCHEDULER.set_attribute('myfilewatcher_job','parallel_instances',TRUE);

END;

Finally, enable everything we just created...

EXEC DBMS_SCHEDULER.enable('myfilewatcher');

EXEC DBMS_SCHEDULER.enable('myfilewatcher_prog');

EXEC DBMS_SCHEDULER.enable('myfilewatcher_job');

Create some test files...

echo "test1" > /tmp/watch1.txt

echo "test2" > /tmp/watch2.txt

echo "test3" > /tmp/watch3.txt

Check whether it worked (remember it could take 10 minutes unless you updated the poll frequency)...

SELECT * FROM myfilewatcher_tab;

Update Credentials

If you need to update the credentials later you will find you cannot drop and recreate the credential (as EXEC dbms_scheduler.drop_credential('MYCREDENTIAL'); will result in:ORA-27479: Cannot drop "SYS.MYCREDENTIAL" because other objects depend on it.

The workaround is to use:

exec DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'MYCREDENTIAL',attribute=>'username',value=>'oracle');

exec DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'MYCREDENTIAL',attribute=>'password',value=>'oracle');

For full list of attributes see: https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72425

12c...

Use: dbms_credential (to be documented)

Troubleshooting

Check Pre-requisites (see earlier on this page).

Scheduler Job

Check if the Scheduler job has actually been executed, and if so, what status it has...

SELECT owner, job_name, status, error#, credential_owner, credential_name, destination_owner, destination, additional_info

  FROM dba_scheduler_job_run_details

 WHERE job_name = 'myfilewatcher_job'

 ORDER BY actual_start_date;

Trace Files

Check Scheduler Slave trace file, which will be called something like...

/u01/app/oracle/diag/rdbms/mysid/MYSID/trace/MYSID_j000_9999.trc

Manually reproduce problem...

Make sure there is a file that matches the file name pattern in the location your file watcher is watching

oradebug setmypid

oradebug tracefile_name

EXEC dbms_isched.file_watch_job;

Check the tracefile and see the Example Issues section later in this document.

Check Credentials

-- 11g

SELECT credential_name, username

  FROM dba_scheduler_credentials;

-- 12c

SELECT credential_name, username

  FROM dba_scheduler_credentials;

-- Create a job to test your credential

BEGIN

DBMS_SCHEDULER.CREATE_JOB(

   job_name            => 'TESTCRED',

   JOB_TYPE            => 'EXECUTABLE',

   JOB_ACTION          => '/bin/ls' ,

   CREDENTIAL_NAME     => 'mycredential',

   ENABLED             => TRUE,

   AUTO_DROP           => TRUE);

END;

-- View the results of your test

SELECT status, error#, additional_info

  FROM dba_scheduler_job_run_details

 WHERE job_name = 'TESTCRED';

If this job shows that the job failed then see the Example Issues section later in this document.

Deeper investigation

Confirm that you have a file watcher defined and enabled...

@/u01/dba/dbs/showfilewatchers.sql

Confirm that the File Watcher scheduler job exists (this should be created by default; you shouldn't need to do anything special to create this)...

SELECT owner, program_owner, program_name, schedule_owner, schedule_name, job_class

  FROM dba_scheduler_jobs where job_name='FILE_WATCHER';

By default the File Watcher scheduler job will only log errors. Use this query to confirm...

SELECT logging_level

  FROM dba_scheduler_job_classes

 WHERE job_class_name='SCHED$_LOG_ON_ERRORS_CLASS';

Assuming the above returned "FAILED RUNS", if the following query returns only 1 row where the operation is 'ENABLE' then this indicates the job is running without error...

SELECT *

  FROM dba_scheduler_job_log

 WHERE job_name = 'FILE_WATCHER';

Confirm that the job is running to the schedule you expect (default 10 minutes)...

SELECT repeat_interval

  FROM dba_scheduler_schedules

 WHERE schedule_name = 'FILE_WATCHER_SCHEDULE';

The File Watcher should be running DBMS_ISCHED.FILE_WATCH_JOB. Confirm with...

SELECT owner, program_type, program_action, number_of_arguments

  FROM dba_scheduler_programs

 WHERE program_name='FILE_WATCHER_PROGRAM';

If DBMS_ISCHED.FILE_WATCH_JOB finds a file with a modification date after the last check then it will be queued to SYS.SCHEDULER_FILEWATCHER_Q

SELECT owner, queue_table, qid, enqueue_enabled, dequeue_enabled

  FROM dba_queues

 WHERE name='SCHEDULER_FILEWATCHER_Q';

The queue table (SCHEDULER_FILEWATCHER_QT) is based on a TYPE called SCHEDULER_FILEWATCHER_RESULT which defines the file attributes returned by the File Watcher...

SELECT owner, type_name, typecode, attributes

  FROM dba_types

 WHERE type_name = 'SCHEDULER_FILEWATCHER_RESULT';

SCHEDULER_FILEWATCHER_RESULT defines the file attributes returned by the File Watcher...

SELECT attr_name, attr_type_name

  FROM DBA_TYPE_ATTRS

 WHERE owner='SYS'

   AND type_name='SCHEDULER_FILEWATCHER_RESULT'

ORDER BY attr_no;

ATTR_NAME                      ATTR_TYPE_NAME

------------------------------ ------------------------------

DESTINATION                    VARCHAR2

DIRECTORY_PATH                 VARCHAR2

ACTUAL_FILE_NAME               VARCHAR2

FILE_SIZE                      NUMBER

FILE_TIMESTAMP                 TIMESTAMP WITH TZ

TS_MS_FROM_EPOCH               NUMBER

MATCHING_REQUESTS              SCHEDULER_FILEWATCHER_REQ_LIST

SELECT owner, type_name, typecode, attributes

  FROM dba_types

 WHERE type_name = 'SCHEDULER_FILEWATCHER_REQ_LIST';

Example issues

File Watcher not picking up files. The trace file for Scheduler Slave process (and the trace from any manual runs as covered above) shows something like this...

*** 2020-11-11 14:59:19.201

*** SESSION ID:(316.5333) 2020-11-11 14:59:19.201

*** CLIENT ID:() 2020-11-11 14:59:19.201

*** SERVICE NAME:(SYS$USERS) 2020-11-11 14:59:19.201

*** MODULE NAME:(DBMS_SCHEDULER) 2020-11-11 14:59:19.201

*** ACTION NAME:(FILE_WATCHER) 2020-11-11 14:59:19.201

FILE_TRANSFER error is:

FILE_TRANSFER error is:

FILE_TRANSFER error is:

Scheduler Slave trace file will be called something like...

/u01/app/oracle/diag/rdbms/mysid/MYSID/trace/MYSID_j000_9999.trc

Running Credential Check from Troubleshooting section gives...

FAILED         27370

EXTERNAL_LOG_ID="job_76916_23644",

ORA-27369: job of type EXECUTABLE failed with exit code: Input/output error

STANDARD_ERROR="Launching external job failed: Login executable not setuid-root"

First check that your credential doesn't have any typos. Are you sure you can login with the username and password you provided?

This could also indicate that the permissions on the oracle binary are incorrect (961019.1)...

ls -l $ORACLE_HOME/bin/oracle

Permissions should be -rwsr-s--x. Correct if necessary with...

chmod 6751 $ORACLE_HOME/bin/oracle

Bibliography

https://aychin.wordpress.com/2014/08/21/oracle-file_watcher-and-its-troubleshooting/
https://oracle-base.com/articles/11g/scheduler-enhancements-11gr2#file_watcherhttps://berxblog.blogspot.com/2012/02/restore-dbmsschedulercreatecredential.htmlhttps://www.morganslibrary.org/reference/pkgs/dbms_isched.htmlhttps://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72256https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72298 (CREATE_FILE_WATCHER)
DBMS_SCHEDULER Extjob Fails With "Login Executable Not Setuid-Root" (Doc ID 961019.1)How to change DBMS_SCHEDULER credentials? (Doc ID 1422706.1)
11.2https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72425 (Credential Attribute Values)
12cR1https://oracle-base.com/articles/12c/dbms_credential-12cr1