小麦苗今天给大家分享的是类似如下的错误的解决方法：
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文档结构图
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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]# [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
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>
|
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/
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文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，学习最实用的数据库技术。
本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。