[20180306]關於DEFERRED ROLLBACK.txt

--//在oracle資料庫存在一種特殊的ROLLBACK段,叫DEFERRED ROLLBACK.也叫做SAVE Undo segments.
--//主要出現在當表空間offline ,這樣一些事務沒有完成
--//必須把這些事務從undo表空間移動到system表空間,型別變為DEFERRED ROLLBACK.

這些DEFERRED ROLLBACK也叫做SAVE Undo segments，具有以下的特性：

    其存在是為了那些突然OFFLINE掉的表空間上的活躍事務存放UNDO/Rollback回滾資料
    Segment_name資料段的名字為FILE#檔案號.Block#塊號
    其SEGMENT_TYPE是DEFERRED ROLLBACK
    一般自動建立在SYSTEM表空間上
    屬於SYS使用者
    如果OFFLINE掉的表空間重新ONLINE且undo資料已經被應用則會被自動DROP掉

Deferred Undo Segments是特殊用途的回滾段，其中的undo資料不同於undo表空間上的資料結構，以一種簡單的順序日誌形式存在。其
SEGMENT_NAME為FILE#.Block#，對應其段頭segment header的物理位置。

從DBA_SEGMENTS查的話就是SEGMENT_TYPE為DEFERRED ROLLBACK的資料段，一般存在SYSTEM表空間上，且屬於SYS使用者。
有使用者遇到過OFFLINE掉幾個表空間後，SYSTEM表空間急速膨脹，最後查出來就是被這些DEFERRED ROLLBACK回滾段佔用了空間。

DEFERRED ROLLBACK的資料結構就是簡單的順序存放著，而沒有對齊或者填充，這一點和普通的undo是不同的。

看tom 的回答
save undo is undo saved for offlined tablespaces. It is also known as deferred rollback

A "Deferred Rollback" segment is created for a tablespace when a tablespace is taken offline. Undo records are written
to the deferred rollback segment of a tablespace when undo is being applied to a tablespace which is offline, hence, the
undo cannot be applied.

The undo stored in the deferred rollback segment is applied when the tablespace comes back online. The deferred rollback
segment is automatically removed after the undo has been applied.

Deferred rollback segments always reside in the system tablespace.

--//更正我自己的一個錯誤,我一直以為資料檔案offline,也會出現這種情況,實際不是這樣,透過測試說明:
--//以前的測試:http://blog.itpub.net/267265/viewspace-2123716/

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t tablespace tea as select * from dba_objects ;
Table created.

SCOTT@book> select * from dba_segments where segment_type='DEFERRED ROLLBACK';
no rows selected

2.測試:
--//session 1:
SCOTT@book> update t set object_name='A';
86992 rows updated.

SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.32.20390

--//session 2:
SCOTT@book> alter database datafile 6 offline;
Database altered.

SCOTT@book> select * from dba_segments where segment_type='DEFERRED ROLLBACK';
no rows selected

--//沒有出現DEFERRED ROLLBACK.

SCOTT@book> recover datafile 6;
Media recovery complete.

SCOTT@book> alter database datafile 6 online;
Database altered.

--//表空間offline:
SCOTT@book> alter tablespace tea offline ;
Tablespace altered.

SCOTT@book> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES from dba_segments where segment_type='DEFERRED ROLLBACK';
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES
------ -------------------- ------------------ ----------- ------------ ----------
SYS 1.2424 DEFERRED ROLLBACK 1 2424 65536

SCOTT@book> select * from dba_extents where segment_name='1.2424';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS 1.2424 DEFERRED ROLLBACK SYSTEM 0 1 2424 65536 8 1

SCOTT@book> alter tablespace tea online ;
Tablespace altered.

SCOTT@book> select * from dba_extents where segment_name='1.2424';
no rows selected

--// segment_name='1.2424消失.

3.繼續測試:
SCOTT@book> alter tablespace tea offline immediate ;
Tablespace altered.

SCOTT@book> select * from dba_extents where segment_name='1.2424';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SYS 1.2424 DEFERRED ROLLBACK SYSTEM 0 1 2424 65536 8 1

SCOTT@book> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES from dba_segments where segment_type='DEFERRED ROLLBACK';
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES
------ -------------------- ------------------ ----------- ------------ ----------
SYS 1.2424 DEFERRED ROLLBACK 1 2424 65536
--//offline時DEFERRED ROLLBACK很小.僅僅用來記錄rollback資訊.

SCOTT@book> alter tablespace tea online ;
alter tablespace tea online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

SCOTT@book> recover tablespace tea;
Media recovery complete.
SCOTT@book> alter tablespace tea online ;
Tablespace altered.

SCOTT@book> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES from dba_segments where segment_type='DEFERRED ROLLBACK';
no rows selected

4.如果offline的資料檔案:
SCOTT@book> alter database datafile 6 offline;
Database altered.

--//能提交嗎?
SCOTT@book> commit ;
Commit complete.
--// 沒有問題呢?

SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
no rows selected

4.oracle為什麼這樣設計呢?

