Re: Intermittent Failure When Accessing DB Constantly?

On May 8, 10:40 am, "lazyboy..._at_yahoo.com" <lazyboy..._at_yahoo.com> wrote:
> Hi All,
>
> We have a script in a cron job which will login our oracle 9.2.0.4 DB
> constantly (meaning every minutes, 7 days/week, & 365 days/yr), and
> the script is to check an availability of a record in a table which is
> passed through a variable in a script. The script is running
> perfectly fine except that it has an intermittent failure :( The
> weird thing is that the failure happens at exactly the same time but
> at random day. My 1st logical thought is that we may have some other
> job in a system which interferes this job; However, when I look
> through the system crontab file and other places & can't find any cron
> job which runs at that time. Please keep in mind that this is an
> intermittent failure. I already look through the log file and the
> failure indicates that "the record is not available in a table xyz",
> but I know for sure that record is there. I know that accessing DB
> like this is that we are looking for troubles, but there is a strong
> argument that we should be able to access oracle DB at any time (which
> is true), but I don't have info to say "Not to that extents".
>
> PROD Cancelled at Sun Apr 8 00:34:00 PDT 2007
> PROD Cancelled at Tue Apr 10 00:34:00 PDT 2007
> PROD Cancelled at Sun Apr 15 00:34:00 PDT 2007
> PROD Cancelled at Wed Apr 18 00:34:00 PDT 2007
> PROD Cancelled at Mon Apr 30 00:34:00 PDT 2007
>
> Question: Is there a limitation in accessing oracle DB constantly &
> intensively like the way we have? If there is, could you please
> provide some info? However, if there is not, please let me know if
> there are any other ideas/ways to troubleshoot this problem?
>
> Any info/suggestions/inputs are greatly appreciated.
>
> Btw, sorry for a long email but i want you to have a detail info as
> much as possible.
>
> TIA,
> -Chris

Wild speculation: You are hitting the db just as a log switch is happening. Do you have log switching set up to happen at specific intervals? That combined with some number of hot block updates might simply latch you out at particular times. Anything happening in the alert*log at those times?

More wild speculation, a variant on Brian's commit comment: you have some severe I/O or log buffer problem, so the other process that needs to commit can't know that it is committed yet. I've seen some unix filesystems do some sort of housekeeping at times that just stops everything else.

Yet more wilder speculation: 9204 shared pool bugs are messing with you.

And as Valentin asked, what exactly is the error? You might want to post your error handling code, too.

Back in the early O7 days I used to see things like this happen because the database writer would just be overwhelmed (even while running catalog!), but that was long ago.

If it is somewhat predictable, maybe you want to record latch waits at that particular time.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20070508/news_1b8micro.html
http://www.nbcsandiego.com/news/3486705/detail.html