Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Possible problem with Oracle jobs...9i

    Hi

    I have 11 oracle jobs created and running every second. But what the jobs are supposed to do, they are not doing. So although they appear to be running, is there any way this can be confirmed?


    JOB_QUEUE_PROCESSES=100

    but dba_jobs_running returns nothing.

    Could someone please advise how to further investigate?

    I have read some documentation but not sure I understand properly. Will read again but if someone could care to advise too, that'd really help. Thanks.

    Regards
    Shajju
    Last edited by shajju; 05-21-10 at 12:57.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have 11 oracle jobs created
    using COPY & PASTE post proof

    >and running every second.
    using COPY & PASTE post proof

    >But what the jobs are supposed to do, they are not doing.
    using COPY & PASTE post proof

    >So although they appear to be running, is there any way this can be confirmed?
    answer depends upon 3 responses above
    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
    Apr 2009
    Posts
    7
    Quote Originally Posted by shajju View Post
    Hi

    I have 11 oracle jobs created and running every second. But what the jobs are supposed to do, they are not doing. So although they appear to be running, is there any way this can be confirmed?


    JOB_QUEUE_PROCESSES=100

    but dba_jobs_running returns nothing.

    Could someone please advise how to further investigate?

    I have read some documentation but not sure I understand properly. Will read again but if someone could care to advise too, that'd really help. Thanks.

    Regards
    Shajju
    Check the script which u have created for jobs. when you said that you have created jobs but Do you have script for it? check the script and check the time and day and date which you have put in script to run. or else post more information for the jobs. so we can understand what's wrong

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    OK.

    Output of dba_jobs is:

    Code:
    JOB	LOG_USER	PRIV_USER	SCHEMA_USER	LAST_DATE	LAST_SEC	THIS_DATE	THIS_SEC	NEXT_DATE	NEXT_SEC	TOTAL_TIME	BROKEN	INTERVAL	FAILURES	WHAT	NLS_ENV	MISC_ENV	INSTANCE
    
    142	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	2031339	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;
    
    	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    141	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	2338725	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;
    
    	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    143	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	1946442	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;
    
    	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    144	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	1865822	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    145	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	2336461	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    146	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	2079537	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    147	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	1858314	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    148	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	1990406	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    161	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	2125072	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;
    
    	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    162	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	1893648	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;
    
    	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0
    181	AESDEM	AESDEM	AESDEM	5/22/2010 8:53:26 AM	08:53:26			5/22/2010 8:53:27 AM	08:53:27	715	N	SYSDATE+1/86400	0	begin
    aesdem.work_summary.do_work;
    commit;
    end;
    
    	NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='OMAN' NLS_CURRENCY='..' NLS_ISO_CURRENCY='OMAN' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD/MM/RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'	0102000200000000	0

    The script of the job is:
    Code:
    DECLARE
      X NUMBER;
    BEGIN
      SYS.DBMS_JOB.SUBMIT
        ( job       => X 
         ,what      => 'begin
    aesdem.work_summary.do_work;
    commit;
    end;
    
    '
         ,next_date => to_date('22/05/2010 08:49:30','dd/mm/yyyy hh24:mi:ss')
         ,interval  => 'SYSDATE+1/86400'
         ,no_parse  => TRUE
        );
      SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
    END;
    /
    
    commit;
    aesdem.work_summary is a package, which when I run manually, works fine but is not just being picked up by the job.

    dba_jobs_running 9 times out of 10 shows nothing but I understand it will only show something when the job is running (which takes 2-3 seconds to complete anyway) so it's a bit of a hit and miss thing.

    I can't think of any reason why the job would not pick the package up.

    JOB_QUEUE_PROCESSES=100

    Also,

    $ ps -ef | grep cj
    oracle 5848 1 0 19:48:11 ? 0:00 ora_cjq0_opt1

    Maybe its worth mentioning that the db server was relocated and given a new IP address and when it brought back online there was a problem with data loading which turned out to be due to the NFS creating some lock on the 'ext' dir so ext files being created were '0' bytes.
    Last edited by shajju; 05-22-10 at 02:45.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    I would like to add that I can see jnnn processes in ps -ef:

    oracle 5910 1 0 19:48:32 ? 3:57 ora_j000_opt1
    oracle 5916 1 0 19:48:32 ? 3:57 ora_j003_opt1
    oracle 5912 1 0 19:48:32 ? 4:08 ora_j001_opt1
    oracle 5918 1 0 19:48:32 ? 3:44 ora_j004_opt1
    oracle 5920 1 0 19:48:32 ? 3:30 ora_j005_opt1
    oracle 5922 1 0 19:48:32 ? 3:20 ora_j006_opt1
    oracle 5924 1 0 19:48:32 ? 3:02 ora_j007_opt1
    oracle 5926 1 0 19:48:32 ? 2:50 ora_j008_opt1
    oracle 5928 1 0 19:48:32 ? 2:34 ora_j009_opt1
    oracle 5930 1 0 19:48:32 ? 2:21 ora_j010_opt1
    oracle 5932 1 0 19:48:32 ? 2:04 ora_j011_opt1
    oracle 5934 1 0 19:48:32 ? 1:38 ora_j012_opt1
    oracle 5936 1 0 19:48:32 ? 1:19 ora_j013_opt1
    oracle 5938 1 0 19:48:33 ? 0:56 ora_j014_opt1
    oracle 5940 1 0 19:48:33 ? 0:40 ora_j015_opt1
    Last edited by shajju; 05-22-10 at 03:31.

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    Is there any way to track what a job goes through, when it runs because couldn't find anything in the alert log.

    Even tried capturing the activity of 'exec dbms_job****n(job_num);' in SQL MON but got nothing. Just

    Timestamp: 13:26:58.784
    BEGIN dbms_job****n(182); END;

    Please I need some help with this.

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Not sure how to show that the job is not doing what it's supposed to because it's supposed to run a 'package'. It seems that the oracle job is not able to run the package because there is no output. I only see output when I run the package manually 'Execute Proc'.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why do you have multiple job running the same procedure (aesdem.work_summary.do_work) at the same time?

    place the following lines in a file named look_at.sql

    ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MIS';
    SELECT SYSDATE FROM DUAL;
    SELECT LAST_DATE, THIS_DATE, NEXT_DATE, BROKEN, FAILURES from DBA_JOBS WHERE JOB = 145;

    then from sqlplus a couple of seconds apart do

    SQL> @look_at.sql
    SQL> @look_at.sql
    SQL> @look_at.sql


    COOPY & PASTE the results back here
    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.

  9. #9
    Join Date
    Aug 2008
    Posts
    464
    Hi

    Thanks.

    This is what I got:

    Code:
    SQL> @look_at.sql
    
    Session altered.
    
    
    SYSDATE
    -------------------
    2010-05-23 08:11:33
    
    
    LAST_DATE           THIS_DATE           NEXT_DATE           B   FAILURES
    ------------------- ------------------- ------------------- - ----------
    2010-05-23 08:11:30                     2010-05-23 08:11:31 N          0
    
    SQL> @look_at.sql
    
    Session altered.
    
    
    SYSDATE
    -------------------
    2010-05-23 08:11:39
    
    
    LAST_DATE           THIS_DATE           NEXT_DATE           B   FAILURES
    ------------------- ------------------- ------------------- - ----------
    2010-05-23 08:11:35                     2010-05-23 08:11:36 N          0
    
    SQL> @look_at.sql
    
    Session altered.
    
    
    SYSDATE
    -------------------
    2010-05-23 08:11:45
    
    
    LAST_DATE           THIS_DATE           NEXT_DATE           B   FAILURES
    ------------------- ------------------- ------------------- - ----------
    2010-05-23 08:11:40                     2010-05-23 08:11:41 N          0

  10. #10
    Join Date
    Aug 2008
    Posts
    464
    Oh, and to answer your question, our app has a lot of sqls to run every hour so it is recommended to have multiple jobs configured for the same task.

  11. #11
    Join Date
    Aug 2008
    Posts
    464
    Whether the oracle job is run by dbms.job or manually (execute....), I suspect it is not picking the package to run up. That's what I am looking for verify.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This is what I got:
    So as far as Oracle is concerned JOB #145 is being invoked & not throwing any error.
    DBMS_JOB is performing as expected & desired.

    If the results are not what you expect/desire then you simply need to debug YOUR application code.
    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.

  13. #13
    Join Date
    Aug 2008
    Posts
    464
    Thanks. However, is it possible to see what the job is doing? You see we have reports to summarize data every hour, every day...etc so the report that summarises data every hour has a next_run_date updated every hour. So when the oracle job runs every 5 seconds, it is able to pick up the next_run_date of the summary report and then call/run a package that runs the report.
    So I was wondering if it was possible to find out why the job isn't able to see any summary report's next_run_date.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >However, is it possible to see what the job is doing

    PL/SQL runs deep inside DB engine.

    To obtain visibility, the job itself must contain code to leave produce its own AUDIT trail, if you choose not to use Oracle's AUDIT.
    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.

  15. #15
    Join Date
    Aug 2008
    Posts
    464
    Thanks for replying. Feels like I've hit a brick wall. Is there any chance you could write any code in the pl/sql of the job I mentioned earlier so that it shows where it is failing to run the package it is supposed to? I don't want to capture the activity of the package the job is running. Only why the job is not able to pick the package up. Any chance?

Posting Permissions

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