My team and I have Googled (and Binged!) our guts out trying to find an answer to this issue, so hopefully the Oracle gurus on SF will know the answer.
A week ago we had a power outage in the building hosting our server. The entire server went down in the middle of a database export. When we brought the server back online, we noticed a new process, SMON, working like crazy on the database instance. Here's a screen shot of the 'Top Activity' in OEM.
Oracle Enterprise Manager http://www.myviewstate.net/images/oem.png
We let it go for a while, but after a day we became worried. After checking the logs we noticed that it appears to be in an infinite loop. Here is a portion of the log file:
Fri Aug 14 14:43:58 2009
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as available
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as available
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as available
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as available
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as available
Here are the steps I've taken so far (most of which I tried due to things I found online), none of which have worked.
- Restart the database instance
- Take the user tablespaces offline,
bring them back online, then restart
the db instance - Created a new REDO tablespace, stop
the db instance, point the spfile to
the new tablesace, then restart the
instance
Keep in mind that I am a developer and have very little Oracle experience, let alone experience as a DBA.
Any suggestions?
Best Answer
Your database is looping as it attempt to do a recovery and bring undo segments back online to recover your database. Since you are not a dba, I would immediately get oracle support on the line and tell them that you need major assistance and that you are not a dba. They will very likely have you startup the database and disable the recovery manager that is having smon loop trying to clean up undo segments. Having had to do undo segment cleanup in the past, it is best done with oracle supports help. Much of the steps will require some events and underscore parameters that disable/enable hidden oracle mojo. I would not recommend attempting anything you find on your own or is posted without understanding the implication and possibly how to recover/back out of your attempts. The object is to recover your database and not make it worse. Get oracle support on the phone asap.