tnsping遇到TNS-12546: TNS: 权限被拒绝

linuxboy823

tnsping遇到TNS-12546: TNS: 权限被拒绝

上一篇 / 下一篇  2009-05-08 11:21:26 / 个人分类：oracle

查看( 568 ) / 评论( 3 ) / 评分( 0 / 0 )

    从客户端tnsping 一台数据库，报错“TNS-12537:TNS:连接关闭”，奇怪~~，服务器端可以tnsping通，也可以正常访问数据库，listener状态也正常，为什么客户端连接不上，查了一下原因，为sqlnet.ora文件做了Validnode Verification 的限制。

    Information from metalink: You can configure the sqlnet.ora file to allow and deny access to the database via the validnode checking parmeters in sqlnet.ora file :
TCP.VALIDNODE_CHECKING
Use to specify whether to screen access to the database.Value is either YES or ON
e.g.TCP.VALIDNODE_CHECKING = YES

TCP.EXCLUDED_NODES
Use to specify which clients using the TCP/IP protocol are denied access to the database. Hostname and ipaddress can be used
e.g.TCP.EXCLUDED_NODES= (138.3.33.33)

TCP.INVITED_NODES
Use to specify which clients using the TCP/IP protocol are allowed access to the database. Hostname and ipadddress can be used.
e.g.TCP.INVITED_NODES=(138.4.44.44, hammer)

Please note that without the servers hostname or ipaddress in the invitied list, then PMON will not register with the listener.
Any changes to the values requires the TNS listener to be reloaded
 
问题重现：
1.客户端tnsping不通 
C:\Documents and Settings\Home>tnsping devdb
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-5月 -2009 10:49:13
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
已使用的参数文件:
C:\oracle\10.2.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 172.22.3.111)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = devdb)))
TNS-12537: TNS: 连接关闭

2.检查服务器端listener状态
C:\Documents and Settings\Administrator>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 08-5月 -2009 11:08:46
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.3.111)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 32-bit Windows: Version 10.2.0.4.0 - Produ
ction
启动日期                  08-5月 -2009 10:54:52
正常运行时间              0 天 0 小时 13 分 58 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          E:\oracle\product\10.2.0\db_1\network\admin\listener.ora
监听程序日志文件          E:\oracle\product\10.2.0\db_1\network\log\listener.log
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.3.111)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
服务摘要..
服务 "devdb_XPT" 包含 1 个例程。
  例程 "devdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "PL***tProc" 包含 1 个例程。
  例程 "PL***tProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "devdb" 包含 1 个例程。
  例程 "devdb", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

3.检查服务器端tnsping状态
C:\Documents and Settings\Administrator>tnsping devdb
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 08-5月 -2009 11:08:33
Copyright (c) 1997,  2007, Oracle.  All rights reserved.
已使用的参数文件:
E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 172.22.3.111)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = devdb)))
OK (20 毫秒)

4.检查服务器端$ORACLE_HOME\NETWORK\log\listener.log文件  
  08-5月 -2009 10:49:43 * 12546
  TNS-12546: TNS: 权限被拒绝
  TNS-12560: TNS: 协议适配器错误
  TNS-00516: 许可被拒绝

  08-5月 -2009 10:49:45 * 12546
  TNS-12546: TNS: 权限被拒绝
  TNS-12560: TNS: 协议适配器错误
  TNS-00516: 许可被拒绝
 
5.检查$ORACLE_HOME\NETWORK\ADMIN\sqlnet.ora文件连接方式及限制机制
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
tcp.validnode_checking = YES
tcp.invited_nodes = (182.16.X.X,182.16.X.X,182.23.X.X,172.16.X.X)

果真有访问db的ip限制，把客户端ip地址添加到sqlnet.ora文件中172.22.3.111

6.服务器端重启监听，客户端测试成功
  服务器端重启监听
  C:\Documents and Settings\Administrator>lsnrctl start
  C:\Documents and Settings\Administrator>lsnrctl stop
  客户端测试成功
  C:\Documents and Settings\Home>tnsping devdb
  TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-5月 -2009 11:18:56 
  Copyright (c) 1997, 2005, Oracle.  All rights reserved. 
  已使用的参数文件:
  C:\oracle\10.2.0\client_1\network\admin\sqlnet.ora 
  已使用 TNSNAMES 适配器来解析别名
  Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
  (HOST = 172.22.3.111)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = devdb)))
  OK (30 毫秒)

参考文献：

Subject:	What is Validnode Verification and How to Use It
	Doc ID:	462933.1	Type:	HOWTO
	Modified Date :	21-APR-2009	Status:	PUBLISHED