sqlnet.ora 增加IP限制 监听不能启动

am196

今天要维护数据库，需要更新部份数据按照这个思路的步骤是：
先限制其他人访问数据库
然后再修改
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
把表的触发器禁用
更新数据
启用触发器
job_queue_processes,aq_tm_processes改回原来的值
让其他人可以正常访问数据库

不让其他人访问数据库可以在sqlnet.ora上做限制
在sqlnet.ora上增加
tcp.validnode_checking=yes
tcp.invited_nodes= (172.24.17.73)   #允許訪問的IP
#tcp.excluded_nodes= (172.24.17.73)     #禁止訪問的IP
重启监听后
用我的电脑可以正常访问
C:\Users\Fei>sqlplus sys@t2.11 as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期六 6月 16 19:19:55 2012
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
輸入密碼:
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
换个IP试试
C:\Documents and Settings\yy-lxf>sqlplus sys@t2.11 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 6月 16 19:21:19 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
輸入密碼:
ERROR:
ORA-12537: TNS:已關閉連線

輸入使用者名稱:
说明设置已经起做用了。
现在换到生产主机上
监听可以正常关闭
启动监听的时候报
[oracle@newais ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 16-JUN-2012 17:08:47
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Starting /product/oracle/10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.5.0 - Production
System parameter file is /product/oracle/10g/network/admin/listener.ora
Log messages written to /product/oracle/10g/network/log/listener.log
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.2.1)(PORT=1521)))
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use
由于在测试机上同样的设置就可以正常限制，所以也没有考试sqlnet.ora
我先试着把动态监听改为静态监听
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.17.73)(PORT = 1521))
  )
[oracle@newais ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 16-JUN-2012 17:09:13
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Starting /product/oracle/10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.5.0 - Production
System parameter file is /product/oracle/10g/network/admin/listener.ora
Log messages written to /product/oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.24.2.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.2.1)(PORT=1521)))
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 104: Connection reset by peer
还是不行。
我觉得不是监听的问题，就把监听又改为动态监听了
启动时还是报该IP被占用，拒绝启动，
然后我想起以前我修改过/etc/hosts文件，
[oracle@newais admin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1              newais.yyhj.com.cn newais localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
#
127.0.0.1               localhost.localdomain localhost
172.24.2.1              newais.yyhj.com.cn newais localhost.localdomain localhost
#172.24.2.2              newais.yyhj.com.cn newais localhost.localdomain localhost
You have new mail in /var/spool/mail/oracle
把新加的#172.24.2.2 newais.yyhj.com.cn newais localhost.localdomain localhost
注掉还是不行
我检查监听的1521端口看有没有被占用
netstat -an | grep 1521
经检查该端口也没有被占用
但是用lsof -i :1521 | grep "\(LISTEN\)"
或者使用 netstat -anp |grep 1521 也可以
检查有程式在使用1521，然后我在root下把该进程号kill掉
启动监听还问题依旧，检查监听日志的大小，也没有超过2G
然后我就检查一下sqlora.log日志文件发现
[oracle@newais log]$ tail sqlnet.log
        TNS for Linux: Version 10.2.0.5.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.5.0 - Production
  Time: 16-JUN-2012 16:17:20
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
    TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
   TNS-12564: TNS:connection refused
现在也没有人进行连接，只有我自己在做监听的启动，会不会是因为
tcp.invited_nodes= (172.24.17.73) #允許訪問的IP
上面的设备，把自己的给限制了，导致不能启动数据库
然后我把它改为
tcp.invited_nodes= (172.24.2.1，172.24.17.73) #允許訪問的IP
然后再启动监听，成功启动，到此问题解决

虽然这个问题解决了，但是我还是不知道为什么在生产库上会出现该问题，
难道跟生产库上有3个实例有关吗？
为什么在测试库上可以正常启动，生产库上不能启动呢？
谁知道具体原因的，帮我解释一下，谢谢！

Maclean Liu(刘相兵

ODM FINDING:


Listener specific :


Problem :-
Listener is failing to start with the error ORA-12547.

Cause :-
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the Database server specified in the address definition of the listener.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the Database server specified in the address definition of the listener.

Solution :-
Either add the IP address of the Database server in the TCP.INVITED_NODES list and remove it from the TCP.EXCLUDED_NODES list if present

It is recommended to restart the database server and the listener  for these parameters to take effect.



要求 将 listener 监听的address 加入到 TCP.INVITED_NODES list  中， 这是一个硬性限制

am196

原帖由 maclean 于 2012-6-17 22:39 发表
ODM FINDING:


Listener specific :


Problem :-
Listener is failing to start with the error ORA-12547.

Cause :-
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES li ...

知道了，非常感谢！