[20160819]什么是DEFERRED ROLLBACK.txt
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时，记录还没有应用的日志会记录到Deferred Rollback中。在返回online时应用日志。
--Deferred rollback一直在system tablespace中。
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
create table t as select * from dba_objects ;
SCOTT@book> update t set object_name='A';
86953 rows updated.
--注意不提交。
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.25.1190
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE C70
------ ------- ------ ------ ------ ------ ------ ------ --------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
10 25 1190 3 2882 233 4 ACTIVE 1990 173905 0A001900A6040000 0000000081904540 2016-08-19 10:26:10 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 25 1190;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 2882;
2.分析：
查看视图dba_segments分析确定访问视图sys.SYS_DBA_SEGS。
SYS@book> SELECT DISTINCT segment_type, SEGMENT_TYPE_ID FROM sys.SYS_DBA_SEGS order by 2 ;
SEGMENT_TYPE SEGMENT_TYPE_ID
------------------ ---------------
ROLLBACK 1
CLUSTER 5
NESTED TABLE 5
TABLE 5
TABLE PARTITION 5
TABLE SUBPARTITION 5
INDEX 6
INDEX PARTITION 6
LOBINDEX 6
LOB PARTITION 8
LOBSEGMENT 8
TYPE2 UNDO 10
12 rows selected.
--可以发现当前我的测试数据库使用这些段。并不存在DEFERRED ROLLBACK段。
SYS@book> alter tablespace users offline;
Tablespace altered.
--//当表空间offline时，实际上要写文件检查点。也就是online不需要recovery。顺便使用bbed检查：
BBED> set dba 4,1889
DBA 0x01000761 (16779105 4,1889)
BBED> x /rcccnncttcccccnc *kdbr[1]
rowdata[7314] @8023
-------------
flag@8023: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8024: 0x02
cols@8025: 14
col 0[11] @8026: APEX_030200
col 1[1] @8038: A
col 2[0] @8040: *NULL*
col 3[3] @8041: 86400
col 4[0] @8045: *NULL*
col 5[7] @8046: TRIGGER
col 6[7] @8054: 2013-08-24 11:59:29
col 7[7] @8062: 2013-08-24 11:59:29
col 8[19] @8070: 2013-08-24:11:59:29
col 9[5] @8090: VALID
col 10[1] @8096: N
col 11[1] @8098: N
col 12[1] @8100: N
col 13[2] @8102: 3
--注意看object_name='A'，也说明数据块已经被写入修改为'A'。
SYS@book> SELECT DISTINCT segment_type, SEGMENT_TYPE_ID FROM sys.SYS_DBA_SEGS order by 2 ;
SEGMENT_TYPE SEGMENT_TYPE_ID
------------------ ---------------
ROLLBACK 1
DEFERRED ROLLBACK 2
CLUSTER 5
NESTED TABLE 5
TABLE 5
TABLE PARTITION 5
TABLE SUBPARTITION 5
INDEX 6
INDEX PARTITION 6
LOBINDEX 6
LOB PARTITION 8
LOBSEGMENT 8
TYPE2 UNDO 10
13 rows selected.
--你可以发现出现DEFERRED ROLLBACK段，SEGMENT_TYPE_ID=2.
select * from dba_segments where segment_type='DEFERRED ROLLBACK';
Record View
As of: 2016/8/19 10:32:00
OWNER: SYS
SEGMENT_NAME: 1.94664
PARTITION_NAME:
SEGMENT_TYPE: DEFERRED ROLLBACK
SEGMENT_SUBTYPE:
TABLESPACE_NAME: SYSTEM
HEADER_FILE: 1
HEADER_BLOCK: 94664
BYTES: 65536
BLOCKS: 8
EXTENTS: 1
INITIAL_EXTENT: 40960
NEXT_EXTENT: 40960
MIN_EXTENTS: 1
MAX_EXTENTS: 2147483645
MAX_SIZE: 2147483645
RETENTION:
MINRETENTION:
PCT_INCREASE:
FREELISTS: 1
FREELIST_GROUPS: 1
RELATIVE_FNO: 1
BUFFER_POOL: DEFAULT
FLASH_CACHE: DEFAULT
CELL_FLASH_CACHE: DEFAULT
SYS@book> select * from dba_extents where segment_name='1.94664';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- -------------- ------------------ ------------------------- ---------- ---------- ---------- ---------- ------------
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 0 1 94664 65536 8 1
--可以发现产生的回滚段在system表空间，segment_name按照FILE_ID.BLOCK_ID命名。占用8块。里面记录什么呢？
3.转储看看：
SCOTT@book> rollback ;
Rollback complete.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
no rows selected
SYS@book> column PARTITION_NAME noprint
SYS@book> select * from dba_extents where segment_name='1.94664';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 0 1 94664 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 1 1 94672 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 2 1 94680 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 3 1 94688 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 4 1 94696 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 5 1 94704 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 6 1 94712 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 7 1 94896 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 8 1 94904 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 9 1 94912 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 10 1 94920 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 11 1 94928 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 12 1 94936 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 13 1 94944 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 14 1 94952 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 15 1 94960 65536 8 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 16 1 95616 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 17 1 95744 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 18 1 95872 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 19 1 96000 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 20 1 96128 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 21 1 96256 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 22 1 96384 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 23 1 96512 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 24 1 96640 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 25 1 96768 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 26 1 96896 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 27 1 97024 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 28 1 97152 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 29 1 97280 1048576 128 1
SYS 1.94664 DEFERRED ROLLBACK SYSTEM 30 1 97408 1048576 128 1
31 rows selected.
SYS@book> select sum(blocks) from dba_extents where segment_name='1.94664';
SUM(BLOCKS)
-----------
2048
--与前面USED_UBLK= 1990
SYS@book> alter system checkpoint ;
System altered.
BBED> map
File: /mnt/ramdisk/book/system01.dbf (1)
Block: 94664 Dba:0x004171c8
------------------------------------------------------------
Unlimited Save Undo Segment Header
struct kcbh, 20 bytes @0
struct ktech, 72 bytes @20
struct ktemh, 16 bytes @92
struct ktetb[31], 248 bytes @108
struct kttas, 20 bytes @4148
ub4 tailchk @8188
SYS@book> alter system dump datafile 1 block 94665;
System altered.
Block dump from disk:
buffer tsn: 0 rdba: 0x004171c9 (1/94665)
scn: 0x0000.00107bbb seq: 0x4a flg: 0x04 tail: 0x7bbb044a
frmt: 0x02 chkval: 0x750a type: 0x04=KTT SAVE UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FD3571EBA00 to 0x00007FD3571EDA00
7FD3571EBA00 0000A204 004171C9 00107BBB 044A0000 [.....qA..{....J.]
7FD3571EBA10 0000750A 004A0060 01010068 0018000C [.u..`.J.h.......]
7FD3571EBA20 001D0010 000E0002 00015A1F 00015A1F [.........Z...Z..]
7FD3571EBA30 00000004 00000000 0319010B 04A60000 [................]
7FD3571EBA40 00000D02 00000000 00C00B42 000300E9 [........B.......]
7FD3571EBA50 01000775 0100020A 010512FA 00000002 [u...............]
7FD3571EBA60 006A022C 010E0008 0000000D 00000000 [,.j.............]
7FD3571EBA70 00000001 5F4C5455 4F434552 535F504D [....UTL_RECOMP_S]
7FD3571EBA80 00005145 0101006C 0018000C 001D0010 [EQ..l...........]
7FD3571EBA90 00140002 00015A1F 00015A1F 00000004 [.....Z...Z......]
7FD3571EBAA0 00000000 0219010B 04A60000 00000D02 [................]
7FD3571EBAB0 00000000 00C00B42 000200E9 01000775 [....B.......u...]
7FD3571EBAC0 0100020A 010512FA 00000002 006A022C [............,.j.]
7FD3571EBAD0 010E0007 00000013 00000000 00000001 [................]
7FD3571EBAE0 5F4C5455 4F434552 535F504D 5F54524F [UTL_RECOMP_SORT_]
7FD3571EBAF0 31584449 01010078 00180010 00140010 [IDX1x...........]
...
SUN BLK:: sbk: 96 nrc: 74 rln: 104 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1
rln: 120 seq: 1 tot: 1 rln: 96 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 96 seq: 1 tot: 1
rln: 100 seq: 1 tot: 1 rln: 96 seq: 1 tot: 1 rln: 96 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1
rln: 96 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1
rln: 96 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1
rln: 112 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1
rln: 108 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1
rln: 100 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1
rln: 100 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1
rln: 104 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1
rln: 100 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1 rln: 120 seq: 1 tot: 1
rln: 112 seq: 1 tot: 1 rln: 116 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1
rln: 100 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1
rln: 100 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1
rln: 104 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1
rln: 108 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1
rln: 104 seq: 1 tot: 1 rln: 96 seq: 1 tot: 1 rln: 108 seq: 1 tot: 1 rln: 100 seq: 1 tot: 1
rln: 112 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 116 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1
rln: 100 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 104 seq: 1 tot: 1 rln: 112 seq: 1 tot: 1
End dump data blocks tsn: 0 file#: 1 minblk 94665 maxblk 94665
--//能力有限，不过还是可以看出里面保存要恢复的前映像，比如像UTL_RECOMP_SEQ等信息。
4.表空间users online看看呢？
SYS@book> alter tablespace users online ;
Tablespace altered.
SYS@book> select * from dba_extents where segment_name='1.94664';
no rows selected
--可以发现online以后，对应的DEFERRED ROLLBACK自动消失，也就是里面的undo 已经自动应用。
SYS@book> alter system checkpoint ;
System altered.
BBED> set dba 4,1889
DBA 0x01000761 (16779105 4,1889)
BBED> x /rcccnncttcccccnc *kdbr[1]
rowdata[107] @700
------------
flag@700: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@701: 0x00
cols@702: 14
col 0[11] @703: APEX_030200
col 1[27] @715: WWV_BIU_MIG_FRM_PROGRAMUNIT
col 2[0] @743: *NULL*
col 3[3] @744: 86400
col 4[0] @748: *NULL*
col 5[7] @749: TRIGGER
col 6[7] @757: 2013-08-24 11:59:29
col 7[7] @765: 2013-08-24 11:59:29
col 8[19] @773: 2013-08-24:11:59:29
col 9[5] @793: VALID
col 10[1] @799: N
col 11[1] @801: N
col 12[1] @803: N
col 13[2] @805: 3
--可以发现事务已经rollback.当然事务是commit，不会使用产生这个多DEFERRED ROLLBACK段的。
