RE: Smon in trouble?

From: Walthour, Jon (GEAE, Compaq) <Jon.walthour_at_ae.ge.com>
Date: Thu, 13 Sep 2001 07:06:03 -0700
Message-ID: <F001.0038D1BC.20010913064519@fatcity.com>

!! Please do not post Off Topic to this List !!

Ian:

Try this:

shutdown immediate or shutdown abort
startup restricted
Take all online user tablespaces offline then back online (not TEMP, RBS, SYSTEM, etc.) This will stop SMON from trying to recover information in the corrupted rollback segment.
Now the problem rollback segment should be fine. Its status should change to online.
shutdown immediate
startup
Drop the problem rollback segment and all the rbs's in its tablespace.
Drop the tablespace and recreate it.
Recreate your rollback segments.

After this, things should return to normal without any loss of data.

Jon Walthour

-----Original Message-----
Sent: Wednesday, September 12, 2001 11:25 PM To: Multiple recipients of list ORACLE-L

Hi all,

I had a large table 31 million rows and was trying to add a column to this table. It used up a lot of Rollback segment, but that was ok as I gave it plenty, but after a long period of time it died before successfully adding the column, ie. The database died.

After restarting the database, I can successfully log into it etc. but in the alert log I am getting a repeated message and the alert log just keeps growing.

Following is from the alert.log file, I wonder if anyone can give some idea of why it is doing this and what I need to do to stop it?

Thanks

alter database open
Beginning crash recovery of 1 threads
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5939 Reading mem 0   Mem# 0 errs 0: D:\ORANT\DATABASE\GCAC\LOG02.ORA Thu Sep 13 11:44:09 2001
Crash recovery completed successfully
Thu Sep 13 11:44:11 2001
Thread 1 advanced to log sequence 5940
  Current log# 4 seq# 5940 mem# 0: X:\ORANT\DATABASE\GCAC\LOG4.ORA Thread 1 opened at log sequence 5940
  Current log# 4 seq# 5940 mem# 0: X:\ORANT\DATABASE\GCAC\LOG4.ORA Successful open of redo thread 1. Thu Sep 13 11:44:11 2001 SMON: enabling cache recovery
SMON: enabling tx recovery
Thu Sep 13 11:44:14 2001
Completed: alter database open
Thu Sep 13 11:44:14 2001

SMON: about to recover undo segment 5
SMON: mark undo segment 5 as available
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as available
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as available
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as available

.... The above 2 lines repeat over and over until I shutdown the database.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Biddell, Ian
INET: Ian.Biddell_at_compaq.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Walthour, Jon (GEAE, Compaq)
INET: Jon.walthour_at_ae.ge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------