OCI connections to multiple Autonomous Transaction Databases in the Oracle Always Free cloud

OCI connections to multiple Autonomous Transaction Databases in the Oracle Always Free cloud

I still use PL/SQL developer to work in our Oracle Databases. It is a tool I’ve been using for years and old habits seldom die.. In fact Pl/SQL developer is the only sound reason I have parallels desktop (nowadays: UTM) installed on my Mac.

🙂

PL/SQL developer connects to the database using an Oracle Client. This can be a full blown client or it can be the InstantClient. I use the latter.

You must have heard about Oracles “Always Free Autonomous cloud”. I love it.

When you create a database and must connect to it, be it SQL Developer or an OCI using program (like PLSQL Developer). You first must dowload a wallet. We should make a small change to our sqlnet.ora file and copy the entries into our tnsnames.ora. This works like a charm, until you must connect to multiple databases, in multiple domains.

I solved it as follows:

  1. My client resides in C:\Oracle\

  2. My windows environment says: TNS_ADMIN = C:\Oracle\

  3. I created an extra folder structure:

    1. C:\Oracle\wallets\devdomain

    2. C:\Oracle\wallets\tstdomain

  4. The wallet.zip for the devdomain I placed in the “devdomain” directory and of course the tstdomain.zip I placed in “tstdomain” and unzipped both in their folders

  5. I didn’t touch the sqlnet.ora for me it looks like
    SSL_SERVER_DN_MATCH=yes

  6. I refer to the wallet location in the tnsnames.ora file:
    oraconfxdev_high.richardmartens.oci =
    (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)
    (port=1522)(host=adb.eu-amsterdam-1.oraclecloud.com))
    (connect_data=
    (service_name=something_goes_here_high.adb.oraclecloud.com))
    (security=
    (MY_WALLET_DIRECTORY = "C:\the\dir\containing\unzipped\wallet")
    (ssl_server_dn_match=yes)))

So I added the line that says where this entry should take the wallet from.

Now PLSQL Developer is able to connect to all my instances, however before switching from one DB to another I first nicely must log-off. Logging on into a database while still connected to another gives me a ORA-12529: TNS:connect request rejected based on current filtering rules.

Logging of before establishing a new connection is something I can live with.

Regards,
Richard

Did you find this article valuable?

Support Oracle APEX, ORDS, PL/SQL and OCI by becoming a sponsor. Any amount is appreciated!