Database Software

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

/R

Edited by: 961345 on 2012-nov-14 10:29