1. 首页
  2. IT资讯

[20150409]只读表空间与延迟块清除.txt

[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 <=lck=4表示操作4条记录,由于块不在内存,scn/fsc没有信息.
0x02   0x000a.022.00001ff2  0x00800037.0ed0.0b  C—    0  scn 0x0002.f4448f75 <= 这个是前面的scn=12688068469.

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2156704/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182852.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code