学习笔记：ORACLE监听限制IP地址登陆数据库TCP.VALIDNODE_CHECKING

原创于 2025-07-20 15:32:46 发布 · 375 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #学习 #笔记

Oracle 同时被 2 个专栏收录

96 篇文章

订阅专栏

学习随笔

33 篇文章

订阅专栏

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

ORACLE监听限制IP地址登陆数据库TCP.VALIDNODE_CHECKING

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

下面是测试案例：

1，查看检查状态

[oracle@www.htz.pw admin]$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 13-OCT-2014 13:56:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl9i)(PORT=1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date                12-OCT-2014 23:09:43

Uptime                    0 days 14 hr. 46 min. 27 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1123/network/admin/listener.ora

Listener Log File         /tmp/www.htz.pw.log

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl9i)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary…

Service “orcl1123” has 1 instance(s).

  Instance “orcl1123”, status READY, has 1 handler(s) for this service…

The command completed successfully

2，配置TCP.VALIDNODE_CHECKING参数

这里只配置了excluded_nodes列表，代表着允许除excluded_nodes中指定的IP地址或者主机名以外的其它所有IP地址与主机名都可以连接数据库。

[oracle@www.htz.pw admin]$cat sqlnet.ora

TCP.VALIDNODE_CHECKING=yes

TCP.EXCLUDED_NODES=(192.168.111.1)

重新加载监听，这里只需要重新加载监听，不需要停监听再启动监听的。

[oracle@www.htz.pw admin]$lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 13-OCT-2014 13:56:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl9i)(PORT=1521)))

The command completed successfully

在192.168.111.5主机测试

[oracle@www.htz.pw admin]$sqlplus -s sys/oracle@192.168.111.5:1521/orcl1123  as sysdba

select open_mode from v$database;

OPEN_MODE

——————–

READ WRITE

exit

在192.168.111.1主机测试

d:\wendang\SkyDrive\rs2\sql>sqlplus -s sys/oracle@192.168.111.5:1521/orcl1123  as sysdba

ERROR:

ORA-12537: TNS:connection closed

这里可以看到报ORA-12537错误。

3、同时配置TCP.INVITED_NODES/TCP.EXCLUDED_NODES列表

当同时配置2个列表的时候，TCP.INVITED_NODES比TCP.EXCLUDED_NODES具有更高的优先性，也就意味着如果IP地址同时在2个列表中时，是允许访问数据库的

[oracle@www.htz.pw admin]$cat sqlnet.ora

TCP.VALIDNODE_CHECKING=yes

TCP.INVITED_NODES=(192.168.111.1,192.168.111.5)

TCP.EXCLUDED_NODES=(192.168.111.1)

[oracle@www.htz.pw admin]$lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 13-OCT-2014 14:29:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl9i)(PORT=1521)))

The command completed successfully


d:\wendang\SkyDrive\rs2\sql>sqlplus -s sys/oracle@192.168.111.5:1521/orcl1123  as sysdba

select open_mode from v$database;

OPEN_MODE

——————–

READ WRITE

exit

4，配置TCP.INVITED_NODES列表

[oracle@www.htz.pw admin]$cat sqlnet.ora

TCP.VALIDNODE_CHECKING=yes

TCP.INVITED_NODES=(192.168.111.5)

[oracle@www.htz.pw admin]$lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 13-OCT-2014 14:30:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl9i)(PORT=1521)))

The command completed successfully


d:\wendang\SkyDrive\rs2\sql>sqlplus -s sys/oracle@192.168.111.5:1521/orcl1123  as sysdba

ERROR:

ORA-12537: TNS:connection closed

5，分析生成的日志信息

由于valid node checking导致不能正常连接数据库会在监听日志中生成如下的信息：

[oracle@www.htz.pw admin]$lsnrctl status|grep log

Listener Log File         /tmp/www.htz.pw.log

查看监听日志

可以发现如下的信息

13-OCT-2014 14:32:50 * 12546

TNS-12546: TNS:permission denied

 TNS-12560: TNS:protocol adapter error

  TNS-00516: Permission denied

配置trace监听

服务器端会生成如下的信息，不建议在服务器端开启监听的TRACE功能，那会生成大量的日志信息的

[oracle@www.htz.pw admin]$tail -3 listener.ora

DIAG_ADR_ENABLED_LISTENER=off

LOG_DIRECTORY_LISTENER=/tmp

LOG_FILE_LISTENER=www.htz.pw

TRACE_DIRECTORY_LISTENER = /tmp

TRACE_FILE_LISTENER = listener.log

TRACE_LEVEL_LISTENER = support

[13-OCT-2014 19:39:56:305] nsopen: opening transport…

[13-OCT-2014 19:39:56:305] nttcon: entry

[13-OCT-2014 19:39:56:305] nttcon: toc = 3

[13-OCT-2014 19:39:56:305] nttcnp: entry

[13-OCT-2014 19:39:56:305] nttcnp: getting sockname

[13-OCT-2014 19:39:56:305] nttcnp: getting peername

[13-OCT-2014 19:39:56:305] nttcnp: exit

[13-OCT-2014 19:39:56:305] nttcnr: entry

