I am hoping someone can nail this or point me in the right direction because I seem to be hitting a brick wall...
Until last night I was successfully using my file watcher job and then it just stopped "watching" for some unexplainable reason. The file watcher and the event job is pretty straightforward based on several examples provided in this forum and elsewhere. Few details;
Oracle EBS R12 (12.1.3)
Database 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 Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
File Watcher and Job DDL. Please note, this is not the original piece that I was working with when the problem started, but in an effort to diagnose the issue I picked a simpler version from one of the posts on this forum (posted by 'RnR'):
begin
dbms_scheduler.create_credential (
'external_user',
'osuser',
'ospassword');
dbms_scheduler.create_file_watcher (
file_watcher_name => 'file_watcher',
directory_path => '/tmp',
file_name => 'file.txt',
credential_name => 'external_user',
enabled => TRUE);
dbms_scheduler.create_job (
job_name =>'filewatchjob',
queue_spec =>'file_watcher',
job_type =>'plsql_block',
job_action =>'null;',
event_condition => NULL,
enabled =>true);
end;
/
NOTE: I have used different parameter values for directory_path, file_watcher & job name, and credential.
1. I have tested and confirmed that 'Credential' works. This was tested by using another 'External' job which moves files from location A to B.
2. I altered 'REPEAT_INTERVAL' attribute of 'FILE_WATCHER_SCHEDULE' from the default 10mins to 3mins.
3. Created and touched the file "file.txt" at frequent intervals in anticipation it will get pooled.
However,
1. **
schedulerjob_run_details* and **
schedulerjob_log* does not show the said job as expected.
2. The file watcher is created correctly and appears in
dba_scheduler_file_watchers.
3. The file watcher job appears in
user_scheduler_jobs as ENABLED
4. From table
dba_scheduler_global_attribute the value for attribute
FILE_WATCHER_COUNT is 6. Which sounds about right before things stopped.
5. Finally (no quite as I tried several other things in-between), as a last resort the database server was bounced, but still no change.
It almost feels like the background process that queues the file watcher queue has stopped!! Just a guess. But I have no idea how to check for that. I have looked at
dba_running_jobs to see if sys.file_watcher job is running, but no. I am not sure if this is something I should be looking for....
So, please any questions, suggestions, thoughts, etc... will be truly appreciated.
thanks
ilyas
P.S. What tags to use to format code in forum post?
Edited by: ilyas001 on Jul 7, 2011 11:38 PM
Our DBA pointed out a BUG ID on metalink 11715143: FILE WATCHER DOESN'T EXECUTE THE JOB WHEN THERE'S FILES IN THE WATCHED DIRECTORY
The symptoms listed in the bug note are very similar to my problem with two exceptions: 1). Our server is not generating a trace file for filewatcher (it did generate trace when I screwing up with 'External' job types), and 2). As mentioned previously, file watcher was working until 24Hrs back.....
Edited by: ilyas001 on Jul 8, 2011 9:27 AM