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

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比较匹配，The '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；

ORA-3137 Troubleshooting Tool (文档 ID 1521913.1)

转到底部

---

修改时间: 2014-9-24 类型: DIAGNOSTIC TOOLS

语言: English日本語

ORA-3137 Troubleshooting Tool The ORA-3137 Troubleshooting tool will provide recommendations to resolve ORA-3137 errors by analyzing uploaded files. When a known solution is available, the tool will display the symptoms and causes which can lead to the issue and one or more suggestions for resolving the issue.  If a known solution is not available, the tool will display the knowledge document which explains the ORA-3137 error and provides a list of resolved bugs for the ORA-3137 error. Benefits of using the ORA-3137 Troubleshooting tool: Analyzes uploaded files to provide recommendations when a known solution is available When a known solution is unavailable, points to knowledge document for the specified ORA-3137 error Troubleshooting report can be saved for later use Create SR option available which will automatically populate many of the SR fields Accessing the ORA-3137 Troubleshooting Tool Click here to access the Troubleshooting Tool (Standalone Version). Note: The Troubleshooting Tool can also be accessed during SR creation when the "ORA-3137" error AND the arguments is entered in the "Error Codes" field. This is referred to as the "Create SR Version" below. Note: Some tool features are only available with the Standalone Version and are designated as "Standalone Version Only" below. Using the Troubleshooting Tool Troubleshoot a New Issue (Standalone Version Only) Describe Problem page Choose the “Troubleshoot a new issue” option. Upload Files page In order to troubleshoot a new ORA-3137 issue you will need to upload the requested files. See the “File Upload Options” section below for file options. Choose the option which is appropriate for the type of file you have available to upload. Save these files to a safe place in case they are needed for future use. Review Recommendations page Review the solution Issue section Lists the symptoms and causes which can lead to the error.  Lists the symptoms and causes which have been identified from your uploaded files.  Resolution section Lists the solutions you can use to resolve the issue. Save the report Use this option to save the report for future use. Enter a name for the report and click the “Save” button. Refer back to the report any time while you are implementing the recommended solutions.  Save the files used for this report to a safe place in case the solutions do not resolve the issue and you need to log a SR. Provide feedback Let us know if the recommendation provided was helpful. Provide any feedback which could help us improve the tool. Click the "Submit Feedback" button once you have completed the fields. Create a SR When logging a SR, the details from the files uploaded will be used to automatically populate many of the fields in the SR. Note: If your issue is not resolved and you saved the troubleshooting report, you will need to re-upload the files before creating a SR when you return. Note: If you have already created an SR for this saved session then you will not be able to create another SR. Please review your SR list. Print Recommendation. Use the printer icon to print the recommendation. Email recommendataion. Use the mail icon to email the recommendation. Troubleshoot a New Issue (Create SR Version Only) Upload Files page In order to troubleshoot a new ORA-3137 issue you will need to upload the requested files. See the “File Upload Options” section below for file options. Choose the option which is appropriate for the type of file you have available to upload. Update the fields with the files to upload and click the "Upload" button. Once the upload is complete, click the "See Solution" button. Save these files to a safe place in case they are needed for future use. Review Recommendations page Review the solution Issue section Lists the symptoms and causes which can lead to the error.  Lists the symptoms and causes which have been identified from your uploaded files.  Resolution section Lists the solutions you can use to resolve the issue. Create a SR When logging a SR, the details from the files uploaded will be used to automatically populate many of the fields in the SR. Print Recommendation. Use the printer icon to print the recommendation. Email recommendataion. Use the mail icon to email the recommendation. Solved my problem Please let us know if the solution solved your issue Review a Troubleshooting Report (Standalone Version Only) From the Describe Problem page, you can choose to review a troubleshooting report that was saved during a previous session.  Choose the “Review a troubleshooting report” option Choose the name of the report from the drop down list. File_Upload_Options In order to troubleshoot a new ORA-3137 issue you will need to upload the trace file containing the error.  The alert log is not a required file.  However, it does contain some information which can be helpful in troubleshooting the issue.  When uploading the alert log make sure that it includes the ORA-3137 error.  Also try to include the startup portion of alert log which took place prior to the occurrence of the error.  When possible you should upload both the alert log and the trace file containing the error.  Another option is to upload the IPS package which contains the error. Consider the following when uploading files: Ensure that the trace file being uploaded contains the error Upload the trace file from the instance that is encountering the error Use the alert log to determine which file to upload For Oracle Database versions 9.0 to 10g, upload the regular trace For Oracle Database version 11i and higher, upload the incident trace file The alert log should contain the following when possible: The ORA-3137 error in question Startup which occurred prior to the ORA-3137 error The IPS file should contain the following: alert log with the ORA-3137 error trace file with the error Refer to ADR Different Methods to Create IPS Package (Doc ID 411.1) for details on how to build an IPS file.

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.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2121912/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2121912/