On an Oracle Database instance, if I set:
|
1 |
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:
|
1 2 3 4 5 6 7 |
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:
|
1 |
REMOTE_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=cluster-scan))) |
and I set:
|
1 |
alter system set remote_listener='remote_listener'; |
I get:
|
1 2 3 4 5 |
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
|
1 |
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:
|
1 2 |
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:
|
1 2 3 4 5 |
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
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Is this still an issue on 18c/19c?
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
Good question! I haven’t tried yet on 19c clusters. Something to test soon.
Yes, it still is an issue on 19c :-/
Wish I found this post, _before_ I encountered the same pitfall 😉
Thanks for the update!
I got the similar issue in oracle 12.2.0.1.0
the service will only register to 2 out of 3 LISTENER_SCAN if i use scan name to set remote listener.
Seems it is oracle bug.