Connect Nexus with Oracle Autonomous Database

Introduction

Nexus supports connection to oracle database with the help of Oracle Extension. Oracle extension is developed and maintained by Nexus Development Team. See extension home page to know more about the oracle extension.

Oracle Autonomous database can only be connected with wallet files from any other application.

Prerequisite

1. Download wallet from Oracle Service Console / OCI - CLI

2. Access to Nexus Infrastructure (Kubernetes)

3. Nexus should be deployed using Nexus Helm chart as described here

Refer this article on how to download the wallet from oracle service console

Contact Nexus Team to setup connection Oracle ADB in case nexus installed without Official Helm chart

Configuration

Create secret to store wallet

Extract the cwallet.sso and ewallet.p12 files from wallet zip file into separate folder

unzip /usr/home/dev-wallet.zip -d /usr/home/wallet

Now create secret to store the wallet files with specific namespace(if nexus not deployed on default namespace).

kubectl create secret generic dev-wallet \
    --from-file cwallet.sso=/usr/home/wallet/cwallet.sso \
    --from-file ewallet.p12=/usr/home/wallet/ewallet.p12 \
    --namespace nx-dev

Create wallet configuration for extension manager

Copy and paste /merge the below code in existing values.yaml file

values.yaml

...
extmgr:
  version: 0.7.9-alpha.15
  nxOracle:
    walletLocation: /usr/lib/oracle/19.8/client64/lib/network/admin
    wallet:
      - name: dev
        secret: dev-wallet

Based on the above setup, wallet files are stored in extension manager pod at below path

# pwd
/usr/lib/oracle/19.8/client64/lib/network/admin
# ls -la
total 16
drwxr-xr-x 1 root root 4096 Oct 11 10:16 .
drwxr-xr-x 1 root root 4096 Oct 11 09:11 ..
-rw-r--r-- 1 root root  502 Jul  3 04:46 README
drwxrwxrwt 3 root root  120 Oct 11 10:16 dev
# cd dev
# ls -l
total 0
lrwxrwxrwx 1 root root 18 Oct 11 10:16 cwallet.sso -> ..data/cwallet.sso
lrwxrwxrwx 1 root root 18 Oct 11 10:16 ewallet.p12 -> ..data/ewallet.p12

Deploy the extension manager

Upgrade Helm instance by running below command

helm upgrade dev nexus-charts/nexus --namespace="nx-dev" \
    --set databaseURL="<Database URL>" \
    --values .\values.yaml
    
kubectl get pod --namespace nx-dev
NAME                                            READY   STATUS    RESTARTS   AGE
nexus-api-dev-0                                 1/1     Running   0          18d
nexus-extension-mgr-dev-0                       1/1     Running   0          12s
...

Configure Oracle Connector

Follow connector configuration document to create a oracle connector with Tables/Procedure/Custom SQL Service & Operations.

1. Navigate to Environments page

2. Select the specific environment which you want to connect Oracle ADB

3. Click + Icon to add connector (If not already added)

Create connection string based on tnsnames.ora file inside the wallet zip. tnsname.ora might look like below

devdwh1stage_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=xxx-yyyy-1.oraclecloud.com))(connect_data=(service_name=abcdef_abcdef_dev_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=xxxx.yyyy-east-1.oraclecloud.com,OU=Oracle ABCD US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

devdwh1stage_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=xxx-yyyy-1.oraclecloud.com))(connect_data=(service_name=abcdef_abcdef_dev_low.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=xxxx.yyyy-east-1.oraclecloud.com,OU=Oracle ABCD US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

devdwh1stage_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=xxx-yyyy-1.oraclecloud.com))(connect_data=(service_name=abcdef_abcdef_dev_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=xxxx.yyyy-east-1.oraclecloud.com,OU=Oracle ABCD US,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))

Use any one of the above like configuration as Connection String. Add MY_WALLET_DIRECTORY value to refer the wallet folder location for our connection. Let's take a example of devdwh1stage_high

(
  description= 
    (retry_count=20)
    (retry_delay=3)
    (address=(protocol=tcps)(port=1522)(host=xxx-yyyy-1.oraclecloud.com))
    (connect_data=(service_name=abcdef_abcdef_dev_high.adwc.oraclecloud.com))
    (security=
        (MY_WALLET_DIRECTORY = "/usr/lib/oracle/19.8/client64/lib/network/admin/dev")
        (ssl_server_cert_dn="CN=xxxx.yyyy-east-1.oraclecloud.com,OU=Oracle ABCD US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
    )
)

Copy & Paste the above as connection string and add username, password.

Now Nexus is ready to integrate with Oracle ADB.