Oracle 12cR2 – DataGuard and TCP.VALID_NODE_CHECKING

By William Sescu

Security is a quite important topic, especially when running Oracle in the cloud. To make your DataGuard a little bit more secure, you can enable the TCP.VALIDNODE_CHECKING feature. I call it SQL*Net Firewall.

Enabling the SQL*Net Firewall is quite straight forward. There are only 3 sqlnet.ora parameters which are involved:

• TCP.VALIDNODE_CHECKING
• TCP.INVITED_NODES
• TCP.EXCLUDED_NODES

The parameter TCP.VALIDNODE_CHECKING turns this feature on or off. If enabled, then the incoming connections are allowed only if they originate from a node that conforms to the list specified by TCP.INVITED_NODES or TCP.EXCLUDED_NODES parameters.

The parameter TCP.INVITED_NODES list all clients that are allowed access to the database.

The parameter TCP.EXCLUDED_NODES specifies which clients are not allowed to access the database. In fact not even the listener.

Some important notes before you start configuring your valid node checking

• All host names in the TCP.INVITED_NODES or TCP.EXCLUDED_NODES must be resolvable or the listener will not start. In case a host name is not resolvable, the start of the listener fails with the error message: TNS-00584: Valid node checking configuration error In the listener trace file, you can identify which host name exactly is causing the issued. In my case it is the host dbidg04 which is not resolvable. Enabling listener tracing is very useful, especially if you have a long list of names and ip addresses.

  [20-DEC-2016 08:18:35:894] nlvlloadp: Adding host dbidg04
  [20-DEC-2016 08:18:35:894] snlinGetAddrInfo: entry
  [20-DEC-2016 08:18:35:937] snlinGetAddrInfo: getaddrinfo() failed with error -2
  [20-DEC-2016 08:18:35:938] snlinGetAddrInfo: exit
  [20-DEC-2016 08:18:35:938] nlvlloadp: Error 7001 while resolving host 17712412
  [20-DEC-2016 08:18:35:938] nlvlloadp: exit
  [20-DEC-2016 08:18:35:938] ntvllt: Problem in Loading tcp.invited_nodes

• Any change of the values in TCP.VALIDNODE_CHECKING, TCP.INVITED_NODES or TCP.EXCLUDED_NODES requires the listener to be stopped and started. Just reloading the listener can be very dangerous is some situation. See alsoBug 22194469 : TCP.INVITED_NODES IS DISABLED AFTER RELOADING LISTENER

• The TCP.INVITED_NODES list takes precedence over the TCP.EXCLUDED_NODES if both lists are used. E.g. if the following entries are present, the the host 192.168.56.203 is allowed to connected.

  TCP.INVITED_NODES  = (127.0.0.1, 192.168.56.201, 192.168.56.202, 192.168.56.203)
  TCP.EXCLUDED_NODES = (192.168.56.203)

• All entries in TCP.INVITED_NODES or TCP.EXCLUDED_NODES have to be on one line. In case the entries are not in one line, the listener will not start and you will receive the following error: TNS-00583: Valid node checking: unable to parse configuration parameters. e.g. the following list is invalid

  TCP.INVITED_NODES = (127.0.0.1,
  192.168.56.201,
  192.168.56.202)

• Adding empty placeholder strings like (comma, space, comma) are possible, however, I would not recommend them.

TCP.INVITED_NODES = (127.0.0.1, 192.168.56.201, 192.168.56.202, , ,)

•  Entries can be added via Net Manager as well. Local -> Profile -> General -> Access Rights. HINT: If you have some special formatting or commenting in your sqlnet.ora, the netmgr might reorganize your file. So better do a copy beforehand. 😉

• In case you work with SCAN and the GRID Infrastructure GRID_HOME/network/admin/sqlnet.ora file, then don’t forget to add your NODE VIP’s and SCAN VIP’s to your TCP.INVITED_NODES list

By knowing all that, we can start configuring our SQL*Net Firewall for our DataGuard environment. In a minimum configuration, we need to add the localhost, all hosts involved in the DataGuard configuration (Primary, Standby 1, standby 2, observer and so on) and of course the application server. Right now I do have my primary (192.168.56.201), my standby (192.168.56.202) and the localhost in the invited nodes list. The application server 192.168.56.203 is missing.

TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES = (127.0.0.1, 192.168.56.201, 192.168.56.202)

If I try to connect now from the application server, I do get the following error: TNS-12547: TNS:lost contact. As you can see, not even a tnsping is possible.

oracle@dbidg03:/home/oracle/ [DBIT122] tnsping DBIT122_SITE1

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 20-DEC-2016 09:11:51

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbidg01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DBIT122_SITE1_DGMGRL)))
TNS-12547: TNS:lost contact

A sqlplus connection gives you the same error:

oracle@dbidg03:/home/oracle/ [DBIT122] sqlplus system@DBIT122_SITE1

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 20 09:16:00 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12547: TNS:lost contact

The TNS: lost contact can mean anything. Turning on listener logging gives you a much clearer error message saying that an incoming connect from 192.168.56.203 is rejected.

Incoming connection from 192.168.56.203 rejected
20-DEC-2016 09:11:51 * 12546
TNS-12546: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00516: Permission denied

Through listener logging, you can monitor all incoming connected that are rejected. That’s quite cool from my point of view. Now … let’s add the application server (192.168.56.203) to the list, and restart the listener. (not reload) Don’t forget to do it on the Primary and the Standby.

TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES = (127.0.0.1, 192.168.56.201, 192.168.56.202, 192.168.56.203)

Now my application can tnsping and connect.

oracle@dbidg03:/home/oracle/ [DBIT122] tnsping DBIT122_SITE1

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 20-DEC-2016 09:21:01

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbidg01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DBIT122_SITE1_DGMGRL)))
OK (0 msec)
oracle@dbidg03:/home/oracle/ [DBIT122]
oracle@dbidg03:/home/oracle/ [DBIT122]
oracle@dbidg03:/home/oracle/ [DBIT122] sqlplus system@DBIT122_SITE1

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 20 09:21:10 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Mon Dec 19 2016 10:44:52 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

Conclusion

Setting up valid node checking is quite easy, and the listener log gives you clear error messages if there are any rejected hosts.  Make sure that you have a DataGuard test environment where you can test this feature including switchover, failover and so on. And very important, verify your name resolution before you add any hostname to the invited host list. The only drawback I see, is that the listener has to be stopped and started, meaning that new connections are not possible for a very short period of time.