Sometimes new things that are really useful show up in new versions and you can miss it if you’r not paying close attention. EZ Connect Plus is one such feature for me. You may think it is not new and that it has been there for a long time. You’d be correct EZ Connect is not new, but the Plus moniker is.

In version 19 that was added. I never noticed and then I was looking for how to connect multiple users to the same database with a wallet and I stumbled on a blog by Franck Pachot that talked about one way to do it. While reading it I went “Wait… What… ???” as it indicated “Oracle 19c extends the EZCONNECT syntax”. Truth be told I had looked over that section a few times before it dawned on me “19 c extends”. Has anything else changed? So I put the breaks on what I was doing and shifted over to reading up on this.

Of course my first search on it turned up another blog by Franck. It is four year sold as as far as I can tell as valid today as the day it was written. So consider it additional reading.

The problem with EZ Connect has been that it does not cover more complex scenarios. The syntax supported by standard EZ Connect looks like this.

username@[//]host[:port][/[service_name][:server_type][/instance_name]]

As you can see this allows just one host to be named, filling it out to the max would look like this.

sql the_user@//host1:1521/pdb_svc_1:dedicated/db1

Pretty good but it is of little help to specify you primary and secondary database in a data guard setup so the same connect string finds the primary database no matter which host it is currently located on. The EZ Connect Plus syntax is updated to this.

Note, this is new fro 19c.

[[protocol:]//]host1{,host12}[:port1]{,host2:port2}[/[service_name][:server][/instance_name]][?parameter_name=value{&parameter_name=value}]

You can now specify protocol, same as you can in a normal connect descriptor. In addition you can add several hosts separated by comma. You can also supply port for each of them or if it is the same on all of the just put it after the last host. There is also an option to add parameters, those go to the DESCRIPTION level in the connect descriptor.

Extending the example used before could look like this.

sql the_user@//host1,host2:1521/pdb_svc_1:dedicated/db1

Now the service/pdb pdb_svc_1 is requested on host1 and host2. If found it is what we connect to. Typical data guard setup is to start the service only one the database that is primary. Thus we connect to the primary database on the host where it currently is primary.

Giving tnsping an EZ Connect URL it will respond with the connect descriptor it translates it into. It can often be a good way to resolve issues when your EZ connect does not work as you expected it to.

the_user@//host1,host2:1521/pdb_svc_1:dedicated/db1
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb_svc_1)
                           (INSTANCE_NAME=db1)
                           (SERVER=dedicated)
             )
 (ADDRESS=(PROTOCOL=tcp)
          (HOST=host1)
          (PORT=1521)
 )
 (ADDRESS=(PROTOCOL=tcp)
          (HOST=host2)
          (PORT=1521)
 )
)

As one last hint I’d like to add one feature this leads to that I found a long time ago while still not realizing all these other features. It was a solution where they could not set TNS_ADMIN to specify where the tnsnames.ora was located. But using a TNS-alias was and adding a parameter to it works.

the_user@tnsalias?tns_admin=/path/to/tns/dir

Being able to specify where the tns-file is located is pretty convenient in many cases and in some it is the only option.