【故障|监听】TNS-12518、TNS-00517和 Linux Error：32：Broken pipe

本文主要是介绍【故障|监听】TNS-12518、TNS-00517和 Linux Error：32：Broken pipe，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

小麦苗今天给大家分享的是类似如下的错误的解决方法：

Thu Mar 16 11:49:27 2017

16-MAR-2017 11:49:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=lhrdb)(CID=(PROGRAM=sqlplus)(HOST=orcltest)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.129)(PORT=11917)) * establish * lhrdb * 12518

TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS:lost contact

TNS-12560: TNS:protocol adapter error

TNS-00517: Lost contact

Linux Error: 32: Broken pipe

详细过程参考博客：http://blog.itpub.net/26736162/viewspace-2135468/

【故障|监听】TNS-12518、TNS-00517和 Linux Error：32：Broken pipe

1.1 BLOG文档结构图

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

1.3 故障分析及解决过程

1.3.1 故障环境介绍

项目	source db
db 类型	单机
db version	11.2.0.3.0
db 存储	ASM
OS版本及kernel版本	RHEL 6.5

1.3.2 故障发生现象及报错信息

登陆报错：

[oracle@orcltest ~]$ sqlplus 'sys/"l@h\r/0"'@LHRDB as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 12:06:36 2017

ERROR:

ORA-12537: TNS:connection closed

查监听日志（/u01/app/11.2.0/grid/network/admin/listener.ora），报错如下：

16-MAR-2017 12:06:36 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=lhrdb)(CID=(PROGRAM=sqlplus)(HOST=orcltest)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.129)(PORT=12333)) * establish * lhrdb * 12518

TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS:lost contact

TNS-12560: TNS:protocol adapter error

TNS-00517: Lost contact

Linux Error: 32: Broken pipe

1.3.3 故障分析

潇湘隐者：http://www.cnblogs.com/kerrycode/p/4164838.html

我的解决参考：http://www.itpub.net/thread-1870217-1-1.html

根据MOS：Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection (文档 ID 550859.1)中，

Error: 32: Broken pipe

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS:lost contact

TNS-12560: TNS:protocol adapter error

TNS-00517: Lost contact

IBM/AIX RISC System/6000 Error: 32: Broken pipe

Cause:

The error 32 indicates the communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process.

Action:

1. One of reason would be processes parameter being low, and can be verified by the v$resource_limit view.

2. In Shared Server mode, check the 'lsnrctl services' output and see if the dispatcher has refused any connections, if so, then consider increasing the number of dispatchers.

3. Check the alert log for any possible errors.

4. Memory resource is also another cause for this issue. Check the swap, memory usage of the OS.

5. If RAC/SCAN or listener is running in separate home, check the following note:

Note: 1069517.1 ORA-12537 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User

根据第5条，因为我的grid是11.2.0.1，而Oracle是11.2.0.3的，查询MOS:ORA-12537 / ORA-12547 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User (文档 ID 1069517.1)，里边明确指出是由于$RDBMS_HOME/bin/oracle文件的权限问题导致。

1.3.4 故障解决

$ORACLE_HOME/bin/oracle文件的权限问题，应该为-rwsr-s--x，属主应该是oracle:asmadmin。

解决办法：

方法：

1、修改oracle的权限为6751

[root@orcltest bin]# ll /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

-rwxr-s--x. 1 oracle asmadmin 210823844 May 18 2014 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest ~]# chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest bin]# ll /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

-rwsr-s--x. 1 oracle asmadmin 210823844 May 18 2014 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest bin]#

2、重新用setasmgidwrap设置oracle

[root@orcltest bin]# ll /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

-rwxr-s--x. 1 oracle asmadmin 210823844 May 18 2014 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest ~]# /u01/app/11.2.0/grid/bin/setasmgidwrap -o /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest bin]# ll /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

-rwsr-s--x. 1 oracle asmadmin 210823844 May 18 2014 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest bin]#

[root@orcltest bin]# stat /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

