How to see your private database from an Oracle Cloud Database

I've been blogging a bit lately about different ways to get data from your private database server to your Oracle Cloud Database server.   Mostly that has involved FTP'ing files combined with some Oracle process or connecting to both databases in Toad.

But I discovered a pretty cool trick last week that I wanted to share.  Those of you that are more skilled in networking probably already know about this, but I'm guessing a lot of you will say "whoa." just like I did the first time you see this work.   It's called "reverse tunneling" or "remote port forwarding", and it opens up some more possibilities for getting data from your company's private database up to the cloud, which I'll write about soon.

So, you have your company's private database, which is safely behind a firewall and not accessible from the outside world, as it should be.   And now you have this cloud database which is the outside world, but you can only access it via SSH.   So how can we get the cloud database to connect to the private database?   Remote SSH Port Tunneling, of course!

I've used PuTTY in my recent blogs, and you can do the following with PuTTY too, but this time I'm going to use a different SSH/Tunneling tool called "MobaXterm", which is a lot prettier than PuTTY, and has worked great for me.  It comes in both free and paid versions.   You can get it here.  This is not one of our products, by way.   Just something I found recommended online as a PuTTY alternative.   There are probably a dozen other products out there that will all do this.

I've installed MobaXterm on my local database server (the one that I want my cloud database to connect to), but instead you can install it on your desktop PC and all of this will still work.

In MobaXterm's main toolbar is a "Tunneling" icon.  Click it.

and it'll take you to this.   Click "New SSH Tunnel" down at the bottom:

First, I'll set up my normal forward port.  This one will let me connect to the Oracle Cloud database as I did with PuTTY in this blog.   My cloud database is that IP address that starts with 129.144, and I'm forwarding port 9999 on my local machine to it.

Click Save and name it "Here to Cloud".     You really only need the "Here to Cloud" tunnel if you are going to connect to the cloud database from this machine that is running MobaXterm.

Then click "New SSH tunnel" once again, and we'll set this one up so the cloud database can connect back.    Notice that I chose "Remote port forwarding" at the top.  Again, my cloud database is the one that starts with 129.144, and JD-OR12012-PLUG is the server that my local database is on.  So the cloud server will be able to look through its port 8888 and see my database.  

Click save and name it "Cloud to DB".   Use the buttons circled in red to set the private key file.   Use the buttons circled in blue to make the tunnel automatically reconnect if it gets connected.   There are buttons at the bottom to start/stop all tunnels.   

Start your tunnels, then go to Toad and connect to your cloud database.     We're going to create a database link to the database running locally.

From Toad's main menu:  Database -> Create -> Database Link.    Under "Database link to connect to", I'm just using Easy Connect notation, so I don't have to add any TNSNames entries on the cloud server.    Did you know you could do that?   It's very convenient.  server:port/service_name.   

Click OK and go to Toad's Editor, and try out a query through the new database link.    So my main connection in Toad is to an Oracle Cloud database, and I've just selected data from my local database, which is running behind a firewall.   Whoa.