Rollback Segments

1
Rollback Segments
Nilendu Misra (MAR99) nilendu_at_innocent.com
2
Areas Covered

• Guidelines to Manage
• Creating/Altering/Dropping RBS
• Storage Parameters
• Monitoring Tuning

3
What Is

• For every transaction taking place RBS keeps the
  BEFORE IMAGE of the DATA
• READ CONSISTENCY
• ROLLBACK Transaction
• RECOVER Database

4
Read Consistency
For a query, only data blocks written on or
before that SCN are read. Blocks changed after
that are read from RBS. e.g.,when this query
started the SCN noted was 10023
5
Number

• During creation SYSTEM RBS
• After creation create several RBS in a separate
  tablespace
• Every RBS should be of same size
• Every EXTENT in a RBS is of same size
• Ideally number of RBS should be equal to Maximum
  number of concurrent transactions (WHY?)

6
Number (Cont.)

• Thumb Rule is to create N RBS where N no. of
  users / 4
• One transaction can use ONE RBS
• Many transactions can acquire the same RBS
• TRANSACTION TABLE contains the address of the row
  modified status(Committed / Active)

7
Number (Cont.)

• CIRCULAR BUFFER. Should have at least 2 extents
• If the NEXT extent is not available, new extent
  is allocated
• PCTINCREASE 0
• OPTIMAL
• SET TRANSACTION USE ROLLBACK SEGMENT R01

8
Tips

• Should be minimum 20 extents
• ALTER ROLLBACK SEGMENT R01 OFFLINE
• Sizing should be done W.R.T largest transaction
• Another Thumb Rule SIZE 10 of Largest Table
  Size
• SYSTEM rollback segment cannot be taken OFFLINE

9
Creation

• CREATE PUBLIC ROLLBACK SEGMENT R5 TABLESPACE
  RBS STORAGE (INITIAL 1024K NEXT 1024K OPTIMAL
  5M MINEXTENTS 20 MAXEXTENTS 40)
• Rules (a) INITIAL NEXT
• (b) MINEXTENTS 2 (DEFAULT)
• (c) MAXEXTENTS Calculated Value
• (d) PCTINCREASE 0 (DEFAULT) (e)
  Unless running PARALLEL instances
  dont use PUBLIC

10
Alter Storage

• ALTER PUBLIC ROLLBACK SEGMENT R05 STORAGE
  (NEXT 256K)
• Note This could result in mismatched extent
  size
• ALTER ROLLBACK SEGMENT R05
• SHRINK TO 1000K
• ALTER ROLLBACK SEGMENT R05 SHRINK TO OPTIMAL

11
Dropping

• A RBS must not be in USE
• After dropping MUST be removed from init.ora.
  Otherwise database cannot be restarted.
• Note RBS mentioned in the init.ora are
  taken ONLINE while the database is restarted. Or
  by default, when an instance starts, it acquires
  TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT
• Drop rollback segments when the extents are too
  fragmented on disk, or the segment needs to be
  relocated in a different tablespace.

12
Display Info

• 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 AVAILABLE Unresolved transaction
  data in a Distributed DB

13
Reiterate

• IDEALLY
• (1) Each Transaction should fit in one extent
  of a RBS
• (2) All user RBS should be outside SYSTEM
  tablespace
• (3) All RBS will have same size

14
ORA - 1555

• SNAPSHOT TOO OLD
• Occurs because the RBS is small.
• So the old Snapshot of the old transaction
  gets overwritten by other undo information.
  When some user queries the data it is not there
  in RBS.
• Solution Create Larger RBS

15
Sizing RBS

• Before After the largest transaction issue
• SELECT SUM(WRITES) FROM VROLLSTAT
• (No other transaction should go on)
• UNDO generated (AFTER Value - BEFORE Value)

16
View OPTIMAL

• SELECT A.NAME,B.OPTSIZE FROM VROLLNAME A,
  VROLLSTAT B WHERE A.USNB.USN
• VROLLSTAT COLUMNS
• XACTS - No of active transactions
• WRITES - No of Bytes written to the RBS
• HWMSIZE - Max size(B) reached during usage
• SHRINKS - No of shrinks to OPTIMAL size
• WRAPS - No of times an entry wrapped into
  a new extent
• EXTENDS - No of new extent acquisition

17
This RBS is used by -------

• Transactions acquire lock (type TX) within RBS
  header.So join VLOCK to VROLLNAME. Each lock is
  owned by a process. So further join VLOCK to
  VPROCESS to map Process with RBS(script given in
  Lab)
• SELECT R.NAME RBS, P.PID ORACLE_PID, P.SPID
  OS_PID, NVL(P.USERNAME,'NO TRANSACTION')
  TRANSACTION, P.TERMINAL
• FROM VLOCK L,VPROCESS P,VROLLNAME R
• WHERE L.ADDR P.ADDR()
• AND TRUNC(L.IDL()/65536) R.USN
• AND L.TYPE() 'TX' AND L.MODE() 6
• ORDER BY R.NAME

18
Tips

• For BATCH jobs use big RBS explicitly assigned
• TRUNCATE does not use RBS well almost
• In Import use COMMIT Y
• Use OPTIMAL judiciously
• Better waste some space on RBS datafile (keep
  AUTOEXTEND)
• PUBLIC cannot be altered to PRIVATE
• PRIVATE cannot be altered to PUBLIC

19
A Few Questions

• Your database has 20 RBS. Each RBS has 50
  maxextents. Each extent size is 20K. Minextent is
  2. Now an overnight batch operation generates 18M
  undo volume. This batch (PLSQL) deletes all
  records from 10 large tables.
• (a) Could the batch operation be done
  normally?
• (b) Suggest improvement(s)

20
Few Questions(!!!)

• DB has 4 RBS. Size Details for each
• INITIAL 1024K NEXT 1024K
• MAXEXTENTS 25 OPTIMAL 5M.
• High Water mark for each is 25M.
• The datafile to RBS tablespace is 100m.
• Now, when the DB is just up what of that
  datafile will be full?