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 个处理程序...
服务 "PLSExtProc" 包含 1 个例程。
例程 "PLSExtProc", 状态 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 毫秒)
come from:http://space.itpub.net/9252210/viewspace-594500
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90618/viewspace-675675/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/90618/viewspace-675675/