Yaping's Weblog

September 2, 2008

Rollback Segments Corruption

Filed under: Oracle — Yaping @ 2:15 am
Tags: ,

If the rollback segments corrupt, we should recover it from backup. Sometimes there’s no backup or it’s not necessary to recover, we maybe consider using undocument parameter to avoid access these rollback segments. Oracle provides _offline_rollback_segments and _corrupted_rollback_segments, before use them, we should be clear the difference between them.

 

When opening a database, any rollback segments listed in _offline_rollback_segments or _corrupted_rollback_segments parameter:

l         Are not scanned, and any active transactions are neither marked as dead nor rolled back;

l         Appear offline in undo$;

l         Can’t be acquired by instance for new transaction;

 

Note that although the rollback segments listed in _offline_rollback_segments, Oracle actually reads the segments to find transaction status and gather undo records to perform rollback. If a corrupt block is found, the oracle will still fail.

 

If an open ITL is found to associated with _corrupted_rollback_segments the segment is not read to find the transaction status:

It is as though the rollback segment had been dropped, the transaction is assumed to be committed and delayed block cleanout is performed;

So the transaction was not committed, logical corruption will occur.

 

 

Session 1

test@CHEN>create table t1 as select rownum id from dual connect by level<6;

Table created.

 

test@CHEN>delete t1 where id=5;

1 row deleted.

 

test@CHEN>select * from t1;

 

        ID

———-

         1

         2

         3

         4

 

test@CHEN>@myid

Wrote file /tmp/myvar.sql

sid:11 serial:4 pid:12 spid:12961

 

Session 2

test@CHEN>insert into t1 values(6);

1 row created.

 

test@CHEN>select * from t1;

        ID

———-

         1

         2

         3

         4

         5

         6

6 rows selected.

 

test@CHEN>@myid

Wrote file /tmp/myvar.sql

sid:15 serial:17 pid:13 spid:12981

 

Session 3

sys@CHEN>@tran1

       SID    SERIAL# USERNAME                 XIDUSN    XIDSLOT     XIDSQN SEGMENT_NAME

———- ———- ——————– ———- ———- ———- ——————————

        15         17 TEST                          8          7        368 _ YSSMU8$S

        11          4 TEST                          2         40        401 _SYSSMU2$

 

sys@CHEN>@getpar _offline_rollback_segments

NAME                                     VALUE                                    DESCRIPTION

—————————————- —————————————- —————————————-

_offline_rollback_segments                                                        offline undo segment list

 

sys@CHEN>@getpar _corrupted_rollback_segments

ANAME                                     VALUE                                    DESCRIPTION

—————————————- —————————————- —————————————-

_corrupted_rollback_segments                                                      corrupted undo segment list

 

sys@CHEN>alter system set “_offline_rollback_segments”=”_SYSSMU8$” scope=spfile;

 

sys@CHEN>alter system set “_corrupted_rollback_segments”=”_SYSSMU2$” scope=spfile;

 

sys@CHEN>alter system checkpoint;

 

sys@CHEN>shutdown abort

ORACLE instance shut down.

 

sys@CHEN>startup mount

ORACLE instance started.

Total System Global Area  487658188 bytes

Fixed Size                   451276 bytes

Variable Size             369098752 bytes

Database Buffers          117440512 bytes

Redo Buffers                 667648 bytes

Database mounted.

 

sys@CHEN>alter system set events ‘10226 trace name context forever’;

 

sys@CHEN>alter database open;

 

sys@CHEN>select NAME,STATUS$ from undo$;

NAME                                        STATUS$

—————————————- ———-

SYSTEM                                            3

_SYSSMU1$                                         3

_SYSSMU2$                                         5

_SYSSMU3$                                         3

_SYSSMU4$                                         3

_SYSSMU5$                                         3

_SYSSMU6$                                         3

_SYSSMU7$                                         3

_SYSSMU8$                                         5

_SYSSMU9$                                         3

_SYSSMU10$                                        3

 

Session 4

test@CHEN>select * from t1;

        ID

———-

         1

         2

         3

         4

 

The trace file shows that Oracle uses the segments listed in _offline_rollback_segments to perform read consistent.

applied to 0x519bc014:

KTB Redo

op: 0x03  ver: 0x01

op: Z

KDO Op code: DRP row dependencies Disabled

  xtype: CR  bdba: 0x00c00013  hdba: 0x00c00011

itli: 1  ispac: 0  maxfr: 4863

tabn: 0 slot: 0(0x0)

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: