dbms_job failed to execute 2005-10-27 - By Marquez, Chris
Magnus,
>> On 10/27/05, Igor Neyman wrote:
>> Magnus,
>> Your output from dba_jobs shows that all
>> your jobs were executed this morning
>> and they all are scheduled to be executed
>> tomorrow at different times (probably,
>> as they are supposed to).
I agree with Igor.
My guess is that your jobs (job procs _j0**_) became hung on while running some
jobs for some reason and no further jobs could run (just happened to me this
week...jobs NOT broken, jobs NOT failing, but jobs *hung* using ALL of the job
procs so no new jobs would start.)
On db bounce they corrected themselves (as Igor explained) and are scheduled to
*your* defined NEXT time. Their failure/hang is strange but can happen.
Spend some time reviewing dba_jobs view and select ***sysdate*** from dual; and
you will see things are fine.
PS You could spend all day (or more) trying to find out why they hung, but a
TAR might be best for this...why bother unless its regular problem?
hth
Chris Marquez
Oracle DBA
SQL> alter session set nls_date_format = 'Mon-DD-YYYY HH:MI:SS AM';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-- ---- ---- ---- ---- ------
Oct-27-2005 11:21:41 AM
SQL> select trunc(sysdate+1) + 22.00/24 from dual;
TRUNC(SYSDATE+1)+22/24
-- ---- ---- ---- ---- ------
Oct-28-2005 10:00:00 PM
SQL> select trunc(sysdate+1) + 22.05/24 from dual;
TRUNC(SYSDATE+1)+22.05/24
-- ---- ---- ---- ---- ------
Oct-28-2005 10:03:00 PM
-- --Original Message-- --
From: oracle-l-bounce@(protected) on behalf of Magnus Andersen
Sent: Thu 10/27/2005 11:28 AM
To: Igor Neyman
Cc: oracle-l@(protected)
Subject: Re: dbms_job failed to execute
Igor,
I did check for broken jobs before I ran it manually. None of the jobs had
the flag set to Y. The only other trace files I have are generated from a
utl_smtp procedure that we have. Sometimes the end-users format the e-mail
addresses wrong and the job fails. That is all. This is a known issue that
I've asked the developers to fix and catch in the application before it
reaches the database.
Magnus
On 10/27/05, Igor Neyman <ineyman@(protected)> wrote:
>
> Magnus,
> Did you check dba_jobs before you run them manually this morning?
> Did you see "broken" jobs? When you run jobs manually, "broken" changes
> from Y to N.
> If yes - there supposed to be corresponding trace files in "udump"
> directory. Or look for more _j000_ or _j001_ and so on in bdump directory
> with the timestamps between Saturday and this morning.
> Igor
>
> -- ---- ---- ---- ---- ---- --
> *From:* Magnus Andersen [mailto:mag.andersen@(protected)]
> *Sent:* Thursday, October 27, 2005 11:04 AM
> *To:* Igor Neyman
> *Cc:* oracle-l@(protected)
> *Subject:* Re: dbms_job failed to execute
>
> Igor,
>
> The database only goes down Saturday mornings for cold backup. I'm doing
> hot backups the rest of the week. I'm thinking about changing this to only
> to hot backups since I've read that there is no reason to do the cold, but
> that is an other thread I might start to iron that out. My guess is that the
> problem started saturday, but I didn't catch the issue before Tuesday
> morning. The reason you see all the jobs executed this morning is that I had
> to run them manually. We have several processes that are depending on these
> procedures being run. They do not execute on their own.
>
> Hope this makes sense.
>
> Magnus
>
> On 10/27/05, Igor Neyman <ineyman@(protected)> wrote:
> >
> > Magnus,
> > When you are saying that jobs stopped executing on Tuesday, does it
> > mean that they were not executed since then?
> > Your output from dba_jobs shows that all your jobs were executed this
> > morning (probably when your instance was started) and they all are scheduled
> > to be executed tomorrow at different times (probably, as they are supposed
> > to).
> > The reason they all were executed this morning right after instance
> > started is that most probably your instance was down at the time when they
> > were scheduled to be executed, and when oracle starts, it checks if it
> > missed to execute some jobs while the instance was down.
> > And if it finds such jobs that "missed" their scheduled time, it will
> > execute them immediately (not waiting for next scheduled time).
> > HTH
> > Igor
> >
> > -- ---- ---- ---- ---- ---- --
> > *From:* oracle-l-bounce@(protected) [mailto:
> > oracle-l-bounce@(protected)] *On Behalf Of *Magnus Andersen
> > *Sent:* Thursday, October 27, 2005 10:17 AM
> > *Cc:* oracle-l@(protected)
> > *Subject:* Re: dbms_job failed to execute
> >
> > I found two trace files with the same time stamp on them. These are the
> > closest to when I saw the problem. Could this have "hung" since the database
> > bounced?
> >
> > --
> > Magnus Andersen
> > Systems Administrator / Oracle DBA
> > Walker & Associates, Inc.
> >
> >
> > Trace file information...
> >
> > Dump file /opt/oracle/ora92/rdbms/log /prd/bdump/prd_j000_9659.trc
> > Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
> > With the Partitioning, OLAP and Oracle Data Mining options
> > JServer Release 9.2.0.6.0 - Production
> > ORACLE_HOME = /opt/oracle/ora92
> > System name: Linux
> > Node name: oraprd.walkerassoc.com <http://oraprd.walkerassoc.com/>
> > Release: 2.4.21-32.0.1.ELsmp
> > Version: #1 SMP Tue May 17 17:52:23 EDT 2005
> > Machine: i686
> > Instance name: prd
> > Redo thread mounted by this instance: 1
> > Oracle process number: 28
> > Unix process pid: 9659, image: oracle@(protected) (J000)
> >
> > *** 2005-10-22 00:38:58.415
> > *** SESSION ID:(27.52095) 2005-10-22 00:38:58.391
> > OPIRIP: Uncaught error 1089. Error stack:
> > ORA-01089 (See ORA-01089.ora-code.com): immediate shutdown in progress - no operations are permitted
> > ORA-00448 (See ORA-00448.ora-code.com): normal completion of background process
> >
>
>
>
> --
> Magnus Andersen
> Systems Administrator / Oracle DBA
> Walker & Associates, Inc.
>
--
Magnus Andersen
Systems Administrator / Oracle DBA
Walker & Associates, Inc.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7233.28">
<TITLE>RE: dbms_job failed to execute</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Magnus,<BR>
<BR>
>> On 10/27/05, Igor Neyman wrote:<BR>
>> Magnus,<BR>
>> Your output from dba_jobs shows that all<BR>
>> your jobs were executed this morning<BR>
>> and they all are scheduled to be executed<BR>
>> tomorrow at different times (probably,<BR>
>> as they are supposed to).<BR>
<BR>
I agree with Igor.<BR>
My guess is that your jobs (job procs _j0**_) became hung on while running some
jobs for some reason and no further jobs could run (just happened to me this
week...jobs NOT broken, jobs NOT failing, but jobs *hung* using ALL of the job
procs so no new jobs would start.)<BR>
On db bounce they corrected themselves (as Igor explained) and are scheduled to
*your* defined NEXT time. Their failure/hang is strange but can happen.
<BR>
<BR>
Spend some time reviewing dba_jobs view and select ***sysdate*** from dual; and
you will see things are fine.<BR>
<BR>
PS You could spend all day (or more) trying to find out why they hung, but a
TAR might be best for this...why bother unless its regular problem?<BR>
<BR>
hth<BR>
<BR>
Chris Marquez<BR>
Oracle DBA<BR>
<BR>
<BR>
<BR>
<BR>
SQL> alter session set nls_date_format = 'Mon-DD-YYYY HH:MI:SS AM';<BR>
Session altered.<BR>
<BR>
SQL> select sysdate from dual;<BR>
SYSDATE<BR>
-- ---- ---- ---- ---- ------<BR>
Oct-27-2005 11:21:41 AM<BR>
<BR>
SQL> select trunc(sysdate+1) + 22.00/24 from dual;<BR>
TRUNC(SYSDATE+1)+22/24<BR>
-- ---- ---- ---- ---- ------<BR>
Oct-28-2005 10:00:00 PM<BR>
<BR>
SQL> select trunc(sysdate+1) + 22.05/24 from dual;<BR>
TRUNC(SYSDATE+1)+22.05/24<BR>
-- ---- ---- ---- ---- ------<BR>
Oct-28-2005 10:03:00 PM<BR>
<BR>
<BR>
<BR>
<BR>
-- --Original Message-- --<BR>
From: oracle-l-bounce@(protected) on behalf of Magnus Andersen<BR>
Sent: Thu 10/27/2005 11:28 AM<BR>
To: Igor Neyman<BR>
Cc: oracle-l@(protected)<BR>
Subject: Re: dbms_job failed to execute<BR>
<BR>
Igor,<BR>
<BR>
I did check for broken jobs before I ran it manually. None of the jobs had<BR>
the flag set to Y. The only other trace files I have are generated from a<BR>
utl_smtp procedure that we have. Sometimes the end-users format the e-mail<BR>
addresses wrong and the job fails. That is all. This is a known issue that<BR>
I've asked the developers to fix and catch in the application before it<BR>
reaches the database.<BR>
<BR>
Magnus<BR>
<BR>
On 10/27/05, Igor Neyman <ineyman@(protected)> wrote:<BR>
><BR>
> Magnus,<BR>
> Did you check dba_jobs before you run them manually this morning?<BR>
> Did you see "broken" jobs? When you run jobs manually, "
;broken" changes<BR>
> from Y to N.<BR>
> If yes - there supposed to be corresponding trace files in "udump
"<BR>
> directory. Or look for more _j000_ or _j001_ and so on in bdump directory
<BR>
> with the timestamps between Saturday and this morning.<BR>
> Igor<BR>
><BR>
> -- ---- ---- ---- ---- ---- --<BR>
> *From:* Magnus Andersen [<A HREF="mailto:mag.andersen@(protected)">mailto
:mag.andersen@(protected)</A>]<BR>
> *Sent:* Thursday, October 27, 2005 11:04 AM<BR>
> *To:* Igor Neyman<BR>
> *Cc:* oracle-l@(protected)<BR>
> *Subject:* Re: dbms_job failed to execute<BR>
><BR>
> Igor,<BR>
><BR>
> The database only goes down Saturday mornings for cold backup. I'm doing
<BR>
> hot backups the rest of the week. I'm thinking about changing this to only
<BR>
> to hot backups since I've read that there is no reason to do the cold, but
<BR>
> that is an other thread I might start to iron that out. My guess is that
the<BR>
> problem started saturday, but I didn't catch the issue before Tuesday<BR>
> morning. The reason you see all the jobs executed this morning is that I
had<BR>
> to run them manually. We have several processes that are depending on
these<BR>
> procedures being run. They do not execute on their own.<BR>
><BR>
> Hope this makes sense.<BR>
><BR>
> Magnus<BR>
><BR>
> On 10/27/05, Igor Neyman <ineyman@(protected)> wrote:<BR>
> ><BR>
> > Magnus,<BR>
> > When you are saying that jobs stopped executing on Tuesday,
does it<BR>
> > mean that they were not executed since then?<BR>
> > Your output from dba_jobs shows that all your jobs were executed this
<BR>
> > morning (probably when your instance was started) and they all are
scheduled<BR>
> > to be executed tomorrow at different times (probably, as they are
supposed<BR>
> > to).<BR>
> > The reason they all were executed this morning right after
instance<BR>
> > started is that most probably your instance was down at the time when
they<BR>
> > were scheduled to be executed, and when oracle starts, it checks if
it<BR>
> > missed to execute some jobs while the instance was down.<BR>
> > And if it finds such jobs that "missed" their scheduled
time, it will<BR>
> > execute them immediately (not waiting for next scheduled time).<BR>
> > HTH<BR>
> > Igor<BR>
> ><BR>
> > -- ---- ---- ---- ---- ---- --<BR>
> > *From:* oracle-l-bounce@(protected) [mailto:<BR>
> > oracle-l-bounce@(protected)] *On Behalf Of *Magnus Andersen<BR>
> > *Sent:* Thursday, October 27, 2005 10:17 AM<BR>
> > *Cc:* oracle-l@(protected)<BR>
> > *Subject:* Re: dbms_job failed to execute<BR>
> ><BR>
> > I found two trace files with the same time stamp on them. These
are the<BR>
> > closest to when I saw the problem. Could this have "hung"
since the database<BR>
> > bounced?<BR>
> ><BR>
> > --<BR>
> > Magnus Andersen<BR>
> > Systems Administrator / Oracle DBA<BR>
> > Walker & Associates, Inc.<BR>
> ><BR>
> ><BR>
> > Trace file information...<BR>
> ><BR>
> > Dump file /opt/oracle/ora92/rdbms/log /prd/bdump/prd_j000_9659.trc<BR>
> > Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production<BR>
> > With the Partitioning, OLAP and Oracle Data Mining options<BR>
> > JServer Release 9.2.0.6.0 - Production<BR>
> > ORACLE_HOME = /opt/oracle/ora92<BR>
> > System name: Linux<BR>
> > Node name: oraprd.walkerassoc.com <<A HREF="http://oraprd
.walkerassoc.com/">http://oraprd.walkerassoc.com/</A>><BR>
> > Release: 2.4.21-32.0.1.ELsmp<BR>
> > Version: #1 SMP Tue May 17 17:52:23 EDT 2005<BR>
> > Machine: i686<BR>
> > Instance name: prd<BR>
> > Redo thread mounted by this instance: 1<BR>
> > Oracle process number: 28<BR>
> > Unix process pid: 9659, image: oracle@(protected) (J000)
<BR>
> ><BR>
> > *** 2005-10-22 00:38:58.415<BR>
> > *** SESSION ID:(27.52095) 2005-10-22 00:38:58.391<BR>
> > OPIRIP: Uncaught error 1089. Error stack:<BR>
> > ORA-01089 (See ORA-01089.ora-code.com): immediate shutdown in progress - no operations are
permitted<BR>
> > ORA-00448 (See ORA-00448.ora-code.com): normal completion of background process<BR>
> ><BR>
><BR>
><BR>
><BR>
> --<BR>
> Magnus Andersen<BR>
> Systems Administrator / Oracle DBA<BR>
> Walker & Associates, Inc.<BR>
><BR>
<BR>
<BR>
<BR>
--<BR>
Magnus Andersen<BR>
Systems Administrator / Oracle DBA<BR>
Walker & Associates, Inc.<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>
|
|
