This discussion is archived
9 Replies Latest reply: Aug 20, 2013 2:56 PM by user8720218 RSS

File Watcher Not Triggering Program

Centinul Guru
Currently Being Moderated
Version:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I followed the example located here:

ORACLE-BASE: Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 11g Release 2

The only difference (obviously) was that I used a different credential.

I validated that "jssu" was working properly with the same username and password my credential uses:
$ ls -l | grep jssu
-rwsr-x---   1 root     dba        24296 Aug 16 13:43 jssu*
$ jssu
user
password
1
/bin/echo
1
hi
hi
$
I've also used jssu to test a cat of my file, which worked fine:
$ ls -l | grep jssu
-rwsr-x---   1 root     dba        24296 Aug 16 13:43 jssu*
$ jssu
user
password
1
/bin/cat
1
/export/home/username/response.txt
test
$
I could be wrong but I believe the file watcher is triggered by a SYS owned job named 'FILE_WATCHER'. I can see it's been executed multiple times since I created my example:
SQL> select owner,job_name,enabled,run_count,failure_count from dba_scheduler_jobs where job_name='FILE_WATCHER';

OWNER                          JOB_NAME                       ENABL  RUN_COUNT FAILURE_COUNT
------------------------------ ------------------------------ ----- ---------- -------------
SYS                            FILE_WATCHER                   TRUE          18             0
However, I only have one row in DBA_SCHEDULER_JOB_LOG, and the timestamp is the same time I created my example today:
SQL> select log_date,owner,job_name,status from dba_scheduler_job_log where job_name='FILE_WATCHER';

LOG_DATE                       OWNER                          JOB_NAME             STATUS
------------------------------ ------------------------------ -------------------- ------------------------------
06-DEC-11 11.08.43.307029 AM - SYS                            FILE_WATCHER
05:00
I don't see any further runs by my job or Oracle's job in the logs. I do see a periodic j000* trace file in the ADR that runs Oracle's job but I don't see any errors in the trace file.

The only thing I can think of is that it comes down to file system permissions. I've tried creating a file with an owner that matches the username for the credential and that doesn't seem to trigger the job. I've tried setting 777 permissions on the file and that hasn't worked. I can't find any documentation that lists any additional permissions that are necessary for this to work.

Here's the file watcher configuration:
FILE_WATCHER_NAME              ENABL DIRECTORY_PATH                 FILE_NAME                      CREDENTIAL_NAME
------------------------------ ----- ------------------------------ ------------------------------ ------------------------------
TEST_FILE_WATCHER              TRUE  /export/home/username          response.txt                   LOCAL_CREDENTIAL
I'm at a loss. What am I doing wrong?

