Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't working. After scaling back the code to a bare minimum, here are somethings I have already done.

  • Verify that files are being created in the folder monitored - They are.
  • Verify that the procedure can be run by itself - It can.
  • Verify that the File Watcher shows up in DBA_Scheduler_File_Watchers and is enabled. - It does and is.
  • Verify that DBA_Scheduler_Job_Run_Details shows a successful execution - It does NOT show any entries for this job.
  • Check the alert log - Nothing interesting.
  • Check for other trace files - I am getting trace files mattching *_j000_*.trc, but they just have this: FILE_TRANSFER error is: with nothing after it.

Could someone explain what I am doing wrong or give me a way to troubleshoot this further? Here is my test code:

--Create Credential.
BEGIN
    sys.dbms_scheduler.create_credential(
        username => 'oracle',
        password => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
        database_role => NULL,
        windows_domain => NULL,
        comments => NULL,
        credential_name => 'TestCredential'
    );
END;
/


CREATE TABLE FilesFound(FoundDate Date);


CREATE OR REPLACE PROCEDURE TestProcedure(iFileInfo In sys.scheduler_filewatcher_result) AS
BEGIN
   INSERT INTO FilesFound VALUES (sysdate);
   COMMIT;
END;
/


BEGIN
   DBMS_SCHEDULER.create_program(
      program_name => 'TestProgram',
      program_type => 'stored_procedure',
      program_action => 'TestProcedure',
      number_of_arguments => 1,
      enabled => False);
   DBMS_SCHEDULER.define_metadata_argument(
      program_name => 'TestProgram',
      metadata_attribute => 'event_message',
      argument_position => 1);
END;
/



BEGIN
dbms_scheduler.create_file_watcher(
   file_watcher_name => 'TestFileWatcher',
   directory_path => '/u01/test',
   file_name => '*.*',
   credential_name => 'TestCredential',
   destination => NULL,
   enabled => False);
END;
/


BEGIN
   dbms_scheduler.create_job(
   job_name => 'TestJob',
   program_name => 'TestProgram',
   event_condition => NULL,
   queue_spec => 'TestFileWatcher',
   auto_drop => False,
   enabled => False);

   --Enable Everything.
   dbms_scheduler.enable('TestProgram, TestFileWatcher, TestJob');
end;
/

--Set a one minute check interval.
BEGIN
  DBMS_SCHEDULER.set_attribute(
    'file_watcher_schedule',
    'repeat_interval',
    'freq=minutely; interval=1');
END;
/

--Create a file.
DECLARE
  vFile utl_file.file_type;
BEGIN
  EXECUTE IMMEDIATE 'create or replace directory TESTDIRECTORY as ''/u01/test''';
  vFile := utl_file.fopen('TESTDIRECTORY', 'TestFileWatcher.txt', 'w', NULL);
  utl_file.put_line(vFile, 'File has arrived '||SYSTIMESTAMP, TRUE);
  utl_file.fclose(vFile);
END;
/


--Delay to give time for file to appear.
BEGIN
   DBMS_LOCK.SLEEP(120);
END;
/

--Check the table.
BEGIN
   FOR vx IN (select count(*) ct from sys.FilesFound) LOOP
      If (vx.ct = 0) Then
         DBMS_Output.Put_Line('Failed - No File Watcher Activity.');
      Else
         DBMS_Output.Put_Line('Success - File Watcher Activity Found.');
      End If;
   END Loop;
END;
/

--Cleanup.
EXECUTE dbms_scheduler.drop_job(job_name => 'TestJob');
EXECUTE dbms_scheduler.drop_program(program_name => 'TestProgram');
EXECUTE dbms_scheduler.drop_file_watcher(file_watcher_name => 'TestFileWatcher');
EXECUTE DBMS_SCHEDULER.drop_credential(credential_name => 'TestCredential');
drop table FilesFound;
drop procedure TestProcedure;
drop directory TestDirectory;
share|improve this question

2 Answers 2

So far sever things have been identified as problems, but we don’t yet have this working yet.

  • Install libpam.so if it is not already installed.
  • Modify $ORACLE_HOME/rdbms/admin/externaljob.ora’s contents to specify the user and group that owns the oracle process.
  • Install patch 10363801 for a bug in removing jobs on 11.2.0.2.
  • Bug 17256073 has been opened for the Oracle development team to investigate further.

I'll update this when I have more information.

share|improve this answer

To use file watchers, the database Java virtual machine (JVM) component must be installed.

Check whether java is installed :

COMP_NAME                                          STATUS
-------------------------------------------------- -----------
Oracle Expression Filter                           VALID
Oracle Enterprise Manager                          VALID
Oracle Workspace Manager                           VALID
Oracle Database Catalog Views                      VALID
Oracle Database Packages and Types                 VALID
JServer JAVA Virtual Machine                       VALID
Oracle XDK                                         VALID
Oracle Database Java Packages                      VALID

8 rows selected.

SQL> l
  1* select comp_name, status from dba_registry
share|improve this answer
    
That's a good thing to check, but in my case is not the problem. –  Leigh Riffel Jul 30 '13 at 18:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.