Oracle11gR2限制指定IP访问

Oracle11gR2限制指定的IP访问

1. 环境说明

Oracle数据库版本： Oracle11gR2

安装位置：E:\app\product

1. 先停止监听

lsnrctl stop

2. 配置监听文件

1. 编辑文件：E:\app\product\11.2.0\dbhome_1\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 = (192.168.1.115,localhost,127.0.0.1,192.168.1.16)

2. 配置说明

tcp.validnode_checking = yes
#tcp.invited_nodes=() #允许访问的IP列表或主机名，用逗号分隔
如：tcp.invited_nodes=(10.10.10.115)
#这里需要注意的是必须把本机ip地址加进来（不能写成localhost和127.0.0.1），否则监听启动会报错
#tcp.excluded_nodes=() #限制访问的IP列表或主机名，用逗号分隔

#说明：
#同时使用tcp.invited_nodes和tcp.excluded_nodes,则会以tcp.invited_nodes为主
# 支持通配符，如tcp.invited_nodes = (192.168.1.*,192.168.1.23/22) 
# 只能限制TCP协议
# 需要重启监听生效：
lsnrctl stop
lsnrctl start

3. 配置后，如果非允许的主机IP连接数据库时，会提示 ORA-12537: TNS:lost contact 或中文提示 ORA-12537: TNS:连接关闭

3. tnsnames.ora中配置IP

E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

# tnsnames.ora Network Configuration File: D:\Program Files\instantclient_12_2\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.115)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL249 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.249)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

4 .重启监听

lsnrctl stop
lsnrctl start

2. 常见问题

1. 开启Oracle的IP访问限制后错误提示

tcp.validnode_checking = yes
tcp.invited_nodes = (192.168.1.115,localhost,127.0.0.1)

1. PL/SQLdevelper连接错误提示

ORA-12541: TNS:监听程序当前无法识别连接描述符中请求的服务

2. Java代码连接数据库错误提示

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Connected to the target VM, address: '127.0.0.1:51801', transport: 'socket'
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
	at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
	at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at com.yuan.OracleDBConnect.oracle(OracleDBConnect.java:15)
	at com.yuan.OracleDBConnect.main(OracleDBConnect.java:8)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
	... 8 more
Disconnected from the target VM, address: '127.0.0.1:51801', transport: 'socket'

Process finished with exit code 0
 

2. 解决方法

在：E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora文件中加入下面信息

(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = E:\app\product\11.2.0\dbhome_1)
(SID_NAME = ORCL)
)

listener.ora完整内容如下

# listener.ora Network Configuration File: E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = orcl)
     (ORACLE_HOME = E:\app\product\11.2.0\dbhome_1)
     (SID_NAME = ORCL)
     )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\product\11.2.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.115)(PORT = 1521))
	  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )