Can't drop UNDO tablespace - active rollback.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Can't drop UNDO tablespace - active rollback.

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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?

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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!

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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!
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  8. #8
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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. #10
    Join Date
    Jan 2003
    Location
    Denver
    Posts
    152
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width