Oracle7 Server Administrator's Guide

Taking Rollback Segments Online and Offline

This section describes aspects of taking rollback segments online and offline, and includes the following topics:

Bringing Rollback Segments Online

Taking Rollback Segments Offline

A rollback segment is either online and available to transactions, or offline and unavailable to transactions. Generally, rollback segments are online and available for use by transactions.

To take a rollback segment online or offline, you must have the ALTER ROLLBACK SEGMENT system privilege.

You may wish to take online rollback segments offline in the following situations:

When you want to take a tablespace offline, and the tablespace contains rollback segments. You cannot take a tablespace offline if it contains rollback segments that transactions are currently using. To prevent associated rollback segments from being used, you can take them offline before taking the tablespace offline.

You want to drop a rollback segment, but cannot because transactions are currently using it. To prevent the rollback segment from being used, you can take it offline before dropping it.

Note: You cannot take the SYSTEM rollback segment offline.

You might later want to bring an offline rollback segment back online so that transactions can use it. When a rollback segment is created, it is initially offline, and you must explicitly bring a newly created rollback segment online before it can be used by an instance's transactions. You can bring an offline rollback segment online via any instance accessing the database that contains the rollback segment.

Bringing Rollback Segments Online

You can bring online only a rollback segment whose current status (as shown in the DBA_ROLLBACK_SEGS data dictionary view) is OFFLINE or PARTLY AVAILABLE. To bring an offline rollback segment online, use either the Place Online menu item of Server Manager or the SQL command ALTER ROLLBACK SEGMENT with the ONLINE option.

Bringing a PARTLY AVAILABLE Rollback Segment Online

A rollback segment in the PARTLY AVAILABLE state contains data for an in-doubt or recovered distributed transaction, and yet to be recovered transactions. You can view its status in the data dictionary view DBA_ROLLBACK_SEGS as PARTLY AVAILABLE. The rollback segment usually remains in this state until the transaction is resolved either automatically by RECO, or manually by a DBA. However, you might find that all rollback segments are PARTLY AVAILABLE. In this case, you can bring a PARTLY AVAILABLE segment online, as described above.

Some resources used by the rollback segment for the in-doubt transaction remain inaccessible until the transaction is resolved. As a result, the rollback segment may have to grow if other transactions assigned to it need additional space.

As an alternative to bringing a PARTLY AVAILABLE segment online, you might find it easier to create a new rollback segment temporarily, until the in-doubt transaction is resolved.

Bringing a Rollback Segment Online Automatically

If you would like a rollback segment to be automatically brought online whenever you start up the database, add the segment's name to the ROLLBACK_SEGMENTS parameter in the database's parameter file.

Bringing Rollback Segments Online: Example

The following statement brings the rollback segment USER_RS_2 online:

ALTER ROLLBACK SEGMENT user_rs_2 ONLINE;

After you bring a rollback segment online, its status in the data dictionary view DBA_ROLLBACK_SEGS is ONLINE.

See Also: For information about the ROLLBACK_SEGMENTS and DBA_ROLLBACK_SEGS parameters, see the Oracle7 Server Reference.

To see a query for checking rollback segment state, see "Displaying Rollback Segment Information" .

Taking Rollback Segments Offline

To take an online rollback segment offline, use either the Take Offline menu item of Server Manager, or the ALTER ROLLBACK SEGMENT command with the OFFLINE option. The rollback segment's status in the DBA_ROLLBACK_SEGS data dictionary view must be "ONLINE", and the rollback segment must be acquired by the current instance.

The following example takes the rollback segment USER_RS_2 offline:

ALTER ROLLBACK SEGMENT user_rs_2 OFFLINE;

If you try to take a rollback segment that does not contain active rollback entries offline, Oracle immediately takes the segment offline and changes its status to "OFFLINE".

In contrast, if you try to take a rollback segment that contains rollback data for active transactions (local, remote, or distributed) offline, Oracle makes the rollback segment unavailable to future transactions and takes it offline after all the active transactions using the rollback segment complete. Until the transactions complete, the rollback segment cannot be brought online by any instance other than the one that was trying to take it offline. During this period, the rollback segment's status in the view DBA_ROLLBACK_SEGS remains ONLINE; however, the rollback segment's status in the view V$ROLLSTAT is PENDING OFFLINE.

The instance that tried to take a rollback segment offline and caused it to change to PENDING OFFLINE can bring it back online at any time; if the rollback segment is brought back online, it will function normally.

Taking Public and Private Rollback Segments Offline

After you take a public or private rollback segment offline, it remains offline until you explicitly bring it back online or you restart the instance.

See Also: For information on viewing rollback segment status, see "Displaying Rollback Segment Information" .

For information about the views DBA_ROLLBACK_SEGS and V$ROLLSTAT, see the Oracle7 Server Reference.