By Franck Pachot

.
When you configure a standby database, you want the application to transparently connect to the primary database, wherever it is. That’s the role of Transparent Application Failover, but this requires configuration on the client side. If you can’t configure TAF, you can use a virtual IP address. But then the question is how to configure the listener.ora to handle connections to this VIP.

Don’t worry, if you configured everything as recommended, with the hostname declared in /etc/hosts, and listener.ora referencing this host name, then you can simply ignore the VIP for your configuration. The reason is that when the host specified in the listener.ora resolves to the same IP address as the hostname of the server, then Oracle listener binds the port on all interfaces, and this includes the VIP.

However, if you mentioned an IP address in the listener.ora, or if you mentioned a host that resolves to a different IP than the hostname, then it listens only tho this interface.

Why not just listen to the VIP? There are two reasons for that. First, you will need to listen to the host IP anyway for the dynamic registration of instances. You don’t want the standby database to contact the listener on the primary server. The second reason is that you cannot start the listener if the IP is not up. Then, if you want to explicitly listen to the VIP you will need two listeners, some security rules to allow only local registration and to manage the start of the listener, monitoring, etc.

The simplest configuration is to have one listener configured on the server hostname, then it listens on all interfaces and clients can connect with the VIP (for the application) or with the server IP (for Data Guard broker, backups, monitoring, administration).

The behaviour is described in How The Listener Binds On TCP Protocol Addresses (Doc ID 421305.1)

Examples

I have two network interfaces on my system, the loopback (lo) and Ethernet (enp0s3). This interface has the IP 192.168.78.104 and I have added a virtual IP 192.168.66.102 with:

 ip a add 192.168.66.102/24 dev enp0s3

Here is the list of interfaces:

[oracle@VM104 tmp]$ ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: enp0s3:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:cc:00:4e:68 brd ff:ff:ff:ff:ff:ff
    inet 192.168.78.104/24 brd 192.168.78.255 scope global enp0s3
    inet 192.168.66.102/24 scope global enp0s3
    inet6 fe80::a00:ccff:fe00:4e68/64 scope link
       valid_lft forever preferred_lft forever

Here is the content of my /etc/hosts where I have two names that resolve to my server IP address 192.168.78.104

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.78.104 VM104 myhost

One of these names is my server hostname:


[oracle@VM104 tmp]$ hostname
VM104

I’ll try different configuration of my listener.ora

(HOST=127.0.0.1)

I mentioned the IP address of the loopback interface


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=6666)))

The listener listens to this address only:


[oracle@VM104 tmp]$ ss -elpunt | grep -E "^Net|tnslsnr"
Netid  State      Recv-Q Send-Q     Local Address:Port       Peer Address:Port
tcp    LISTEN     0      128            127.0.0.1:6666                  *:*      users:(("tnslsnr",4685,8))

With this configuration, I’m able to connect only through the mentioned address, 127.0.0.1


Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.104)(PORT=6666)))
TNS-12541: TNS:no listener
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.66.102)(PORT=6666)))
TNS-12541: TNS:no listener
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=6666)))
OK (10 msec)

(HOST=localhost)

I mentioned the loopback interface by a host name


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=6666)))

This is actually the same as above: the host mentioned has been resolved at listener startup.

(HOST=1192.168.78.104)

I mentioned the IP address of the host interface


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.104)(PORT=6666)))

The listener listens to this address only:


[oracle@VM104 tmp]$ ss -elpunt | grep -E "^Net|tnslsnr"
Netid  State      Recv-Q Send-Q     Local Address:Port       Peer Address:Port
tcp    LISTEN     0      128       192.168.78.104:6666                  *:*      users:(("tnslsnr",4735,8))

With this configuration, I’m able to connect only through the mentioned address, not the virtual IP, not other interfaces:


Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.104)(PORT=6666)))
OK (0 msec)
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.66.102)(PORT=6666)))
TNS-12541: TNS:no listener
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=6666)))
TNS-12541: TNS:no listener

(HOST=localhost)

I mentioned the loopback interface by a host name


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=6666)))

This is actually the same as above: the host mentioned has been resolved at listener startup.

(HOST=VM104)

I mentioned the host name which resolves to the IP address of the host interface – this is the default when creating with DBCA, and the recommended configuration.


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM104)(PORT=6666)))

The listener socket do not mention the IP address:


[oracle@VM104 tmp]$ ss -elpunt | grep -E "^Net|tnslsnr"
Netid  State      Recv-Q Send-Q     Local Address:Port       Peer Address:Port
tcp    LISTEN     0      128                   :::6666                 :::*      users:(("tnslsnr",4760,8))

We see something different here as there’s no mention of a local address in :::6666

With this configuration, I’m able to connect through any IP address, including the virtual IP


Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.104)(PORT=6666)))
OK (0 msec)
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.66.102)(PORT=6666)))
OK (10 msec)
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=6666)))
OK (10 msec)

(HOST=myhost)

I mentioned another host name which resolves to the IP address of the host interface (see the /etc/hosts above). It is not the hostname returned by $(hostname) but it resolve to same IP.


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM104)(PORT=6666)))

The listener has resolved the address through /etc/hosts and then, because the IP matches the resolution of $(hostname), has used the $(hostname). We are then in the same situation as above where we can connect through any interface:


[oracle@VM104 tmp]$ ss -elpunt | grep -E "^Net|tnslsnr"
Netid  State      Recv-Q Send-Q     Local Address:Port       Peer Address:Port
tcp    LISTEN     0      128                   :::6666                 :::*      users:(("tnslsnr",4760,8))

(HOST=0.0.0.0)

Finally, when you want to listen on all interfaces, why not configure the host to 0.0.0.0


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM104)(PORT=6666)))

We are again in the same situation here and the listener has replaced it with the hostname. This may be convenient when you want to use the same listener.ora for different hosts. However, as it finally show the hostname, better to avoid confusion and have it in the listener.ora

(HOST=VM104)(IP=FIRST)

This is the way to bypass the ‘listen on all interfaces’ rule, even when you resolve to the hostname.


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.104)(PORT=6666)))

Because of (IP=FIRST) the listener listens to the first IP address returned by gethostbyname()

Conclusion

It is easy to know if the listener listens on one specific IP address, or on all interfaces. You get the hostname and the listener endpoints


hostname
lsnrctl status

If the ‘HOST=’ matches the hostname, then it listens to all interfaces. If the ‘HOST=’ mentions an IP address, then it listens on this IP only. If it mentions a name which is not the hostname, then maybe someone has changed the hostname after the listener was started?

The other way is to look at the socket information with:


netstat -elpunt
ss -elpunt

If you think that it is a security problem to listen to all interfaces, then you should understand that the listener is not a firewall. It is just a convenient way to route connections by service name to the right instance. But remember that you can even connect to the database without the listener (read https://amitzil.wordpress.com/2015/10/19/bypassing-the-listener/), just connecting to the dispatcher:


Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SID=CDB1))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=30229)))
OK (0 msec)

And this one listens to all interfaces:


[oracle@VM104 tmp]$ ss -elpunt | grep -E "(^Net|ora_d)"
Netid  State      Recv-Q Send-Q     Local Address:Port       Peer Address:Port
tcp    LISTEN     0      128                   :::30229                :::*      users:(("ora_d000_cdb1",1362,8)) uid:54321 ino:20635 sk:ffff880117900800 
tcp    LISTEN     0      128                   :::32316                :::*      users:(("ora_d000_cdb2",1729,8)) uid:54321 ino:20958 sk:ffff880117900040 

Security is done by firewall rules. Listener is there only to help, so keep it simple.