Connect to Multiple Oracle Autonomous Data Warehouse

[Esraa90]

Hello,

I want to connect to 2 ADW. I can make connection to the first ADW successfully. but when I try to connect to the second I get fatal ssl error

[cjbj]

Use 19c client and put the wallet location in the tnsnames.ora connect descriptor, or in the Easy Connect syntax, see https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases

[Esraa90]

Use 19c client and put the wallet location in the tnsnames.ora connect descriptor, or in the Easy Connect syntax, see https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases

I use instant client 19_5 and I extracted the wallet of the second ADW in location inside /network/admin/wallet
I modify the tnsnames.ora ( of the first ADW) in /network/admin to include the connection string of the second ADW
and I added the WALLET_LOCATION of the second ADW in sqlnet.ora of the first ADW.

I do not if I need to add anything else ?

[Esraa90]

Use 19c client and put the wallet location in the tnsnames.ora connect descriptor, or in the Easy Connect syntax, see https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases

I use instant client 19_5 and I extracted the wallet of the second ADW in location inside /network/admin/wallet
I modify the tnsnames.ora ( of the first ADW) in /network/admin to include the connection string of the second ADW
and I added the WALLET_LOCATION of the second ADW in sqlnet.ora of the first ADW.

I do not if I need to add anything else ?

I also tried using connect_string and get the same error

[cjbj]

Try the latest client you can; I forget when support for this landed.

You can't add two wallet locations to one sqlnet.ora, which is why I mentioned putting the info in the tnsnames.ora connect descriptors.

It would be helpful if you could revisit the issue logging template and tell us basic information - such as the actual error you are getting.

[Esraa90]

I get ORA-28860: Fatal SSL error when I try to connect to the second ADW although the connection to the first ADW is successful.
without adding to sqlnet.ora I get ORA-12154: TNS:could not resolve the connect identifier specified. and I add it based on oracle document to connect to already existing client.
can you tell me what additional information is required?

[cjbj]

I was wrong. Support for this has not been backported to 19c. Separate processes will be needed.

[Esraa90]

what do you mean by separate processes?

[cjbj]

python app1.py; python app2.py

[Esraa90]

I use a separate zeppelin notebook and still get the same error

[anthony-tuininga]

The following example works with Oracle Client 12.2 and higher.

import cx_Oracle
import os
import tempfile
import zipfile

print("Client version used:", cx_Oracle.clientversion())

USER_NAME_1 = "admin"
PASSWORD_1 = "password_1"
DSN_1 = "db202005051413_tp"
CLOUD_CONFIG_1 = "Wallet_DB202005051413.zip"

USER_NAME_2 = "admin"
PASSWORD_2 = "password_2"
DSN_2 = "db202005061630_low"
CLOUD_CONFIG_2 = "Wallet_DB202005061630.zip"

class Connection(cx_Oracle.Connection):

    def __init__(self, *args, cloud_config=None, **kwargs):
        self.temp_dir = None
        self.tns_entries = {}
        if cloud_config is not None:
            self._setup_cloud_config(cloud_config)
            if len(args) > 2:
                dsn = args[2]
            else:
                dsn = kwargs.get("dsn")
            if dsn in self.tns_entries:
                dsn = self.tns_entries[dsn]
                if len(args) > 2:
                    args = args[:2] + (dsn,) + args[3:]
                else:
                    kwargs = kwargs.copy()
                    kwargs["dsn"] = dsn
        super(Connection, self).__init__(*args, **kwargs)

    def __del__(self):
        if self.temp_dir is not None:
            self.temp_dir.cleanup()

    def _setup_cloud_config(self, cloud_config):

        # extract files in wallet zip to a temporary directory
        self.temp_dir = tempfile.TemporaryDirectory()
        zipfile.ZipFile(cloud_config).extractall(self.temp_dir.name)

        # parse tnsnames.ora to get list of entries and modify them to include
        # the wallet location
        fname = os.path.join(self.temp_dir.name, "tnsnames.ora")
        for line in open(fname):
            pos = line.find(" = ")
            if pos < 0:
                continue
            name = line[:pos]
            entry = line[pos + 3:].strip()
            key_phrase = "(security="
            pos = entry.find(key_phrase) + len(key_phrase)
            wallet_entry = "(MY_WALLET_DIRECTORY=%s)" % self.temp_dir.name
            entry = entry[:pos] + wallet_entry + entry[pos:]
            self.tns_entries[name] = entry


def test_connection(user_name, password, dsn, cloud_config):
    conn = Connection(user_name, password, dsn, cloud_config=cloud_config)
    print("Version:", conn.version)
    cursor = conn.cursor()
    cursor.execute("select sys_context('userenv', 'service_name') from dual")
    result, = cursor.fetchone()
    print(result)


if __name__ == "__main__":
    test_connection(USER_NAME_1, PASSWORD_1, DSN_1, CLOUD_CONFIG_1)
    test_connection(USER_NAME_2, PASSWORD_2, DSN_2, CLOUD_CONFIG_2)

[anthony-tuininga]

@Esraa90, please try the above code and let me know if it works for you as it does for me. Thanks!

[Esraa90]

@anthony-tuininga I try it but he did not recognize where the wallet is located and I get the below error

No such file or directory: 'Wallet_ADWPROD.zip'

[anthony-tuininga]

Use a fully qualified path if you're trying to run it from some application (like Zeppelin) as the path may be different from what you expect. Or just run it from a Python script first to verify that it works as expected before trying in an application.

[Esraa90]

@anthony-tuininga I am using Zeppelin. so can you tell me what do you mean by fully qualified path