Managing Rollback Segments

1
Managing Rollback Segments
2
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

3
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

4
Rollback Segment
Old image
New image
Table
Rollback segment
Update transaction
5
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

6
Rollback Segments Purpose
Transaction recovery (when instance fails)
Transaction rollback
Rollback segment
Read consistency
7
Read-Consistency
SELECT FROM table
Table
New image
Image at statement commencement
8
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

9
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
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)

11
Transactions and Rollback Segments

• Transactions need rollback segments to be
  assigned
• SET TRANSACTION Use Rollback Segment rbs1
• 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

12
Transactions and Rollback Segments Example
Begin at extent 3 When full, move to extent 4
(called a wrap) When that fills, can use the 1st
extent if it is free or inactive
1
2
Transaction 1
Transaction 2
4
3
Active extent
Inactive extent
13
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
2
1
1
2
5
3
4
3
4
Active extent
New extent
Inactive extent
14
Shrinkage of Rollback Segments
6
1
1
2
2
5
6
3
3
4
Active extent
Optimal
Inactive extent
15
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 gt OPTIMAL
• There are contiguous inactive extents

16
Creating Rollback Segments
Example

• CREATE ROLLBACK SEGMENT rbs01
• TABLESPACE rbs
• STORAGE (
• INITIAL 100K NEXT 100K OPTIMAL 4M 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
17
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 INITIALNEXT
• PCTINCREASE cannot be specified (always 0)
• Set an OPTIMAL value

18
Planning Rollback Segments Number of Extents
0.50
0.40
Probabilityof extending
0.30
0.20
0.10
0.00
0
10
20
30
40
Number of extents
19
Bringing Rollback Segments Online

• Use the following command to make a rollback
  segment available (when created, they are
  offline)
• Specify the following initialization parameter
  to ensure rollback segments are brought online at
  STARTUP

• ALTER ROLLBACK SEGMENT rbs01 ONLINE

ROLLBACK_SEGMENTS(rbs01, rbs02)
20
How Instances Acquire Rollback Segments when DB
is opened
Acquire named privateRBS
Computerequired numberof RBS
No
Yes
Acquire publicRBS
Bring all acquired RBSs ONLINE
21
Changing Rollback Segment Storage Settings
Use ALTER ROLLBACK SEGMENT

• ALTER ROLLBACK SEGMENT rbs01
• STORAGE( MAXEXTENTS 200 )

22
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 rbs01
• SHRINK TO 4M

23
Taking Rollback Segment Offline
Take a rollback segment offline to makeit
unavailable.

• ALTER ROLLBACK SEGMENT rbs01 OFFLINE

24
Dropping Rollback Segments
A rollback segment must be offline before it can
be dropped.

• DROP ROLLBACK SEGMENT rbs01

25
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)

26
Rollback Segments in the Database

• Select segment_name, tablespace_name,
• owner, status
• From dba_rollback_segs

27
Rollback Segment Statistics
VROLLNAMEUSNNAME
VROLLSTAT USN EXTENTS RSSIZE
XACTS OPTSIZE HWMSIZE
AVEACTIVE STATUS CUREXT
CURBLK
28
Rollback Segment Current Activity
VSESSION SADDR USERNAME SID SERIAL
VTRANSACTION SES_ADDR XIDUSN UBAFIL
UBABLK UBASQN UBAREC
STATUS USED_UBLK USED_UREC
29
Rollback Segment Problems

• Insufficient space for transactions
• Read-consistency error
• Blocking transaction
• Error in taking tablespace offline

30
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

31
Read-Consistency Error(snapshot too old)
SELECT FROM table
A long running Trans Another transaction
commits Rollback segment is reused Soln Higher
MINEXTENTS larger extents higher OPTIMAL value
Reused block
Table
New image
Image at statement commencement
32
Blocking Session
A long running trans keeps an extent occupied and
blocks other trans from writing to it segment
keeps extending Soln DBA intervention
Blocking session
2
1
4
1
Extent 3
5
3
3
2
4
Existing extent
New extent
33
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

34
Summary

• Creating adequate rollback segments
• Troubleshooting rollback segment problems