Connecting to Your Autonomous Database from Your Laptop

Hello. Today I’m going to show you how to connect remotely to your OCI Autonomous Database that’s in the cloud from your laptop, and the tool that we’re going to be using to do that is the Oracle Instant Client. Now there are two prerequisites before you do this. You have to have created a free Oracle OCI cloud account and you have to have provisioned a free autonomous database in Oracle’s OCI cloud environment. I’ve covered those two topics in prior presentations, so you might want to look at those now if you haven’t had a chance to do that.

I’m going to review the steps that are required to connect from your laptop to the OCI Oracle cloud and then I’m going to go into detail on each of these steps. So first, you have to install the Oracle Instant Client on your laptop. I’ll show you how to do that.

Second, you have to copy the wallet files from Oracle’s OCI cloud to your laptop. You also have to copy the tnsnames.ora file from Oracle’s OCI cloud to your laptop, and then you have to add the Instant Client path to your Windows PATH variable and that’s all there is to it. And then you can test starting up SQL Plus from your laptop and connecting to your Oracle Cloud database.

So to install the Oracle Instant Client on your laptop, you need to go to otn.oracle.com. You need to download the Oracle Instant Client, and then Instant Client requires a Microsoft Visual Studio package that’s required for the Basic Lite Package of Instant Client. And then you need to download the SQL Plus Package because the SQL Plus Package is what has the SQL Plus executable.

So I’ll walk you through each of these steps. Let’s get started. So if you navigate to otn.oracle.com it takes you to this page. This is Oracle’s technical resources page. You need to go to the Oracle Instant Client download. So I’m going to click on this.

And then once you’re on this page, you need to pick the appropriate download for your operating system. I’m running a Windows 64-bit, Microsoft Windows 64-bit. So I’m going to click on that. And then once you’re here, then you have these various options. Now what we want is the Instant Client Basic Lite Package, and this also requires the Microsoft Visual Studio 2007 package, so you’ll need to click on this and install this as well. So go ahead and do that first.

I’m running the X 64. I’m running a 64-bit Windows operating system, so I’m going to click on that. That downloads it over here. So now I can open that, agree to the licensing terms, and install it.

And that will run for a few minutes. I’ll pause here while that’s running. And as part of this, you need to restart your computer so I’m going to go ahead and do that. I’ll come back to this screen after I’m done.

So once you’ve installed this Microsoft Visual Studio 2017, then you need to come here and download the Basic Light Package and the SQL Plus Package. You’re going to need both of these. This instant lite is the client software and then you’ll need SQL Plus so you have access to the SQL Plus executable. So I’m going to go ahead and download those. And I’m also going to download the SQL Plus Package.

And then once you have those downloaded, you need to copy those somewhere on to your local disk. So I’m going to copy these two on my C drive. I have this Oracle client. I’m going to put both of those packages in there. And then to extract these, you can right-click on this and Extract All.

That’s the instant client, and it’ll ask you for a directory. I’m just going to keep that default directory. So now I have that Instant Client. I also need to get the SQL Plus Package, which is this one. So I need to extract that as well. And then once the SQL Plus Package is extracted, go to where that was extracted– and you need these six files. So I’m just going to copy all these.

And these might need to go into your Instant Client where you unzip that. So I’m going to go back to the Basic Lite Instant Client. Go where all the files are. And I’m just going to paste those six files into this directory. So I can see those six got pasted in there. So once you’ve installed the Instant Client Basic Package, the SQL Plus Package, then you need to go to your Oracle Cloud account and download the wallet files and the tns names file for your database.

So here I am in Oracle’s cloud environment, the OCI environment. I’m going to click on the three bars up here, the hamburger menu. I’m going to go to Database, Autonomous Database. Here’s my database. I’m going to click on that.

And now what you need is the db connection information. So I’m going to go ahead and click on this and then you can download the wallet. If you scroll down here, you’ll see that here are the connection strings. They’re down here. So you could either cut and paste those.

But you’re going to need the wallet files as well, so this download wallet has the tnsnames.ora file and it has the wallet files that you’ll need. So I’m going to go ahead and click on Download. And then you need to give it a password. And go ahead and download. Click on Download.

I can see that that was downloaded here. Now I need to get these files. Show those in the folder. Wallet. I’m going to go ahead and extract these.

So I’m going to copy all these files. There’s 1, 2, 3, 4, 5, 6, 7, 8, 9. So I’m going to copy these nine files. And I need to put those to where my Instant Client can access those. And so here I am basic lite Windows instant client. And the network admin folder. I’m going to paste those. I’m going to paste those files into here.

So now I have these files, which allow me to connect securely to my remote cloud database. So the last thing that I need to set up here is to set my path variable to contain the location of where my instant client, where my SQL Plus executable is. So I can right-click here and do the Copy Address.

And then I can go here and then in Advanced System Settings and Environment Variables. And go down here to Path. Edit that and make sure that the Instant Client to where your SQL Plus executable is, make sure it’s part of the path.

So click OK. OK. OK. And then, once that’s set, you can come here and access your SQL Plus client. So here’s the SQL Plus executable. I’ll just double-click on that.

Before we do this, I want to take a quick look at the sqlnet.ora file. That’s in Network, Admin, and take a look at tnsnames.org. So I’ve created copies of some of these connection strings here just to do some testing. So these are the default connections strings that came with it and then I just changed the name to something that would be a little bit easier to connect to.

For example, for this one it could be my clouddb, for example. And I’ll go ahead and save that and exit. So now over here, I can connect as admin@clouddb. And then give it the password. And so here I am in my database, my Oracle cloud database.

So here I can do things like select instance name or anything really– username from dba users and so on. So I’m connected from my laptop to my Oracle cloud database. Now you should be able to run any client tool. If you have Cygwin installed, you should be able to run that as well.

So for example, if I start up Cygwin, I should be able to type in sqlplus admin@clouddb. And then enter the password. And here I am. I’m connected from Cygwin to my cloud database. It really should work from any client to, like, a SQL developer, Toad, and so forth.

For troubleshooting, if you can’t connect, I would check to make sure that to make sure that the path is set correctly. You need to have your SQL client, the SQL Plus executable, in your path and make sure you don’t have any other variables from maybe other installations that you’ve done, like TNS_ADMIN. Make sure that that’s not set, or take that into account while you’re doing the troubleshooting.