猜测的力量.ppt

《猜测的力量.ppt》由会员分享，可在线阅读，更多相关《猜测的力量.ppt（44页珍藏版）》请在三一办公上搜索。

1、,猜测的力量,刘磊（sundog315）,DTCC2012,1-1,什么是猜测?,DTCC2012,1-2,猜测,释义：推测；凭想象估计。,常作为数学用语,猜测是人们以自己已有的知识为基础，通过对问题的分析、归纳，或将其与有类似关系的特例进行比较、分析，通过判断、推理对问题结果作出的估测。猜测是数学理论的胚胎，许多伟大的数学家都是通过猜测(或猜想)发现了别人都不曾发现的真理,DTCC2012,1-3,两个案例,OTN China Tour 分析案例,ORA-12592:TNS:bad packet,DTCC2012,1-4,楔子,DTCC2012,1-5,数据在这里,DTCC2012,1-6,

2、Why？,DTCC2012,1-7,猜想的依据,DTCC2012,1-8,收集信息,收集信息,待分析,待分析,DTCC20121-9,初次猜想,是否Oracle Logminer功能的限制？,1)Simple and nested abstract datatypes(ADTs)2)Collections(nested tables and VARRAYs)3)Object Refs,4)Index organized tables(IOTs),5)CREATE TABLE AS SELECT of a table with,a clustered key,UNSUPPORTED Value

3、In Sql_redo,Operation Columns Of V$Logmnr_contents,ID 282994.1,DTCC2012,1-10,初次猜想-失败,收集信息,初次猜测-失败,待分析,DTCC20121-11,再次收集信息,DTCC2012,1-12,再次猜想,ROWID:AAAQXKAAAAAAAAAAAA,CHANGE#1 TYP:0 CLS:22 AFN:2 DBA:0 x00801542 OBJ:0,SCN:0 x0001.4be86efe SEQ:1 OP:5.1,DTCC2012,1-13,试验验证,DTCC2012,1-14,遭遇CheckSUM,ERROR

4、at line 1:,ORA-00368:checksum error in redo log block,ORA-00353:log corruption near block 166733 change 5568491448,time 07/05/2011 16:41:54,ORA-00334:archived log:/1_12446.dbf,DTCC2012,1-15,失败,DTCC2012,1-16,再次猜想-失败,收集信息,初次猜测-失败,再次猜测-失败,DTCC20121-17,山穷水尽疑无路-第三次猜想,DTCC2012,1-18,ROW HEAD,DTCC2012,1-19,

5、试验验证,DTCC2012,1-20,成功,166733 16 66237 AAAQXKAAvAAAB2yAAv UPDATE update,UNKNOWN.OBJ#66237 set COL 8=HEXTORAW(31),COL 16=HEXTORAW(c305)where COL 8=HEXTORAW(35)and COL 16=HEXTORAW(80)and ROWID=AAAQXKAAvAAAB2yAAv;Dictionary Mismatch,DTCC2012,1-21,三次猜想-成功,收集信息,初次猜测-失败,再次猜测-失败,三次猜测-成功,DTCC20121-22,结论证明,1.

6、创建一个表,2.插入数据，并使一条数据产生迁移,3.修改迁移的数据，观察是否与案例情况相同,create table t(a varchar2(4000);insert into t values(a);,insert into t values(b);insert into t values(c);,commit;,SQL select t.rowid from t;,ROWID,-,AAADZiAABAAAIDBAAAAAADZiAABAAAIDBAABAAADZiAABAAAIDBAAC,DTCC2012,1-23,使第三条记录产生行迁移,update t set t.a=lpad(a,

7、4000,a)where t.a=a;,update t set t.a=lpad(a,4000,b)where t.a=b;update t set t.a=lpad(a,4000,c)where t.a=c;,commit;,SQL select t.rowid from t;,ROWID,-,AAADZiAABAAAIDBAAAAAADZiAABAAAIDBAABAAADZiAABAAAIDBAAC,DTCC2012,1-24,DUMP数据块SQL alter system dump datafile 1 block 32961;System altered.tab 0,row 2,0

8、x3ctl:9 fb:-H-lb:0 x2 cc:0nrid:0 x004080c2.0SQL alter system dump datafile 1 block 32962;System altered.tab 0,row 0,0 xfdc,tl:4012 fb:-FL-lb:0 x1 cc:1,-row flag是-FL-，也就是0 x0c,hrid:0 x004080c1.2col 0:400063 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63DTCC20121-25,结果基本一致,1.L

9、ogminer无法挖掘出内容,2.Logfile Dump内容与案例相同,CHANGE#1 TYP:0 CLS:22 AFN:3 DBA:0 x00c0096c OBJ:4294967295,SCN:0 x0000.00043ec4 SEQ:1 OP:5.1 ENC:0 RBL:0,tabn:0 slot:0(0 x0)flag:0 x0c lock:0 ckix:0,ncol:1 nnew:1 size:4001col 0:4000,63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63,CHANG

10、E#2 TYP:2 CLS:1 AFN:1 DBA:0 x004080c2 OBJ:13922 SCN:0 x0000.00043def,SEQ:5 OP:11.5 ENC:0 RBL:0,tabn:0 slot:0(0 x0)flag:0 x0c lock:2 ckix:0,ncol:1 nnew:1 size:-4001col 0:1 64,DTCC2012,1-26,开启附加日志真的可以,alter database add supplemental log data;,修改行迁移记录,Logminer,SQL select,t.RBABLK,t.RBABYTE,t.DATA_OBJ#,

11、t.ROW_ID,t.OPERATION,t.SQL_REDO,t.INFO from v$logmnr_contents t;,13159 16 13922 AAADZiAABAAAIDBAAC UPDATE update,UNKNOWN.OBJ#13922 set COL 1=HEXTORAW(656565)whereCOL 1=HEXTORAW(64)and ROWID=,AAADZiAABAAAIDBAAC;Dictionary Mismatch,DTCC2012,1-27,Why?,05001d0862000000030001,0100000036000000000c00,20000

12、20100cd0000004001,0000000000000000000000,030101cd000400c20c0001,00000000000d0080000000,ffff1c0000000040004000,ffff000000000000000000,fa108c2c000100c0006400,0000000000000080001210,c014a40600000040010200,00001c0000000000010100,671822620b0000fffe0100,4300003601008012ff0400,0420000001000025000000,000000

13、0005000001000000,c1,80,40,00,02,00,00,00,这部分的内容是不是很眼熟？看看下面这段就明白了，红色这部分内容对应的就是hrid：tab 0,row 0,0 xfdc,tl:4012 fb:-FL-lb:0 x1 cc:1,-row flag是-FL-，也就是0 x0c,hrid:0 x004080c1.2col 0:400063 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63DTCC20121-28,验证结论,收集信息,初次猜测-失败,再次猜测-失败,三次猜测-成

14、功,验证,DTCC20121-29,两个案例,OTN China Tour 分析案例,ORA-12592:TNS:bad packet,DTCC2012,1-30,ORA-12592:TNS:bad packet,ORA-12592:Cause:Action:,TNS:bad packetAn ill-formed packet has beendetected by the TNS software.For further details,turn on tracing,and reexecute the operation.Iferror persists,contact OracleCu

15、stomer Support.DTCC20121-31,查询一个表报错,DTCC2012,1-32,怎么办？-收集信息,应用程序没有变更,数据库版本没有变更,操作系统没有变更,服务器没有变更,网络发生了变化,DTCC2012,1-33,网络问题？出在哪里？-分析问题,Ping,Tnsping,select操作,S trace,抓包,DTCC2012,1-34,Oracle Net Stack&OSI,DTCC2012,1-35,抓包分析,DTCC2012,1-36,抓包分析,No.,Time,Sequence Nextnumbersequencenumber,171185186190,3.70

16、25564.1611194.1615474.162446,16260166861669716686,16686166971680816697,DTCC20121-37,抓病机,1.只有网络做了变更,2.小表没有问题,1.Oracle SQL*Net调整,2.修改监听端口,DTCC2012,1-38,SUD&TDU,Session data unit(SDU),A buffer that Oracle Net uses to place data before,transmitting it across the network.Oracle Net sends the data inthe b

17、uffer either when requested or when it is full.,Transport data unit(TDU),Transparent Network Substrate Network Transport(TNS NT)layer(the layer that communicates to the Operating Systemprotocol layer),DTCC2012,1-39,Configure SDU,Server Side:,sqlnet.ora,DEFAULT_SDU_SIZE=8192,listener.ora,SID_LIST_lis

18、tener_name=,(SID_LIST=(SID_DESC=(SDU=8192),(SID_NAME=sales),Initialization parameter file,DISPATCHERS=(DESCRIPTION=(ADDRESS=(PROTO,COL=tcp)(SDU=8192),DTCC2012,1-40,Configure SDU,Client Side:,sqlnet.ora,DEFAULT_SDU_SIZE=8192,tnsnames.ora,=(DESCRIPTION=(SDU=8192)(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=),DTCC2012,1-41,修改监听端口,将监听端口修改未非默认端口，并进行静态注册,DTCC2012,1-42,Summary,收集信息出现问题,问题推测 大胆猜测 小心求证,实例验证验证,DTCC20121-43,About Me,某商业地产集团数据库管理员,ACOUG Member,Itpub Oracle专题深入讨论区版主,博客：,微博：,DTCC2012,1-44,