SOURCE_ROUTE in easy connect plus syntax

I regularly use Oracle connection manager (CMAN) to simplify connectivity to the database in complex IT environments. Normally the network infrastructure between the CMAN host and the target service is transparent for the client. 

but in some cases, the client can (or need) to define not only the connection to the CMAN, but also the 2nd leg to the DB service. This is called SOURCE_ROUTE in Oracles network naming. 

A common (quite simple) connection description is this tnsnames.ora entry: 

ALIAS=
 (DESCRIPTION= 
   (SOURCE_ROUTE=yes) 
   (ADDRESS=(PROTOCOL=tcp)(HOST=CMAN_HOST)(PORT=1521))  
   (ADDRESS=(PROTOCOL=tcp)(HOST=DB_INSTANCE_HOST)(PORT=1521))  
   (CONNECT_DATA=(SERVICE_NAME=DB_Service))
 )

By the definition SOURCE_ROUTE=yes the first entry defines the direct connection to the CMAN_HOST. In this connection the 2nd connection is described implicit to go to DB_INSTANCE_HOST. 

 

But sometimes, a tnsnames.ora entry isn't useful and a Easy Connect syntax is preferred. The simplest syntax does not show any way for a multi step connection. 

host:port/SERVICE_NAME

But this very simple syntax

the full possible syntax is

[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name][:server_type][/instance_name]][?parameter_name1=value1{&parameter_name2= value2}]

In a more specific case it looks like

//CMAN_HOST,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

And it works quite fine. I can do a simple test with sqlplus:

sqlplus x/x@CMAN_HOST,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Jun 26 10:54:02 2023
Version 21.3.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied

And ORA-01017 is exactly what I expect: I could reach the instance, but of course authentication for user x with password x failed.

Another feature worth to mention is tnsping. This only tries to connect to the given listener but never checks for the service available. In addition it also prints the connection it tested in tnsnames.ora format. A simple connect string looks like: 

tnsping CMAN_HOST:1521/DB_Service

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 26-JUN-2023 10:53:44

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

Used parameter files:
/tns/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB_Service))(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521)))
OK (10 msec)

But even with a source route defined, tnsping is quite honest what's (not) doing: 

tnsping CMAN_HOST:1521,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 26-JUN-2023 10:53:51

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

Used parameter files:
/tns/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB_Service))(SOURCE_ROUTE=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521)))
OK (10 msec)

It adds the SOURCE_ROUTE=YES entries, but does not add the 2nd host (or IP) it doesn't even try to reach the DB_INSTANCE_HOST. 

The usage of tnsping is limited in any cman environment.