ORA-03137:TTC protocol internal error:[12333]

数据库版本： Oracle 11.2.0.1.0

数据库服务器操作系统： Linux

问题现象：某系统登录时，报错 " No more data to read from socket "

解决方案：查看 alert 警告日志，查找对应的 trace 文件 ;

Tue May 24 13:31:22 2016

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1796.trc  (incident=31766):

ORA-03137: TTC protocol internal error : [12333] [20] [48] [48] [] [] [] []

Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_31766/orcl_ora_1796_i31766.trc

找到触发此 bug 的 sql 语句如下：

----- Current SQL Statement for this session (sql_id=38x1qtjzgn3f0) -----

select power.resource_data_id

  from sm_power_func power, sm_user_role urole

 where urole.cuserid = :1

   and urole.pk_corp = :2

   and urole.pk_role = power.pk_role

   and (power.pk_org in (:3, :4) or iscommon_power = 'Y')

根据 MOS 查看，和 Bug 9703463 比较匹配， T he 'Current SQL' Statement is using bind variables. 时，会触发此 BUG ；

解决方案： 禁用绑定变量窥探特性

1. The quickest solution is to apply the workaround of setting _optim_peek_user_binds to false to disable bind peeking. However, this may impact performance.
SQL> alter system set "_optim_peek_user_binds"=false;

关于 ORA-03137 TTC 问题 ,MOS 上有专门故障排除工具，通过此工具上次对应的 alert 和 trace 日志后，可以自动生成解决方案，也可以直接提交 SR ；

Primary Issue:
Bug 9703463

1. The Oracle Database version is greater than or equal to 11.1 and less than or equal to 11.2.0.1.

2. ORA-3137[12333] or ORA-600[kpobav-1] was found in the trace file.

3. The 'Current SQL' Statement is using bind variables.

4. Optimizer parameter _optim_peek_user_binds is set to TRUE.

5. The call stack in the trace file contains the functions 'kxsPeekBinds'.

6. The KXSCFBPE flag is set in the trace file.

通过分析上传的文件 , 我们已发现可导致此问题的下列症状 :

• In your    trace file, _optim_peek_user_binds=true

• The current    SQL statement is using bind variables: 
  select power.resource_data_id from sm_power_func power, sm_user_role urole    where urole.cuserid = :1 and urole.pk_corp = :2 and urole.pk_role = power.pk_role    and ( power.pk_org in ( :3 , :4 ) or iscommon_power = 'Y' )

• The KXSCFBPE    flag is on in your trace file

• In your    trace file, the Database version is 11.2.0.1.0

• In your    trace file, the following ORA600/ORA3137 error is found: ORA-03137: TTC    protocol internal error : [12333] [20] [48] [48] [] [] [] []

建议的解决方案

You may be encountering bug 9703463 based on the details found in the trace file. This bug can occur when using bind peeking.

Currently bugs 9703463, 8625762, 6737706, and 11059133 can occur under similar circumstances. Bug 9703463 is the closest match out of the 4 bugs based on the details found in the trace file. However, it is possible that one of the other 3 bugs could be the cause.

The ORA-600 [kpobav-1] error will occur if the patch for bug: 9243912 has been applied. Otherwise you will see the ORA-3137[12333] error as a symptom of bug 9703463.

Refer to  Document 9703463.8  (Bug 9703463 - ORA-3137 [12333] or ORA-600 [kpobav-1] When Using Bind Peeking - superceded) for details about the bug. Document 1388487.1  (OERR: ORA-3137 "TTC protocol internal error : [n] []" Master Note) provides general information regarding the ORA-3137 error.

This bug is fixed in: 
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 12 for Exadata Database
11.1.0.7.8 Patch Set Update
11.2.0.1 Patch 8 on Windows Platforms
11.1.0.7 Patch 37 on Windows Platforms

Solutions:

1. The quickest solution is to apply the workaround of setting _optim_peek_user_binds to false to disable bind peeking. However, this may impact performance.
SQL> alter system set "_optim_peek_user_binds"=false;

2. If you would prefer a fix to the issue and are on database version 11.2, apply the latest patch set. If you are on database version 11.1, apply the latest PSU. The latest patch set contains the fix to all 4 bugs. Oracle recommends applying patch sets when available instead of individual patches.

3. If you would prefer an individual patch, then refer to  Patch 9703463  for available patches. You may also need to apply  Patch 12534597  to correct an issue introduced by the fix to bug 9703463. If the issue is not resolved by these patches, then you may need to apply the patch for the other 3 bugs.

To determine if a patch is available click on the patch link provided above and do the following:

1. Choose 11.2.0.1.0 as the "Release" 
2. Choose Linux as the "Platform".

NOTE: The database version and platform will not appear in the drop down menus when a patch is unavailable for that database version and platform.

  欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！