Transcript Rollback Segment

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
SELECT *
FROM table
New image
Image at statement
commencement
10-7
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 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
10-11
Transactions and Rollback
Segments Example
Begin at extent 3
1
Transaction 1
Transaction 2
4
Active extent
10-12
2
3
When full, move
to extent 4
(called a wrap)
When that fills,
can use the 1st
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 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
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
of extending
0.30
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 rbs01 ONLINE;
• Specify the following initialization
parameter to ensure rollback segments
are brought online at STARTUP:
ROLLBACK_SEGMENTS=(rbs01, rbs02)
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
ALTER ROLLBACK SEGMENT rbs01
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 rbs01
SHRINK TO 4M;
10-22
Taking Rollback Segment Offline
Take a rollback segment offline to make
it unavailable.
ALTER ROLLBACK SEGMENT rbs01
OFFLINE;
10-23
Dropping Rollback Segments
A rollback segment must be offline before
it can be dropped.
DROP ROLLBACK SEGMENT rbs01;
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