File: `/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle'

Size: 210823844 Blocks: 411776 IO Block: 4096 regular file

Device: 802h/2050d Inode: 1717737 Links: 1

Access: (6751/-rwsr-s--x) Uid: ( 501/ oracle) Gid: ( 504/asmadmin)

Access: 2017-03-16 11:05:44.809363974 +0800

Modify: 2014-05-18 17:09:50.508549983 +0800

Change: 2017-03-16 12:33:15.733816820 +0800

[root@orcltest bin]#

重新连接：

[oracle@orcltest ~]$ sqlplus 'sys/"l@h\r/0"'@LHRDB as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 13:32:48 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SYS@LHRDB>

1.4 故障处理总结

如果可执行文件$ORACLE_HOME/bin/oracle的属主或权限设定出了问题，那么可能会造成很多问题。例如：无法登陆到数据库、ora-600错误、“TNS-12518: TNS:listener could not hand off client connection”、“Linux Error: 32: Broken pipe”、“ORA-12537: TNS:connection closed”、访问ASM磁盘出错等。解决办法很简单，可以在grid用户下运行setasmgidwrap命令重新配置$ORACLE_HOME/bin/oracle可执行文件的权限和属主或者直接将oracle文件的权限修改为6751。$ORACLE_HOME/bin/oracle可执行文件正确属主应该是oracle:asmadmin，并且权限必须有s共享才可以，如下所示：

[root@orcltest ~]$ which setasmgidwrap

/u01/app/11.2.0/grid/bin/setasmgidwrap

[root@orcltest ~]$ setasmgidwrap -o /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest ~]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

-rwsr-s--x 1 oracle asmadmin 232399083 Apr 21 2015 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest ~]# ll /u01/app/11.2.0/grid/bin/oracle

-rwsr-s--x. 1 grid oinstall 203972117 Jan 5 2015 /u01/app/11.2.0/grid/bin/oracle

[root@orcltest ~]# chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest ~]# ll /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

-rwsr-s--x 1 oracle asmadmin 232399083 Apr 21 2015 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[root@orcltest bin]# cd /u01/app/oracle/product/11.2.0/dbhome_1/bin/

[root@orcltest bin]# which stat

/usr/bin/stat

[root@orcltest bin]# stat oracle

File: `oracle'

Size: 210823844 Blocks: 411776 IO Block: 4096 regular file

Device: 802h/2050d Inode: 1717737 Links: 1

Access: (6751/-rwsr-s--x) Uid: ( 501/ oracle) Gid: ( 504/asmadmin)

Access: 2017-03-16 12:33:44.809363974 +0800

Modify: 2014-05-18 17:09:50.508549983 +0800

Change: 2017-03-16 11:05:15.733816820 +0800

问：在UNIX/LINUX环境中，oracle数据库启动后存在许多后台进程和前台进程，虽然相关进程产生一些trace文件也是常有的事情，但是真正是什么决定了oracle相关进程的属性呢？

答：通常来说，oracle的后台进程的调用是依赖于$ORACLE_HOME/bin/oracle这个二进制文件，但它从远端连入而分配的服务器进程（server process）相关属主的属性则是继承自listener进程，而listener进程的属主属性同样是进程自其启动的用户（分oracle用户和grid用户）$ORACLE_HOME/bin/oracle的属主属性。

其他原因：

启动ORACLE监听的时候报错 Linux Error: 32: Broken pipe

原因：原来/home/oracle/product/9.2.0.1.0/network/log/listener.log 文件超过2G，监听就会断掉。

解决办法：清空日志文件

[oracle@localhost oracle]$ cd $ORACLE_HOME/network/log

[oracle@chicago log]$ cat </dev/null> listener.log

至此问题解决

有个命令可以对这个写日志进行关闭

LSNRCTL> set log_status off

要继续写日志，只需要

LSNRCTL> set log_status on

也可以在 listener.ora 文件里面添加一项

LOGGING_LISTENER = OFF

添加了这个之后无论你怎么重启监听，都不会写日志了。

1.5 用到的SQL集合

setasmgidwrap -o /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

stat oracle

1 参考文章

1.1 MOS

1.1.1 Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection (文档 ID 550859.1)

1.1.2 ORA-12537 / ORA-12547 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User (文档 ID 1069517.1)

1.1.3 TNS Listener Crash with Core Dump (文档 ID 549932.1)

1.2 博客

潇湘隐者：http://www.cnblogs.com/kerrycode/p/4164838.html

http://blog.csdn.net/killvoon/article/details/49420485

我的解决参考：http://www.itpub.net/thread-1870217-1-1.html

详细内容参考博客：http://blog.itpub.net/26736162/viewspace-2135468/

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2135468/

● 本文博客园地址：http://www.cnblogs.com/lhrbest/p/6559092.html

● 2017-03-16 08:00 ~ 2017-03-16 19:00 完成

● 文章内容来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

这篇关于【故障|监听】TNS-12518、TNS-00517和 Linux Error：32：Broken pipe的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

Linux教程