Connecting to Oracle Autonomous Database in OCI – A Step-by-Step Guide

Once you've provisioned your Oracle Autonomous Database (ADB) in Oracle Cloud Infrastructure (OCI), the next crucial step is to connect to it from your applications or development tools. Connecting to an Autonomous Database is slightly different from traditional Oracle databases due to its secure, cloud-native architecture, primarily relying on a client credentials wallet.

This guide will walk you through the essential steps and common methods to establish a connection.

Prerequisites

Before you start, ensure you have:

• An Oracle Autonomous Database instance provisioned and in an "Available" state in your OCI tenancy.

• The ADMIN user password you set during provisioning.

• Outbound network access from your client machine/application to the OCI public IP range (or private endpoint if configured).

• For desktop tools, Java Development Kit (JDK) 8 or later installed.

Step 1: Download the Client Credentials (Wallet)

The wallet contains the necessary connection information (like TNS entries and SSL certificates) to securely connect to your Autonomous Database.

1. Log in to the OCI Console: Go to the OCI Console and log in with your credentials.

2. Navigate to your Autonomous Database:

   • From the Navigation Menu (☰), go to Oracle Database > Autonomous Database.

   • Select the Compartment where your database resides.

   • Click on the Display Name of your Autonomous Database instance to view its details.

3. Download the Wallet:

• On the Autonomous Database Details page, click the "Database Connection" button.

• In the "Database Connection" dialog, select the "Wallet Type":

  • Instance Wallet: For a single database connection.

  • Regional Wallet: For connecting to all Autonomous Databases in a region (useful for multiple ADBs).

• Click the "Download Wallet" button. 

• You will be prompted to enter a password for the wallet. This password is different from your ADMIN database user password. Choose a strong password and remember it, as you'll need it when connecting.

• Save the Wallet_<databasename>.zip file to a secure location on your local machine.

Step 2: Extract the Wallet Contents

Locate the downloaded Wallet_<databasename>.zip file and extract its contents into a dedicated directory. For example, you might create a directory like C:\Oracle\Wallets\MyADBDemo (Windows) or ~/oracle/wallets/myadbdemo (Linux/macOS).

Important files in the wallet:

• tnsnames.ora: Contains the connection strings (TNS entries) for your database.

• sqlnet.ora: Contains network configuration parameters.

• cwallet.sso, ewallet.p12: SSL certificates for secure connection.

Step 3: Choose Your Connection Method

Now that you have the wallet, you can connect using various tools and programming languages.

Method A: Connecting via SQL Developer Web (Easiest - Browser Based)

SQL Developer Web is a browser-based interface for Autonomous Database, requiring no local client installation.

1. Access SQL using Web:

• On your Autonomous Database Details page in the OCI Console, click the "Database Action" button.

• In the Database Action, click "SQL" on the bottom navigation.

• A new browser tab will open

You are now connected and can start running SQL queries directly from your browser!

Method B: Connecting via SQL Developer Desktop

Oracle SQL Developer is a popular free IDE for database development.

1. Download and Install SQL Developer: If you don't have it, download the latest version from the Oracle website. Extract the zip file and run sqldeveloper.exe (Windows) or sqldeveloper.sh (Linux/macOS).

2. Create a New Database Connection:

• In SQL Developer, right-click on "Connections" in the left pane and select "New Connection...".

• Fill in the connection details:

  • Connection Name: Give it a meaningful name (e.g., MyADBDemo_ATP).

  • Username: ADMIN

  • Password: Your ADMIN user password.

  • Connection Type: Select Cloud Wallet.

  • Configuration File: Browse to the directory where you extracted your wallet and select the Wallet_<databasename>.zip file itself (SQL Developer will automatically find the necessary files inside).

  • Service: Choose the desired service from the dropdown. You'll typically see options like _high, _medium, _low (representing different performance levels/concurrency). For most cases, _medium is a good starting point.

• Click "Test" to verify the connection. If successful, the status will be "Success."

• Click "Connect".

You are now connected to your Autonomous Database from SQL Developer Desktop.

Troubleshooting Common Connection Issues

• ORA-28759: failure to open file or TNS-12538: TNS:no such protocol adapter:

  • Ensure TNS_ADMIN environment variable is correctly set to the directory where you extracted the wallet files.

  • Verify the wallet files (tnsnames.ora, sqlnet.ora, etc.) are indeed in that directory.

  • Check file permissions on the wallet directory and files.

• ORA-01017: invalid username/password; logon denied:

  • You are using the wrong ADMIN database user password.

• ORA-28042: Authentication for the user is not supported:

  • You might be using an older client driver or an unsupported connection method. Ensure you are using the wallet and a recent client.

• Network Connectivity Issues:

  • Check your client machine's firewall.

  • Verify the network access rules configured for your Autonomous Database (Step 8 during provisioning). If you chose "Secure Access from specified IP addresses," ensure your client's IP is included.

  • If using a private endpoint, ensure your client is within the specified VCN or has routing to it.

• Wallet Password Incorrect:

  • Ensure you are providing the correct password for the wallet file itself, not the ADMIN user password, when prompted by tools that require it (like SQL Developer).

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment