[20180306]关于DEFERRED ROLLBACK.txt
--//在oracle数据库存在一种特殊的ROLLBACK段,叫DEFERRED ROLLBACK.也叫做SAVE Undo segments.
--//主要出现在当表空间offline ,这样一些事务没有完成
--//必须把这些事务从undo表空间移动到system表空间,类型变为DEFERRED ROLLBACK.
http://www.askmaclean.com/archives/deferred-rollback.html
这些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.
