3 Replies Latest reply: Dec 13, 2012 5:41 PM by 964348 RSS

    Filewatcher problems - again

      Hi all

      Been reading almost every post in this subject, and tried almost every suggested solution found, but still...

      First some technical info:

      OS: Linux 2.6.18-308.11.1.e15 ... EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
      Ora DB: ver. 11g R2

      A short description of the problematic setup:
      A Linux directory (+/home/infiles/bs/in+) is (should be) monitored by a dedicated file watcher with owner=+cts+ (other than sys)
      Oracle user is oraprd, belonging to groups: dba wheel pb
      Credential is created, infiles, in Linux belonging to groups: users pb, owner = cts
      All scheduling objects (filewatcher, program,metadata_argument, job) are created with cts as owner using dbms_scheduler.create_object style from within SQLPLUS (logged in as user cts)
      The file watcher is using above mentioned and dedicated Credential - which is verified through a simple test job calling a shell-script touching a file, using this credential, so this cred works OK). I have also verified it by su to oraprd (oracle OS user) and then tried jssu with this credential (uname/pwd) and this also works OK - also verified by Linux Audit-logs as res=success.
      A program is created referring a Store Procedure within a PL/SQL package
      A job is created having it's action referring this program that should trigger that package procedure inserting file names from files found in monitored direcrtory into a DB table

      Each time I touch the file in Linux directory +/home/infiles/bs/in+ I can see in the tailed audit-log that jssu have been accessed with res=success
      I also see, some short time after each touch, in a trace file ORAINSTNAME_j00#.trc (name differs over time of course) the following logs:
      *** 2012-11-14 18:43:54.074
      *** SESSION ID:(27.5) 2012-11-14 18:43:54.074
      *** CLIENT ID:() 2012-11-14 18:43:54.074
      *** SERVICE NAME:(SYS$USERS) 2012-11-14 18:43:54.074
      *** MODULE NAME:(DBMS_SCHEDULER) 2012-11-14 18:43:54.074
      *** ACTION NAME:(FILE_WATCHER) 2012-11-14 18:43:54.074

      FILE_TRANSFER error is:

      Don't know what this means at all.
      And there is no log history for the job (not in job_log nor in job_run_detail) so I assume the Job is never triggered, but it seems the jssu is called (due to Linux audit logs) and also something happens with the File watcher (but the SYS default one, not my custom created under cts schema)

      Worth mentioning is also that the Job is referring, in the queue_spec, the custom created file watcher name and I've also tried a several variations on the event_condition attribute of the Job (right now it's set to NULL => 1=1)
      Both oraprd and infiles users have read access to the monitored directory +/home/infiles/bs/in+...

      So, here I stand, not knowing how to verify the Filewatcher funtionality or what to do next to track down the problem.

      Oh, almost forgot, server guys have implemented lots of hardening on the dedicated server the DB runs on, but still, directory permissions still seems OK for both the oracle user (+oraprd+) and the credential user (+infiles+)...

      So my questions are:
      What more can I do to verify things are correctly setup ?
      Or better off, what's causing my Filewatcher not triggering the Job/Program . and hence gets the file name inserted into DB table?
      Is there a way to monitor the Filewtcher activity or the SYS.SCHEDULER_FILEWATCHER_Q - is this queue somewhat involved?

      May the force be with me...


      Edited by: 961345 on 2012-nov-14 10:29
        • 1. Re: Filewatcher problems - again
          have u found any solution?
          • 2. Re: Filewatcher problems - again

            Unfortunately we have not, an SR has been raised but suggestions made by Oracle support has not helped...
            Not a single row appears in SYS.SCHEDULER_FILEWATCHER_QT table, which should indicate problems with the built in file watcher and not the user created (or cerdentials)
            Next step for us is to raise a Bug on this...
            • 3. Re: Filewatcher problems - again
              Additional info on the subject

              There is a table in the SYS schema, SCHEDULER_FILEWATCHER_QT, that I guess should be populated with info on files detected in the "watched" directory.
              In our dev and test environment this table is filled with info on watched files (column USER_DATA) but in prod environment this table is, and has always been empty...

              So, this even more makes me think the problem lies within some sys object or configuration... and not in any user created object, cause they should only matter when events get queued and records put into this above mentioned sys table.


              Edited by: 961345 on 2012-dec-13 15:41