Oracle Rollback Segment Management

Oracle
Rollback segment management is always a difficult problem in Oracle database management, this paper introduces the concept, usage, planning and problem solving of Oracle rollback segment through an example.

Rollback Segment Overview

The rollback segment is used to hold the value before the data modification, including the position and value before the data was modified. The head of the rollback segment contains information about the rollback segment transaction that is in use. A transaction can only use a rollback segment to hold its rollback information, and a rollback segment may hold multiple transaction rollback information.

The role of the rollback segment

Transaction rollback: When the transaction modifies the data in the table, the value before the data modification (that is, the front image) is stored in the rollback segment, and when the user rolls back the transaction (ROLLBACK), Oracle will use the data before the rollback segment to restore the modified data to its original value.

Transaction recovery: When the transaction is being processed, the routine fails and the rollback segment information is saved in the Redo log file, and Oracle will use the rollback to recover the uncommitted data the next time the database is opened.

Read consistency: When a session is modifying data, other sessions will not see the uncommitted changes to the session. Also, when a statement is executing, the statement does not see uncommitted modifications (statement-level read consistency) that have been executed since the statement started. When Oracle executes a SELECT statement, Oracle follows the current system change number (SYSTEMCHANGENUMBER-SCN) to ensure that any uncommitted changes prior to the current SCN are not handled by the statement. As you can imagine: When a long-running query is executing, if another session changes a block of data to be queried by the query, Oracle constructs a read-consistent view with the data-fore image of the rollback segment.

Read consistency at transaction level

Oracle generally provides read consistency for SQL statement level (sqlstatementlevel), and you can use the following statement to implement transactional-level read consistency.

Settransactionreadonly;

Or:

settannsactionserializable;

All two of these statements provide read consistency after the transaction starts. It should be noted that using the second statement will have an impact on the concurrency and performance of the database.

Type of rollback segment

System rollback segment: When the database is created, a system rollback segment is automatically created, which is used only to hold the front image of objects in the system tablespace.

Non-system rollback segment: A database that has more than one table space should have at least one non-system rollback segment for storing data-front images of objects in a system-table space. The non-system rollback segment is divided into private rollback segment and public rollback segment, and the private rollback segment should be listed in the parameter file's rollbacksegments parameter so that it can be automatically online when the routine starts. Public rollback segments typically appear in OPS (Oracle parallel servers) and are automatically online when the routine is started.

defered rollback segment: The rollback segment is automatically created by the system when the tablespace is offline (OFFLINE) and automatically deleted by the system when the tablespace is online again, which is used to store rollback information generated when the tablespace is offline.

Use of rollback segment

Assign a rollback segment: When the transaction begins, Oracle assigns a rollback segment to the transaction and assigns the rollback segment with the least transaction to the transaction. A transaction can request a specified rollback segment with the following statement:

Settranstractionuserollbacksegmentrollback_segment

Transactions use the area of the rollback segment (extents) in a sequential, circular manner, and move to the next area when the current area is full. Several transactions can be written in the same area of the rollback segment, but the block of each rollback segment can only contain information for one transaction.

For example (two transactions use the same rollback segment, and the rollback segment has four extents):

1. Transactions are in progress, and they are using the third area of the rollback segment;

2. When two transactions generate more rollback information, they will continue to use the third zone;

3, when the third area full, the transaction will be written to the fourth area, when the transaction begins to write to a new area, called Flip (WRAP);

4. When the fourth area is full, if the first zone is idle or inactive (all transactions using the zone are completed without active transactions), the transaction will then use the first zone.

Expansion of the rollback segment (EXTEND)

When all the blocks of the current rollback segment are exhausted and the transaction requires more rollback space, the pointer to the rollback segment moves to the next area. When the last area is run out, the pointer moves to the front of the first area. The rollback segment pointer moves to the next area if the next zone has no active transactions, and the pointer cannot cross the zone. When the next zone is in use, the transaction assigns a new zone to the rollback segment, which is called the extension of the rollback segment. The rollback segment extends to the number of the rollback segment area until the parameter maxextents of the rollback segment reaches the value.

Recovery and optimal parameters for rollback segments

The optimal parameter indicates where the rollback segment is shrunk to when it is idle, indicating that the optimal parameter of the rollback segment can reduce the waste of the rollback segment space.

Create a rollback segment

Grammar:

