Can't drop UNDO tablespace - active rollback.

Printable View

09-18-2003, 08:24 AM
JMac

Can't drop UNDO tablespace - active rollback.

Using 9i on Win2K.

Had a corrupt UNDO datafile so I created a new one, made that the UNDO Tablespace for the database. Restarted the database without problem and the system is now up and running.

When I came to drop the old UNDO tablespace I got an error:

ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace

What transactions could be active? No users are logged in?
I looked at V$ROLLNAME ...

SQL> SELECT * FROM v$rollname;

USN NAME
---------- ------------------------------
0 SYSTEM
11 _SYSSMU11$
12 _SYSSMU12$
13 _SYSSMU13$
14 _SYSSMU14$
15 _SYSSMU15$
16 _SYSSMU16$
17 _SYSSMU17$
18 _SYSSMU18$
19 _SYSSMU19$
20 _SYSSMU20$

And this rollback segment doesn't even exist. The V$TABLESPACE view shows the TBS as ONLINE but when I try to offline it it says:

ERROR at line 1:
ORA-01191: file 2 is already offline - cannot do a normal offline
ORA-01110: data file 2: 'J:\ORACLE\ORA92\ADMSDEV1\DATA\AC_UNDO01DEV1.DBF'

If its offline, how can it have an active RBS?

Any ideas?
09-19-2003, 05:35 AM
JMac

Sorry to push this back onto page 1.

But all support have told me to do is to reboot!!! (Which I have done - to no avail)

Anyone have any ideas?
09-19-2003, 10:15 AM
OracleDoc

Jmac,

Here's something you can try to get rid of the old undo tablespace.

In your init.ora you can comment out your UNDO_TABLESPACE parameter. The instance will start but it will use the system rollback segement. If you do this your undo management will not be used. This "should" give you the ablility to clean up your tablespaces.

I got this info from the Oracle9i New features for Admins- page 14-7 if you want to look it up.
09-19-2003, 11:22 AM
JMac

Will that allow me to drop the old undo TBS then? Then shutdown, set the UNDO_TABLESPACE param back to its orig setting and startup again?

Thanks Doc!
09-19-2003, 12:01 PM
OracleDoc

In theory yes. I had a simlar problem and I did run the database with only the system rollback seg.

Just remember though if you're messing with your production database always have a fresh backup on hand incase Murphy decides to poke his head in the situation :)

Good luck and let me know what the out come is!
09-23-2003, 06:12 AM
JMac

Doc,
Having some 'issues' with this:

I shutdown the DB, commented out the UNDO_TABLESPACE entry in the pfile and created a new spfile from this pfile.

I restarted the DB - Did a 'show parameter undo' and saw that the undo_tablespace parameter was still set to the old value.

I did an: ALTER SYSTEM SET UNDO_TABLESPACE=''

Then shutdown and restarted using the pfile/spfile which has that parameter commented out and it, again, restarted with the undo_tablespace set to the old value.

How can I set this parameter to '' and restart the db?
09-23-2003, 06:23 AM
JMac

By also commenting out UNDO_MANAGEMENT=AUTO I managed to get the DB to start with NO undo_tablespace specified and management set to MANUAL.

However I can still not DROP the offline old UNDO tablespace because of this active rolback segment. The alert log continues to have entries when SMON is attempting to recover these segments. The logs are growing extremely quickly and other alert logs for individial processes are clogging up the folders.
09-23-2003, 08:13 AM
OracleDoc

Hmmmm, This is quite perplexing.
If you commented out everything that has to do with undo management and you still got that error, I'm at a loss on that one. Sounds like it's time to bite the bullet and open a TAR. I'm sorry that I couldn't of been more help.
10-02-2003, 05:28 AM
JMac

Sneaky support and their undocumented parameters.

Heres to resolve this one:

(1) Take a Backup.
(2) Shutdown the DB
(2) Set UNDO_MANAGEMENT=MANUAL
(3) Set parameter _OFFLINE_ROLLBACK_SEGMENT = (_SYSSMU1$, _SYSSMU2$...) etc for all the segments that were in the original UNDO tablespace. I checked v$rollname for the name of those active ones in the replacement UNDO TBS (they were numbered 11 - 20) so I put 1 - 10 in the offline_rollback_segment parameter.
(4) Open the DB
(5) Drop each RBS 1 - 10 individually using 'DROP ROLLBACK SEGMENT "seg_name".
(6) Drop the UNDO tablespace.
(7) Shutdown the DB
(8) Comment out or remove the parameter _OFFLINE_ROLLBACK_SEGMENT
(9) Set UNDO_MANAGEMENT back to AUTO
(10) Ensure the UNDO_TABLESPACE is set to your new UNDO TBS name
(11) Restart the DB
(12) Check the alert log for any errors - if all looks pukka then shutdown and take another backup. (You know it makes sense)

_OFFLINE_ROLLBACK_SEGMENT is undocumented and support warn that its use should be restricted to those occasions when support ask you to use it. Be warned!
10-02-2003, 08:38 AM
BJE_DBA

Thats a weird one. I had to restore production to another machine and it went well but the undo TS was corrupted. Setting to manual and dropping after having created a new one did the trick for me.
10-02-2003, 09:33 AM
JMac

It was the active transaction somehow 'stuck' in the offline (corrupted) undo TBS. As the segment held an active transaction the DB kept trying to resolve it and wouldn't let me drop the TBS.

As you say - weird.
10-02-2003, 11:05 AM
OracleDoc

Quote:

(12) Check the alert log for any errors - if all looks pukka then shutdown and take another backup. (You know it makes sense)

Is that a relative of the Kabuki dance?