Deferred Rollback Segments

From: Tracy Rahmlow <tracy.rahmlow_at_aexp.com>
Date: Tue, 11 May 2004 10:05:02 -0500
Message-ID: <OF9EF079F6.D9F8F363-ON86256E91.004D2538-86256E91.0052D5A7@ipc.us.aexp.com>

We have upgraded from 8.1.7.4 to 9.2.0.5 on AIX 5.1 and converted to automated undo. All tablespaces are locally managed except for system. During the conversion of the system tablespace from DMT to LMT we receive an ORA-03113. We have filed a TAR and Oracle states that we have deferred rollback segments that were never applied or never removed after application. (specifically to the user_data tablespace). All of our tablespaces are online including user_data. Their solution is to create a new database and import all the data to the new database due to the "corruption". I have researched several other databases and we have an additional 20+ databases with deferred rollback segments within the system tablespace. (these databases do not have any offline tablespaces either) Here is the query I ran:
select * from dba_extents where segment_type = 'DEFERRED ROLLBACK';

Has anyone experienced something similar? Any ideas on why we would have so many databases with this issue? I am not crazy about rebuilding 20+ databases. Is there a way to remove these segments? Here is Oracle's explanation which was included in the tar:

A "Deferred Rollback" segment is created for a tablespace when a tablespace is taken offline. Undo records are written to the deferred rollback segment of a tablespace when undo is being applied to a tablespace which is offline, hence, the undo cannot be applied.

The undo stored in the deferred rollback segment is applied when the tablespace comes back online. The deferred rollback segment is automatically removed after the undo has been applied.

Deferred rollback segments always reside in the system tablespace.

Please help! Thanks

American Express made the following
annotations on 05/11/2004 08:04:54 AM

"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Tue May 11 2004 - 10:08:54 CDT

Deferred Rollback Segments - Help!