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?