Objective: How to delete/remove Non-CDB or 11g database or database home from ExaCC Prerequisite: Should have Non-CDB or 11g home and database on ... Read More
Connect Autonomous Database in 3 Different Ways
In this blog, I will show you how to connect your Autonomous Database using three different ways:
Pre-Requisite: You should have an ADW or ATP DB provisioned.
1. Connect with SQL Worksheet
We can access the browser-based SQL Worksheet directly from our ADW/ATP console. (I have ADW already provisioned).
Login to OCI console -> Oracle Database -> Autonomous Data Warehouse -> Click on ADW DB.
Click on Database Actions dropdown list and select “View all database actions”.
A sign-in page will open for the database actions. Use the administrator account, Username – admin (you create this when provisioning ADW) and click Sign in.
The database Actions page opens. In the Development box, click SQL.
The SQL Worksheet will open with a series of pop-up informational boxes to provide you with a tour of the main features, so just click next, next on each pop-up.
We can run a query on a sample data set to see the result.
2. Connect SQL Developer to Autonomous Database securely without Wallet, using TLS authentication
When we provisioned an autonomous database instance with a network access type of “Secure access from everywhere”, by default, mTLS authentication was required, and the only ways to enable TLS in addition to mTLS are either to define an access control list (ACL) or to use a private endpoint.
I will configure the IP ACL. Then I will disable the mTLS which in turn will enable the TLS for connecting without a wallet.
To enable TLS, go to the ADW home page, in the network section, and click Edit next to the Access control list.
In the Edit ACL window, select the IP address from the IP notation type drop-down list and click on “Add my IP address” and your computer IP address will be added. Click save.
Now in the network section, we will see ACL is enabled now and the Access type automatically changed from Allow secure access from anywhere to allow secure access from specified IPs and VCNs.
Now disable the mTLS by clicking on the Edit button. and then deselect the checkbox and save it.
Now you will see mTLS changed to Not Required.
Next, we have to obtain the TLS connection string to connect from the SQL Developer.
Go to the ADW home page and click on the “Database Connection” button.
Scroll down to the connection string section and change TLS Authentication from mTLS to TLS. this will enable SQL Developers and other applications to connect to our ADW securely without a wallet.
Choose one of the connection strings, for example, I choose adwtest_high and click copy to copy the connection string. we can save that in Notepad to use in SQL Development.
Now go to SQL developer on your laptop and in the connections panel, click on the “New Connection” button.
Enter any Name, DB username, and password.
Choose connection type: Custom JDBC
Custom JDBC URL: Enter the following:
JDBC:oracle: thin:@ followed by the connection string copied in the above step
Click test and connect.
3. Connect Securely using a connection wallet containing credentials.
For this, we have to download the connection wallet from the autonomous database home Page.
Go to Console –> ADW/ATP home page and click on the Database connection
Choose wallet type as Instance wallet and click on the Download wallet button.
Specify a password, you will need this password when connecting the database via SQL developer. Click Download.
Once Wallet is downloaded, close the database connection dialog.
Now start the SQL developer from your laptop and click on the new connection icon.
Fill in the details:
Name: any name ( I used adw_wallet_test)
Authentication type: Default
Use the admin username and password you used when provisioning the database.
Connection Type: Cloud wallet
Configuration file: browse for the wallet you downloaded in the previous step
Service: there are 3 services whenever we provision an autonomous database, I choose <databasename>_high.
Click test, save, and connect and it’s connected.
Now you can query your database, so these were the 3 different ways to connect Autonomous Database hope you like the blog!
More from this Author
Introduction: In this blog, I will show you how to use Ansible to automate Oracle database patching. I applied the latest Oracle 19c RU Patch ... Read More