Parameter REMOTE_LISTENER pointing to a TNS alias? Beware of how it registers.

On an Oracle Database instance, if I set:

alter system set remote_listener='cluster-scan:1521';

The instance tries to resolve the cluster-scan name to detect if it is a SCAN address.
So, after it solves, it stores all the addresses it gets and registers to them.
I can check which addresses there are with this query:

SQL>  select type, value from v$listener_network where type='REMOTE LISTENER';

TYPE             VALUE
---------------- ---------------------------------------------------------------------------------------------------
REMOTE LISTENER  (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.1)(PORT=1521)))
REMOTE LISTENER  (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.2)(PORT=1521)))
REMOTE LISTENER  (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.3)(PORT=1521)))

In this case, the instance registers to the three addresses discovered, which is OK: all three SCAN listeners will get service updates from the instance.

But if I have this TNS alias:

REMOTE_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=cluster-scan)))

and I set:

alter system set remote_listener='remote_listener';

I get:

SQL>  select type, value from v$listener_network where type='REMOTE LISTENER';

TYPE             VALUE
---------------- ---------------------------------------------------------------------------
REMOTE LISTENER  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=cluster-scan)))

the result is that the instance registers only at the first IP fot from the DNS, leaving the other SCANs without the service registration and thus random

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

This is in my opinion quite annoying, as my goal here was to have all the DBs set with:

local_listener=local_listener
remote_listener=remote_listener

in order to facilitate changes of ports, database migrations from different clusters, clones, etc.

So the solution is either to revert to the syntax “cluster-scan:port”, or specifying explicitly all the endpoints in the address list:

REMOTE_LISTENER = (DESCRIPTION= (ADDRESS_LIST=
  (ADDRESS= (PROTOCOL=TCP) (PORT=1521) (HOST=10.0.0.1))
  (ADDRESS= (PROTOCOL=TCP) (PORT=1521) (HOST=10.0.0.2))
  (ADDRESS= (PROTOCOL=TCP) (PORT=1521) (HOST=10.0.0.3))
 ))

I am sure it is “working as designed”, but I wonder if it could be an enhancement to have the address expended fully also in case of TNS alias….
Or… do you know any way to do it from a TNS alias without having the full IP list?

Cheers

Ludo

The following two tabs change content below.

Ludovico

Oracle ACE Director and Computing Engineer at CERN
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Computing Engineer at CERN, the European Organization for Nuclear Research, in Switzerland.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.