9 Replies Latest reply: Aug 20, 2013 4:56 PM by user8720218 RSS

    File Watcher Not Triggering Program

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

                          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