Re: Waiting for smon to disable tx recovery. 9i on unix.

HI Mark,

Thanks for responding. Yes, I was looking for things before crashing away in a trial and error manner like so many others that I work with do. See inline comments for answers.

For anyone else who gets these symptoms, here is the result. I just waited for the database to shut down. Eventually, it did, after about 12 hours. The instance started up, and shutdown easily after that.

> How did you kill the session? If you used alter system kill session it
> is possible that the Oracle background process still existed.

alter system kill session

Did you
> check the session to see what it was doing, v$session_wait, v$sql,
> v$transaction prior to killing it? Did you look at the plan to see if
> it was performing the task in an inefficient manner?

Check transactions by user, selecting fields with: FROM v$transaction t,

   v$session s
WHERE t.addr = s.taddr

• there were no transactions at all.

> Oracle has been opening the database and beginning new work before all
> backouts are complete for several versions. If a user session attempts
> to access data that needs backing out to a consistent state then that
> session is used to perform the backout. In other words the reader pays
> the price for fast startup and opening.
>
> Killing the session and bouncing the database just adds to the work
> that needs to be done. Being that you are on a crappy version,
> 9.2.0.3, you could also be hitting a bug. I would let the database
> just run for a while and take the time to search metalink for any
> related issues. But in general if a transaction takes 7 hours to do
> work it could take more than 7 hours to back it out.

Yes, I did lots of searching, and have dozens of pages of notes. When I couldn't find a answer specific to all the symptoms, then I made the posting.

> Next time before killing something see what it is doing if anything and
> recored the front and back-end pids. Also check the alert log and
> trace direcories to make sure nothing bad is going on or has happened.
>
> HTH -- Mark D Powell --

Thanks again! Received on Mon Jun 12 2006 - 09:02:41 CDT