ORA-12537: TNS:connection closed

报错：ORA-12537: TNS:connection closed 。

环境：

oracle:11.2.0.2

OS：LINUX 5.6

环境：RAC

NODE1：10.20.31.36

NODE2：10.20.31.38

[oracle@ract1 ~]$ sqlplus sys/oracle@10.20.31.36:1521/racdb as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 15 16:19:04 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

使用同样的监听，登陆ASM实例却可以

[oracle@ract1 ~]$ sqlplus sys/oracle@10.20.31.36:1521/+ASM as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 15 16:32:29 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>

登陆到节点二也没问题

[oracle@ract1 ~]$ sqlplus sys/oracle@10.20.31.38:1521/racdb as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 15 16:33:46 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

监听的配置：

[oracle@ract1 admin]$ cat sqlnet.ora
# sqlnet.ora.ract1 Network Configuration File: /grid/product/11.2.0/network/admin/sqlnet.ora.ract1
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /grid/app

#tcp.validnode_checking = yes

[oracle@ract1 admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
# listener.ora.ract1 Network Configuration File: /grid/product/11.2.0/network/admin/listener.ora.ract1
# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
)

监听状态：

[oracle@ract1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-JAN-2013 16:40:42

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 15-JAN-2013 16:06:59
Uptime 0 days 0 hr. 33 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /grid/product/11.2.0/network/admin/listener.ora
Listener Log File /grid/app/diag/tnslsnr/ract1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.31.36)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.31.37)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

报ORA-12537: TNS:connection closed 时候的日志如下：

[grid@ract1 alert]$ pwd
/grid/app/diag/tnslsnr/ract1/listener/alert
[grid@ract1 alert]$ cat log.xml
type='UNKNOWN' level='16' host_id='ract1'
host_addr='10.20.31.36' version='1'>
15-JAN-2013 16:43:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ract1)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0


