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
add comment

2 Answers

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
add comment

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
add comment

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.