Re: unsure of how to handle problematic undotbs

Comments embedded.
ujangmz_at_gmail.com wrote:
> Hi,
>
> I have just started a DBA job to maintain oracle 9i database on
> RHAS3.0. One of the databases has a huge UNDOTBS1 that doesn't seem to
> shrink by itself. The size of UNDOTBS1 now is more than 60GB which is
> totally ridiculous!

Ridiculous to whom? You? What experience leads you to this conclusion? To what value is undo_retention set? Before you make such 'blanket' claims you should perform a bit of research.

>
> What is the safe way to shrink the undotbs ? I have searched the
> internet and other people seems to suggest that I create and use
> UNDOTBS2 temporarily, then totally drop UNDOTBS1 and then recreate and
> use UNDOTBS1.
>

Without checking on the values to which your undo_* parameters are set this would be an exercise in futility.

> But I am not confident enough to totally drop the UNDOTBS1 and what I
> did instead was, create and use UNDOTBS2, then I put UNDOTBS1 offline.
> I would then observe the database for a while, if everything looks
> normal, only then will I totally drop the problematic UNDOTBS1.
>

And, if it doesn't look 'normal' but continues along this same path, then what shall you do?

> As soon as I put UNDOTBS1 offline, my users were unable to write to the
> database. I guest it is still pointing to UNDOTBS1. However, at the
> same time I detect an increase usage in UNDOTBS2, so I know it is
> pointing to UNDOTBS2. When I turn UNDOTBS1 back online everything is
> back to normal! What's going on?
>

You can't simply create another UNDO tablespace without informing Oracle that you've done so and now wish to use it; the undo_tablespace parameter now needs to 'point' to this new UNDO tablespace you desperately desire to use. A simple:

alter system set undo_tablespace=UNDOTBS2;

should be all that is needed. I now have serious doubts as to your ability to manage this undo tablespace situation that you believe exists.

> My objective is of course to drop UNDOTBS1 and reclaim the space. How
> do I do this without dropping UNDOTBS1 immediately. There is no
> particular need for me to use the name UNDOTBS1, and I will be
> satisfied if I have to use UNDOTBS2 from now on. I just want to get
> back the space. Please advise.

You cannot guarantee that reclaiming this space now will result in a permanent space gain after 30 days have passed as you've failed to check the relevant undo_* parameter settings in force. You *need* to check the following parameter settings:

undo_management
undo_retention
undo_suppress_errors
undo_tablespace

You already know undo_tablespace is set incorrectly to use your brand new UNDOTBS2 tablespace (that you weren't aware of this before now leaves me to wonder what you *do* know regarding undo tablespace management); what you do *not* know is the undo retention policy in force, which could be the source of this 'ridiculous' 60 gb UNTOTBS1. Without fully investigating this 'problem' you can never hope to 'solve' it.

>
> -Ujang

David Fitzjarrell Received on Mon Jul 24 2006 - 10:02:24 CDT