Troubleshooting database connection issues

Sometimes it can be difficult to get all settings correct when connecting to an Oracle database from the Kubernetes cluster. A way to troubleshoot connection issues is to connect from the db-upgrade pod where you will find the necessary tools to do so.

When connected to the pod, you can:

Verifyi the network connection to the database.
TNSPING is missing on the container requires full Oracle Client.
Verify database environment variables.
Verify the database connection to the database using SQL*Plus.
Verify that Oracle ADB mTLS wallet is mapped correctly to the pod.

Connect to the DB Upgrade pod:

# List all Pods, find OData or kubectl get pods -n workzone

# remote connect to powershell on relevant POD.

Kubectl -it exec pod/<pod name> -n workzone -powershell

# Connect using SQL Plus to test connection.

sqlplus admin/<password>@’<TNS>’

Verifying the network connection to the database

Test-NetConnection -Port <port> <hostname>

Example using mTLS on Oracle ADB:

`Test-NetConnection -Port 1522 adb.eu-frankfurt-1.oraclecloud.com`

ComputerName : adb.eu-frankfurt-1.oraclecloud.com

RemoteAddress : 138.1.3.227

RemotePort : 1522

InterfaceAlias : vEthernet (7759b3f7-eth0)

SourceAddress : 10.224.0.46

TcpTestSucceeded : True

`curl ifconfig.me -UseBasicParsing`

StatusCode : 200

StatusDescription : OK

Content : 20.166.210.91

RawContent : HTTP/1.1 200 OK

access-control-allow-origin: *

Content-Type: text/plain

Date: Thu, 14 Dec 2023 13:52:53 GMT

Server: fasthttp

Via: 1.1 google

20.166.210.91

Forms

Headers : {[access-control-allow-origin, *], [Content-Length, 13], [Content-Type, text/plain], [Date, Thu, 14 Dec 2023 13:52:53 GMT]...}

Images : {}

InputFields : {}

Links : {}

ParsedHtml :

Verifying database environment variables

You can check if the database settings are applied as expected. For example, write

$env:WORKZONE_DB_ARCHIVE

which will output the value of the environment variable “WORKZONE_DB_ARCHIVE”.

Verifying the connection to the Database using SQL*Plus

Once connected to the pod, you can check that there is a normal SQL*Plus access to the database from the pod. Execute:

SQL*Plus “sqlplus <username>/<password>@<TNS Descriptor>

Example:

sqlplus admin/<password>@'(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=<servicename>.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'

SQL*Plus: Release 21.0.0.0.0 - Production on To Dec 14 14:01:39 2023

Version 21.8.0.0.0

Tidspunkt for sidste logon: Sø Dec 10 2023 13:37:21 +00:00

Forbindelse er oprettet til :

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.1.0

SQL>

You can now do different “select * from dual” or other steps to verify the connection.

Verifying Oracle ADB mTLS wallet is mapped correctly to the pod

Note: This verification is only relevant for connection to the Oracle ADB running mTLS.

The secret created that was earlier named “wallet” is mapped to the db-upgrade pod. The way that it works is that the mTLS wallet for the Oracle ADB is mapped to the secret named “wallet”. By default, this secret should be mapped to the db-upgrade pod in the folder C:\wallet. Verify that the wallet exists and has content similar to the content below.

dir C:\wallet

Directory: C:\wallet

-a---l 12/14/2023 1:46 PM 0 cwallet.sso

-a---l 12/14/2023 1:46 PM 0 ewallet.p12

-a---l 12/14/2023 1:46 PM 0 keystore.jks

-a---l 12/14/2023 1:46 PM 0 ojdbc.properties

-a---l 12/14/2023 1:46 PM 0 README

-a---l 12/14/2023 1:46 PM 0 sqlnet.ora

-a---l 12/14/2023 1:46 PM 0 tnsnames.ora

-a---l 12/14/2023 1:46 PM 0 truststore.jks

Check the content of sqlnet.ora and tnsnames.ora. They must match the wallet files that you imported to the secret.

cat c:\wallet\tnsnames.ora

….

cat c:\wallet\sqlnet.ora

…