Oracle TCP.VALIDNODE_CHECKING 监听限制IP登陆

使用tcp.validnode_checking允许、限制机器访问数据库，在$OREACLE_HOME/network/admin下直接修改sqlnet.ora文件，增加如下内容：
tcp.validnode_checking=yes
#允许访问的ip
tcp.invited_nodes=(ip1,ip2,……)
#不允许访问的ip
tcp.excluded_nodes=(ip1,ip2,……)
修改sqlnet.ora后，重新启动listener服务。如果不允许的IP客户端连接过来，会出现以下错误：

[oracle@Database1 admin]$ tnsping oradb

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2018 06:47:32

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.129)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb)))
TNS-12547: TNS:lost contact

ORACLE的Valid node checking(TCP.VALIDNODE_CHECKING)是监听的一个功能，可以用于允许或者拒绝指定的IP地址连接数据库，可以把成防火墙在1521端口上面的一个规则。
Valid node checking可以定义2个列表，一个是INVITEDNODES列表，指定监听允许连接数据库的IP地址或者是主机名。一个是EXCLUDED_NODES列表，指定监听不允许连接数据库的IP地址或者是主机名。
下面是测试案例：

（1）在主机Database1的Tnsname配置要远程登入数据库的网络名
[oracle@Database1 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORADB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradb)
    )
  )

（2）在要远程登入的数据库的$ORACLE_HOME/network/admin下面使用vim编辑一个sqlnet.ora文件，在里面加上参数制定不予许远程登入数据库的ip,即Database1的ip
[oracle@Database2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@Database2 admin]$ ls
listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora
[oracle@Database2 admin]$ cat sqlnet.ora  （加上下面标红的两行，即开启VALIDNODE_CHECKING功能，同时限制连接数据库的Ip）

TCP.VALIDNODE_CHECKING=yes
TCP.EXCLUDED_NODES=(192.168.57.128)

（3）在远程登入数据库配置好sqlnet.ora文件之后，将监听关闭然后重启监听

[oracle@Database2 admin]$ lsnrctl stop
[oracle@Database2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JUL-2018 20:33:58

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Database2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Database2)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                16-JUL-2018 20:33:58
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Database2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Database2)(PORT=1521)))
Services Summary...
Service "oradba" has 1 instance(s).
  Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

（4）避免动态注册监听需要一段时间，手工动态将服务注册到监听
[oracle@Database2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 16 20:34:14 2018

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

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

SQL> alter system register;

System altered.

SQL> exit;

（5）在主机Database1上面测试远程登入Database2，同时观察Database2上面监听日志
[oracle@Database1 admin]$ sqlplus system/system@oradb

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 6 06:11:11 2018

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

ERROR:
ORA-12547: TNS:lost contact

在远程要登入的数据库上面查看监听日志信息
[oracle@Database2 trace]$ tail -f listener.log 
16-JUL-2018 20:34:23 * service_update * oradb * 0
Incoming connection from 192.168.57.128 rejected 
16-JUL-2018 20:34:28 * 12546
TNS-12546: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00516: Permission denied

可以看到拒绝了远程登入

总结：可以在sqlnet.ora来限制登入数据库主机的IP地址