Skip to content

Connect to Oracle Database System in Private Subnet Using OCI Connection

Anas Darkal Aug 7, 2023 10:00:00 AM
Connect to Oracle Database System in Private Subnet Using OCI Connection – Eclipsys
4:42

The Database Tools service allows you to create connections to Oracle database systems running in Oracle OCI. Connections work with Autonomous Database (ADB), Oracle Base Database (VM, BM, and Exadata DB Systems), and customer-managed Oracle databases running on OCI compute instances.

When an Oracle DB System (VM or BM) is configured to restrict network access using a private subnet, then a Database Tools private endpoint should be set up in a subnet such that network traffic can be routed from the Database Tools service to the target database.

subnet

 

Prerequisites:

  • An Oracle cloud free trial or paid account.
  • OCI Virtual Cloud Network (VCN) with a private subnet.
  • Oracle DB system (VM) located in a VCN’s private subnet.

 

Step #1: Create OCI Vault

Vaults let you centrally manage the encryption keys that protect your data and the secret credentials that you use to securely access resources

1. Open the navigation menu, click “Identity & Security”, and then click “Vault

subnet2

 

2. Click “Create Vault”.

subnet3

 

3. In the “Create Vault“ dialog box, select the compartment where you want to put your vault and enter your vault name, then click “Create Vault

subnet4

subnet5

 

4. Create Master Encryption Key: Click your vault name to view vault details. Under the “Resources” section, click “Master Encryption Keys”, then click “Create Key

subnet6

 

5. In the “Create Key” dialog window, select the compartment where you want to put your and enter the key name. Leave all other options using default values, then click “Create Key

subnet7

subnet8

 

6. Create a Vault’s Secret: The secret will be used to store the database’s user password. Click your vault name to view vault details. Under the “Resources” section, click “Secrets”, then click “Create Secret

subnet9

 

7. In the “Create Secret” dialog window, select the compartment where you want to put your, enter your secret name, and select the master key created in the previous step, for “Secret Contents” enter the database’s user password, then click “Create Secret

subnet10

 

Step #2: Create a Private Endpoint

Private endpoints allow Database Tools to access databases securely via private networks

1. Open the navigation menu, click “Developer Services”, and then click “Private Endpoints

subnet11

 

2. Click “Create private endpoint”.

subnet12

 

3. In the “Create Private Endpoint” dialog window. Select/specify the below options then click “Create

  • Select the compartment where you want to put your private endpoint
  • Select the “Select Database” option
  • For “Database Cloud Service” select “Oracle Base Database
  • For “Database system” select the DB system name from the drop list
  • For Subnet select the private subnet name where the DB system is located

subnet13

subnet14

 

Step #3: Create Database Connection

Connections are resources that contain the necessary information for accessing an Oracle Database in Oracle Cloud Infrastructure. Along with information about the database, the connection also contains the user used to connect to the database as well as the location of the password that is stored in the Oracle Cloud Infrastructure vault. Other connection details like the JDBC string and if the connection uses a private endpoint are also stored. 

The database connection will be linked to the private endpoint created in step #2

1. Open the navigation menu, click “Developer Services”, and then click “Connections

subnet15

 

2. Click “Create Connection

subnet16

 

3. In the “Create connection” dialog box, select/specify the below options then click “Next

  • Enter the connection name
  • Select the compartment where you want to put your connection
  • Select the “Select Database” option
  • For “Database Cloud Service” select “Oracle Base Database”.
  • For “Database System” select the DB system name from the drop list. Database and Oracle Home will automatically be populated
  • Optionally select the “Pluggable Database” name from the list if you want to connect to a PDB
  • Enter “Username” and select the database user’s “Role” type
  • Select “User Password Secret”. Select the vault’s secret created in step #1 for the database user entered

subnet17

 

Keep the wallet format to none and click “Create”.

subnet18

subnet19

 

Step #4: Connect to DB System from SQL Worksheet Service

Use the new connection created in step #3 to connect to the Oracle database system from SQL Worksheet Service.

1. In the “Database Tools” screen, click “SQL Worksheet”.

subnet20

 

2. Select a database connection: select the compartment where the connection has been located and the connection that has been created in step #3 from the list of available connections.

subnet21

 

Now we can run SQL commands on the Oracle DB system located in a private subnet from OCI SQL Worksheet.

Leave a Comment