Step-by-Step Guide: Connecting TOAD to Oracle Autonomous Database
(ATP/ADW)
This document provides detailed steps to connect TOAD for Oracle to an Oracle Autonomous
Database (ATP/ADW) using an Oracle Wallet.
🔹 Prerequisites
Before proceeding, ensure the following:
✅ TOAD for Oracle is installed.
✅ Oracle Instant Client (Basic & ODBC Package) is installed.
✅ Wallet File (wallet.zip) is downloaded from the Oracle Cloud Console.
🔹 Step 1: Download the Oracle Wallet File
1. Log in to Oracle Cloud Console → Autonomous Database.
2. Select your ATP or ADW database.
3. Click DB Connection.
4. Under Download Client Credentials (Wallet):
o Click Download Wallet.
o Enter a Wallet Password and save the .zip file securely.
🔹 Step 2: Install & Configure Oracle Instant Client
If Not Installed, Follow These Steps:
1. Download the Oracle Instant Client from:
🔗 https://www.oracle.com/database/technologies/instant-client.html
2. Extract it to a location like:
o Windows: C:\oracle\instantclient_21_6
o Linux/Mac: /opt/oracle/instantclient_21_6
3. Set Environment Variables (Windows Example):
o ORACLE_HOME → C:\oracle\instantclient_21_6
o TNS_ADMIN → Path to the extracted Wallet folder.
o PATH → Add C:\oracle\instantclient_21_6.
🔹 Step 3: Configure TNSNAMES.ORA
1. Extract the Wallet.zip file to a secure location:
o Example: C:\oracle\wallet_myadb\
2. Open tnsnames.ora in a text editor (found inside the extracted wallet folder).
3. Copy the TNS entry, e.g.:
makefile
CopyEdit
MYATPDB_HIGH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = adb.us-ashburn-1.oraclecloud.com)
(PORT = 1522))
(CONNECT_DATA = (SERVICE_NAME = myatpdb_high.adb.oraclecloud.com))
(SECURITY = (SSL_SERVER_CERT_DN="CN=adb.oraclecloud.com, O=Oracle
Corporation, L=Redwood City, ST=California, C=US"))
)
4. Save the file in the Wallet directory and update TNS_ADMIN accordingly.
🔹 Step 4: Configure TOAD for Oracle
1. Open TOAD for Oracle.
2. Click Session → New Connection.
3. In the Database Login Window:
o User Name: ADMIN (or other database user).
o Password: Wallet password set earlier.
o Database:
Click the drop-down and select TNS.
Choose the TNS Alias (MYATPDB_HIGH).
4. Click Test Connection.
5. If successful, click Connect.
🔹 Step 5: Verify the Connection
✅ Run the following SQL to verify connectivity:
SELECT sysdate FROM dual;
✅ Ensure you can access tables in ALL_TABLES:
SELECT table_name FROM all_tables WHERE owner = 'ADMIN';
🔹 Troubleshooting
🔴 Error: ORA-28759: Failure to Open File
✔ Ensure TNS_ADMIN points to the Wallet directory.
🔴 Error: No Listener
✔ Verify your database is running in OCI Console.
🔴 Error: Connection Refused
✔ Ensure Port 1522 is not blocked by your firewall.
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
📌 Step-by-Step Guide: Connect SQL Developer to Oracle Autonomous
Database (ATP/ADW)
🔹 Prerequisites
✅ Oracle SQL Developer is installed.
✅ Oracle Wallet (wallet.zip) is downloaded from Oracle Cloud.
✅ Autonomous Database (ATP/ADW) is running in Oracle Cloud.
🔹 Step 1: Download the Oracle Wallet File
1. Log in to the Oracle Cloud Console.
2. Navigate to:
o ☰ Menu → Autonomous Database.
3. Select your database (ATP or ADW).
4. Click DB Connection.
5. Click Download Wallet.
6. Enter a Wallet Password, confirm it, and download the wallet.zip file.
7. Save the wallet.zip file to a secure location.
🔹 Step 2: Extract the Wallet File
1. Locate the downloaded wallet.zip file.
2. Extract it to a directory, e.g.,
o Windows: C:\oracle\wallet_mydb\
o Linux/Mac: /home/user/wallet_mydb/
3. After extraction, the folder should contain files like:
o tnsnames.ora
o sqlnet.ora
o cwallet.sso
o keystore.jks
🔹 Step 3: Configure SQL Developer
1. Open SQL Developer.
2. Click New Connection (🔽 drop-down beside the green "+" icon).
3. Enter Connection Details:
o Connection Name: Any name (e.g., MyATPDB).
o Username: ADMIN (or another DB user).
o Password: Your DB password (set during DB creation).
o Connection Type: Select Cloud Wallet.
o Configuration File: Click Browse and select the wallet.zip file (do not extract it here,
select the ZIP file directly).
4. Click Test to verify the connection.
5. If successful, click Connect.
🔹 Step 4: Verify the Connection
Once connected, test with the following SQL commands:
✅ Check database name:
SELECT sys_context('USERENV', 'CON_NAME') FROM dual;
✅ View available tables:
SELECT table_name FROM user_tables;
🔹 Troubleshooting
🔴 1. "ORA-28759: Failure to Open File"
✔ Ensure TNS_ADMIN is set correctly to the extracted wallet folder.
✔ Check sqlnet.ora inside the wallet directory to confirm correct file paths.
🔴 2. "Network Adapter Could Not Establish Connection"
✔ Make sure your Autonomous Database is running in OCI.
✔ Verify that your firewall is not blocking port 1522.
🔴 3. No TNS Aliases in SQL Developer
✔ Ensure tnsnames.ora is inside the extracted wallet folder.
✔ Restart SQL Developer.
