DBAI_xppB_Rev1_1

Views:
 
Category: Education
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

Manually Managing Undo Data(Rollback Segments) : 

B Manually Managing Undo Data(Rollback Segments)

Objectives : 

After completing this lesson, you should be able to do the following: Create rollback segments using appropriate storage settings Maintain rollback segments Plan the number and size of rollback segments Troubleshoot common rollback segment problems Objectives

Creating Rollback Segments : 

Creating Rollback Segments CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 20 MAXEXTENTS 100 OPTIMAL 2000K );

Transactions and Rollback Segments : 

Transactions and Rollback Segments Transaction 1 Active extent Inactive extent 4 3 1 2 Transaction 2

Growth of Rollback Segments : 

Growth of Rollback Segments Active extent Inactive extent 1 2 4 3 1 2 3 4 5 New extent

Shrinkage of Rollback Segments : 

Shrinkage of Rollback Segments Active extent Inactive extent 1 2 6 3 1 2 3 4 5 6 OPTIMAL

Bringing Rollback Segments Online : 

Bringing Rollback Segments Online Use the following command to make a rollback segment available: Specify the following initialization parameter to ensure that rollback segments are brought online at startup: ROLLBACK_SEGMENTS=(rbs01, rbs02) ALTER ROLLBACK SEGMENT rbs01 ONLINE;

How Instances Acquire Rollback Segments : 

How Instances Acquire Rollback Segments Bring all acquired rollback segments online. Acquire named privaterollback segments. Are thereenough RBS? Acquire public rollback segments. Computethe required numberof rollback segments. Yes No

Changing Rollback Segment Storage Settings : 

Changing Rollback Segment Storage Settings Use the ALTER ROLLBACK SEGMENT command. You can change OPTIMAL or MAXEXTENTS. ALTER ROLLBACK SEGMENT rbs01 STORAGE( MAXEXTENTS 200 );

Deallocating Space From Rollback Segments : 

Deallocating Space From Rollback Segments Use the ALTER ROLLBACK SEGMENT command. If extents are active, they may not shrink to the requested size. ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 4M;

Taking Rollback Segment Offline : 

Taking Rollback Segment Offline Take a rollback segment offline to make it unavailable. If transactions are using the rollback segment, the status is temporarily changed to PENDING OFFLINE. ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

Dropping Rollback Segments : 

Dropping Rollback Segments A rollback segment must be offline before it can be dropped. To drop a rollback segment: DROP ROLLBACK SEGMENT rbs01;

Planning Rollback Segments: Number : 

Planning Rollback Segments: Number OLTP Many small rollback segments Four transactions per rollback segment Up to ten transactions per rollback segment Batch Few large rollback segments One per transaction

Planning Rollback Segments: Number of Extents : 

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

Rollback Segment Problems : 

Rollback Segment Problems Insufficient space for transactions Read-consistency errors Blocking sessions Errors in taking a tablespace offline

Insufficient Space for Transactions : 

Insufficient Space for Transactions No space in tablespace: Extend data files Enables automatic extension of data files Add data files MAXEXTENTS reached for segment Increase MAXEXTENTS Re-create segments with larger extent sizes

Read-Consistency Errors : 

Image at statement commencement New image Table SELECT * FROM table Read-Consistency Errors Reused block

Blocking Sessions : 

Blocking Sessions Extent 3 Existing extent New extent 4 1 3 2 1 2 3 4 5 Blocking session

Errors in Taking a Tablespace Offline : 

Errors in Taking a Tablespace Offline You cannot take a tablespace that contains an active rollback segment offline. 1. Determine which rollback segments are in the tablespace. 2. Take all of these rollback segments offline. 3. Find active transactions using these rollback segments. 4. Find the session ID and serial number. 5. Terminate the session, if necessary. 6. Take the tablespace offline.

authorStream Live Help