P.S. All these commands were run directly from the database server (e.g. not trying to create the file client side).
  • 1. Re: File Watcher Not Triggering Program
    mseberg Guru
    Currently Being Moderated
    Hello;

    Created a similar setup using Linux and a different directory. ( instead of /tmp I used a new directory under ORACLE_BASE )

    I get 1 small step farther along, this :
    select log_date,owner,job_name,status from dba_scheduler_job_log where job_name='FILE_WATCHER';
    
    26 Rows
    Is working on mine. However the ( SYS.file_watcher_test_proc ) is not being executed so the table is empty.

    Will work on post update if I have one. Don't believe you have done anything wrong.

    Best Regards

    mseberg


    Looking at note 1109216.1 for compare.

    Edited by: mseberg on Dec 7, 2011 11:18 AM

    Don't see anything wrong. Looking at libpam.so as possible issue. I believe linux or solaris have the same issue here.


    OK I did this on mine ( as root )
    ln -s /lib64/libpam.so.0 /lib64/libpam.so
    Then : ( as the oracle user )
    echo "Yes another test" > /u01/app/oracle/fw/test_file_3.txt
    Then
    SQL> SELECT * FROM file_watcher_output;
    
    MESSAGE
    ----------------------------------------------------------------------------------------------------
    / ()
    /u01/app/oracle/fw/test_file_3.txt (17)
    
    SQL> 
    The / () being junk from me forcing the procedure to fire.

    So you might have "libpam" issue, but you have something else not working before that, a JOB issue

    Edited by: mseberg on Dec 7, 2011 11:39 AM
  • 2. Re: File Watcher Not Triggering Program
    Centinul Guru
    Currently Being Moderated
    mseberg wrote:
    So you might have "libpam" issue, but you have something else not working before that, a JOB issue
    That's what I was thinking. It's like the FILE_WATCHER job has run once, instead of the default 10 minute intervals. So I decided to dive a bit deeper.
    SQL> DECLARE
      2     d VARCHAR2(2000);
      3  BEGIN
      4     DBMS_SCHEDULER.GET_ATTRIBUTE('sys.file_watcher_schedule','repeat_interval',d);
      5
      6     DBMS_OUTPUT.PUT_LINE(d);
      7  END;
      8  /
    FREQ=MINUTELY;INTERVAL=10
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT job_name,schedule_name,enabled FROM dba_scheduler_jobs WHERE job_name='FILE_WATCHER';
    
    JOB_NAME                       SCHEDULE_NAME                  ENABL
    ------------------------------ ------------------------------ -----
    FILE_WATCHER                   FILE_WATCHER_SCHEDULE          TRUE
    
    1 row selected.
    
    SQL> select owner,schedule_name,schedule_type,repeat_interval from dba_scheduler_schedules where schedule_name='FILE_WATCHER_SCHEDULE';
    
    OWNER                          SCHEDULE_NAME                  SCHEDULE_TYP REPEAT_INTERVAL
    ------------------------------ ------------------------------ ------------ --------------------------------------------------
    SYS                            FILE_WATCHER_SCHEDULE          CALENDAR     FREQ=MINUTELY;INTERVAL=10
    
    1 row selected.
    
    SQL> SELECT owner,program_name,program_type,enabled,max_runs FROM dba_scheduler_programs WHERE program_name='FILE_WATCHER_PROGRAM';
    
    OWNER                          PROGRAM_NAME                   PROGRAM_TYPE     ENABL   MAX_RUNS
    ------------------------------ ------------------------------ ---------------- ----- ----------
    SYS                            FILE_WATCHER_PROGRAM           STORED_PROCEDURE TRUE
    
    1 row selected.
    I don't see anything out of the ordinary.
    As a side note, with respect to the libpam item I believe the 64-bit libraries on solaris are in /lib/sparcv9. Here's an ls on that structure:
    $ ls -l | grep pam
    lrwxrwxrwx   1 root     root          11 Jun 28  2008 libpam.so -> libpam.so.1*
    -rwxr-xr-x   1 root     bin        58616 Jun 10  2010 libpam.so.1*
    -rw-r--r--   1 root     bin        21452 Mar 11  2008 llib-lpam.ln
    I also have an SR open with Oracle on this as well so if I get a resolution there I'll post back.

    Here's an updated RUN_COUNT:
    SQL> select owner,job_name,enabled,run_count,failure_count from dba_scheduler_jobs where job_name='FILE_WATCHER';
    
    OWNER                          JOB_NAME                       ENABL  RUN_COUNT FAILURE_COUNT
    ------------------------------ ------------------------------ ----- ---------- -------------
    SYS                            FILE_WATCHER                   TRUE         157             0
    Doing the math that count seems right based on when I enabled the file watcher for the first time. However, there is still only ONE row in DBA_SCHEDULER_JOB_LOGS for this job with a date that matches the time I initially created the file watcher.

    Edited by: Centinul on Dec 7, 2011 1:15 PM
  • 3. Re: File Watcher Not Triggering Program
    Centinul Guru
    Currently Being Moderated
    I'd be interested to see your output of:
    select object_name,object_type,status from dba_objects where object_name like 'SCHEDULER_FILEWATCHER_Q';
  • 4. Re: File Watcher Not Triggering Program
    mseberg Guru
    Currently Being Moderated
    Here it is :
    SQL> select object_name,object_type,status from dba_objects where object_name like 'SCHEDULER_FILEWATCHER_Q';
    
    OBJECT_NAME
    ----------------------------------------------------------------------------------------------------
    OBJECT_TYPE         STATUS
    ------------------- -------
    SCHEDULER_FILEWATCHER_Q
    QUEUE               VALID
    
    SCHEDULER_FILEWATCHER_Q
    UNDEFINED           VALID
    
    
    SQL> 
    Let me know if you want anything else.

    Agreed, you should have a log for every run I'm thinking.

    Best Regards

    mseberg
  • 5. Re: File Watcher Not Triggering Program
    Centinul Guru
    Currently Being Moderated
    Thanks, that's the same output I"m getting so nothing there. Hopefully Oracle can shed some light on this.
  • 6. Re: File Watcher Not Triggering Program
    mseberg Guru
    Currently Being Moderated
    My best guess is :

    The directory must be readable by the user that oracle runs as (as well as the os user specified in the credential ).

    For my test I used the Oracle user and a directory owned by that user.

    This might explain why your first shows many rows and the second only one.

    I might consider changing this and testing.

    Best Regards

    mseberg
  • 7. Re: File Watcher Not Triggering Program
    Centinul Guru
    Currently Being Moderated
    mseberg wrote:
    My best guess is :
    The directory must be readable by the user that oracle runs as (as well as the os user specified in the credential ).
    I've had an system administrator validate that the the user oracle runs as can read the directory.
    I might consider changing this and testing.
    I'll be annoyed if you need to have a credential for the Oracle software owner and use that. That seems to defeat the purpose of credentials altogether. It has to be something else. It doesn't hurt to test it though. I'll see if I can get a system administrator to help me out.

    Thanks!
  • 8. Re: File Watcher Not Triggering Program
    Centinul Guru
    Currently Being Moderated
    As of this point in time Oracle has marked my problem as a bug. If/when I get more information I'll try and post it to others that have this problem.
  • 9. Re: File Watcher Not Triggering Program
    user8720218 Newbie
    Currently Being Moderated

    Hi everyone,

     

      Any answers from Oracle on that File Watcher issue?

     

      Did anyone get it to work.

     

      I had a file watcher working but now it has stopped running. I am trying to recreate everything

    and get the File Watcher to work again.

     

      Any recommendations are greatly appreciated.

     

      Have a good day.

     

    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points