ORA-12537: TNS:connection closed

联系:QQ(5163721)

标题:ORA-12537: TNS:connection closed

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

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

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 ~]$ 
此条目发表在 network, troubleshoooting 分类目录,贴了 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>