5 Replies Latest reply: Oct 25, 2012 3:18 AM by xxsawer RSS

    Oracle scheduler again - file watcher

    xxsawer
      Hello guys,
      I need a help with Oracle Scheduler again.
      What I need to do is to run a job when a file arrives to some specific folder.
      I have read the tutorial here:
      http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm

      SELECT * FROM V$VERSION;
      
      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      "CORE     11.2.0.1.0     Production"
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      After solving this for several hours I just copied the example which is at the end of the tutorial and ran it.
      begin
        dbms_scheduler.create_credential(
           credential_name => 'watch_credential',
           username        => 'xxxxx',
           password        => 'xxxxx');
      end;
      /
       
      create table eod_reports (when timestamp, file_name varchar2(100), 
         file_size number, processed char(1));
       
      create or replace procedure q_eod_report 
        (payload IN sys.scheduler_filewatcher_result) as 
      begin
        insert into eod_reports values 
           (payload.file_timestamp,
            payload.directory_path || '/' || payload.actual_file_name,
            payload.file_size,
            'N');
      end;
      /
       
      begin
        dbms_scheduler.create_program(
          program_name        => 'eod_prog',
          program_type        => 'stored_procedure',
          program_action      => 'q_eod_report',
          number_of_arguments => 1,
          enabled             => false);
        dbms_scheduler.define_metadata_argument(
          program_name        => 'eod_prog',
          metadata_attribute  => 'event_message',
          argument_position   => 1);
        dbms_scheduler.enable('eod_prog');
      end;
      /
       
      begin
        dbms_scheduler.create_file_watcher(
          file_watcher_name => 'eod_reports_watcher',
          directory_path    => '/home/xxxxx/xxxxx/xxxx/xxxx/xxxxx/xxxx/xxxxx',
          file_name         => 'eod*.txt',
          credential_name   => 'watch_credential',
          destination       => null,
          enabled           => false);
      end;
      /
       
      begin
        dbms_scheduler.create_job(
          job_name        => 'eod_job',
          program_name    => 'eod_prog',
          event_condition => Null,
          queue_spec      => 'eod_reports_watcher',
          auto_drop       => false,
          enabled         => false);
        dbms_scheduler.set_attribute('eod_job','parallel_instances',true);
      end;
      /
       
      exec dbms_scheduler.enable('eod_reports_watcher,eod_job');
      I did three changes to the code in comparison with the example
      1) Changed username and password in the credentials (username and password which I use for login to the OS)
      2) Changed directory path for the file watcher (I started the directory path with the /home/...)
      3) Set the event_condition parameter to Null

      Finally I ran this as SYS to make the watcher check the folder every second:
      BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE', 'REPEAT_INTERVAL',
          'FREQ=SECONDLY');
      END;
      /
      Still nothing happens when move some file (e.g. eod1.txt) into specified folder.
      SELECT * FROM USER_SCHEDULER_JOBS;
      JOB_NAME = EOD_JOB
      ENABLED = TRUE
      STATE = SCHEDULED
      RUN_COUNT = 0

      Anybody can help me with this?
        • 1. Re: Oracle scheduler again - file watcher
          damorgan
          Two thoughts.

          1. Recreate the demo code environment exactly and see if it runs.

          2. Have you queried the supporting tables to see if they are reporting an error? For example FAILURE_COUNT in dba_scheduler_jobs and ERROR# in DBA_SCHEDULER_JOB_RUN_DETAILS?
          If not see what's there.
          • 2. Re: Oracle scheduler again - file watcher
            xxsawer
            Hi damorgan,
            thanks for reply, I will be testing this today to make this run...

            ad 1) I will try this as well, but anyway I need to change the username and password for the credential object (to match with the user which is created in the OS) and removing the event condition should also mean no harm.
            Maybe oracle cannot login to the OS (but credentials are correct, the user has access everywhere). Is the log attempt somewhere logged?
            Or maybe just oracle cannot find the path, but why, the path is also correct. Again, is somewhere logged that some path was not found?

            ad 2)
            SELECT * FROM DBA_SCHEDULER_JOBS;
            The imporant job properties:

            ENABLED = TRUE
            STATE = SCHEDULED
            RUN_COUNT = 0
            FAILURE_COUNT = 0
            LAST_START_DATE = null

            So from this is clear the job was never ran. The DBA_SCHEDULER_JOB_RUN_DETAILS doesn't display anything for this job, because it was never ran...
            • 3. Re: Oracle scheduler again - file watcher
              xxsawer
              Hi,
              just to update the status of this issue.
              After many hours of reading documentation, running everything again from beginning and searching the internet, I found this thread:
              Problems with credentials

              I am also running on linux, so I tried to create a job running an external script and also got the error about autentication.
              SELECT * FROM USER_SCHEDULER_JOB_LOG;
              STATUS = FAILED
              SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS;
              ERROR# = 27370
              ADDITIONAL_INFO = "EXTERNAL_LOG_ID="job_542302_197412",
              ORA-27369: úloha typu EXECUTABLE selhala s kódem ukon¿ení: Argument list too long
              STANDARD_ERROR="Launching external job failed: Invalid username or password""

              After creating of symbilic link the external job started to work
              ln -s /lib64/libpam.so.0 /lib64/libpam.so
              The stupid thing thing is that if you create an external job and if fails for some reason (in this case the credentials are bounded to directly to the job), you will see an error in USER_SCHEDULER_JOB_RUN_DETAILS because the job ran and failed for some reason. So you see whats wrong.
              If you are creating a file watcher, the credentials are bounded to the file watcher and there is no view or something where you could find whats wrong.

              I recreated all objects and it seems to be working somehow.
              What I found is that the watcher is relying on the date and time when the file was created. So if I copy some file created yesterday into target folder, it will not trigger the file watcher.
              This leads me to another question. By default the file watcher checks the presence of new file every 10 minutes. According to the documentation here:

              http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm

              this can be changed e.g. like
              BEGIN
                DBMS_SCHEDULER.SET_ATTRIBUTE(Name => 'FILE_WATCHER_SCHEDULE',
                                             Attribute => 'REPEAT_INTERVAL',
                                             Value => 'FREQ=SECONDLY;INTERVAL=2');
              END;
              /
              The problem is that even when setting this, the file watcher is not checking the folder every 2s. I think because the watcher relies on creation date of a file, the lowest possible value would be 2 minutes.
              Am I right or is it somehow possible to make the interval lower than a minute?

              Edited by: xxsawer on 24.10.2012 9:26
              • 4. Re: Oracle scheduler again - file watcher
                damorgan
                Since it appears the issue is related to permissions ... focus on that aspect.

                First confirm that the operating system user "oracle" can see the directory and the file. You can easily do this with UTL_FILE. Can you read it?

                Also, if you can open SQL*Plus on the instance host (not from your desktop) you can use the host command to shell out to the operating system and determine the exact nature of the issue if it is one of permissions.

                If UNIX/Linux your System Admin should be able to get to the bottom of it quickly.
                • 5. Re: Oracle scheduler again - file watcher
                  xxsawer
                  Hi Damorgan,
                  thanks for reply. Maybe I was not exact in my previous post. After creating the symbolic link to for libpam.so.0 the file watcher started to work. So the problem was the missing symbolic link.
                  Now on our system in /lib64 it looks like:
                  lrwxrwxrwx  1 root root      11 2012-10-24 16:33 libpam.so -> libpam.so.0*
                  lrwxrwxrwx  1 root root      17 2010-01-21 10:54 libpam.so.0 -> libpam.so.0.81.12*
                  -rwxr-xr-x  1 root root   51904 2009-02-21 06:31 libpam.so.0.81.12*
                  We are running Suse linux
                  Distributor ID: SUSE LINUX
                  Description:    SUSE Linux Enterprise Server 11 (x86_64)
                  Release:        11
                  Codename:       n/a
                  Before making this link I think Oracle even didn't try to connect to the system (I didn't find any logs in /var/log/messages).
                  I referenced to the thread with external job, because the issue there was the same. The good thing when running an external job is, that the reason why it failed is logged and you can see it in the USER_SCHEDULER_JOB_RUN_DETAILS view.
                  It is logged because in case of external job the credentials are used by the job itself. The job is running the script.
                  Example
                  BEGIN
                     DBMS_SCHEDULER.CREATE_CREDENTIAL(credential_name => 'TEST_CREDENTIALS',
                                                      username => 'xxxxx',
                                                      password => 'xxxxx',
                                                      comments => 'Test credentials');
                  END;
                  /
                  
                  BEGIN
                     DBMS_SCHEDULER.CREATE_JOB(job_name => 'TEST_CREDENTIALS_JOB',
                                               job_type => 'EXECUTABLE',
                                               job_action => '/home/xxxxx/tmp/xxx/jobs/job.sh',
                                               job_class => 'SOME_CLASS',
                                               credential_name => 'TEST_CREDENTIALS',
                                               start_date => SYSDATE,
                                               repeat_interval => 'FREQ=SECONDLY;INTERVAL=2',
                                               enabled => True,
                                               auto_drop => False);
                  END;
                  /
                  In case of file watcher, the file watcher uses the credentials. If I understand it correctly the file watcher should periodically check the target folder using specified credentials. If it detects new file (which fulfills conditions), it sends an event to the job and so on...

                  Example
                  BEGIN
                     DBMS_SCHEDULER.CREATE_CREDENTIAL(credential_name => 'HOST_SIMU_CREDENTIALS',
                                                      username => 'xxxx',
                                                      password => 'xxxx',
                                                      comments => 'Credentials used by file watcher');
                  END;
                  /
                  -- File watcher
                  BEGIN
                     DBMS_SCHEDULER.CREATE_FILE_WATCHER(file_watcher_name => 'HOST_SIMU_FILE_WATCHER',
                                                        directory_path => '/home/xxxx/tmp/xxx/jobs',
                                                        file_name => '*',
                                                        credential_name => 'HOST_SIMU_CREDENTIALS',
                                                        steady_state_duration => '0 0:00:01',
                                                        comments => 'Host-side file watcher checking for files exported by WCS',
                                                        enabled => True);
                  END;
                  /
                  .
                  .
                  .
                  So in case the file watcher fails to login, there is no view where you can check what is wrong! The problem is not visible in the USER_SCHEDULER_JOB_RUN_DETAILS simply because the job was never run (the watcher never sent an event because it could not login).

                  Basicaly I think this is Oracle bug, because the link should be created during installation and for sure not when somebody creates job or watcher.

                  Regarding your questions...
                  Yes, oracle user and all users that I used in the credential object had access to the target folder. When I was desperate enough, I set full rights on target folder and was trying to use root in the credentials :) I was also creating the folder in ORACLE_HOME directory and so on.

                  So one again, the problem were not access rights, but the missig symbolic link.

                  Edited by: xxsawer on 25.10.2012 1:16