OCI - Making DBaaS Accessible using port 1521

When setting up a Database on Oracle Cloud Infrastructure (OCI) for the first time there are a few pre and post steps to complete before you can access the database using a JDBC type of connect, just like what you have in SQL Developer, or using Python or other similar tools and/or languages.  

1. Setup Virtual Cloud Network (VCN)
The first step, when starting off with OCI, is to create a Virtual Cloud Network.

 


Create a VCN and take all the defaults. But change the radio button shown in the following image.
 

That's it. We will come back to this later.

2. Create the Oracle Database To create the database
select 'Bare Metal, VM and Exadata' from the menu.

 

Click on the 'Launch DB System' button.
 

 Fill in the details of the Database you want to create and select from the various options from the drop-downs.
 
Fill in the details of the VCN you created in the previous set, and give the name of the DB and the Admin password.
 
When you are finished everything that is needed, the 'Launch DB System' at the bottom of the page will be enabled. After clicking on this botton, the VM will be built and should be ready in a few minutes. When finished you should see something like this.
   3. SSH to the Database server
 When the DB VM has been created you can now SSH to it. You will need to use the SSH key file used when creating the DB VM. You will need to connect to the opc (operating system user), and from there sudo to the oracle user. For example

<ssh file> opc@<public IP address>

The public IP address can be found with the Database VM details
 

[opc@tudublins1 ~]$ sudo su - oracle
[oracle@tudublins1 ~]$ . oraenv
ORACLE_SID = [cdb1] ? 
The Oracle base has been set to /u01/app/oracle
[oracle@tudublins1 ~]$ 
[oracle@tudublins1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 13 11:28:05 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> alter session set container = pdb1;

Session altered.

SQL> create user demo_user identified by DEMO_user123##;

User created.

SQL> grant create session to demo_user;

Grant succeeded.

SQL>

4. Open port 1521 
To be able to access this with a Basic connection in SQL Developer and most programming languages, we will need to open port 1521 to allow these tools and languages to connect to the database. To do this go back to the Virtual Cloud Networks section from the menu.



Click into your VCN, that you created earlier. You should see something like the following.



Click on the Security Lists, menu option on the left hand side.

From that screen, click on Default Security List, and then click on the 'Edit All Rules' button at the top of the next screen. Add a new rule to have a 'Destination Port Range' set for 1521



That's it.

 5. Connect to the Database from anywhere

Now you can connect to the OCI Database using a basic SQL Developer Connection.