当前位置: 首页 > LISTENER > 正文

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

             ORACLEValid 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_NODESTCP.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窗口的输出都丢失了

本文固定链接: http://www.htz.pw/2014/10/24/oracle%e7%9b%91%e5%90%ac%e9%99%90%e5%88%b6ip%e5%9c%b0%e5%9d%80%e7%99%bb%e9%99%86%e6%95%b0%e6%8d%ae%e5%ba%93tcp-validnode_checking.html | 认真就输

该日志由 huangtingzhong 于2014年10月24日发表在 LISTENER 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: ORACLE监听限制IP地址登陆数据库TCP.VALIDNODE_CHECKING | 认真就输
关键字: ,