[20180306]關於DEFERRED ROLLBACK2.txt

[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.

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

--//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段.