Setup Oracle Base Database Service in OCI

 This is the first step in my The Oracle Database Developer OCI Environment series We begin with setting up the required networking, then the Base Database Service and test the connection.

Create a Virtual Compute Network (VCN)

Navigate to Networking|Virtual Cloud Networks.

In Networking|Virtual Cloud Networks choose Actions|Start VCN Wizard. 

Select Create VCN with Internet Connectivity and Start VCN Wizard.

As long as you don’t need something else, mostly go with the defaults. Give it a name, choose your compartment and a CIDR block. 

The public and private subnet address ranges derived from the VCN range should be fine, click Next.
Double check, click Create and View VCN.

Go to the Security tab and click on the Default Security List to edit it.

Go to the Security rules tab and select Add Ingress Rules.

Select your Source CIDR and enter 1521 for the Destination Port Range. Important: leave the Source Port Range empty. Give it a name and finish

The new rule should appear in the list and should look like the one for SSH, except for the name and port.

Create Database Base service

Navigate to Oracle Database|Oracle Base Database Service.

Click on Create DB system.

For most settings, I go with the defaults but YMMV, so adapt the settings to your needs.

Give it a name, the compartment should be pre-selected. Adapt the shape and Edition to your like.

Upload your existing SSH public key or let OCI create you a new key pair. When you download a new key pair, save it to the .ssh folder in your home directory (any OS), for example on my Windows desktop it is 

C:\Users\Arne\.ssh

Choose your preferred license type.

Select the newly created VCN and subnet. We use the public subnet to have SQLNet access via the internet, equivalent to what we would have with a shared Autonomous Database. For a production environment, you should consider creating the database in a private subnet. Enter a hostname prefix

Change the database name, if you don’t like the auto generated one.

As you probably want to use 23ai, click on Change database image.

When you switch on Display all available versions, you will see that 23.7. is the latest image at the time of writing, though 23.8. is already out. Take this image, we will upgrade it later.

Name the PDB and enter your new password. Disable b ackups, if you don’t need them. Then click on Create.

To watch the progress, click on Work requests.

When finished, switch to the Nodes tab and note down the public IP address.

Connect to instance and check database

Connect via ssh

When you have more than one ssh key pair in your .ssh directory, create a config file to assign the keys to different servers. Create or edit your ~/.ssh/config file and add a new entry like this:

Host <your-public-IP-address>

  User "opc"

  IdentityFile c:\Users\<your-user-name>\.ssh\ssh-key-2025-04-23.key

eg.

Replace ssh-key-2025-04-23.key with your key file name.

Next connect to your instance via ssh by running:

ssh opc@<your-public-IP-address>

You should immediately be connected to your virtual machine as user opc. Change user to oracle.

If you get prompted for a password when running the ssh command, most probably something is wrong with your files in the .ssh folder. Double check key pair and config files.

First connect to the database

As user oracle take a first look at your database:

Check the listener first, should look good.

Do a direct connect to the database: sql / as sysdba

Take a look at your PDB’s: sho pdbs

SQLNet connect to your database

Next lets check SQLNet connect.

To find the SQLNet connection information, go to the Databases tab and click on your database.

Click on the DB connection button.

Note down the Easy Connect string for the CDB.

Navigate to Pluggable Database und select your PDB.

Also note down the connect string for the PDB.

From the ssh session connect to the CDB via 

sql system/<password>@<quick-connect-string>

So the database is up and running and we have access via SQLNet.

The Oracle Database Developer OCI Environment

This is a step-by-step guide for setting up an Oracle Database Development Environment on OCI. It will use the Base Database Service instead of an Autonomous Database, so an OCI account where a DB Base Service can be set up is required. In addition to the database, all necessary tools like ORDS and APEX will be set up, similar to my 'The Oracle Database Developer VM Project' series. And same as my older series, this is a work-in-progress, continuously updates when I find something new to cover.

• Setup Oracle Base Database Service in OCI

Use your Oracle Athenticator on Android via your Garmin smartwatch

In fact, this is a generic feature of the Garmin integration with Android smartphones. If you use the Oracle Authenticator to log into your Oracle Cloud account, you can use your Android smartphone for two-factor authentication. When logging into your account, you will get a notification on your phone.

Luckily, Oracle included the Allow and Deny options directly into the notification, so you can make your choice without opening the app. I know other authenticators, that are not so smart (hello PayPal).

You can make use of this, if you have Smart Notifcations enabled at your Garmin watch and added the Oracle Authenticator to your allow list.1

When you get a Login Attempt message on your phone, that will also be mirrored to your watch. Notice the blue'ish mark on the upper right button, press it.

That will bring up the same choices as in the phone notification. So choose your option here and you are done.

How to connect via ssh to Database from the Oracle SQL Developer Extension for VSCode

This post is inspired by a LinkedIn posting from Kris Rice, so kudos to him. Based on that, this post will show how to connect from your Windows host to a VirtualBox VM running Oracle Linux via ssh from the Oracle SQL Developer Extension for VSCode.

First check if the sshd in your VM is running

sudo systemctl status sshd

If you don't already have an ssh key pair on your Windows host, create one via ssh-keygen in the .ssh folder under your home directory

In your Linux VM add the public key from your Windows host by

sudo vi ~oracle/.ssh/authorized_keys

Paste your public key into that file and save it.

Test your connection from the Windows command line.

As you will not be able to choose a username when connecting from VSCode, create an ssh config file on Windows in your .shh folder like this:

Host 192.168.56.11
  User "oracle"
  IdentityFile c:\Users\Arne\.ssh\abruenin-lap

If you don't already have, install the Remote - SSH extension in VSCode

With that extension, you will get the Remote Explorer. It scans your .ssh config file and offers you the ssh hosts that it found.

Click the folder icon next to your connection to 'Connect in New Window...'

In the new server side window, VSCode will recommend you to install the old Oracle Developer Tools for VS Code, though these are marked as deprecated (at least my VSCode did that). Instead install the Oracle SQL Developer Extension for VSCode.

Once installed, you can use SQLDeveloper in the remote ssh window just as you would use it with a direct connection.