Fun with UNDO tablespaces

Its been a long time since I last wrote about how things went very wrong when doing something very routine. Thankfully, this time it was on the test system.

It all started when our storage manager complained that our test system is taking too much space. Since it is important to keep a good relation with the storage manager, I agreed to take a look.
cd \u05\oradata\STG ls -lSh
and then, in a different window:
select * from dba_data_files

Turned out that we have two undo tablespaces, taking up over 50G together. Obviously, one of them is not in use and can be dropped. Easy win.
Started by checking which one is in use:
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

And try to drop the other:
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
drop tablespace UNDOTBS2 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

Strange, right? But no problem, I’ll connect to the DB machine, shutdown the test system, startup in restricted mode and then drop it. (Ignoring strange things is the first step on the road to hell!)


ssh dbstg02
sqlplus "/ as sysdba"
....
shutdown immediate
....
startup restrict
....
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

Looks like now we are using the other UNDO? strange, but we won’t let that stop us, right? (mistake #2).

ALTER SYSTEM SET undo_tablespace='UNDOTBS1';
drop tablespace UNDOTBS2 including contents and datafiles;

Deleted file /u05/oradata/STG/undotbs02.dbf
Completed: drop tablespace UNDOTBS2 including contents and datafiles
shutdown immediate
startup

ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong type
ORA-1092 signalled during: ALTER DATABASE OPEN...

That was unexpected, but maybe for some reason the spfile was not written? lets startup nomount and switch to the right undo (Ignoring the 3rd sign that something went wrong, this time too late):

SQL> startup nomount
ORACLE instance started.
...
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> alter system set undo_tablespace=UNDOTBS1;
alter system set undo_tablespace=UNDOTBS1
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES

What? What do you mean “does not exist”? When in trouble or in doubt, use pfiles.

SQL> create pfile='/tmp/chen1.ini' from spfile;
File created.

And in the pfile I see…

STG1.undo_tablespace='UNDOTBS1'
STG2.undo_tablespace='UNDOTBS2'
*.undo_tablespace='UNDOTBS1'

Oops! I completely forgot that I’m maintaining a RAC system, and that “Oracle RAC automatically manages undo segments within a specific undo tablespace that is assigned to an instance. ” Each RAC node should have its own undo tablespace!

Fixing the mess:
I edited the pfile to show only the one undo tablespace I have and started one node.
Once I had a running database, I could recreate the missing tablespace:
create undo tablespace UNDOTBS2 datafile '/u05/oradata/STG/undotbs2_01.dbf' size 2G;
And assign each instance its own tablespace once again:
alter system set undo_tablespace=UNDOTBS1 SID='STG1';
alter system set undo_tablespace=UNDOTBS2 SID='STG2';

Of course, messing up a system like that should only be done by trained professionals. Kids, don’t try this at home!

*****
Messing up my own system is not nearly fun enough. Immediately after OOW2007, I started a habit of telling people how to mess up their system through the OTN forum. I’m happy to say that I messed up enough of these to get that nice “active member” gold icon next to my name 🙂

Advertisements

9 Comments on “Fun with UNDO tablespaces”

  1. mdinh says:

    Good stuff.

    It’s so easy to forget to specify SID.

  2. prodlife says:

    Yes, but it takes special talent to completely forget that the system I’m working with is RAC!

  3. Interesting. I never use SHOW PARAMETER, but rather select * from v$parameter. I’m assuming select * from gv$parameter would have shown that both tablespaces were in use? Maybe a new habit would be to always use gv$parameter…

  4. Hmmm.. Might also be a good idea to modify glogin.sql to alter the prompt to tell you you’ve connected to an instance participating in a RAC cluster…

  5. heheh 🙂

    that is what I call the RAC mindset.. happened to me in a slightly different form: http://oraclezone.wordpress.com/2007/10/17/getting-the-oracle-rac-mindset/

  6. lutz says:

    what was the name of this disease again?
    It starts with an “A” I am pretty sure about that.

    =;-)
    Have a nice day Chen
    BR,
    Lutz

  7. Dominica Leung says:

    Hi Chen,

    Thanks for posting your blog about undotbsp. Very interesting.
    Also your system is RAC.
    I myself never run RAC or manage oracle RAC.
    And I kind of afraid of RAC, need to learn more at home first.
    Need to build my own db server at home to try out RAC.

    Dominica L.

  8. gsorbara says:

    Nice 😉
    right at the beginning of the article when I saw the two tablespaces i thought: hmm might be this guy is fiddling with a rac installation?
    Yeah… but it happens, anybody has his own five minutes of insanity. So to share, mine was a chown -R oracle.dba from the root of a unix system fresh installed 🙂

    g


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s