Oracle Base Database Service: Connect from SQL Developer OR App VM
Use Case: Every customer will be using a Database and an Application Tier from where they want to connect to Database to insert or retrieve data from it.
In this blog we will cover both the scenarios from a developer perspective where he would be needing to access the database from some IDE like SQL Developer as well from his application (hosted on a VM) via a connection string.
Let us first start with the easiest part i.e., connecting it from SQL Developer. I am pertaining this blog around OCI Base Database Service aka OCI DBCS aka OCI PaaS Database.
Most of the customers especially those in Finance sectors won’t allow Organizations to allow traffic on default ports i.e., 1521 in our case for Oracle Database. Please follow this blog where I have explained the default port change.
Solution: Connect through SQL Developer via default port i.e., 1521
1 & 2 are the username and password for your user. I have taken the sys user credentials.
3 I have selected the role as SYSDBA, it ain’t mandatory to choose this role.
4 this is the address of the node on which my Database is present i.e., “Public IP Address” of the Node which can be fetched from here
Apart from this you have to make sure the subnet on which your DBCS is provisioned has entry for Internet Gateway in it’s route table and an Ingress Rule entry with TCP protocol and port 1521 open on the Security List associated to that subnet.
Now, this is straight forward as my DBCS is provisioned on a Public subnet where I am allowing 1521 traffic to the subnet from my laptop IP.
Now, when you have provisioned your DBCS in a Private Subnet and there is an IPSec Tunnel established and you are in the same network where your IPSec Tunnel is ending then the only change in connection would be to use Private IP in place of Hostname rather than Public IP.
If you are not on the same network as your IPSec Tunnel and want to connect to Private DBCS from SQL Developer, then you need to use SSH Hosts i.e., tunnel via a Public resource provisioned on OCI which I have covered in detail here.
Connect from Application/VM via default port i.e., 1521
To demonstrate this I have created a Virtual Machine on which my application will be running and this application VM is in the same VCN as my OCI DBCS aka Oracle Base Database Service but in a different subnet.
Pre-Requisites:
- Update Security Lists between both (App. & DB) subnets to allow traffic between the subnets on TCP protocol on ports 22 & 1521. (Enabled 22 for checking ping).
2. Install Oracle Instant Client on App. VM from where you will be connecting to the Database.
3. Copy tnsnames.ora file from your DBCS VM to App VM. You can fetch “tnsnames.ora” in DBCS VM (/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora) on to appl. VM where your Oracle Instant Client is installed (/usr/lib/oracle/21/client64/lib/network/admin). Once copied update the host name to Private IP of DBCS.
4. Update environment variables to set $JAVA_HOME, $ORACLE_HOME & $TNS_ADMIN.
Once all the above pre-requisites are met, you can connect to Oracle DBCS from Appl. VM by following the below:
- TNS_ADMIN path which will be under your Oracle Instant Client
- This is the service name to be used while connecting to DBCS via SQLPLUS command.
- Private IP of the database which is to be given in the tnsnames.ora of appl. VM.
- SQL command to use for connecting to DBCS.
NOTE: Here I have used SYS user, hence in tnsnames.ora file of my appl VM SERVICE_NAME contains Container Database details, if you are connecting to database using a user you have created then you should use the SERVICE_NAME from Pluggable Database as the user will be created in Pluggable Database.
Please follow this blog, to know how to create a user on OCI DBCS aka Oracle Base Database Service.
!!Happy Learning!!
