Home » RDBMS Server » Server Utilities » Oracle jobs appear to be running but..... (Oracle 9i running on HPUX ia64 11.23)
Oracle jobs appear to be running but..... [message #457243] Sat, 22 May 2010 08:08 Go to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Hi

I have a number of oracle jobs set up to do the same thing and to run every second. These jobs, run a package which summarizes data in tables. All was working fine until a few days ago. Before the server was shut down, the jobs were not disabled. When the server was restarted and the db brought back online, things seemed normal apart from these jobs not doing what they had been doing for the last 5 years. i.e., no output.

The script of the job is:

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 15:03:11','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;


This job runs the work_summary package but it seems it is not doing so as I can't see any output from that package. If I run the package manually, it works fine and I see output.

dba_running_jobs=100
CJQo process exists.

dba_jobs shows all the jobs.
Can see jnnn processes too.

Is there any other way I can confirm that the jobs are really running? And track what happens when the job runs?

Regards
Shajju
Re: Oracle jobs appear to be running but..... [message #457254 is a reply to message #457243] Sat, 22 May 2010 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add trace in the job? For instance write in a log file or table.

Regards
Michel
Re: Oracle jobs appear to be running but..... [message #457256 is a reply to message #457243] Sat, 22 May 2010 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_48a.htm#2110076

You can AUDIT package execution
Re: Oracle jobs appear to be running but..... [message #457263 is a reply to message #457256] Sat, 22 May 2010 10:42 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Thanks very much for replying. Will explore the suggestion. I just wanted to mention that when viewing the jobs in dba_jobs or all_jobs, the instance is set to '0'.

Not sure if it should be '0'!

Regards
Re: Oracle jobs appear to be running but..... [message #457267 is a reply to message #457263] Sat, 22 May 2010 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are not in RAC, so 0 is fine.

Regards
Michel
Re: Oracle jobs appear to be running but..... [message #457284 is a reply to message #457243] Sat, 22 May 2010 23:47 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Hi

Could you please guide me on how to set up a trace for the oracle job? Setting an Audit up required to restart the db which I would like avoid right now, if possible.

Thanks
Shajju
Re: Oracle jobs appear to be running but..... [message #457288 is a reply to message #457284] Sun, 23 May 2010 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said add instructions to write into a file (UTL_FILE) or insert in to log table preferably calling an autonomous procedure that does it.

Regards
Michel
Re: Oracle jobs appear to be running but..... [message #457303 is a reply to message #457243] Sun, 23 May 2010 09:32 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Thanks Michel.

Hope you wouldn't mind being a bit more descriptive as my mind is refusing to think about this anymore. Sorry. I've been trying to figure it out for a few days now exhausting my limited knowledge about oracle.
Re: Oracle jobs appear to be running but..... [message #457305 is a reply to message #457303] Sun, 23 May 2010 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try this: http://www.google.com/search?hl=en&source=hp&q=oracle+log+procedure&aq=f&aqi=&aql=&oq=&gs_rfai=

Regards
Michel
icon9.gif  Re: Oracle jobs appear to be running but..... [message #457685 is a reply to message #457305] Tue, 25 May 2010 11:31 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Thanks Michele....I sense this is going to take some time. And I need to debug why the job is failing to pick the package up very very asap!.

Is there any chance you could help me embed UTL_FILE into the PL?SQL code of the job I pasted earlier? You know it's not a good feeling when you feel like a complete failure.

Sad
Re: Oracle jobs appear to be running but..... [message #457695 is a reply to message #457685] Tue, 25 May 2010 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to put the statements in aesdem.work_summary.do_work procedure which I have not.
So there is most likely less time if you do it.

Regards
Michel
Re: Oracle jobs appear to be running but..... [message #457767 is a reply to message #457695] Tue, 25 May 2010 22:27 Go to previous messageGo to next message
shajju
Messages: 29
Registered: August 2009
Junior Member
Hmmm, ok and thanks for the info. I thought, to debug the steps an oracle job is going through you have to insert the debugging code into the pl/sql of the job.

So using UTL_FILE in the code of the package will tell me why the job is not able to pick it up?

Hmmmm
Re: Oracle jobs appear to be running but..... [message #457768 is a reply to message #457767] Tue, 25 May 2010 22:39 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So using UTL_FILE in the code of the package will tell me why the job is not able to pick it up?
Or add INSERT statements which would leave audit trail.

You keep stating that job does not start.
Michel & I believe the job does start.
So by either UTL_FILE or INSERT done as very first action in the procedure, you can quickly conclude if/when job really starts.

Does aesdem.work_summary.do_work contain an EXCEPTION handler that would result in any error being silently ignored?
Previous Topic: IMPDP full tablespace
Next Topic: running export utility online
Goto Forum:
  


Current Time: Wed Apr 24 19:55:27 CDT 2024