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
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
.