dbms_job failed to execute 2005-10-27 - By Magnus Andersen
I will bounce the db tonight to see if that takes care of the problem. I'll
let you know tomorrow.
Thanks for the help.
Magnus
On 10/27/05, Marquez, Chris <cmarquez@(protected)> wrote:
>
> 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> <
> 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.
>
>
>
--
Magnus Andersen
Systems Administrator / Oracle DBA
Walker & Associates, Inc.
I will bounce the db tonight to see if that takes care of the problem. I
'll let you know tomorrow.<br>
<br>
Thanks for the help.<br>
<br>
Magnus<br>
<br>
<br><br><div><span class="gmail_quote">On 10/27/05, <b class="gmail_sendername"
>Marquez, Chris</b> <<a href="mailto:cmarquez@(protected)">cmarquez
@(protected)</a>> wrote:</span><blockquote class="gmail_quote" style=
"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding
-left: 1ex;">
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: <a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce
@(protected)</a> on behalf of Magnus Andersen<br>Sent: Thu 10/27/2005 11:28 AM
<br>To: Igor Neyman<br>Cc: <a href="mailto:oracle-l@(protected)">oracle-l
@(protected)
</a><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 <<a
href="mailto:ineyman@(protected)">ineyman@(protected)</a>> 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 [mailto:<a href="mailto:mag.andersen@(protected)">mag
.andersen@(protected)
</a>]<br>> *Sent:* Thursday, October 27, 2005 11:04 AM<br>> *To:* Igor
Neyman<br>> *Cc:* <a href="mailto:oracle-l@(protected)">oracle-l@(protected)
.org</a><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 <<a href="mailto:ineyman@(protected)"
>ineyman@(protected)</a>> 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:* <a href="mailto:oracle
-l-bounce@(protected)">
oracle-l-bounce@(protected)</a> [mailto:<br>> > <a href="mailto:oracle
-l-bounce@(protected)">oracle-l-bounce@(protected)</a>] *On Behalf Of
*Magnus Andersen<br>> > *Sent:* Thursday, October 27, 2005 10:17 AM
<br>> > *Cc:* <a href="mailto:oracle-l@(protected)">oracle-l@(protected)
.org</a><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: <a href="http://oraprd.walkerassoc.com">oraprd
.walkerassoc.com</a> <<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:
<a href="mailto:oracle@(protected)">oracle@(protected)</a
> (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><br></blockquote>
</div><br><br clear="all"><br>-- <br>Magnus Andersen<br>Systems Administrator /
Oracle DBA<br>Walker & Associates, Inc.
|
|
