分享程序员开发的那些事...
更新时间：2022-09-13 23:20:06
[20150409]只读表空间与延迟块清除.txt
--昨天测试只读表空间的数据库恢复问题,突然想到一种情况,如果只读表空间存在延迟块清除情况,这样在下次访问是会更新块的信息吗?
--自己还是做1个测试:
1.首先在测试前,说明1点,设置表空间只读,仅仅阻止dml操作,并不能阻止ddl操作,ddl操作的是数据字典.
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 16M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
create table scott.deptx tablespace mssm as select * from scott.dept ;
create table scott.empy tablespace mssm as select * from scott.emp ;
SCOTT@test> update deptx set loc=loc ;
update deptx set loc=loc
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--无法修改表中数据.
SCOTT@test> delete from empy ;
delete from empy
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--无法删除表中数据.
SCOTT@test> drop table empy ;
Table dropped.
SCOTT@test> select * from empy ;
select * from empy
*
ERROR at line 1:
ORA-00942: table or view does not exist
--可以发现表依旧可以drop掉.
SCOTT@test> column SEGMENT_NAME format a30
SCOTT@test> select segment_name,header_file,header_block from dba_segments where tablespace_name='MSSM';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------ ------------
BIN$E0GFElhsudLgUKjAWWRv7w==$0 6 17
DEPTX 6 9
SCOTT@test> flashback table empy to before drop;
Flashback complete.
SCOTT@test> drop table empy purge ;
Table dropped.
SCOTT@test> select segment_name,header_file,header_block from dba_segments where tablespace_name='MSSM';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------ ------------
DEPTX 6 9
6.17 6 17
--SEGMENT_NAME以HEADER_FILE.HEADER_BLOCK表示,如果设置读写后.才会清除.
SCOTT@test> ALTER TABLESPACE MSSM READ write;
Tablespace altered.
SCOTT@test> select segment_name,header_file,header_block from dba_segments where tablespace_name='MSSM';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------ ------------
DEPTX 6 9
2.延迟块清除测试:
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA 12688068469 10 ACCOUNTING new york
AAAO1FAAGAAAAAKAAB 12688068469 20 RESEARCH dallas
AAAO1FAAGAAAAAKAAC 12688068469 30 SALES chicago
AAAO1FAAGAAAAAKAAD 12688068469 40 OPERATIONS boston
SCOTT@test> @ &r/lookup_rowid AAAO1FAAGAAAAAKAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
60741 6 10 0 6,10 alter system dump datafile 6 block 10 ;
SCOTT@test> update deptx set loc=lower(loc) ;
4 rows updated.
--打开另外会话执行.
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system flush buffer_cache ;
System altered.
SYS@test> @ &r/bh 6 10
old 23: dbarfil = &1 and
new 23: dbarfil = 6 and
old 24: dbablk = &2
new 24: dbablk = 10
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006DC1A000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006E12E000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006B074000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006ACA8000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006ACF4000 DEPTX
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12688068873
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12688068877
SCOTT@test> alter tablespace mssm read only ;
Tablespace altered.
SCOTT@test> alter system dump datafile 6 block 10 ;
System altered.
Block header dump: 0x0180000a
Object id on Block? Y
seg/obj: 0xed45 csc: 0x02.f44490bc itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.016.00001ff2 0x00800038.0ed0.0f ---- 4 fsc 0x0000.00000000 0x02 0x000a.022.00001ff2 0x00800037.0ed0.0b C--- 0 scn 0x0002.f4448f75
data_block_dump,data header at 0x8420c5c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x08420c5c
bdba: 0x0180000a
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f44
avsp=0x1f2a
tosp=0x1f2a
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f44
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f44
tl: 26 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 2: [ 8] 6e 65 77 20 79 6f 72 6b
tab 0, row 1, @0x1f5e
tl: 22 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 15
col 1: [ 8] 52 45 53 45 41 52 43 48
col 2: [ 6] 64 61 6c 6c 61 73
tab 0, row 2, @0x1f74
tl: 20 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 1f
col 1: [ 5] 53 41 4c 45 53
col 2: [ 7] 63 68 69 63 61 67 6f
tab 0, row 3, @0x1f88
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [ 6] 62 6f 73 74 6f 6e
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 10 maxblk 10
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA 12688068875 10 ACCOUNTING new york
AAAO1FAAGAAAAAKAAB 12688068875 20 RESEARCH dallas
AAAO1FAAGAAAAAKAAC 12688068875 30 SALES chicago
AAAO1FAAGAAAAAKAAD 12688068875 40 OPERATIONS boston
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system dump datafile 6 block 10 ;
System altered.
Block header dump: 0x0180000a
Object id on Block? Y
seg/obj: 0xed45 csc: 0x02.f44490bc itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.016.00001ff2 0x00800038.0ed0.0f ---- 4 fsc 0x0000.00000000
0x02 0x000a.022.00001ff2 0x00800037.0ed0.0b C--- 0 scn 0x0002.f4448f75
data_block_dump,data header at 0x8420c5c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x08420c5c
bdba: 0x0180000a
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f44
avsp=0x1f2a
tosp=0x1f2a
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f44
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f44
tl: 26 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 2: [ 8] 6e 65 77 20 79 6f 72 6b
tab 0, row 1, @0x1f5e
tl: 22 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 15
col 1: [ 8] 52 45 53 45 41 52 43 48
col 2: [ 6] 64 61 6c 6c 61 73
tab 0, row 2, @0x1f74
tl: 20 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 1f
col 1: [ 5] 53 41 4c 45 53
col 2: [ 7] 63 68 69 63 61 67 6f
tab 0, row 3, @0x1f88
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [ 6] 62 6f 73 74 6f 6e
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 10 maxblk 10
--可以发现一个奇特的情况,块没有变化,scn是从undo段来的.这样每次的逻辑读都要读undo段.
SYS@test> alter system checkpoint ;
System altered.
SYS@test> alter system flush buffer_cache ;
System altered.
SYS@test> @ &r/bh 6 10
old 23: dbarfil = &1 and
new 23: dbarfil = 6 and
old 24: dbablk = &2
new 24: dbablk = 10
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006D9AC000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006CF0C000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006CCA0000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006E01A000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006D25C000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006B074000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006ACA8000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006ACF4000 DEPTX
8 rows selected.
SYS@test> @ &r/bh 6 10
old 23: dbarfil = &1 and
new 23: dbarfil = 6 and
old 24: dbablk = &2
new 24: dbablk = 10
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8 6 10 1 data block cr 1 4294967295 65535 0 0 0 000000006EB40000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006D9AC000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006CF0C000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006CCA0000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006E01A000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006D25C000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006B074000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006ACA8000 DEPTX
000000007B6FDFD8 6 10 1 data block free 0 0 0 0 0 0 000000006ACF4000 DEPTX
9 rows selected.
--出现的STATE=CR块. 而不是XCUR.
SYS@test> alter system flush buffer_cache ;
System altered.
SCOTT@test> @ &r/10046on 12
Session altered.
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA 12688068875 10 ACCOUNTING new york
AAAO1FAAGAAAAAKAAB 12688068875 20 RESEARCH dallas
AAAO1FAAGAAAAAKAAC 12688068875 30 SALES chicago
AAAO1FAAGAAAAAKAAD 12688068875 40 OPERATIONS boston
SCOTT@test> @ &r/10046off
Session altered.
=====================
PARSING IN CURSOR #9 len=43 dep=0 uid=57 oct=3 lid=57 tim=1395066357333567 hv=1599918056 ad='75289110'
select rowid,ora_rowscn,deptx.* from deptx
END OF STMT
PARSE #9:c=1000,e=740,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1395066357333561
BINDS #9:
EXEC #9:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1395066357333671
WAIT #9: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066357333711
WAIT #9: nam='db file sequential read' ela= 9 file#=6 block#=9 blocks=1 obj#=60741 tim=1395066357333776
WAIT #9: nam='db file scattered read' ela= 32 file#=6 block#=10 blocks=7 obj#=60741 tim=1395066357333990
WAIT #9: nam='db file sequential read' ela= 8 file#=2 block#=153 blocks=1 obj#=0 tim=1395066357334040
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #9:c=0,e=350,p=9,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1395066357334092
WAIT #9: nam='SQL*Net message from client' ela= 387 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066357334518
WAIT #9: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066357334573
FETCH #9:c=0,e=45,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,tim=1395066357334603
*** 2015-04-09 10:52:37.005
WAIT #9: nam='SQL*Net message from client' ela= 6928105 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066364262753
STAT #9 id=1 cnt=4 pid=0 pos=1 obj=60741 op='TABLE ACCESS FULL DEPTX (cr=5 pr=9 pw=0 time=337 us)'
=====================
--注意看~,可以发现要读file#=2 block#=153 blocks=1.而这个正是undo文件.
SYS@test> column name format a35
SYS@test> show parameter undo_tablespace
NAME TYPE VALUE
---------------- -------- ---------
undo_tablespace string UNDOTBS1
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile where file#=2;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
----------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/undotbs01.dbf 2 12688069220 0 0 0 ONLINE
--另外你可以看到一个奇怪的现象:ORA_ROWSCN会变(顺着时间的推移,原来undo slot的信息会被覆盖).我关闭数据库再打开.
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA 12688069729 10 ACCOUNTING new york
AAAO1FAAGAAAAAKAAB 12688069729 20 RESEARCH dallas
AAAO1FAAGAAAAAKAAC 12688069729 30 SALES chicago
AAAO1FAAGAAAAAKAAD 12688069729 40 OPERATIONS boston
--再关闭数据库再打开.
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA 12688070201 10 ACCOUNTING new york
AAAO1FAAGAAAAAKAAB 12688070201 20 RESEARCH dallas
AAAO1FAAGAAAAAKAAC 12688070201 30 SALES chicago
AAAO1FAAGAAAAAKAAD 12688070201 40 OPERATIONS boston
SYS@test> @&r/bh 6 10
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8 6 10 1 data block cr 1 4294967295 65535 0 0 0 000000006C8FA000 DEPTX
总结:
1.对于只读表空间数据文件,出现延迟块清除,在下次访问块时是通过undo构造块,但是由于是只读,不会更改块内信息.
2.这样每次访问都会访问undo段,如果大量出现,也许会影响性能.
--补充一些测试:
SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.
SCOTT@test> set autot traceonly
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
Execution Plan
----------------------------------------------------------
Plan hash value: 428208148
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPTX | 4 | 80 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
8 physical reads
116 redo size
983 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
--可以发现产生redo.
SCOTT@test> set autot off
SYS@test> alter system checkpoint ;
System altered.
SYS@test> alter system dump datafile 6 block 10;
System altered.
Block header dump: 0x0180000a
Object id on Block? Y
seg/obj: 0xed45 csc: 0x02.f44497e6 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.016.00001ff2 0x00800038.0ed0.0f C-U- 0 scn 0x0002.f4449645 0x02 0x000a.022.00001ff2 0x00800037.0ed0.0b C--- 0 scn 0x0002.f4448f75
SCOTT@test> select power(2,33)+to_number('f4449645','xxxxxxxxxxxxxx') from dual;
POWER(2,33)+TO_NUMBER('F4449645','XXXXXXXXXXXXXX')
--------------------------------------------------
12688070213
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA 12688070213 10 ACCOUNTING new york
AAAO1FAAGAAAAAKAAB 12688070213 20 RESEARCH dallas
AAAO1FAAGAAAAAKAAC 12688070213 30 SALES chicago
AAAO1FAAGAAAAAKAAD 12688070213 40 OPERATIONS boston