sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

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

时间:2016-11-01 21:32   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于如何限制IP地址访问Oracle数据库的详细文章,sqlnet.ora文件中TCP.VALIDNODE_CHECKING参数配置的方法。
ORACLE的Valid node checking(TCP.VALIDNODE_CHECKING)是监听的一个功能,可以用于允许或者拒绝指定的IP地址连接数据库,可以把成防火墙在1521端口上面的一个规则。Valid node checking可以定义2个列表,一个是INVITEDNODES列表,指定监听允许连接数据库的IP地址或者是主机名。一个是EXCLUDED_NODES列表,指定监听不允许连接数据库的IP地址或者是主机名。下面是测试案例:

1,查看检查状态

[oracle@oracleplus.net admin]$lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 13-OCT-2014 13:56:10
Copyright (c) 191, 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@oracleplus.net admin]$cat sqlnet.ora
TCP.VALIDNODE_CHECKING=yes
TCP.EXCLUDED_NODES=(192.168.111.1)
重新加载监听,这里只需要重新加载监听,不需要停监听再启动监听的。

[oracle@oracleplus.net 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@oracleplus.net 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@oracleplus.net 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@oracleplus.net 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@oracleplus.net admin]$cat sqlnet.ora
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.111.5)
[oracle@oracleplus.net 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@oracleplus.net 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@oracleplus.net 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, opOracleoracleplus.net=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 failur
[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@oracleplus.net ~]#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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】ORACLE监听限制IP地址登陆数据库TCP.VALIDNODE_CHECKING

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1134.html

Oracle研究中心

关键词:

TCP.VALIDNODE_CHECKING官方使用介绍

如何监控限制某IP地址访问Oracle数据库

Oracle数据库限制IP地址访问