--//因為對於資料檔案offline,沒有寫檢查檔案頭點,必須要經歷recover恢復後才能online,可能需要歸檔與線上日誌.
--//而表空間是一個邏輯概念,offline可能是一組資料檔案,預設要寫檔案檢查點(除非immediate引數).oracle要把沒有完成的事務在
--//rollback時記錄到DEFERRED ROLLBACK(估計提交也一樣,我僅僅猜出).在online表空間,再應用DEFERRED ROLLBACK記錄,rollback對應事務.

--//關於提交是否記錄在EFERRED ROLLBACK,以後有機會測試.

--//而如果表空間offline沒有DEFERRED ROLLBACK這樣的設計,這樣online表空間資料檔案也要透過掃描歸檔與線上日誌確定沒有完成事務的走向.
--//這樣類似recover操作.而恢復到那個時間點無法確定,這樣可能恢復的時間會很長.(如果offline與online間隔太長的情況下).

--//可以參考我以前寫的文章http://blog.itpub.net/267265/viewspace-2126709/=>[20161019]資料檔案offline後恢復到那個scn,裡面提到
--//recover是僅僅恢復到控制檔案記錄的LAST_CHANGE#.

--//我再做一個測試就很清楚了:

--//session 1:
SCOTT@book> update t set object_name='a' where rownum<=10;
10 rows updated.

--//session 2:
SCOTT@book> alter database datafile 6 offline;
Database altered.

--//session 1:
SCOTT@book> commit ;
Commit complete.

--//可以發現提交成功,相當於延遲塊提交的情況(不涉及資料塊修改).繼續測試:
--//online後在提交肯定成功,不再測試.

--//recover與online後,重複測試rollback:
--//session 1:
SCOTT@book> update t set object_name='b' where rownum<=10;
10 rows updated.

--//session 2:
SCOTT@book> alter database datafile 6 offline;
Database altered.

--//session 1:
SCOTT@book> rollback;
rollback
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'
Process ID: 11434
Session ID: 28 Serial number: 893

--//在offline資料檔案的情況下,回滾是不會成功的.因為回滾要修改資料塊,而設定資料檔案已經offline了.自然報錯.
--//如果online後再回滾,不會報錯,大家可以自行測試.

--//現在看看錶空間offline後rollback的情況:

--//session 1:
SCOTT@book> update t set object_name='c' where rownum<=10;
10 rows updated.

--//session 2:
SCOTT@book> alter tablespace tea offline immediate ;
Tablespace altered.

--//session 1:
SCOTT@book> rollback ;
Rollback complete.

--//在表空間offline的情況下依舊可以rollback.這個時候的rollback記錄在DEFERRED ROLLBACK中,如何證明呢?加大事務看看.

--//session 1:
SCOTT@book> update t set object_name='zzzzzzzz' ;
86992 rows updated.

--//session 2:
SCOTT@book> alter tablespace tea offline immediate ;
Tablespace altered.

SCOTT@book> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES from dba_segments where segment_type='DEFERRED ROLLBACK';
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES
------ -------------------- ------------------ ------------ ------------ ------------
SYS 1.2424 DEFERRED ROLLBACK 1 2424 65536
--//開始佔用空間很小,僅僅64K.

SCOTT@book> select * from dba_extents where segment_name='1.2424';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------------ ------------------------------ ------------ ------------ ------------ ------------ ------------ ------------
SYS 1.2424 DEFERRED ROLLBACK SYSTEM 0 1 2424 65536 8 1

--//session 1:
SCOTT@book> rollback ;
Rollback complete.

--//session 2:
SCOTT@book> select * from dba_extents where segment_name='1.2424';
OWNER SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME EXTENT_ID      FILE_ID     BLOCK_ID        BYTES       BLOCKS RELATIVE_FNO
------ ------------ ------------------ ---------------- --------- ------------ ------------ ------------ ------------ ------------
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   0            1         2424        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   1            1         2432        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   2            1         2440        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   3            1         2448        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   4            1         2456        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   5            1         2504        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   6            1         2512        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   7            1         2520        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   8            1         2528        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                   9            1         2536        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  10            1        94664        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  11            1        94680        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  12            1        94704        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  13            1        94712        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  14            1        94896        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  15            1        94904        65536            8            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  16            1        96896      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  17            1        97024      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  18            1        97152      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  19            1        97280      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  20            1        97408      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  21            1        97536      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  22            1        97664      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  23            1        97792      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  24            1        97920      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  25            1        98048      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  26            1        98176      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  27            1        98304      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  28            1        98432      1048576          128            1
SYS    1.2424       DEFERRED ROLLBACK SYSTEM                  29            1        98560      1048576          128            1
30 rows selected.
--//DEFERRED ROLLBACK 變大,也很形象理解DEFERRED ROLLBACK的含義.

SCOTT@book> alter tablespace tea online ;
Tablespace altered.

SCOTT@book> select * from dba_extents where segment_name='1.2424';
no rows selected

SCOTT@book> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES from dba_segments where segment_type='DEFERRED ROLLBACK';
no rows selected

總結:
DEFERRED ROLLBACK是針對表空間的offline的,不對資料檔案offline.

[20180306]關於DEFERRED ROLLBACK.txt

相關文章