10 Managing Rollback Segments Objectives Planning the number

10 Managing Rollback Segments

Objectives • Planning the number and size of rollback segments • Creating rollback segments using appropriate storage settings • Maintaining rollback segments • Obtaining rollback segment information from the data dictionary • Troubleshooting rollback segment problems 10 -2

Rollback Segment (Introduction) • Used for delete and update operations only • Used to ‘undo’ a transaction • Used to save the old value when a process changes data. • Stores location and value of data before modification • A transaction can use only one rollback segment 10 -3

Rollback Segment Old image Table New image Rollback segment Update transaction 10 -4

Rollback Segment (Steps) • Data block brought into DB buffer • Rollback segment brought into DB buffer • Old data written to the rollback segment • Update made to data block 10 -5

Rollback Segments: Purpose Transaction recovery (when instance fails) Transaction rollback Rollback segment 10 -6 Read consistency

Read-Consistency Table 10 -7 SELECT * FROM table New image Image at statement commencement

Read-Consistency • When Oracle begins execution of a SELECT statement, it determines the current SCN • Ensures that changes not committed before the SCN are not processed by the statement • In the case of a long running transaction, Oracle constructs a read-consistent image of the block by retrieving the before-image and applying changes to it in memory 10 -8

Read-Consistency • Read consistency is always provided for a SQL statement • Request read consistency for a transaction: SET TRANSACTION READ ONLY; Or SET TRANSACTION SERIALIZABLE; 10 -9

Types of Rollback Segments • SYSTEM: used for objects in the SYSTEM tablespace; created in the SYSTEM ts • Non-SYSTEM: objects in other tablespaces – At least one needed – Private: acquire by a single instance – Public: acquired by any instance (used with parallel server) 10 -10

Transactions and Rollback Segments • Transactions need rollback segments to be assigned: – SET TRANSACTION Use Rollback Segment rbs 1 • If no request is made, Oracle uses segment with fewest transactions • Transactions use extents in a circular fashion • More than one transaction can write to the same extent of a segment; however each block contains information from only one transaction 10 -11

Transactions and Rollback Segments Example 1 Transaction 2 Active extent 10 -12 4 2 3 Begin at extent 3 When full, move to extent 4 (called a wrap) When that fills, can use the 1 st extent if it is free or inactive Inactive extent

Growth of Rollback Segments Cannot skip an extent; if next extent is being used, an additional extent is allocated. This is called an extend. Can grow this way until it reaches the maximum number of extents specified in the MAXEXTENTS parameter 1 4 2 3 2 1 5 3 4 Active extent Inactive extent 10 -13 New extent

Shrinkage of Rollback Segments 6 1 1 2 6 3 2 5 4 3 Active extent Optimal 10 -14 Inactive extent

Shrinkage of Rollback Segments • Deallocation of extents is not done as soon as transactions end. • Deallocation performed when head moves from one extent to next and: • Current size of rollback segment > OPTIMAL • There are contiguous inactive extents 10 -15

Creating Rollback Segments Example CREATE ROLLBACK SEGMENT rbs 01 TABLESPACE rbs STORAGE ( INITIAL 100 K NEXT 100 K OPTIMAL 4 M MINEXTENTS 20 MAXEXTENTS 100); MINEXTENTS: at least 2; PCTINCREASE not specified: always 0 Always use INITIAL = NEXT; OPTIMAL specifies size in bytes that the rollback segment must shrink to, if possible AVOID setting MAXEXTENTS to UNLIMITED: can cause unnecessary extension due to program error 10 -16

Planning Rollback Segments: Number • OLTP – Small, but many segments – One RBS/4 transactions • Batch – Large, but few • Always place rollback segments in a separate exclusive tablespace • Always use INITIAL=NEXT • PCTINCREASE cannot be specified (always 0) • Set an OPTIMAL value 10 -17

Planning Rollback Segments: Number of Extents 0. 50 0. 40 Probability 0. 30 of extending 0. 20 0. 10 0. 00 0 10 20 30 40 Number of extents 10 -18

Bringing Rollback Segments Online • Use the following command to make a rollback segment available (when created, they are offline): ALTER ROLLBACK SEGMENT rbs 01 ONLINE; • Specify the following initialization parameter to ensure rollback segments are brought online at STARTUP: ROLLBACK_SEGMENTS=(rbs 01, rbs 02) 10 -19

How Instances Acquire Rollback Segments when DB is opened Acquire named private RBS Compute required number of RBS Are there enough RBS ? Yes Bring all acquired RBSs ONLINE 10 -20 No Acquire public RBS

Changing Rollback Segment Storage Settings Use ALTER ROLLBACK SEGMENT rbs 01 STORAGE( MAXEXTENTS 200 ); 10 -21

Manually Deallocating Space from Rollback Segments If OPTIMAL is specified, Oracle will attempt to deallocate Use ALTER ROLLBACK SEGMENT (if extents are active, may not shrink to the requested size) ALTER ROLLBACK SEGMENT rbs 01 SHRINK TO 4 M; 10 -22

Taking Rollback Segment Offline Take a rollback segment offline to make it unavailable. ALTER ROLLBACK SEGMENT rbs 01 OFFLINE; 10 -23

Dropping Rollback Segments A rollback segment must be offline before it can be dropped. DROP ROLLBACK SEGMENT rbs 01; 10 -24

Rollback Segments in the Database DBA_ROLLBACK_SEGS • Identification – SEGMENT_ID – SEGMENT_NAME • Location, type, and status – TABLESPACE_NAME – OWNER (PUBLIC or SYS) – STATUS (ONLINE or OFFLINE) 10 -25

Rollback Segments in the Database Select segment_name, tablespace_name, owner, status From dba_rollback_segs; 10 -26

Rollback Segment Statistics V$ROLLNAME USN NAME 10 -27 V$ROLLSTAT USN EXTENTS RSSIZE XACTS OPTSIZE HWMSIZE AVEACTIVE STATUS CUREXT CURBLK

Rollback Segment: Current Activity V$SESSION SADDR USERNAME SID SERIAL# 10 -28 V$TRANSACTION SES_ADDR XIDUSN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC

Rollback Segment Problems • Insufficient space for transactions • Read-consistency error • Blocking transaction • Error in taking tablespace offline 10 -29

Insufficient Space for Transactions • No space in tablespace to extend – Extend data files – Allow automatic extension of data files – Add data files • MAXEXTENTS reached for segment – Increase MAXEXTENTS – Recreate segments with larger extent sizes 10 -30

Read-Consistency Error (snapshot too old) Table SELECT * FROM table Reused block A long running Trans Another transaction commits Rollback segment is reused New image Image at statement commencement 10 -31 Soln: Higher MINEXTENTS; larger extents; higher OPTIMAL value

Blocking Session Blocking session 4 2 1 1 Extent 3 3 5 2 3 4 Existing extent New extent 10 -32 A long running trans keeps an extent occupied and blocks other trans from writing to it; segment keeps extending Soln: DBA intervention

Error in Taking a Tablespace Offline Cannot take tablespace containing active RBS offline • Check rollback segments in the tablespace • Find active transactions using these rollback segments • Find session ID and serial number • Terminate the session, if necessary 10 -33

Summary • Creating adequate rollback segments • Troubleshooting rollback segment problems 10 -34