转载

ORA-12537: TNS:connection closed

联系：QQ(5163721)

标题： ORA-12537: TNS:connection closed

今天测试数据库的时候，忽然发现使用远程连接的方式会报错：

SQL> conn sys/oracle@lunar as sysdba ERROR: ORA-12537: TNS:connection closed   Warning: You are no longer connected to ORACLE. SQL> conn sys/oracle@lunar as sysdba ERROR: ORA-12537: TNS:connection closed   SQL>

listener.log的日志如下：

24-JUN-2013 06:21:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=lunar)(CID=(PROGRAM=sqlplus)(HOST=lunar1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=48962)) * establish * lunar * 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

这个报错，只解决上应该跟OS的什么配置有关系，但是不清楚哪里的配置问题。正常的系统，使用strace跟踪，相关log如下：

。。。。。。。。。。。。。。。。。 16123      0.001337 open("/etc/hosts", O_RDONLY) = 10 16123      0.001629 fcntl(10, F_GETFD)  = 0 16123      0.001044 fcntl(10, F_SETFD, FD_CLOEXEC) = 0 16123      0.001640 fstat(10, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0 16123      0.000826 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f11f0d1c000 16123      0.001343 read(10, "# Do not remove the following li"..., 4096) = 590 16123      0.001433 close(10)           = 0 16123      0.001342 munmap(0x7f11f0d1c000, 4096) = 0 16123      0.001008 open("/etc/hostid", O_RDONLY) = -1 ENOENT (No such file or directory) 16123      0.000998 uname({sys="Linux", node="lunar1", ...}) = 0 16123      0.001304 open("/etc/hosts", O_RDONLY) = 10 16123      0.004079 fcntl(10, F_GETFD)  = 0 16123      0.000199 fcntl(10, F_SETFD, FD_CLOEXEC) = 0 16123      0.000167 fstat(10, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0 16123      0.001394 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f11f0d1c000 16123      0.001537 read(10, "# Do not remove the following li"..., 4096) = 590 16123      0.001640 close(10)           = 0 16123      0.001891 munmap(0x7f11f0d1c000, 4096) = 0 16123      0.001114 gettimeofday({1372028588, 2119}, NULL) = 0 16123      0.000792 write(9, "/3/214/0/0/6/0/0/0/0/0/3s/3/376/377/377/377/377/377/377/377/t/0/0/0!/1/0/0/376/377/377"..., 908) = 908 16123      0.003459 read(9, "/6/315/0/0/6/0/0/0/0/0/10&/0/23/0/0/0/23AUTH_VERSION_S"..., 8208) = 1741 16123      0.005878 open("/u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb", O_RDONLY) = 10 16123      0.001656 fcntl(10, F_SETFD, FD_CLOEXEC) = 0 16123      0.001247 lseek(10, 0, SEEK_SET) = 0 16123      0.001266 read(10, "/25/23/"/1/23/3/t/t/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0"..., 256) = 256 16123      0.001189 lseek(10, 512, SEEK_SET) = 512 16123      0.001447 read(10, "l/31/3013/276J/213hv{/316/210/200/227S/3113/373/0/0/0/0/0/0/0/0/0/0/0/0/0/0"..., 512) = 512 16123      0.002147 lseek(10, 1024, SEEK_SET) = 1024 16123      0.001641 read(10, "/30/0$/0002/0;/0D/0Q/0[/0f/0o/0{/0/210/0/240/0/310/0/321/0/331/0/340/0"..., 512) = 512 16123      0.002218 lseek(10, 55808, SEEK_SET) = 55808 16123      0.000759 read(10, "/10/0e/5/0/0008/0f/5/0/0/232/0g/5/0/0/7/1w/5/0/0Z/1x/5/0/0o/1"..., 512) = 512 16123      0.002017 lseek(10, 512, SEEK_SET) = 512 16123      0.000381 read(10, "l/31/3013/276J/213hv{/316/210/200/227S/3113/373/0/0/0/0/0/0/0/0/0/0/0/0/0/0"..., 512) = 512 16123      0.001120 lseek(10, 1024, SEEK_SET) = 1024 16123      0.000799 read(10, "/30/0$/0002/0;/0D/0Q/0[/0f/0o/0{/0/210/0/240/0/310/0/321/0/331/0/340/0"..., 512) = 512 16123      0.001908 lseek(10, 56320, SEEK_SET) = 56320 。。。。。。。。。。。。。。。。。。

这个有问题的系统使用strace跟踪，相关log如下：

。。。。。。。。。。。。。。。。。。 11647      0.002544 open("/etc/hosts", O_RDONLY) = 9 11647      0.000358 fcntl(9, F_GETFD)   = 0 11647      0.000122 fcntl(9, F_SETFD, FD_CLOEXEC) = 0 11647      0.000169 fstat(9, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0 11647      0.000120 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f48f89b9000 11647      0.000157 read(9, "# Do not remove the following li"..., 4096) = 590 11647      0.000393 read(9, "", 4096)   = 0 11647      0.000107 close(9)            = 0 11647      0.000155 munmap(0x7f48f89b9000, 4096) = 0 11647      0.000178 open("/etc/hosts", O_RDONLY) = 9 11647      0.000129 fcntl(9, F_GETFD)   = 0 11647      0.000559 fcntl(9, F_SETFD, FD_CLOEXEC) = 0 11647      0.000204 fstat(9, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0 11647      0.001389 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f48f89b9000 11647      0.000577 read(9, "# Do not remove the following li"..., 4096) = 590 11647      0.000127 close(9)            = 0 11647      0.000123 munmap(0x7f48f89b9000, 4096) = 0 11647      0.000300 lstat("/u01/app/oracle/diag/clients/user_oracle/host_918442181_80/alert/log.xml", {st_mode=S_IFREG|0640, st_size=132505, ...}) = 0 。。。。。。。。。。。。

上述较大的不同在于fcntl(9, F_GETFD)相关的内容，这个东西我也不懂，c语言都还给老师了…… ：（

于是查询官方文档，根据MOS介绍，逐一核对文件权限：

检查oracle用户的权限：1，grid的是正确的：

[grid@lunar1 trace]$ ls -l $ORACLE_HOME/bin/oracle -rwsr-s--x 1 grid asmadmin 203973009 May  3 12:42 /u01/11.2.0/grid/bin/oracle [grid@lunar1 trace]$

oracle的是错误的：

[oracle@lunar1 ~]$ ls -l $ORACLE_HOME/bin/oracle -rwxr-s--x 1 oracle asmadmin 232399431 May  3 15:11 /u01/app/oracle/product/11.2.0/db_1/bin/oracle [oracle@lunar1 ~]$

使用root修改

[root@lunar1 ~]# chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle [root@lunar1 ~]#

再次检查已经正确了：

[oracle@lunar1 ~]$ ls -l $ORACLE_HOME/bin/oracle -rwsr-s--x 1 oracle asmadmin 232399431 May  3 15:11 /u01/app/oracle/product/11.2.0/db_1/bin/oracle [oracle@lunar1 ~]$

2，oracle用户的ORACLE_HOME所在文件系统不支持setuid/suid（也不支持 nosetuid/nosuid 设置），例如:

mount| grep <mount_point_of_ORACLE_HOME> /home/oracle on /dev/dsk/diskoracle read/write/nosuid..

我这里正常的：

[oracle@lunar1 ~]$ mount| grep /u01 /dev/sdb1 on /u01 type ext3 (rw) [oracle@lunar1 ~]$

再次测试还是连接不上：

SQL> conn sys/oracle@lunar as sysdba ERROR: ORA-12537: TNS:connection closed   Warning: You are no longer connected to ORACLE. SQL>

3，检查oracle用户的$ORACLE_HOME应该为755（注意，不要带 -R ！！！）：

[oracle@lunar1 ~]$ chmod 755 $ORACLE_HOME [oracle@lunar1 ~]$

这次可以了：

SQL> conn sys/oracle@lunar as sysdba Connected. SQL>

把节点2也修改了：

[oracle@RAC2 ~]$ chmod 755 $ORACLE_HOME [oracle@RAC2 ~]$   [oracle@RAC2 ~]$ ls -l $ORACLE_HOME/bin/oracle -rwxr-s--x 1 oracle asmadmin 232399431 May  3 15:16 /u01/app/oracle/product/11.2.0/db_1/bin/oracle [oracle@RAC2 ~]$ exit logout [root@RAC2 ~]# chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle [root@RAC2 ~]# su - oracle [oracle@RAC2 ~]$ ls -l $ORACLE_HOME/bin/oracle -rwsr-s--x 1 oracle asmadmin 232399431 May  3 15:16 /u01/app/oracle/product/11.2.0/db_1/bin/oracle [oracle@RAC2 ~]$   [grid@RAC2 ~]$ ls -l $ORACLE_HOME/bin/oracle -rwsr-s--x 1 grid oinstall 203973009 May  3 12:46 /u01/11.2.0/grid/bin/oracle [grid@RAC2 ~]$

正文到此结束