Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2010
    Posts
    5

    Unanswered: Strange dbms_scheduler behaior: job won't run under certain conditions (listed)

    I am a java programmer, not a dba, so bear with me.

    I'm using Oracle 10g on a Windows 2003 Server platform.

    I have written a stored procedure that creates a dbms_scheduler job. The job executes another stored procedure. The start_date is set to 'systimestamp' so that it will run immediately upon creation, and the repeat_interval is 'freq=daily'.

    When the server that hosts Oracle is on its own, the job is created normally, it runs once immediately, the stored procedure does what it was intended to do, and the job runs daily as intended.

    However, the system for which my application is written is comprised of three servers, the one that hosts the Oracle database, and two others (also Windows 2003 Server) that, when booted and running other parts of the application, establish ODBC connections with the Oracle database on the Oracle host.

    When I attempt to create the job with all three servers (and associated applications) running (i.e. ODBC connections established) the job will not run.

    It does show up in the dba_scheduler_jobs view with a next_run_date that is milliseconds BEFORE the listed start_date. The run_count is 0, enabled is true, it is not listed as 'broken.'

    If I let the system sit like that, the job will never run.

    However, as soon as I shut down the other two servers (i.e. remove the ODBC connections) the job will run on the Oracle host server, the next_run_date displays the correct time for running the next day, and run_count is incremented.

    For some reason, having the ODBC connections from external servers seems to lock the whole scheduling process.

    Can anyone tell me why this is and how I can correct it? The job needs to be able to run immediately on creation, and then on schedule (daily) even when the other servers have active ODBC connections to the db.

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone tell me why this is and how I can correct it?
    It is a challenge to debug code that I can not see.

    Consider delaying the start of the job by a "short delay".

    Please consider using CUT & PASTE to show us what exists as opposed to describing what you think you observe.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2010
    Posts
    5
    anacedent,

    here is the stored procedure that creates the job:

    PROCEDURE plan_manager_start( plan_age IN NUMBER ) IS
    BEGIN
    DBMS_SCHEDULER.create_job(
    job_name => 'plan_purge_job',
    job_type => 'STORED_PROCEDURE',
    job_action => 'plan_state_mgr.purge_plans',
    number_of_arguments => 1,
    start_date => SYSTIMESTAMP,
    repeat_interval => 'freq=daily',
    end_date => NULL,
    job_class => 'DEFAULT_JOB_CLASS',
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'purge plans older than a specified age');


    DBMS_SCHEDULER.set_job_argument_value(
    job_name => 'plan_purge_job',
    argument_position => 1,
    argument_value => plan_age);

    DBMS_SCHEDULER.enable('plan_purge_job');
    END;


    I didn't post it in my initial message, because I know that it works.

    I don't think I observed what I described. I did observe what I described.

    If all of your replies are going to exhibit the same level of Geek-snobbery...I'd rather wait for assistance from someone else, thanks.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    take anacedent's comments with a grain of salt. Very sharp programmer but also very sharp of tongue. Have you tried putting a TAR into metalink. This sounds like it might be a bug.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2010
    Posts
    5
    beilstwh,

    I'm not familiar with the terms TAR or metalink. I'm assuming that it is some kind of Oracle bug archive, or Oracle bug reporting process. I'll research that and take your advice.

    I do pay for Oracle support, and have an active service request regarding this problem. Progress on a resolution, however, has been slow.

    Thanks for your response.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Yes, a TAR is an oracle service request and

    metalink.oracle.com

    is the web address for oracle support. If they are taking to long, ask for an escalation.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I do pay for Oracle support, and have an active service request regarding this problem.
    If a bug, then a reproducible test case is needed.
    How can I obtain same results on my system as OP?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2010
    Posts
    5

    More Information

    After searching several forums and Oracle 'notes' and 'bug reports' I found Oracle note 313102.1 (Jobs are no longer executing automatically).

    I created the following dbms_scheduler job while the ODBC connections from my other two servers were connected to the database on the Oracle host:

    dbms_scheduler.create_job(
    job_name => 'dave_test_job',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin select systimestamp from dual; end;',
    start_date => systimestamp,
    repeat_interval => 'freq=minutely',
    end_date => NULL,
    enabled => TRUE,
    comments => 'test job');

    The job doesn't do much, but I just wanted to have a job that would run each minute so I could test the scheduling.

    As expected, the job did not run after it was created. Several minutes after creation, 'select run_count from dba_scheduler_jobs;' showed 0; the job had not run.

    I began working through the checklist in Oracle note 313102.1.

    In item 2 (show parameter job_queue_processes) I noted that the value was 10.

    In item 7 ( select * from dba_jobs_running; ) I noted that there were 10 jobs running.

    Item 10 of the checklist recommends setting the value of job_queue_processes to 0, and then back to its original value in order to recycle the 'CJQ process(es)'.

    When I set the value of job_queue_processes to 0, I looked at the run_count of my dbms_scheduler job, and it had incremented. It continued incrementing once per minute as it should.

    'select * from dba_jobs_running;' showed no jobs running. (I think this only shows dbms_job information as opposed to dbms_scheduler information, but I am not sure.)

    When I set the value of job_queue_processes back to 10 (its original value), the dbms_scheduler job cease executing on schedule; the run_count stopped incrementing.

    I changed the value of job_queue_processes back and forth between 0 and 10 several times. Each time it was set to 0, the dbms_scheduler job would run as scheduled. Each time it was set back to 10, the dbms_scheduler job would cease running.

    From what I've read, dba_jobs_running contains information about 'dbms_job' jobs as opposed to 'dbms_scheduler' jobs. In fact, I believe the Oracle note was written for dbms_job problems that were experienced prior to Oracle 10g, when dbms_scheduler was introduced.

    Is this correct?

    What, exactly, is the parameter job_queue_processes controlling (processes for dbms_job processing, or all scheduled job processing including dbms_scheduler jobs)?

    Everything I've read says that job_queue_processes should NOT be 0, so I'm sure that I shouldn't leave it set to 0. Why is setting this value to 0 enabling the running of my dbms_scheduler jobs?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >job_name => 'dave_test_job',
    has a fatal flaw & won't run

    dbms_sceduler works for me

    Code:
      1* select last_start_date, next_run_date from dba_scheduler_jobs where job_name = 'BCM_TEST_JOB'
    19:27:17 SQL> /
    
    LAST_START_DATE
    ---------------------------------------------------------------------------
    NEXT_RUN_DATE
    ---------------------------------------------------------------------------
    22-JAN-10 07.26.54.153610 PM -08:00
    22-JAN-10 07.27.54.200000 PM -08:00
    
    19:27:18 SQL> /
    
    LAST_START_DATE
    ---------------------------------------------------------------------------
    NEXT_RUN_DATE
    ---------------------------------------------------------------------------
    22-JAN-10 08.10.54.092230 PM -08:00
    22-JAN-10 08.11.54.000000 PM -08:00
    
    
    20:11:52 SQL>
    Last edited by anacedent; 01-23-10 at 00:12.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jan 2010
    Posts
    5

    Resolution

    After much digging, I was able to identify the problem and verify my findings with Oracle support:

    I sent:
    "Raising the 'job_queue_processes' parameter from 10 to 20 seems to have corrected our problem."

    "The application that I am maintaining already makes much use of the DBMS_JOB functionality of the Oracle DBMS. Another developer had added additional DBMS_JOB jobs as part of his maintenance effort. These new jobs brought the total that could be running at one time to 13 jobs."

    "When I looked at DBA_JOBS_RUNNING, I would see 10 jobs running. Even though DBA_JOBS_RUNNING only contains information on DBMS_JOB jobs, the JOB_QUEUE_PROCESSES parameter appears to control both DBMS_JOB jobs and DBMS_SCHEDULER jobs. If the process limit has been reached because of too many DBMS_JOB jobs running, additional DBMS_SCHEDULER jobs will not be allowed to run."


    Oracle reply:
    "I have found the following information regarding this behavior. It appears it is expected behavior, but it is not fully documented."

    "Bug 3817936 - closed as not a bug"

    "This is the intended behavior, not a bug. If the JOB_QUEUE_PROCESSES
    parameter is not 0, then it's value will take precedence over the
    MAX_JOB_SLAVE_PROCESSES attribute. However, if the JOB_QUEUE_PROCESSES
    parameter is 0, then the MAX_JOB_SLAVE_PROCESSES attribute will act as the
    upper limit for the number of slave processes. I modified the test case for
    this bug and verified that this is indeed the case."
    .
    "At some point in the future, it is expected that DBMS_JOB will be updated so
    that it is implemented by the existing DBMS_SCHEDULER code. When this
    happens, the JOB_QUEUE_PROCESSES parameter will no longer be needed and the
    MAX_JOB_SLAVE_PROCESSES attribute will always act as the upper limit for the
    number of slave processes."

    "Also:

    Note that the init parameter max_job_queue_processes does not exist. When
    job_queue_processes = 0, you will not be able to run any dbms_job jobs. At
    that point you will only be able to run dbms_scheduler jobs. It will also
    switch to the scheduler's auto start/stop feature which stops and starts the
    job coordinator and the job slaves for dbms_scheduler jobs on an as needed
    basis."

    "If you set job_queue_processes to 0, and you don't have any jobs to run you
    will not see the coordinator process, nor any of the slave processes. Once
    you start creating dbms_scheduler jobs you will start seeing the coordinator
    process and the slave processes. The scheduler will automatically determine
    (based on the load/configuration of the machine) what it thinks the proper
    maximum is for the number of slaves. You can lower this self determined
    maximum by setting the max_job_slave_processes attribute."

    "Please let me know if you have more questions on this issue. I will search some more to verify this behavior is indeed not documented. If I still do not find references, I will create a new Note so that this will be more visible for you and for us as well."

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Thanks for posting the follow up status
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •