大量SQL*Net break/reset to client ，应用连接不了DB

mastermouse

环境: Oracle RAC 11.2.0.3 +Redhat6.1+ASM
应用程序:C++/Java

现象表现为应用连接不了DB

请各位专家指导!

mastermouse

补充Alert log

Maclean Liu(刘相兵

SELECT CUST_ID, CUST_CODE, BUSI_CODE, CUST_TYPE, CUST_SEX, CUST_NAME, CUST_PWD, AREA_CODE, CERT_TYPE, CERT_CODE, PRTN_ID, VIP_LEV, AUTH_LEV, TO_CHAR(EFF_DATE, 'YYYYMMDDHH24MISS') as EFF_DATE, TO_CHAR(EXP_DATE, 'YYYYMMDDHH24MISS') as EXP_DATE, STAT, OPER_FLAG , SVR_CODE FROM T_CUM_INFO WHERE CUST_CODE = '68140601@ipos.com';

这个语句执行下是否报错

还需要alert.log

查一下，最近做过DDL的对象

Maclean Liu(刘相兵

alert.log 里没有看到什么有效的报错；
建议你查一下 该段时间里应用有什么报错，这个报错信息比较有价值

Mille

The only way to reduce the 'SQL*Net break/reset to client' wait events would be to set the MDB timeouts to a higher value than 30 seconds.

mastermouse

应用那边一直给不出什么Log;

请问下MDB timeouts是哪个参数？

Mille

mastermouse 发表于 2014-3-5 15:18
应用那边一直给不出什么Log;

请问下MDB timeouts是哪个参数？

http://docs.oracle.com/cd/E16439_01/doc.1013/e13981/servtran005.htm

生生不息

SQL*Net break/reset to client represent some error in the SQL statement submitted by the application ,

SQL*Net break/reset happens when an error/unhandled exception is raised during a call (which means that the call executed didn’t complete normally, thus the call state must be reset).

10046 Trace from the Statement that is getting the "SQL*Net break/reset to client" wait , shows the following :

Solution
1- Enable 10046 Trace from the Statement that is getting the "SQL*Net break/reset to client" ,


Please review the following note, for different ways of enabling the 10046 trace event after the session is created : Note 376442.1 - Recommended Method for Obtaining 10046 trace for Tuning

Then search for keyword 'err='

The Number will represent the Oracle error number occurred , ORA-n

You should also be able to Identify the Statement caused this Error and bind variables used , to check and Fix these errors :

=====================
PARSING IN CURSOR #4577575528 len=158 dep=0 uid=236 oct=6 lid=236 tim=49240680785729 hv=2510433232 ad='7000000181663b8' sqlid='1bukxaqau4ayh'
UPDATE IPS_MAC_TO_IP
SET fqdn = null, interface = :p1, last_upd = SYSDATE
WHERE mac = :p2 AND ip = INET_ATON(:p3) AND router_found_loopback = 170543874
END OF STMT
BINDS #4577575528:
Bind#0
  oacdty=01 mxl=128(72) mxlc=00 mal=00 scl=00 pre=00
  oacflg=05 fl2=1000000 frm=01 csi=873 siz=384 off=0
  kxsbbbfp=110d7c058  bln=128  avl=07  flg=05
  value="Vlan101"
Bind#1
  oacdty=01 mxl=128(48) mxlc=00 mal=00 scl=00 pre=00
  oacflg=05 fl2=1000000 frm=01 csi=873 siz=0 off=128
  kxsbbbfp=110d7c0d8  bln=128  avl=12  flg=01
  value="005056913a4a"
Bind#2
  oacdty=01 mxl=128(48) mxlc=00 mal=00 scl=00 pre=00
  oacflg=05 fl2=1000000 frm=01 csi=873 siz=0 off=256
  kxsbbbfp=110d7c158  bln=128  avl=14  flg=01
  value="169.173.200.74"
....
EXEC #4577284448:c=0,e=904,p=0,cr=8,cu=10,mis=0,r=0,dep=0,og=1,plh=0,tim=49240680822112
ERROR #3:err=1 tim=49240680822126
WAIT #4577284448: nam='SQL*Net break/reset to client' ela= 4 driver id=1952673792 break?=1 p3=0 obj#=-1 tim=49240680822228
Also Please engage the application team/vendor to put the proper exception handling appropriately (ex: OTHERS exception) in the application code .


or

2- If no errors is reported in the 10046 Trace , Please put the following parameters in sqlnet.ora file for both client and server side :

BREAK_POLL_SKIP=10000
DISABLE_OOB=on

And then retry the scenario .