Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

pslu (Programmer)
24 Sep 01 8:39
Hi All
I am facing a typical problem in DBMS_JOB Package.

I have two servers, both are running on SunOS 5.6. and placed at different time zone locations.
One is on IST time Zone,
Second one is on GMT time Zone,
Oracle version is 8.0.5.

Please follow the problem.... This problem is on GMT Time zone server.
/*********************
Job submit Code:
----------------
declare
    jobnumber number;
begin
    DBMS_JOB.SUBMIT(jobnumber,'insert_rec;',sysdate,'trunc(sysdate+1)');
    COMMIT;
    dbms_output.put_line('Job Number is :'||to_char(jobnumber));
end;
Note: insert_rec is a procedure,  which inserts a record in one of the table. This I have scheduled to run every day at 00:00 hours that is every day midnight.
I have submitted this piece of code on both servers, When IST time zone server is working perfectly where as GMT time zone server is not working properly
Please see the output of following query on GMT time zone server.
The output :
------------
SQL> select job,last_date,last_sec,next_date,next_sec from user_jobs;
JOB     LAST_DATE     LAST_SEC     NEXT_DATE     NEXT_SEC
----    --------------   -------------    ---------------  ------------
64     17-SEP-01     09:45:48     00-DECEMB     00:00:00

SQL> select job,last_date,last_sec,to_char(next_date,'MM-DD-YYYY') next_date,next_sec from user_jobs;
JOB     LAST_DATE     LAST_SEC     NEXT_DATE     NEXT_SEC
----    --------------   -------------    ---------------  ------------
64     17-SEP-01     09:45:48     00-00-0000     00:00:00


Actually the     next_date should be 18-SEP-01, On 17th sep ,
        next_date should be 19-SEP-01, On 18th sep ,
        ... and so on.

*************************************************************************
I have waited till 21 sep it doesn't work.

I am not able to dig out the problem.

I thought It would be the problem of DBMS_JOB package, That is why I have re-created with catproc.sql.
But I am facing the same Error.

I able to run successfully on the other servers which are placed at other places.

For your Information All servers OS is SunOS5.6 and Oracle 8.0.5 Production.

Time zone of different Server (OS)
    IST
    GMT

And also one more thing on GMT time zone server, DBMS_ASYNCRPC_PUSH and DBMS_DEFFER_SYS_PART1 Package bodies are in
INVALID state. Will these effect the DBMS_JOB Packages ?

and on both servers parameters relating to the JOB is same, i.e.
job_queue_interval = 60
job_queue_keep_connections = FALSE
job_queue_processes = 0

Please find the snp backgroud process difference.


No Problem server (IST)

$ ps -ef |grep "snp"
  oracle  7512     1  0 03:54:06 ?        0:00 ora_snp5_TMP
  oracle  7516     1  0 03:54:06 ?        0:00 ora_snp7_TMP
  oracle  7520     1  0 03:54:06 ?        0:00 ora_snp9_TMP
  oracle  7510     1  0 03:54:06 ?        0:00 ora_snp4_TMP
  oracle  7504     1  0 03:54:05 ?        0:00 ora_snp1_TMP
  oracle  7518     1  0 03:54:06 ?        0:00 ora_snp8_TMP
  oracle  7508     1  0 03:54:06 ?        0:00 ora_snp3_TMP
  oracle  7502     1  0 03:54:05 ?        0:00 ora_snp0_TMP
  oracle  7506     1  0 03:54:05 ?        0:00 ora_snp2_TMP
  oracle  7514     1  0 03:54:06 ?        0:00 ora_snp6_TMP

Problomatic Server (GMT)
 $ ps -ef | grep "snp"
  oracle 28819     1  0   Sep 17 ?        0:36 ora_snp3_TMP
  oracle 28817     1  0   Sep 17 ?        0:10 ora_snp2_TMP
  oracle 28821     1  0   Sep 17 ?        0:39 ora_snp4_TMP
  oracle 28815     1  0   Sep 17 ?        1:09 ora_snp1_TMP
  oracle 28831     1  0   Sep 17 ?        1:05 ora_snp9_TMP
  oracle 28829     1  0   Sep 17 ?        1:45 ora_snp8_TMP
  oracle 28827     1  0   Sep 17 ?        1:24 ora_snp7_TMP
  oracle 28825     1  0   Sep 17 ?        1:15 ora_snp6_TMP
  oracle 28823     1  0   Sep 17 ?        1:20 ora_snp5_TMP
  oracle 28813     1  0   Sep 17 ?        0:41 ora_snp0_TMP

Some more finding on problematic Server.

SQL> exec dbms_job.next_date(64,sysdate+1/480);

PL/SQL procedure successfully completed.

SQL> commit;

SQL>     select to_char(next_date,'dd-mm-yyyy hh24:mi:ss') nextdate,to_char(last_date,'dd-mm-yyyy hh24:mi:ss') lastdate,
    broken,total_time,failures,job from user_jobs;

    NEXTDATE                    LASTDATE                    B     TOTAL_TIME       FAILURES            JOB
    -------------------         -------------------         -     ----------         ----------         ----------
    21-09-2001 08:47:00     21-09-2001 08:36:01     N              0                  0                64

After three minutes,

It added new record and showing this.

SQL>     select to_char(next_date,'dd-mm-yyyy hh24:mi:ss') nextdate,to_char(last_date,'dd-mm-yyyy hh24:mi:ss') lastdate,
    broken,total_time,failures,job from user_jobs;

    NEXTDATE                    LASTDATE                    B     TOTAL_TIME       FAILURES            JOB
    -------------------         -------------------         -     ----------         ----------         ----------
    00-00-0000 00:00:00     21-09-2001 08:47:00     N              0                  0                64

After sometime I have selected from the dummy table it showing same number of records.

After some time

SQL> exec dbms_job.run(64);

PL/SQL procedure successfully completed.

It sp was executed  and It added one more record, After this when I select from the user_jobs , i.e.

SQL>     select to_char(next_date,'dd-mm-yyyy hh24:mi:ss') nextdate,to_char(last_date,'dd-mm-yyyy hh24:mi:ss') lastdate,
    broken,total_time,failures,job from user_jobs;

select to_char(next_date,'dd-mm-yyyy hh24:mi:ss') nextdate,
       *
ERROR at line 1:
ORA-01801: date format is too long for internal buffer

Then I executed

SQL> exec dbms_job.next_date(64,sysdate+1/480);

PL/SQL procedure successfully completed.

SQL>     select to_char(next_date,'dd-mm-yyyy hh24:mi:ss') nextdate,to_char(last_date,'dd-mm-yyyy hh24:mi:ss') lastdate,
    broken,total_time,failures,job from user_jobs;

    NEXTDATE                    LASTDATE                    B     TOTAL_TIME       FAILURES            JOB
    -------------------         -------------------         -     ----------         ----------         ----------
    21-09-2001 09:02:03     21-09-2001 08:57:50     N              0                  0                64

After three minutes...

It added new record and showing this.

SQL>     select to_char(next_date,'dd-mm-yyyy hh24:mi:ss') nextdate,to_char(last_date,'dd-mm-yyyy hh24:mi:ss') lastdate,
    broken,total_time,failures,job from user_jobs;

    NEXTDATE                    LASTDATE                    B     TOTAL_TIME       FAILURES            JOB
    -------------------         -------------------         -     ----------         ----------         ----------
    00-00-0000 00:00:00     21-09-2001 08:47:00     N              0                  0                64

This is the  situation....

and so on........

I have changed the interval by giving the following
    SQL>    exec dbms_job.interval(64,'sysdate + 1/160') /* Job Schedule every 9 minutes */

    After this I have run the job immediatly by the following statement

    SQL>     exec dbms_job.run(64)
    It has inserted a record into table. (The time is around 21-09-2001 11:09:03)

    So 1 record is existing the dummy table.

    I have waited about 37 minutes, because If job runs perfectly, then It should insert 4 more records.
    But this does not happen,

    Then around 21-09-2001 11:46:56, I have run the job immeadiatly by giving the following
    SQL>     exec dbms_job.run(64)
    This time it has inserted 5 records at time.

This clearly tells that, It is not invoking at regular intervals what we have given but, When run I immeadiatly then
It is invoking all the intervals and immeadiate run also.

But it still showing wrong message in the views.

Does mean SNP background process is not working properly ?

The other ie. on IST time zone Server is working perfectly................

What could be the problem...?

Your Help is Greatly Appreciated..

Thanks and Regards
PSLU
carp (MIS)
24 Sep 01 10:35
For starters, please get a value greater than 0 for your job_queue_processes parameter.  I believe a value of zero will prevent your job from ever running (which also explains why you don't have a valid date for "next_date" - if the job never runs, there can never be a "next date").  
You can change this parameter dynamically with an ALTER SYSTEM command.  Also, be sure to update this setting in your init.ora file.
sem (Programmer)
24 Sep 01 10:41
Your job can not be executed if job_queue_processes = 0.

The server that works properly was not restarted after changing parameters so it still works with previous values. Try to find the real value from v$parameter and set it for both servers

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close