Exclude SQL Net Connection from particular node (or list of nodes).

As I mentioned in previous post, we had tough time recovering production database which happened due to some SQL script update against production instead of development.

To prevent any sql client connection from development boxes to production server we did following.

1. Add following entry in $TNS_ADMIN/sqlnet.ora file on production database server.

>pwd

/local/oracle/product/9.2.0.8/network/admin

nxtffind:/local/oracle/product/9.2.0.8/network/admin

>cat sqlnet.ora

TCP.EXCLUDED_NODES= (nxtfsapq)

TCP.VALIDNODE_CHECKING = YES

nysefind:/local/oracle/product/9.2.0.8/network/admin

2. Restarted listener

$export ORACLE_SID=<sid>

$export ORACLE_HOME=/local/oracle/product/9.2.0.8

$export $TNS_ADMIN=$ORACLE_HOME/network/admin

$export PATH=$ORACLE_HOME/bin:$PATH

$lsnrctl stop ndev2

$lsnrctl start ndev2

Noteà Listener reload will not work so you need to stop listener and start it again.

3. Tried to connect to production database from development server which were included in sqlnet.ora file on RDBMS server to exclude sql * net connection coming from these server SQL client.

$hostname

nxtfsapq

$cat $TNS_ADMIN/network/tnsnames.ora

ndev2 = (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=nxtffind)(PORT=1527))

(CONNECT_DATA=(SID=ndev2))

)

$ sqlplus apps@ndev2

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 11 22:34:13 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

ERROR:

ORA-12537: TNS:connection closed

Reference:

Metalink Note

What is Validnode Verification?

Doc ID: Note:1006500.6