当前位置： 首页 > 监听 > 正文

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

监听 huangtingzhong 发布于 2014-10-24 22:48 抢沙发

             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:wendangSkyDrivers2sql>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:wendangSkyDrivers2sql>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:wendangSkyDrivers2sql>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:wendangSkyDrivers2sql>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:wendangSkyDrivers2sql>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:wendangSkyDrivers2sql>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:wendangSkyDrivers2sql>telnet 192.168.111.5 1521
会出现一个横线在那里一直不停的闪

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

d:wendangSkyDrivers2sql>telnet 192.168.111.5 1521会闪一次后出现
d:wendangSkyDrivers2sql>的提示，之前所有的cmd窗口的输出都丢失了
赏

您可能还会对这些文章感兴趣！

ORACLE监听限制IP地址登陆数据库TCP.VALIDNODE_CHECKING：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter