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监听限制IP地址登陆数据库TCP.VALIDNODE_CHECKING：等您坐沙发呢！