[20180306]关于DEFERRED ROLLBACK2.txt-CSDN博客

[20180306]关于DEFERRED ROLLBACK2.txt

--//上午测试DEFERRED ROLLBACK针对表空间offline才有效,我测试回滚一定会写到DEFERRED ROLLBACK段.
--//但是提交是否会写入会EFERRED ROLLBACK呢?测试看看.

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

2.建立测试例子:
--//session 1:
SCOTT@book> create table DEMO (id number, update_scn number, commit_scn number,vc varchar2(20)) SEGMENT CREATION IMMEDIATE tablespace tea ;
Table created.

SCOTT@book> select current_scn,sysdate from v$database ;
CURRENT_SCN SYSDATE
------------ -------------------
13277394884 2018-03-05 14:53:21

SCOTT@book> insert into DEMO (id )values (1);
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> update demo set update_scn=dbms_flashback.get_system_change_number, commit_scn=userenv('commitscn'),vc='aaa1bbb2' where id=1;
1 row updated.

--//session 2:
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 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> @ &r/bbvi 1 2424
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 19857408 -s 8192 /mnt/ramdisk/book/system01.dbf

--//8192*8 = 65536,执行bvi -b 19857408 -s 65536 /mnt/ramdisk/book/system01.dbf,查询无法找到字符串aaa1bbb2.

SCOTT@book> commit ;
commit
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

--//为什么呢?因为调用userenv('commitscn')函数非常特殊,类似触发器在commit时写入新的scn号到块中,而这个时候表空间offline无法读取,报错.
SCOTT@book> alter tablespace tea online ;
Tablespace altered.

3.继续测试:
--//session 1:
SCOTT@book> alter tablespace tea online ;
Tablespace altered.

SCOTT@book> select rowid,demo.* from demo;
ROWID ID UPDATE_SCN COMMIT_SCN VC
------------------ ------------ ------------ ------------ --------------------
AAAWGSAAGAAAACBAAA 1

SCOTT@book> update demo set update_scn=dbms_flashback.get_system_change_number, vc='AAA1ZZZ2' where id=1;
1 row updated.

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

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

SCOTT@book> alter system checkpoint;
System altered.

$ bvi -b 19857408 -s 65536 /mnt/ramdisk/book/system01.dbf

--//另存为/tmp/1.2424

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

SCOTT@book> select ora_rowscn,rowid,demo.* from demo;
ORA_ROWSCN ROWID ID UPDATE_SCN COMMIT_SCN VC
------------ ------------------ ------------ ------------ ------------ --------------------
13277396291 AAAWGSAAGAAAACBAAA 1 13277395703 AAA1ZZZ2

select 13277396291,32,trunc(13277396291/power(2,32)) scn_wrap,mod(13277396291,power(2,32)) scn_base from dual
13277396291 32 SCN_WRAP SCN_BASE
------------ ------------ ------------ ------------
13277396291 32 3 392494403

SCOTT@book> @ &r/10to16 392494403
10 to 16 HEX REVERSE16
-------------- ------------------
000001764fd43 0x43fd6417

--//在/tmp/1.2424文件中查询1764fd43, 43fd6417都没有找到,说明commit并不写DEFERRED ROLLBACK段.