11.2.0.4 通過 scan ip 連線資料庫報TNS-12537 連線關閉問題總結

paulyibinyi發表於2014-10-20
資料庫
     今天在自己的虛擬機器上安裝好一套oracle 11.2.0.4 rac for linux,
sqlplus 通過tnsnames 連線資料庫時報 TNS-12537 錯誤，客戶端連線資料庫也是一樣，報同樣錯誤

錯誤如下:
[oracle@rac1 ~]$ sqlplus a/a@racdb

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 20 14:13:58 2014

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

ERROR:
ORA-12537: TNS:connection closed


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

而 tnsping 是正常的
[oracle@rac1 ~]$ tnsping racdb

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 20-OCT-2014 14:13:46

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/home/oracle/11gR2/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb)))
OK (0 msec)

檢查監聽日誌如下:

20-OCT-2014 14:01:09 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(CID=(PROGRAM=sqlplus@rac1)(HOST=rac1)(USER=oracle))(INST
ANCE_NAME=racdb1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=138.30.0.201)(PORT=58757)) * establish * racdb * 12518
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

可以看到提示許可權錯誤

通過查詢metalink ORA-12537 / ORA-12547 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User (文件 ID 1069517.1)


4. Another cause may be permissions on the RDBMS Directory structure or Home directory which needs to be accessed by the CRS user.
Check that the RDBMS $ORACLE_HOME is set to 755.
This can be seen from an OS trace such as strace or truss when using it to trace the CRS user running the "oracle" executable which fails with the "Permission denied" error.


原因找到，因為我在我自己的虛擬機器上oracle安裝目錄放在/home/oracle下面,grid安裝目錄放在/home/grid下面

[root@rac1 ~]# cd /home
[root@rac1 home]# ls -ltr
total 12
drwxr-xr-x 10 root   oinstall 4096 Aug 12 14:45 grid
drwx------ 25 oracle oinstall 4096 Oct 20 13:55 oracle

/home/oracle目錄許可權太低
修改成755即可
[root@rac1 home]# chmod 755 oracle
[root@rac1 home]#
[root@rac1 home]#
[root@rac1 home]# ls -ltr
total 12
drwxr-xr-x 10 root   oinstall 4096 Aug 12 14:45 grid
drwxr-xr-x 25 oracle oinstall 4096 Oct 20 13:55 oracle

sqlplus 和客戶端就可以正常登入了

[oracle@rac1 ~]$ sqlplus test/test@racdb

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 20 14:17:13 2014

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


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

SQL>
SQL>

總結解決問題思路方法:看問題引起原因對應的log,比如客戶端連線不上資料庫，則看監聽日誌，根據報錯提示，查相關文件，再進一步診斷。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-1304680/，如需轉載，請註明出處，否則將追究法律責任。

相關文章