[20191206]隐含参数_db_always_check_system_ts.txt
–//今年年头我做tab$删除恢复时,遇到的问题,就是遇到延迟块清除的问题.参考链接:
http://blog.itpub.net/267265/viewspace-2564716/
http://blog.itpub.net/267265/viewspace-2564717/
–//当时测试如果发生延迟块清除,修复删除记录的块,再读取时如果是系统表空间时报错,一般用户的表空间是没有问题.
–//一直想知道系统表空间有什么隐含参数可以绕过这个问题,毕竟修复数据库如果错误太多,无法一块一块来修复.
–//昨天才知道有一个隐含参数_db_always_check_system_ts可以绕过这个错误.今天测试看看.
1.环境:
scott@book> @ 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
sys@book> @ hide _db_always_check_system_ts
name description default_value session_value system_value isses issys_mod
————————– ————————————————————- ————- ————- ———— —– ———
_db_always_check_system_ts always perform block check and checksum for system tablespace true true true false immediate
–//缺省_db_always_check_system_ts=true.
2.测试:
sys@book> create table t tablespace system as select rownum id,’test’ name from dual connect by level<=2;
table created.
sys@book> select rowid,t.* from t;
rowid id name
—————— ———- —-
aaawegaabaaaal5aaa 1 test
aaawegaabaaaal5aab 2 test
sys@book> @ rowid aaawegaabaaaal5aaa
object file block row rowid_dba dba text
———- ———- ———- ———- ——————– ——————– —————————————-
90400 1 2425 0 0x400979 1,2425 alter system dump datafile 1 block 2425
-//建立在system表空间.
sys@book> delete from t where id=1;
1 row deleted.
sys@book> alter system flush buffer_cache;
system altered.
sys@book> alter system flush buffer_cache;
system altered.
sys@book> @ bh 1 2425
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
—————- ———- ———- ———- —————— ———- ———- ———- ———- ———- ———- ———- —————- ——————–
0000000084deacf8 1 2425 1 data block free 0 0 0 0 0 0 00000000772c2000 t
0000000084deacf8 1 2425 1 data block free 0 0 0 0 0 0 00000000772c4000 t
–//确定该块不在数据库缓存.
sys@book> commit ;
commit complete.
–//这个时候不会写块提交到块中,因为数据块已经不在数据缓存了.
3.使用bbed修复该记录看看:
bbed> set dba 1,2425
dba 0x00400979 (4196729 1,2425)
bbed> x /rnc *kdbr[1]
rowdata[0] @8166
———-
flag@8166: 0x2c (kdrhfl, kdrhff, kdrhfh)
lock@8167: 0x00
cols@8168: 2
col 0[2] @8169: 2
col 1[4] @8172: test
bbed> x /rnc *kdbr[0]
rowdata[11] @8177
———–
flag@8177: 0x3c (kdrhfl, kdrhff, kdrhfd, kdrhfh)
lock@8178: 0x02
cols@8179: 0
–//第1条记录已经删除,flag=0x3c.
bbed> assign offset 8177 =0x2c;
warning: contents of previous bifile will be lost. proceed? (y/n) y
ub1 rowdata[0] @8177 0x2c
bbed> x /rnc *kdbr[0]
rowdata[11] @8177
———–
flag@8177: 0x2c (kdrhfl, kdrhff, kdrhfh)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: test
–//ok,现在已经恢复.lock=0x02,使用itl槽1(从0开始)
bbed> sum apply
check value for file 1, block 2425:
current = 0xff20, required = 0xff20
bbed> verify
dbverify – verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 2425
block checking: dba = 4196729, block type = ktb-managed data block
data header at 0x7f344dbba274
kdbchk: the amount of space used is not equal to block size
used=44 fsc=9 avsp=8028 dtl=8072
block 2425 failed with check code 6110
–//注:这个报错不必理会在select读取时.
bbed> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0013
ub4 kxidsqn @72 0x00004d92
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00288
ub2 kubaseq @80 0x0f0a
ub1 kubarec @82 0x0e
ub2 ktbitflg @84 0x0002 (none)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 9
ub2 _ktbitwrp @86 0x0009
ub4 ktbitbas @88 0x00000000
–//可以发现ktbitflg=0x0002,表示没有提交.有点奇怪为什么是0x0002,应该是0x0001(因为我仅仅删除1条记录)
–//注:关于这点我在以前blog提到参考链接http://blog.itpub.net/267265/viewspace-2564779/,视乎使用表空间类型是mssm就能看到
–//这样的情况.
–//ktbitbas=0x00000000,也就是没有scn相关信息写入.
–//如果我这时读取该块就会遇到链接测试遇到的情况:链接http://blog.itpub.net/267265/viewspace-2564717/
ora-00607: internal error occurred while making a change to a data block
ora-00600: internal error code, arguments: [kdblkcheckerror], [1], [2537], [6110], [], [], [], [], [], [], [], []
–//注意错误号6110,与bbed的错误号一致.
–//因为延迟块清除,在读取该块时要写入itl槽scn号.设置提交标识.这样对于system表空间这样的块就会报错(bbed verify没有通过)
–//现在修改参数:
sys@book> alter system set “_db_always_check_system_ts”=false scope=memory ;
system altered.
sys@book> @ hide “_db_always_check_system_ts”
name description default_value session_value system_value isses issys_mod
————————– ————————————————————- ————- ————- ———— —– ———
_db_always_check_system_ts always perform block check and checksum for system tablespace true false false false immediate
–//按照介绍该参数是立即生效issys_mod=immediate.保险起见还是退出会话在登录看看.
sys@book> select rowid,t.* from t;
rowid id name
—————— — —–
aaawegaabaaaal5aaa 1 test
aaawegaabaaaal5aab 2 test
–//ok,现在读取就没有问题.再次通过bbed观察:
bbed> set dba 1,2425
dba 0x00400979 (4196729 1,2425)
bbed> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0013
ub4 kxidsqn @72 0x00004d92
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00288
ub2 kubaseq @80 0x0f0a
ub1 kubarec @82 0x0e
ub2 ktbitflg @84 0xa000 (ktbfupb, ktbfcom)`
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 3
ub2 _ktbitwrp @86 0x0003
ub4 ktbitbas @88 0x17600426
–//可以发现ktbitflg=0xa000(ktbfupb, ktbfcom),表示提交.
–//ktbitbas=0x17600426,也就是scn相关信息已经写入.
bbed> verify
dbverify – verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 2425
block checking: dba = 4196729, block type = ktb-managed data block
data header at 0x1f09e74
kdbchk: the amount of space used is not equal to block size
used=44 fsc=0 avsp=8037 dtl=8072
block 2425 failed with check code 6110
–//还是报6110错误.但是该块的读取是没有问题的.
sys@book> alter system set “_db_always_check_system_ts”=true scope=memory ;
system altered.
sys@book> select rowid,t.* from t;
rowid id name
—————— ———- —-
aaawegaabaaaal5aaa 1 test
aaawegaabaaaal5aab 2 test
–//读取没有问题,如果修改该记录现在就会报错(注意”_db_always_check_system_ts”=true),验证看看.
sys@book> update t set name=’test’ where id=2;
update t set name=’test’ where id=2
*
error at line 1:
ora-00600: internal error code, arguments: [kdblkcheckerror], [1], [2425], [6110], [], [], [], [], [], [], [], []
sys@book> alter system set “_db_always_check_system_ts”=false scope=memory ;
system altered.
sys@book> update t set name=’test’ where id=2;
update t set name=’test’ where id=2
*
error at line 1:
ora-01578: oracle data block corrupted (file # 1, block # 2425)
ora-01110: data file 1: ‘/mnt/ramdisk/book/system01.dbf’
sys@book> alter system flush buffer_cache;
system altered.
sys@book> update t set name=’test’ where id=2;
1 row updated.
–//ok现在没有问题.
sys@book> commit ;
commit complete.
sys@book> select rowid,t.* from t;
rowid id name
—————— — —–
aaawegaabaaaal5aaa 1 test
aaawegaabaaaal5aab 2 test
–//实际上这个时候使用bbed verify检查还是报错.
bbed> set dba 1,2425
dba 0x00400979 (4196729 1,2425)
bbed> verify
dbverify – verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 2425
block checking: dba = 4196729, block type = ktb-managed data block
data header at 0x7f756ceee274
kdbchk: the amount of space used is not equal to block size
used=44 fsc=0 avsp=8037 dtl=8072
block 2425 failed with check code 6110
4.总结:
–//设置_db_always_check_system_ts=false,可以绕过一些数据库块错误,实际上遇到这样的情况,最佳的方式设置read only.
–//采用exp或者expdp方式尽快取出数据重新建库.
5.补充如何修复该块:
bbed> verify dba 1,2425
dbverify – verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 2425
block checking: dba = 4196729, block type = ktb-managed data block
data header at 0x15b9e74
kdbchk: the amount of space used is not equal to block size
used=44 fsc=0 avsp=8037 dtl=8072
block 2425 failed with check code 6110
–//avsp= dtl-used-fsc = 8072-0 -44 = 8028
bbed> assign kdbh.kdbhavsp=8028
warning: contents of previous bifile will be lost. proceed? (y/n) y
sb2 kdbhavsp @126 8116
bbed> assign kdbh.kdbhavsp=8028
sb2 kdbhavsp @126 8028
bbed> sum apply
check value for file 1, block 2425:
current = 0x8014, required = 0x8014
bbed> verify dba 1,2425
dbverify – verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 2425
block checking: dba = 4196729, block type = ktb-managed data block
data header at 0x16d5e74
kdbchk: space available on commit is incorrect
tosp=8039 fsc=0 stb=0 avsp=8028
block 2425 failed with check code 6111
–// tosp = avsp+stb+fsc= 8028+0+0 = 8028
bbed> assign kdbh.kdbhtosp=8028
sb2 kdbhtosp @128 8028
bbed> sum apply
check value for file 1, block 2425:
current = 0x802f, required = 0x802f
bbed> verify dba 1,2425
dbverify – verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 2425
–//现在设置”_db_always_check_system_ts”=true,在执行dml就不错报错了.
sys@book> alter system set “_db_always_check_system_ts”=true scope=memory ;
system altered.
sys@book> update t set name=’tttt’ where id=2;
1 row updated.
sys@book> commit ;
commit complete.
sys@book> select rowid,t.* from t;
rowid id name
—————— ———- —-
aaawegaabaaaal5aaa 1 test
aaawegaabaaaal5aab 2 tttt