[13-OCT-2014 19:39:56:305] nttcnr: waiting to accept a connection.

[13-OCT-2014 19:39:56:305] nttcnr: getting sockname

[13-OCT-2014 19:39:56:305] snlinGetNameInfo: entry

[13-OCT-2014 19:39:56:305] snlinGetNameInfo: exit

[13-OCT-2014 19:39:56:305] nttcnr: connected on ipaddr 192.168.111.5

[13-OCT-2014 19:39:56:305] nttvlser: entry

[13-OCT-2014 19:39:56:305] snlinGetNameInfo: entry

[13-OCT-2014 19:39:56:305] snlinGetNameInfo: exit

[13-OCT-2014 19:39:56:305] snlinGetNameInfo: entry

[13-OCT-2014 19:39:56:305] snlinGetNameInfo: exit

[13-OCT-2014 19:39:56:305] snlinGetAddrInfo: entry

[13-OCT-2014 19:39:56:305] snlinGetAddrInfo: exit

[13-OCT-2014 19:39:56:305] snlinFreeAddrInfo: entry

[13-OCT-2014 19:39:56:305] snlinFreeAddrInfo: exit

[13-OCT-2014 19:39:56:305] nttvlser: Denied Entry

[13-OCT-2014 19:39:56:305] nttcon: exit

[13-OCT-2014 19:39:56:305] nserror: entry

[13-OCT-2014 19:39:56:305] nserror: nsres: id=1, op=65, ns=12546, ns2=12560; nt[0]=516, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

[13-OCT-2014 19:39:56:305] nsopen: unable to open transport

[13-OCT-2014 19:39:56:305] nstoClearTimeout: entry

[13-OCT-2014 19:39:56:305] nstoClearTimeout: ATO disabled for ctx=0x0x64ed40

[13-OCT-2014 19:39:56:305] nstoClearTimeout: STO disabled for ctx=0x0x64ed40

[13-OCT-2014 19:39:56:305] nstoClearTimeout: RTO disabled for ctx=0x0x64ed40

[13-OCT-2014 19:39:56:305] nstoClearTimeout: PITO disabled for ctx=0x0x64ed40

[13-OCT-2014 19:39:56:305] nstoUpdateActive: entry

[13-OCT-2014 19:39:56:305] nstoUpdateActive: Active timeout is -1 (see nstotyp)

[13-OCT-2014 19:39:56:305] nsiocancel: entry

服务端生成的trace信息

DIAG_ADR_ENABLED=off

TRACE_LEVEL_CLIENT = 16 

TRACE_FILE_CLIENT = listener.trc

TRACE_DIRECTORY_CLIENT = e:\install 

TRACE_TIMESTAMP_CLIENT = ON

d:\wendang\SkyDrive\rs2\sql>sqlplus -s sys/oracle@192.168.111.5:1521/orcl1123 as sysdba

ERROR:

ORA-12570: TNS:packet reader failure

[24-OCT-2014 15:41:04:013] nsdo: cid=0, opcode=68, *bl=2048, *what=9, uflgs=0x0, cflgs=0x3

[24-OCT-2014 15:41:04:014] nsdo: rank=64, nsctxrnk=0

[24-OCT-2014 15:41:04:014] nsdo: nsctx: state=2, flg=0x4005, mvd=0

[24-OCT-2014 15:41:04:014] nsdo: gtn=10, gtc=10, ptn=10, ptc=8155

[24-OCT-2014 15:41:04:014] nscon: entry

[24-OCT-2014 15:41:04:014] nscon: recving a packet

[24-OCT-2014 15:41:04:014] nsprecv: entry

[24-OCT-2014 15:41:04:014] nsprecv: reading from transport…

[24-OCT-2014 15:41:04:014] nttrd: entry

[24-OCT-2014 15:41:04:014] ntt2err: entry

[24-OCT-2014 15:41:04:014] ntt2err: soc 508 error – operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0

[24-OCT-2014 15:41:04:014] ntt2err: exit

[24-OCT-2014 15:41:04:014] nttrd: exit

[24-OCT-2014 15:41:04:014] nsprecv: error exit

[24-OCT-2014 15:41:04:014] nserror: entry

[24-OCT-2014 15:41:04:014] nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

……………………

[24-OCT-2014 15:41:04:015] nioqper:  error from nscall

[24-OCT-2014 15:41:04:015] nioqper:    ns main err code: 12570

[24-OCT-2014 15:41:04:015] nioqper:    ns (2)  err code: 12560

[24-OCT-2014 15:41:04:015] nioqper:    nt main err code: 530

[24-OCT-2014 15:41:04:015] nioqper:    nt (2)  err code: 53

[24-OCT-2014 15:41:04:015] nioqper:    nt OS   err code: 0

6、主机防火墙导致不能连接数据库

这里开启主机防火墙，拒绝所有的IP地址连接

d:\wendang\SkyDrive\rs2\sql>sqlplus -s sys/oracle@192.168.111.5:1521/orcl1123 as sysdba

ERROR:

ORA-12170: TNS:Connect timeout occurred

这里大概会HANG住一会儿后报ORA-12170错误

