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

Published by Richard Martens on

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 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 new 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:
dev_high = 
   (description= 
      (address=(protocol=tcps)(port=1522)
               (host=adb.eu-frankfurt-1.oraclecloud.com))
      (connect_data=(service_name=dev_high.atp.oraclecloud.com))
      (security=(MY_WALLET_DIRECTORY = "C:\\Oracle\\wallets\\dev")
      (ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )

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


Richard Martens

Richard Martens has been involved in information technology for more than 15 years. He started as a web developer using the Oracle database as no more than data storage. Richard has been responsible for major European multilingual websites and has been working with the Oracle database since 2000. During those years, he developed himself using a multitude of technologies and specialized in PL/SQL and Oracle APEX. With APEX, he combines the things he loves most: the Oracle database and web technologies.