Once you have your Oracle Cloud DBaaS service up and running and you can connect to it via SSH, then the next step is connecting to your database.
This is where things get a little confusing at first (or at least I got more than a little confused). Because let’s imagine you want to connect SQL Developer running on your laptop to the Oracle Database, there’s a couple of ways we can do that
- Open up the database so that port 1521 is open to the world (really bad idea – don’t do that!)
- Use an SSH tunnel from your laptop to the DBaaS service
Let’s say for the sake of argument you want to do option #1, how do we open up the firewall to allow external access to port 1521?
Well – again this is where I found it slightly confusing and maybe not the most logically laid out. After a bit of searching I found you need to go to the Compute Service rather than the DBaaS Service console. When you created a DBaaS Service a Compute Service (i.e. a virtual machine) will have been created for you, so instead of configuring the network rules and filtering at the DBaaS level you configure them at the Compute Service level. This does make sense when you think about it, but it would be nice if I could jump directly to the network rules from the DBaaS Service console.
here I’m filtering for the rules just for my DEMO instance.
Opening Port 1521 to the Public Internet (PLEASE DON’T DO THIS!!!)
So, firstly lets try and connect from my laptop to the DBaaS instance using SQLPlus:
[jes@AEMBP ~]$ sqlplus system@\'<my.public.ip.here>:1521/ORCL.ae.oraclecloud.internal\' SQL*Plus: Release 126.96.36.199.0 Production on Fri Nov 7 14:28:26 2015 Copyright (c) 1982, 2012, Oracle. All rights reserved. Enter password: ERROR: ORA-12170: TNS:Connect timeout occurred
notice that I’m using Easy Connect syntax so I can connect using an IP address and don’t need to setup a TNSNames etc.
The connection hangs because there’s no path through the firewall to port 1521.
Now let’s open up the firewall to allow access to port 1521. To do this click the little ‘hamburger’ menu option next to the rule for ora_dblistener (unfortunately this page does not display the port numbers which would be helpful).
Let’s update that rule to enable it:
Now lets try and login via SQLPlus again
[jes@mac ~]$ sqlplus system@\'<my.public.ip.here>:1521/ORCL.ae.oraclecloud.internal\' SQL*Plus: Release 188.8.131.52.0 Production on Fri Nov 7 14:39:51 2015 Copyright (c) 1982, 2012, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c EE Extreme Perf Release 184.108.40.206.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options SQL>
Ok, now go back to the rule and disable it! Immediately! Please don’t have port 1521 open to the public internet. There’s much better ways to do this in SQLPlus using a custom SSH Tunnel or by using SQLDeveloper (which we’ll blog about later).
So now we know the basics of opening network ports and connecting to the DBaaS database remotely.
Use an SSH Tunnel Instead (Please!)
Ok, assuming you’ve now closed port 1521 to the public internet, let’s see how to use an SSH tunnel to connect via SQLPlus securely to our DBaaS instance.
We can create an SSH tunnel by starting a new terminal window (I’m on a Mac, which means essentially the same command would work on a Linux machine). If you’re on Windows you could use PuTTY or other software to create your tunnel.
So in a terminal window I can run:
[jes@mac oracle-cloud]$ ssh -L 10521:localhost:1521 -i oracle_cloud_rsa opc@<my.remote.ip.here> [opc@DEMO ~]$
the syntax here opens up a port on my local machine (10521 – you can choose any number you like) which will be tunnelled over the SSH connection and connect to port 1521 on ‘localhost’ where in this case localhost represents my DBaaS instance.
This maintains an open session on the DBaaS instance and my tunnel will be valid for as long as this connection is active (if I close the session then the tunnel will end).
So now lets re-run the SQLPlus connection and try to connect via the tunnel:
[jes@mac ~]$ sqlplus system@\'localhost:10521/ORCL.ae.oraclecloud.internal\' SQL*Plus: Release 220.127.116.11.0 Production on Fri Nov 7 17:28:52 2015 Copyright (c) 1982, 2012, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c EE Extreme Perf Release 18.104.22.168.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options SQL>
Success! Notice how I change the hostname / ip address to be ‘localhost’ (i.e. my laptop) and the local port to be 10521, i.e. connect to the SSH tunnel I just created.
This is a really nice way to not have to open up your DBaaS instance to the world but still enjoy SQLPlus connections from your local machine (or any other machine which can SSH to the DBaaS instance).
It’s worth pointing out that SQL Developer has SSH Tunnelling built in, you can read more on Jeff Smiths blog here.