下面来看看客户端的TRACE信息

[24-OCT-2014 16:01:50:437] ntctst: size of NTTEST list is 1 – not calling poll

[24-OCT-2014 16:01:50:437] sntseltst: Testing for WRITE on socket 508

[24-OCT-2014 16:02:11:439] sntseltst: FOUND: write request on socket 508

[24-OCT-2014 16:02:11:439] ntt2err: entry

[24-OCT-2014 16:02:11:439] ntt2err: soc 508 error – operation=1, ntresnt[0]=505, ntresnt[1]=60, ntresnt[2]=0

[24-OCT-2014 16:02:11:439] ntt2err: exit

[24-OCT-2014 16:02:11:539] nttcni: exit

[24-OCT-2014 16:02:11:539] nttcon: exit

[24-OCT-2014 16:02:11:539] nserror: entry

[24-OCT-2014 16:02:11:539] nserror: nsres: id=0, op=65, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

[24-OCT-2014 16:02:11:539] nsopen: unable to open transport

………………………..

[24-OCT-2014 16:02:11:539] nsmfr: 2944 bytes at 0x66503d0

[24-OCT-2014 16:02:11:539] nsmfr: normal exit

[24-OCT-2014 16:02:11:539] nsmfr: entry

[24-OCT-2014 16:02:11:539] nsmfr: 1496 bytes at 0x664fdf0

[24-OCT-2014 16:02:11:539] nsmfr: normal exit

[24-OCT-2014 16:02:11:539] nsopen: error exit

[24-OCT-2014 16:02:11:539] nsclose: entry

[24-OCT-2014 16:02:11:539] nsclose: normal exit

[24-OCT-2014 16:02:11:539] nladget: entry

[24-OCT-2014 16:02:11:539] nladget: exit

[24-OCT-2014 16:02:11:539] nsmfr: entry

[24-OCT-2014 16:02:11:539] nsmfr: 206 bytes at 0x664fd10

[24-OCT-2014 16:02:11:539] nsmfr: normal exit

[24-OCT-2014 16:02:11:539] nsmfr: entry

[24-OCT-2014 16:02:11:539] nsmfr: 280 bytes at 0x664ea70

[24-OCT-2014 16:02:11:539] nsmfr: normal exit

[24-OCT-2014 16:02:11:539] nladtrm: entry

[24-OCT-2014 16:02:11:539] nladtrm: exit

[24-OCT-2014 16:02:11:539] nscall: error exit

[24-OCT-2014 16:02:11:539] nioqper:  error from nscall

[24-OCT-2014 16:02:11:539] nioqper:    ns main err code: 12535

[24-OCT-2014 16:02:11:539] nioqper:    ns (2)  err code: 12560

[24-OCT-2014 16:02:11:539] nioqper:    nt main err code: 505

[24-OCT-2014 16:02:11:539] nioqper:    nt (2)  err code: 60

[24-OCT-2014 16:02:11:539] nioqper:    nt OS   err code: 0

[24-OCT-2014 16:02:11:539] niomapnserror: entry

[24-OCT-2014 16:02:11:539] niqme: entry

[24-OCT-2014 16:02:11:539] niqme: reporting NS-12535 error as ORA-12535

[24-OCT-2014 16:02:11:539] niqme: exit

[24-OCT-2014 16:02:11:539] niomapnserror: exit

[24-OCT-2014 16:02:11:539] niotns: Couldn’t connect, returning 12170

[24-OCT-2014 16:02:11:539] nioqer: entry

[24-OCT-2014 16:02:11:539] nioqer:  incoming err = 12170

[24-OCT-2014 16:02:11:540] nioqce: entry

[24-OCT-2014 16:02:11:540] nioqce: exit

[24-OCT-2014 16:02:11:540] nioqer:  returning err = 3113

[24-OCT-2014 16:02:11:540] nioqer: exit

[24-OCT-2014 16:02:11:540] niotns: exit

[24-OCT-2014 16:02:11:540] nsbfrfl: entry

[24-OCT-2014 16:02:11:540] nsbrfr: entry

[24-OCT-2014 16:02:11:540] nsbrfr: nsbfs at 0x6651190, data at 0x6651240.

[24-OCT-2014 16:02:11:540] nsbrfr: normal exit

下面是使用telnet来测试

d:\wendang\SkyDrive\rs2\sql>telnet 192.168.111.5 1521

正在连接192.168.111.5…无法打开到主机的连接。在端口 1521: 连接失败

可以看到这个报错

正常情况下

unix环境应该是

[root@www.htz.pw ~]#telnet 192.168.111.5 1521

Trying 192.168.111.5…

Connected to orcl9i (192.168.111.5).

Escape character is ‘^]’.

WIN环境应该是

d:\wendang\SkyDrive\rs2\sql>telnet 192.168.111.5 1521

会出现一个横线在那里一直不停的闪

如果由于监听导致指定的IP地址不能连接在telnet时

d:\wendang\SkyDrive\rs2\sql>telnet 192.168.111.5 1521会闪一次后出现

d:\wendang\SkyDrive\rs2\sql>的提示，之前所有的cmd窗口的输出都丢失了

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)