Oracle File Watcher

Pre-Requisites

File Watcher needs the database JVM Installed and working...

SELECT comp_name, status 
FROM dba_registry 
WHERE comp_name LIKE '%JAVA%'

extjob 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/extjob

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