1 / 34

Managing Rollback Segments

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.

tillie
Download Presentation

Managing Rollback Segments

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Read consistency Rollback segment

  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 > 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 INITIAL=NEXT • 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. Are there enoughRBS ? 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 • ALTER ROLLBACK SEGMENT rbs01 • STORAGE( MAXEXTENTS 200 ); • Use ALTER ROLLBACK SEGMENT

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

  23. Taking Rollback Segment Offline • ALTER ROLLBACK SEGMENT rbs01 OFFLINE; Take a rollback segment offline to makeit unavailable.

  24. Dropping Rollback Segments • DROP ROLLBACK SEGMENT rbs01; A rollback segment must be offline before it can be dropped.

  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 V$ROLLNAMEUSNNAME V$ROLLSTAT USN EXTENTS RSSIZE XACTS OPTSIZE HWMSIZE AVEACTIVE STATUS CUREXT CURBLK

  28. Rollback Segment: Current Activity V$SESSION SADDR USERNAME SID SERIAL# V$TRANSACTION 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

More Related