type='UNKNOWN' level='16' host_id='ract1'
host_addr='10.20.31.36'>
15-JAN-2013 16:43:03 * (CONNECT_DATA=(SERVICE_NAME=racdb)(CID=( PROGRAM=sqlplus@ract1)(HOST=ract1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.20.31.36)(PORT=47268)) * establish * racdb * 12518


type='UNKNOWN' level='16' host_id='ract1'
host_addr='10.20.31.36'>
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


type='UNKNOWN' level='16' host_id='ract1'
host_addr='10.20.31.36'>
15-JAN-2013 16:43:05 * service_update * racdb1 * 0

[grid@ract1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.BACKUP.dg ora....up.type ONLINE ONLINE ract1
ora.DATA.dg ora....up.type ONLINE ONLINE ract1
ora....FILE.dg ora....up.type ONLINE ONLINE ract1
ora....ER.lsnr ora....er.type ONLINE ONLINE ract1
ora....N1.lsnr ora....er.type ONLINE ONLINE ract2
ora.asm ora.asm.type ONLINE ONLINE ract1
ora.cvu ora.cvu.type ONLINE ONLINE ract2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE ract1
ora.oc4j ora.oc4j.type ONLINE ONLINE ract2
ora.ons ora.ons.type ONLINE ONLINE ract1
ora.racdb.db ora....se.type ONLINE ONLINE ract1
ora....SM1.asm application ONLINE ONLINE ract1
ora....T1.lsnr application ONLINE ONLINE ract1
ora.ract1.gsd application OFFLINE OFFLINE
ora.ract1.ons application ONLINE ONLINE ract1
ora.ract1.vip ora....t1.type ONLINE ONLINE ract1
ora....SM2.asm application ONLINE ONLINE ract2
ora....T2.lsnr application ONLINE ONLINE ract2
ora.ract2.gsd application OFFLINE OFFLINE
ora.ract2.ons application ONLINE ONLINE ract2
ora.ract2.vip ora....t1.type ONLINE ONLINE ract2
ora....ry.acfs ora....fs.type ONLINE ONLINE ract1
ora.scan1.vip ora....ip.type ONLINE ONLINE ract2

官方解释如下：

Applies to:
Oracle Net Services - Version: 8.1.7.0 to 11.2.0.1 - Release: 8.1.7 to 11.2
Generic UNIX
Checked for relevance on 30-Apr-2010
Symptoms
Connection to the database server fails with ORA-12546 or ORA-12537

Local connections working fine but the connection via listener fails with ORA-12537 or ORA-12547

[oracle@myhost]$ sqlplus myuser/mypass@mytns

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Apr 30 21:32:18 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

The listener log for the failing connection shows following error message -

TNS-12518: TNS:listener could not hand off client connection
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Linux Error: 13: Permission denied

Or

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
Permissions on oracle binary ( $ORACLE_HOME/bin/oracle ) are not set correctly.

Solution
The OS level error message in the listener log "Linux Error: 13: Permission denied" indicates that the listener was not able to hand off the connection to oracle processes or not able to spawn a new dedicated user process because of lack of permission at the OS level. To resolve the error -

1. Check and correct the permission on /var/tmp/.oracle directory. Permissions on this directory should be 777 with sticky bit set.

# chmod 01777 /var/tmp/.oracle

2. Check and correct permissions on $ORACLE_HOME/bin/oracle binary and make sure that it is set to 6751.

$ chmod 6751 $ORACLE_HOME/bin/oracle

检查自己环境，确实发现不是正确的权限

[root@ract1 bin]# pwd
/oracle/product/11.2.0/bin
[root@ract1 bin]# ls -la oracle
-rwxr-s--x 1 oracle asmadmin 228886426 Jan 7 15:46 oracle
[root@ract1 bin]# chmod 6751 oracle - -注意使用root用户给权限
[root@ract1 bin]# ls -la oracle
-rwsr-s--x 1 oracle asmadmin 228886426 Jan 7 15:46 oracle

修改后登陆正常：

[oracle@ract2 ~]$ sqlplus sys/oracle@10.20.31.36:1521/racdb as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 17 14:48:25 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

总结：

设置suid/guid的作用：

suid意味着如果某个用户对属于自己的shell脚本设置了这种权限，那么其他用户在执行这一脚本时也会具有其属主的相应权限。于是，如果根用户的某一个脚本设置了这样的权限，那么其他普通用户在执行它的期间也同样具有根用户的权限。同样的原则也适用于guid，执行相应脚本的用户将具有该文件所属用户组中用户的权限。

为什么要使用suid/guid
为什么要使用这种类型的脚本？这里有一个很好的例子。我管理着几个大型的数据库系统，而对它们进行备份需要有系统管理权限。我写了几个脚本，并设置了它们的guid，这样我指定的一些用户只要执行这些脚本就能够完成相应的工作，而无须以数据库管理员的身份登录，以免不小心破坏了数据库服务器。通过执行这些脚本，他们可以完成数据库备份及其他管理任务，但是在这些脚本运行结束之后，他们就又回复到他们作为普通用户的权限。有相当一些UNIX命令也设置了suid和guid。

设置suid/guid的例子
chmod 4755 rws r-x r- x 文文件被设置了suid，文件属主具有读、写和执行的权限，所有其
他用户具有读和执行的权限
chmod 6711 rws --s --s 文文件被设置了suid和guid，文件属主具有读、写和执行的权限，
所有其他用户具有执行的权限
chmod 4764 rws rw- r- - 文文件被设置了suid，文件属主具有读、写和执行的权限，同组用
户具有读和执行的权限，其他用户具有读权限

还可以使用符号方式来设置suid/guid。如果某个文件具有这样的权限： rwx r-x r- x，那么
可以这样设置其suid：
chmod u+s
于是该文件的权限将变为： rws r-x r-x

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

转载于:http://blog.itpub.net/26655292/viewspace-752814/