ROLLBACK SEGMENTS

ROLLBACK SEGMENTS

Rollback segments are used to store the previous image of the data. This is essential because of the following reasons:

ü  To provide read consistency i.e. when ever users are accessing the data that is being modified by other users they all should receive the same data. This is provided using the data stored in rollback segments

ü  To Rollback the transaction i.e. whenever the user wants to rollback the transaction the Rollback segments are used to get the previous data.

ü  To recover the database i.e. whenever the database is restarted, firstly the rollback segments are checked for any uncommitted/committed  data and if there is any committed data then the database is reflected with the data and if there is any uncommitted data then the transaction is rolled back.

Using Multiple Rollback Segments

Using multiple rollback segments distributes the rollback segments contention across many segments and hence increases the performance. Multiple rollback segments are required in the following situations:

1. When a database is created a single rollback segment named SYSTEM is created in SYSTEM tablespace. This is used for system level rollback transactions. You cannot create any objects in non-system (user) tablespaces until at least one rollback segment is created and brought online in the system tablespace
2. When many transactions are running concurrently, more rollback information is generated at the same time. So we need to create many rollback segments .We can specify the max. number of transaction that we expect using Transaction  and the number of transactions per rollback segment using  TRANSACTIONS_PER_ROLLBACK_SEGMENT in the parameter file. Then when the database is started the instance tries to acquire the (Transaction/Transaction_per_rollback_segment) no of rollback segments.

The extents in a rollback segment takes the shape of a circular buffer i.e. if all the extents in a rollback segment are filled up then the first extent is taken to fill the information so if the data in that extent is not committed then we get the snap shot too old error.

Rollback Segment Assignment

Transactions are assigned to rollback segments in a round-robin manner attempting to keep all rollback segments equally busy. In this example all three rollback segments are equally “busy,” they each have one active transaction.

When transaction 4 requires rollback segment space, it will be assigned to RBS1. If a transaction exceeds the number of blocks in its extent, a second extent is allocated. Rollback segments are filled in a circular fashion so that old data is available for the longest time possible. If a rollback segment extends as far as possible, a wrap will occur, causing old transactions to be overwritten. If the first transaction in the rollback segment is still active, (i.e.  it has not been committed), the wrap will fail and the user will receive an error message. A single transaction must always begin and end in the same rollback segment – transactions cannot span rollback segments.

Public vs. Private Rollback Segments

Public Rollback segments forms a pool of rollback segments which can be acquired by any instance.

Private Rollback Segments can only used by the instance that owns them.

Ø  To specify that the instance acquire the rollback segments automatically, we need to specify the rollback segments in parameter file using the parameter ROLLBACK_SEGMENT and specifying the name(s) of the rollback segments

Ø  Create a tablespace specifically for the rollback segments, this provides

a.    A tablespace holding rollback segments can always be kept online .

b.    Because the tablespaces with active transactions cannot be taken offline, designating a separate tablespace allows other data to be made offline.

c.    A tablespace containing the rollback segments are likely to get more fragmented  because of frequent allocation and de-allocation

d.     

Creating a rollback segment:

To create a rollback segment, you must have create rollback segment privilege

We can create a rollback segment either using Enterprise manager or sql command

CREATE PUBLIC ROLLBACK SEGMENT <rollback segment name> TABLESPACE <Tablespace name>;

The tablespace should be online before creating the rollback segments.

Setting the storage parameters

CREATE PUBLIC ROLLBACK SEGMENT <segment name>

                 TABLESPACE <tablespace name>

                 STORAGE (

      INITIAL <size >

      NEXT <size>

      OPTIMAL <size>

      MINEXTENTS <number>

      MAXEXTENTS <number>);

You can also use create Rollback segment property sheet of enterprise Manager to set the rollback segment‘s storage parameters.

Optimal Parameter

The Optimal parameter is used to specify the size to which a rollback segment will shrink after automatically extending itself. If the optimal parameter is not specified when a rollback segment is created, any additional extents dynamically allocated for the rollback segment will remain allocated. By setting the optimal parameter the database administrator can control the size of rollback

We can alter the storage parameter of the rollback segment after creating the rollback segment .We can affect the future extents but not the extents that are already allocated.

ALTER PUBLIC ROLLBACK SEGMENT <segment name> 

                               STORAGE (<storage parameters>);

You can alter the settings for the SYSTEM rollback segment, including the OPTIMAL parameter, just as you can alter those of any rollback segment.

Example:

ALTER PUBLIC ROLLBACK SEGMENT data1_rs 

                               STORAGE (MAXEXTENTS 120);

Note: To alter rollback segments, you must have the ALTER ROLLBACK SEGMENT system privilege

To shrink a rollback segment using you must have the ALTER ROLLBACK SEGMENT system privilege. 

You can manually decrease the size of a rollback segment using the SQL command ALTER ROLLBACK SEGMENT. The rollback segment you are trying shrink must be online. 

The following statement shrinks rollback segment RBS1 to 100K: 

ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

Rollback segments can be offline or online. Offline rollback segments cannot be used for transaction and generally rollback segments are online for use by transactions

To display the status of the rollback segments

Select segment_name, tablespace_name,status from sys.dba_rollback_segs;

Status                                    Description

IN USE                                   online

AVAILABLE                          created but not online

OFFLINE                               offline

INVALID                                Dropped

NEEDS RECOVERY          corrupted

PARTLY AVALIABLE         Unresolved transactions data in Distributed DB

INVALID means the rollback segment has been dropped

If you make a rollback segment offline that contains the active transactions, oracle makes that rollback segment unavailable for other transactions and takes it offline after all the active transactions are completed. 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.

If we expect that a particular transaction has more amount of rollback information then we can explicitly assign a large rollback segment to that transaction.

To assign a transaction to a rollback segment explicitly, the rollback segment must be online for the current instance, and the SET TRANSACTION USE ROLLBACK SEGMENT statement must be the first statement of the transaction. If a specified rollback segment is not online or a SET TRANSACTION USE ROLLBACK SEGMENT statement is not the first statement in a transaction, an error is returned.

Oracle will assign the next available rollback segment to any transaction unless the new transaction explicitly assigned to a specific rollback segment by the user.

Dropping a rollback segment:

You can drop a rollback segment if it is too fragmented or the segment needs to be relocated.

Before dropping a rollback segment make sure that the status of the rollback segment is offline. If the rollback segment that you want to drop is currently ONLINE, PARTLY AVAILABLE, NEEDS RECOVERY or INVALID, then you cannot drop the rollback segment. If the status is INVALID then the segment is already dropped.

To drop a rollback segment you need to have DROP ROLLBACK SEGMENT system privilege.

DROP PUBLIC ROLLBACK SEGMENT <rollback segment name>

If the segment is dropped, remove the entry from parameter file  the name of the rollback segment name from ROLLBACK_SEGMENT .If this step is  not done then the next time the instance starts it will fail because the instance cannot acquire the rollback segment. After the rollback segment is dropped its status in DBA_ROLLBACK_SEGS changes to INVALID . The next time a rollback segment is created, it takes the row vacated by a dropped rollback segment, if one is available, and the dropped rollback segment's row no longer appears in the DBA_ROLLBACK_SEGS view.