Re: Unable to really switch undo tablespace

Hi,

I think the issue here is that some of the datablocks haven't been cleaned up after transactions in those have completed. So when you read such block, it has some rows in it with lock bytes set, ITLs pointing to old undo segments.

When the undo segments still exist in data dictionary (despite their tablespace is online), Oracle wants to read their headers to see whether the transactions corresponding to uncleaned blocks are committed.

The solutions be:

1. drop old undo tablespace - then Oracle knows that the transactions have finished (as you couldn't drop the undo segments otherwise) or
2. performn delayed block cleanout on all blocks which could be unclean, having references back to old undo tablespace. This should be done both for tables and indexes. You could either force a full table scan on all suspect tables and fast full index scan on all of their indexes - or you could do an analyze on all those segments (btw, make sure that you analyze 100% of these then, not just a sample). This should result in cleaning up all datablocks.

Tanel.

• Original Message ----- From: "Wolfgang Breitling" To: Cc: "ORACLE-L" Sent: Wednesday, December 21, 2005 8:56 AM Subject: Re: Unable to really switch undo tablespace

>I had the same problem some time ago. The solution, and IIRC I found it
>even on Metalink, was to offline and then drop the old undo tablespace. I
>know, it sounds a bit unnerving. I tried the same as you - offline the
>tablespace for a while, THEN drop it. But as I said, I ran into the same
>problem that some transactions attempted to use the offlined OLD undo
>tablespace - even after a restart. In order to feel better about it I
>dropped the old undo tablespace immediately after a startup where I could
>be reasonably sure that no transactions would be active yet.
>
> Giovanni Cuccu wrote:
>> Hi all,
>> I'm facing a problem with undo tablespace with oracle 9.2.0.4
>> running on linux redhat 3.
>> Due to an error a procedure has consumed a lot of undo space, causing
>> the growth of the undo tablespace datafile up to 16gb (the datafile
>> was in autoextend mode). Now I'd like to go back to the previous
>> situation where the undo tablespace was 4gb.
>> I created a new undo, namely undotbs_02, and made the switch as
>> described in the manual via
>> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
>> the switch completed but when I tried (some weeks later and after a
>> clean shutodown) to offline the orginal one some of the programs began
>> to report
>> "unable to read datafile 2..."
>> Needless to say datafile 2 is the old undo tablespace.
>> If I query the dba_rollback_segs I can see all the rollback segments
>> belonging to the old undo tablespace as offline, the dba_undo_extents
>> show the status as unexpired.
>> I already shutdown immediate the database, after the switch and server
>> days later, is there anything I can do to get out of this enpasse?
>> I already opened a tar (now SR) but until now I didn't received any
>> useful suggestion.
>> Thanks to all,
>> Giovanni
>>
>>
>>
>>
>> --
>> --------------------------------------------------------------------
>> Another free oracle resource profiler
>> http://sourceforge.net/projects/oraresprof/
>> Now version 0.9
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> http://www.centrexcc.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l