19c Easy Connect Plus

When TCP/IP was the protocol used mostly everywhere, Oracle introduced EZCONNECT naming method to avoid long connection strings with parentheses everywhere. They said that it was a way to avoid tnsnames.ora but that’s not completely true:

• you can use full connection strings without a tnsnames.ora
• you still need a tnsnames.ora for more complex connection strings

But the idea was to replace:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=APP.service.net))(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=2483)))

by:

localhost:2483/APP.service.net

and even reduce it to a simple hostname when using the default port and default service name.

I use it a lot when connecting manually, but as soon as there’s a need to add multiple hosts for transparent failover or load balancing, or to add some additional parameters, the full connection string is required.

In 19c, Oracle has extended the Easy Connect syntax to allow this in EZCONNECT naming and again telling us that it is a way to avoid a tnsnames.ora:

https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-naming-methods.html#GUID-B0437826-43C1-49EC-A94D-B650B6A4A6EE

I still don’t agree. You probably still prefer to deploy a common tnsnames.ora to be sure that all applications connect with the correct string, or use an LDAP directory. However, they are not wrong... I am. I use EZCONNECT as a syntactic sugar. But for Oracle it a naming method as defined in NAMES.DIRECTORY_PATH.

However, if you have a centralized way to store the JDBC URL, then EZCONNECT will avoid any local configuration in 19c as mentioned in the documentation. Let’s see what is possible.

I’m using tnsping to resolve the EZCONNECT string to a full description one, which I ident for better readability.

This is the most simple, and not new, using the default port 1521:

tnsping //geneva/PDB1
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
  )

And the most complex before 19c is mentioning the server type (dedicated, shared or DRCP) and the instance name

tnsping //geneva:1521/PDB1:pooled/CDB1
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
      (INSTANCE_NAME=CDB1)
      (SERVER=pooled)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
  )

Now in 19c we can mention multiple hosts which will be converted to a Load Balancing address list

tnsping //geneva,lausanne/PDB1
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
    )
    (LOAD_BALANCE=on)
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=lausanne)
      (PORT=1521)
    )
  )

With the same port for:

tnsping //geneva,lausanne:1522/PDB1
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
    )
    (LOAD_BALANCE=on)
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1522)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=lausanne)
      (PORT=1522)
    )
  )

or different port:

tnsping //geneva:1521,lausanne:1522/PDB1
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
    )
    (LOAD_BALANCE=on)
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=lausanne)
      (PORT=1522)
    )
  )

and even add parameters in URL style (starting with ? and separated by &):

tnsping //geneva:1521,lausanne:1522/PDB1?transport_connect_timeout=5&retry_count=2
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
    )
    (transport_connect_timeout=5)
    (retry_count=2)
    (LOAD_BALANCE=on)
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=lausanne)
      (PORT=1522)
    )
  )

By the way, lowering the default TCP timeout is a good idea most of the times because your network probably answers in less than 60 seconds when available, and you don’t want to wait one minute before trying another address. More about those settings:

TRANSPORT_CONNECT_TIMEOUT and RETRY_COUNT - Blog dbi services

Note that there are no verifications in the EZCONNECT string. You can mention non-existing parameters:

tnsping //geneva:1521,lausanne:1522/PDB1?make_application_fast=on
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
    )
    (make_application_fast=on)
    (LOAD_BALANCE=on)
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=lausanne)
      (PORT=1522)
    )
  )

and even try to disable the automatically added Load Balancing which probably results in something you don’t want (both on and off):

tnsping //geneva:1521,lausanne:1522/PDB1?LOAD_BALANCE=off
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=PDB1)
    )
    (LOAD_BALANCE=off)
    (LOAD_BALANCE=on)
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=lausanne)
      (PORT=1522)
    )
  )

(I opened a SR for this one, will update this post about this)

There’s something I like to do when quickly connecting to an unmounted instance (undocumented and even more unrecommended in this way):

tnsping //geneva/CDB1)(UR=A
(DESCRIPTION=
    (CONNECT_DATA=
      (SERVICE_NAME=CDB1)
      (UR=A)
    )
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=geneva)
      (PORT=1521)
    )
  )

So it is still possible here. Do not put the UR=A as a parameter (after ?) because those go with the address list rather than CONNECT_DATA.

Note that SQLCL, the SQL Developer command line accepts all that (tested in 18.4) except when mentioning an instance name in addition to a parameter:

But this combination is probably not very useful in most of the cases.

In summary, if you have a centralized way to store the connection strings, then EZCONNECT is a good idea. Because the developers do not like the SQL*Net descriptions with all those parentheses (they are born with XML or JSON or even YAML for the younger ones) and this URL-like syntax will be better accepted by everyone.