CREATE [public] ROLLBACK SEGMENT rollback_segment
[Tablespace tablespace]
[STORAGE ([INITIAL integer[k| M]] [NEXT integer[k| M]]
[Minextents integer]
[Maxtents {integer| UNLIMITED}]
[OPTIMAL {integer[k| m]| NULL}])

Note:


The rollback segment can be specified private or public when it is created and cannot be modified once it is created. Minextents must be greater than or equal to 2 pctincrease must be 0 optimal if you want to specify, you must be greater than or equal to the initial size of the rollback segment (specified by minextents)

Suggestions:

Under normal circumstances, Initial=next set optimal parameters to save space use do not set maxextents for unlimited rollback segment should be created in a specific rollback segment tablespace

Cases:

CREATE ROLLBACK SEGMENT rbs01
Tablespace RBS
STORAGE (INITIAL 100K NEXT 100K minextents 10
Maxextents OPTIMAL 1000K);

Make the rollback segment online

When the rollback segment is created, the rollback segment is offline and cannot be used by the database, and the rollback segment must be online in order for the rollback segment to be exploited by the transaction. You can use the following command to make the rollback segment online:

ALTER ROLLBACK SEGMENT rollback_segment ONLINE;

Cases:

ALTER ROLLBACK SEGMENT rbs01 ONLINE;

To make the rollback segment automatically online when the database is started, you can list the name of the rollback segment in the database's parameter file. For example, add the following line to the parameter file:

Rollback_segment= (RBS01,RBS02)

Modifying storage parameters for a rollback segment

You can use the Alter ROLLBACK segment command to modify the storage parameters (including optimal,maxextents) of the rollback segment.

Grammar:

ALTER ROLLBACK SEGMENT rollback_segment
[STORAGE ([NEXT integer[k| M]]
[Minextents integer]
[Maxextents {integer| UNLIMITED}]
[OPTIMAL {integer[k| m]| NULL}])

Cases:

ALTER ROLLBACK SEGMENT rbs01 STORAGE (maxextents 1000);

Reclaim space for a rollback segment

If the optimal parameter of the rollback segment is specified, Oracle automatically reclaims the roll segment to the optimal specified location. The user can also manually recycle the space of the rollback segment.

Grammar:

ALTER ROLLBACK SEGMENT rollback_segment SHRINK [to Integer [k| M]];

Description

If the value of to integer is not indicated, Oracle will attempt to reclaim the location of the optimal.

Cases:

ALTER ROLLBACK SEGMENT rbs01 SHRINK to 2M;

To get the rollback segment offline

The rollback segment will be offline for the following two purposes:

1. Prevent new transactions from using the rollback segment;

2. The rollback segment must be deleted.

Grammar:

ALTER ROLLBACK SEGMENT rollback_segment OFFLINE;

Cases:

ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

Description

If a transaction is using the rollback segment, the status of the rollback segment will be pending OFFLINE after the command is run. After the transaction ends, the status is changed to offline, and the status of the segment can be rolled back by V$rollstat query.

Delete a rollback segment

You can delete a rollback segment when it is no longer needed or to be rebuilt to change the Initial,next or minextents parameters. To delete a rollback segment, do not allow the rollback segment to be offline.

Grammar:

DROP ROLLBACK SEGMENT rollback_segment;

Cases:

DROP ROLLBACK SEGMENT rbs01;

Querying for information about rollback segments

Data dictionary used: Dba_rollback_segs

Information you can query: the identity of the rollback segment (segment_id), the name (segment_name), the table space (tablespace_name), type (OWNER), status (status).

Cases:

Sql>select segment_name,tablespace_name,owner,status from Dba_rollback_segs;

Statistics for rolling back segments

Data dictionary: V$rollname,v$rollstat

Cases:

Sql>select N.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status
From V$rollname N,v$rollstat s
WHERE N.usn=s.usn;



The current active transaction of the rollback segment

Data dictionary: v$session,v$transaction

Cases:


Sql>select s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublkfrom v$session s,v$transaction TWHERE S.saddr=t.ses_ Addr Usernamexidusn ubafil ubablkused_ublk------------------------------------------------SYSTEM22 SCOTT 121631 2 rows Selected.


Planning for the number of rollback segments

For OLTP systems, there are a lot of small transactions, generally recommended: a large number of small rollback segments, a rollback segment per four transactions, and no more than 10 transactions per rollback segment.

For batch processing, it is generally recommended that fewer large rollback segments be rolled back, and one rollback segment per transaction.

Problems and solutions of rollback segment

Problem one: The transaction requirements of the rollback segment space is not enough, the performance of the table space with full (ORA-01560 error), rollback segment extension reached the value of the parameter maxextents (ORA-01628).

Workaround: Add a file to the rollback segment tablespace or make the existing file larger, and increase the value of the maxextents.

Problem Two: Read consistency error (ORA-01555 SNAPSHOT TOO old)

Workaround: Increase the value of the minextents, increase the size of the zone, and set a high optimal value.