[20150715]理解_offline_rollback_segments.txt

--曾經寫過一篇 [0126]理解_corrupted_rollback_segments，連結http://blog.itpub.net/267265/viewspace-1415396/
--今天測試_offline_rollback_segments引數的情況。

--offline表示脫線。corrupted表示損壞。這兩個引數放在一起很容易混淆，我自己也不是很清楚。做一個測試：

1.建立測試環境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> @hide _offline_rollback_segments
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------------- -------------------------- ---------------------- ---------------------- ----------------------
_offline_rollback_segments offline undo segment list TRUE

SCOTT@test> create table t as select rownum id , cast ( 'test' as varchar2(20)) name from xmltable ('1 to 100');
Table created.

SYS@test> select rowid,t.* from scott.t where rownum<=1;
ROWID ID NAME
------------------ ---------- ----------------------------------------
AABLUPAAEAAABKLAAA 1 test

SYS@test> @ lookup_rowid AABLUPAAEAAABKLAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    308495          4       4747          0 4,4747               alter system dump datafile 4 block 4747
                                                                 ;

SCOTT@test> alter system checkpoint;
System altered.

--開啟會話1，修改不提交：
SCOTT@test> update t set name='BBBB' ;
100 rows updated.

SCOTT@test> @xid
X
------------------------------
4.0.35872

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBAREC     UBASQN STATUS            USED_UBLK USED_UREC XID              ADDR             START_DATE          C70
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
         4          0      35872          3       8210         38      10696 ACTIVE                    2        100 04000000208C0000 00000000BA3D4AA8 2015-07-15 22:09:24 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU4_1665036189$' XID 4 0 35872;
                                                                                                                                                                          ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU4_1665036189$';
SCOTT@test> column spare4 noprint
SCOTT@test> column spare5 noprint
SCOTT@test> column spare6 noprint
SCOTT@test> select * from sys.undo$ where us#=4;
       US# NAME                                          USER#      FILE#     BLOCK#     SCNBAS     SCNWRP    XACTSQN    UNDOSQN      INST#    STATUS$        TS#      UGRP#       KEEP    OPTIMAL      FLAGS     SPARE1     SPARE2     SPARE3
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         4 _SYSSMU4_1665036189$                              1          3        176 4108924233          2      35868      10694          0          3          2                                                      2

--開啟會話2：
SYS@test> shutdown abort;
ORACLE instance shut down.

--使用bbed觀察:

BBED> set dba 4,4747
DBA 0x0100128b (16781963 4,4747)

BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c

BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2

col 0[2] @8180: 1
col 1[4] @8183: test
--name='test'.

2.測試：
SYS@test> create pfile='/tmp/aa.ora' from spfile ;
File created.

--修改/tmp/aa.ora，加入如下內容：
*._offline_rollback_segments='_SYSSMU4_1665036189$'

SYS@test> startup mount pfile='/tmp/aa.ora'
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             973082064 bytes
Database Buffers          620756992 bytes
Redo Buffers                7344128 bytes
Database mounted.

SYS@test> @hide _offline_rollback_segments
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------------- -------------------------- ---------------------- ---------------------- ----------------------
_offline_rollback_segments offline undo segment list FALSE _SYSSMU4_1665036189$ _SYSSMU4_1665036189$

SYS@test> alter database open ;
Database altered.

SYS@test> alter system checkpoint;
System altered.

--透過bbed觀察，可以發現如下：
BBED> set dba 4,4747
DBA 0x0100128b (16781963 4,4747)

BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c

BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2

col 0[2] @8180: 1
col 1[4] @8183: BBBB
--name='BBBB',開啟資料庫時，應用了redo。但是由於*._offline_rollback_segments='_SYSSMU4_1665036189$'，沒有提交的事務沒有回滾。

column spare4 noprint
column spare5 noprint
column spare6 noprint
SYS@test> select * from sys.undo$ where us#=4;
US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# UGRP# KEEP OPTIMAL FLAGS SPARE1 SPARE2 SPARE3
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
4 _SYSSMU4_1665036189$ 1 3 176 4108924233 2 35868 10694 0 5 2 2
--注意看STATUS$＝5，與上面不同正常是STATUS$＝3。

SYS@test> select * from v$rollname ;
       USN NAME
---------- ----------------------------------------
         0 SYSTEM
         1 _SYSSMU1_559505304$
         2 _SYSSMU2_3752879465$
         3 _SYSSMU3_2763804800$
         5 _SYSSMU5_2973757209$
         6 _SYSSMU6_3709901187$
         7 _SYSSMU7_3362111860$
         8 _SYSSMU8_819560936$
         9 _SYSSMU9_3043963034$
        12 _SYSSMU12_1585900997$
        13 _SYSSMU13_494349874$

11 rows selected.

SCOTT@test> select rowid,t.* from scott.t where rownum<=1;
ROWID ID NAME
------------------ ---------- --------------------
AABLUPAAEAAABKLAAA 1 test

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8h9jfmz0pmjj8, child number 1
-------------------------------------
select rowid,t.* from scott.t where rownum<=1
Plan hash value: 508354683
---------------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |     104 |
|* 1 | COUNT STOPKEY     |      |      1 |        |       |            |          |      1 |00:00:00.01 |     104 |
|   2 |   TABLE ACCESS FULL| T    |      1 |    100 | 3700 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |     104 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)

--Buffers=104,說明讀取undo段，得到正確的資訊。name='test'.

SCOTT@test> alter system checkpoint;
System altered.

--透過bbed觀察：
BBED> set dba 4,4747
DBA 0x0100128b (16781963 4,4747)

BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c

BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2

col 0[2] @8180: 1
col 1[4] @8183: BBBB

--塊內資訊並沒有更改。

3.現在取消引數設定：
SYS@test> startup open read only;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             973082064 bytes
Database Buffers          620756992 bytes
Redo Buffers                7344128 bytes
Database mounted.
Database opened.
--以只讀開啟。

SYS@test> select rowid,t.* from scott.t where rownum<=1;
ROWID ID NAME
------------------ ---------- --------------------
AABLUPAAEAAABKLAAA 1 test

SYS@test> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             973082064 bytes
Database Buffers          620756992 bytes
Redo Buffers                7344128 bytes
Database mounted.
Database opened.

SYS@test> select rowid,t.* from scott.t where rownum<=1;
ROWID ID NAME
------------------ ---------- --------------------
AABLUPAAEAAABKLAAA 1 test

SYS@test> alter system checkpoint;
System altered.

--bbed觀察：

BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c

BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2

col 0[2] @8180: 1
col 1[4] @8183: test

--總結：
1._offline_rollback_segments作用跟10513事件很相似，異常abort時，重啟資料庫應用日誌，但是讀取資訊時要透過undo段來構造，但是不會更新資料塊的資訊。
2._corrupted_rollback_segments相當於undo段損壞，這樣讀取的是未提交的資訊，參見http://blog.itpub.net/267265/viewspace-1415396/.

0715理解_offline_rollback_segments.txt

相關文章