How to create connection to DBCS/ATP DB in SQLDeveloper on your Local Machine
In this article, I will walk you through the process of creating a connection to ATP Database and DBCS (Database Cloud Service) of OCI in SQL Developer on your local machine.
ATP & DBCS are two variants of Database services offered by Oracle.
As discussed in my previous article, Database is mostly provisioned in a Private Subnet so that it won’t have a Public IP to access from outside world, hence providing security.
We will be discussing around creating connection for Database in Private Subnet in this article as creating connection to Database in Public subnet is straight forward and also follows almost the same approach.
How to create a connection to ATP Database in SQL Developer
- VCN created with Public and Private subnets, where your ATP DB is provisioned on Private Subnet and Bastion on Public Subnet.
- Rules are configured in Routing Table & Security List to connect to ATP DB from Bastion Server.
Steps to configure ATP Connection:
Creating connection to ATP DB is straight forward be it the DB in Public Subnet or Private Subnet.
- Download wallet for the provisioned ATP DB from OCI console. By clicking on DB Connection button on your ATP DB.
2. Click on Download Wallet button to download the wallet by providing password for your wallet. The wallet would be a .zip file.
3. Open SQLDeveloper in your local, click on create new connection. Key in the Username & Password of your ATP DB. In connection type choose “Cloud Wallet”
4. Browse the downloaded wallet .zip file and go with default service i.e., <DBName>_high for now
5. Save & Test your connection. You can open the DB connection created and you are connected to ATP DB on OCI through SQL Developer on local.
How to create a connection to DBCS in SQL Developer
- VCN created with Public and Private subnets, where your DBCS is provisioned on Private Subnet and Bastion on Public Subnet.
- Rules are configured in Routing Table & Security List to connect to DBCS from Bastion Server.
Steps to configure DBCS Connection:
To create connection to DBCS from local, we need to establish a SSH Tunnel as DBCS will be installed on VM, so we need to create a tunnel between your local and the VM on which DBCS is provisioned.
If DBCS is provisioned on Private Subnet, then we need to establish tunnel through Bastion Server from your local.
- Open SQL Developer, right click on SSH Hosts to create a “New SSH Host”.
2. Give a name to your SSH Host, followed by Public IP of your Bastion/Jump server through which you will be connecting to DBCS in Private Subnet, leave the port to 22 which is the SSH port, username of Bastion server (default username for Oracle VM is “opc”) and provide the private key you would have generated while creating Bastion/Jump server.
3. Also, select “Add a Local Port Forward” to forward the connection from Bastion to your DB. In Host, provide Private IP of your DBCS and port as 1521.
You can fetch Private IP of your DBCS from OCI console, by clicking on the DBCS Display Name → Nodes.
Leave rest of the configuration as it is and click Ok.
NOTE: I have provisioned Bastion as a Linux machine.
4. Now, right click on the SSH Tunnel created and click on Test to see a successful Tunnel establishment.
5. Now, click on create new connection. Give a name to your DBCS Connection. Provide DBCS Username and Password which you have used while provisioning DBCS along with Role as “SYSDBA”. Select the Connection Type as “SSH” as we have already established an SSH Tunnel to connect to DBCS.
6. From the Port Forward drop down, choose the Local Port Forward which you have created in Step 3 and provide Service Name of your DBCS.
Service Name for DBCS can be fetched from OCI console, by clicking on the DBCS Display Name → Databases → Database Name → DB Connection
7. Save and Test your connection, this establishes connection to your DBCS in Private Subnet.
Incase you want to connect to DBCS on Public Subnet, then in Step 3 you’ll be providing the Public IP of DBCS rather than Public IP of Bastion. Rest of the steps remain same for DBCS in both Public & Private subnets.