2

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.

  1. Restart the database instance
  2. Take the user tablespaces offline, bring them back online, then restart the db instance
  3. 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?

5 Answers 5

3

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.

3

Oracle 9i and above uses special UNDO tablespaces and REDO logs to manage its transactions.

REDO stores a log of the statements executed, and UNDO stores before and after images of database blocks affected by those statements.

Did a bit more digging... If you have access to Oracle Metalink (their support site) look up Bug ID 3418428 This seems to be your problem. I can't reproduce the information here, as I believe it violates Oracles support agreement.

The general gist of the issue is that Automatic Undo Management dynamically creates ROLLBACK segments in the UNDO tablespace on demand. The number and size of the segments created varies depending on the load on the database.

Before the database crashed it had created and brought online more than the default number of ROLLBACK segments.

After the crash the database has not brought online all the ROLLBACK segments it had prior to the crash.

I think 10g may be able to recover from this if you give it enough time; 9i had more problems. Is the database open to connections and responding?

Beyond that you could be into the murky world of Oracle recovery. I'd suggesting getting some help, as it is easy to totally fubar Oracle when attempting recoveries.

3
  • 2
    "Loosly UNDO stores uncommited transactions and REDO stores commited transactions." Not true. As changes (insert/update/deletes) happen, the change is written to the redo logs and a way to reverse the change is written to undo. It is true that the commit itself gets written to the redo log, but there's no undo for it.
    – Gary
    Aug 17, 2009 at 0:14
  • I stand corrected. Redo has the DML statements, and UNDO has the before / after images of the effected blocks.
    – Cephas
    Aug 18, 2009 at 3:31
  • Updated post with new and more accurate info...
    – Cephas
    Aug 18, 2009 at 3:55
1

You don't mention what version of Oracle this is... there is an issue on Metalink for 10.2.0.4 with this symptom (821743.1). Does your system do distributed transactions? There may be an uncommitted two-phase commit transaction that is "stuck". Query the view dba_2pc_pending:

select local_tran_id, global_tran_id, state from dba_2pc_pending;

If there are records there that don't go away you'll need to rollback force on them. Be very careful in this realm... The advice from @Geoff is good, Oracle support should probably be consulted here, that's what you're paying for. This symptom after a crash might indicate some corruption in the database. It's rare, but is possible. Oracle is supposed to be "corruption proof", but the best laid plans and all that....

0

I'm no Oracle admin or developer, but a quick google on "smon oracle" told me what the process is and what it's doing after your DB had the power pulled to increase CPU usage. It'll carry on like this until it's fixed the damage caused by the power being pulled. If in doubt open a ticket with Oracle - you pay a lot for support after all.

0

The rollforward/open/rollbackward is explained here

Broadly, the redo logs are applied pushing changes back to the database. At the end of that any change that has been applied but didn't finish with a commit has to be rolled back. It reads through the UNDO to undo the change.

If it is hitting undo, it means that there was an uncommitted change which needs to be rolled-back. It wouldn't have been an export (as that should be pure select). Look at USED_UREC in v$transaction. There should be one row (maybe more) with a non-zero value which (hopefully) should go down as the undo records are applied to remove the uncommitted change.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .