Books > Dbai Xppb Rev2

B 1

Manually Managing Undo Data (Rollback Segments)

Copyright © Oracle Corporation, 2002. All rights reserved.

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

B-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Creating Rollback Segments

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

B-3

Copyright © Oracle Corporation, 2002. All rights reserved.

B-4

Copyright © Oracle Corporation, 2002. All rights reserved.

Transactions and Rollback Segments

Transaction 1 Transaction 2

Active extent

B-5

1

2

4

3 Inactive extent

Copyright © Oracle Corporation, 2002. All rights reserved.

B-6

Copyright © Oracle Corporation, 2002. All rights reserved.

Growth of Rollback Segments

1 4

2

1

2 5

3

3 4

Active extent

New extent

Inactive extent

B-7

Copyright © Oracle Corporation, 2002. All rights reserved.

Shrinkage of Rollback Segments

6

1

1

2

6

3

2

5 4

3

Active extent OPTIMAL

B-8

Inactive extent

Copyright © Oracle Corporation, 2002. All rights reserved.

Bringing Rollback Segments Online • Use the following command to make a rollback segment available: – ALTER ROLLBACK SEGMENT rbs01 ONLINE;

• Specify the following initialization parameter to ensure that rollback segments are brought online at startup: ROLLBACK_SEGMENTS=(rbs01, rbs02)

B-9

Copyright © Oracle Corporation, 2002. All rights reserved.

How Instances Acquire Rollback Segments Acquire named private rollback segments.

Compute the required number of rollback segments.

Are there enough RBS’s?

Yes

Bring all acquired rollback segments online.

B-10

Copyright © Oracle Corporation, 2002. All rights reserved.

No

Acquire public rollback segments.

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

B-11

Copyright © Oracle Corporation, 2002. All rights reserved.

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

B-12

Copyright © Oracle Corporation, 2002. All rights reserved.

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;

B-13

Copyright © Oracle Corporation, 2002. All rights reserved.

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

B-14

Copyright © Oracle Corporation, 2002. All rights reserved.

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

B-15

Copyright © Oracle Corporation, 2002. All rights reserved.

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

Number of extents

B-16

Copyright © Oracle Corporation, 2002. All rights reserved.

40

Rollback Segment Problems • • • •

B-17

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

Copyright © Oracle Corporation, 2002. All rights reserved.

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

B-18

Copyright © Oracle Corporation, 2002. All rights reserved.

Read-Consistency Errors SELECT * FROM table

Tabl e

New image Image at statement commencement

B-19

Copyright © Oracle Corporation, 2002. All rights reserved.

Reused block

Blocking Sessions

Blocking session

4

2

1

1

Extent 3

3

5

2

3 4

Existing extent New extent B-20

Copyright © Oracle Corporation, 2002. All rights reserved.

B-21

Copyright © Oracle Corporation, 2002. All rights reserved.

Errors in Taking a Tablespace Offline You cannot take a tablespace offline if it contains an active rollback segment. 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.

B-22

Copyright © Oracle Corporation, 2002. All rights reserved.