TNS-12518、TNS-00517、IBM/AIX RISC System/6000 Error: 32: Broken pipe
Tags： IBM/AIX RISC System/6000 Error: 32: Broken pipeOracleTNS-00517TNS-12518故障处理
【故障|监听】TNS-12518、TNS-00517和 Linux Error：32：Broken pipe
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，\~O(∩_∩)O\~：
① Linux Error: 32: Broken pipe的可能原因（重点）
② TNS-12518: TNS:listener could not hand off client connection的一般解决过程
③ SQL*Plus登录报错：ORA-12537: TNS:connection closed
故障分析及解决过程
故障环境介绍
| 项目 | source db |
|---|---|
| db 类型 | 单机 |
| db version | 11.2.0.3.0 |
| db 存储 | ASM |
| OS版本及kernel版本 | RHEL 6.5 |
故障发生现象及报错信息
登陆报错：
1 2 3 4 5 6 7 8 9 | [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 Copyright (c) 1982, 2011, Oracle. All rights reserved. ERROR: ORA-12537: TNS:connection closed |
查监听日志（/u01/app/11.2.0/grid/network/admin/listener.ora），报错如下：
1 2 3 4 5 6 7 | 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 |
故障分析
潇湘隐者：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)中，
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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文件的权限问题导致。
故障解决
\$ORACLE_HOME/bin/oracle文件的权限问题，应该为-rwsr-s--x，属主应该是oracle:asmadmin。
解决办法：
方法：
- 修改oracle的权限为6751
1 2 3 4 5 6 7 | [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]# |
- 重新用setasmgidwrap设置oracle
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [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]# [root@orcltest bin]# |
重新连接：
1 2 3 4 5 6 7 8 9 10 11 12 13 | [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 Copyright (c) 1982, 2011, Oracle. All rights reserved. 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> |
故障处理总结
如果可执行文件\$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共享才可以，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [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
至此问题解决
