Remote Connection to Oracle from Linux (Ubuntu) Using SQL*Plus

I followed instructions from the Oracle documentation to connect to our remote server with a public IP address from home and check that everything was running ok. It is easy when you know how, as usual!

First off, make sure you have enough swap space (over 1GB) to install the Oracle Express Client. I didn’t have enough so had to use a livecd and a third party partition manager to resize my swap partition to a reasonable size.

Next up I downloaded the Oracle Express Client from Oracle themselves (click here to download it) and installed it using the ubuntu package manager. Easiest way, double click on it, click install!

Now the oracle client is installed you have SQL*Plus, a command line interface to your remote and local databases. It isn’t great to use, no command completion, no remembering previous commands but it is the quickest way to get a SQL command line interface.

Open up SQL*Plus (in the main menu > Oracle Client 10g Express Edition > Run SQL Command Line) and now you have a SQL> prompt. This is where you enter “connect” and your connection string and user details etc. For example, connecting as “sys”:

connect sys/password@ip.ad.dre.ss/instancename as sysdba

The oracle default instance name (if you installed the example database) is “orcl”. So if your password is “foobar” and your ip address is “192.168.1.1″ then you need to type into the command prompt:

connect sys/foobar@192.168.1.1/orcl as sysdba

Of course you can change the login to whoever you want and if you look at the documentation you will  find a better way to do this in future using a connection identifier. If you are successful then SQL*Plus will say “Connected.” and you now have a connection, it’s just like being there.

To test out your connection as “sys” try typing the following (thanks Viktor) into the terminal to list all the current schemas/users in the database:

select username from all_users;

You should get a list back and the number of rows selected (about 27 rows if you installed the example database). Now you know how to connect you can run any SQL commands (inc PL/SQL etc) you want plus all the SQL*Plus commands, which I am just beginning to get to grips with.

Connecting to Remote Oracle Server with SQL*Plus in Windows

This was a huge pain at first but it turns out you just have to include the user and the connection string when you run sqlplus.exe from the command line eg:

sqlplus sys@CONNECTIONSTRINGNAME AS sysdba

And make sure your connection string is in the correct format in the “TNSNAMES.ORA” file (located in the same directory as sqlplus.exe) and your “TNS_ADMIN” environment variable is set to the directory the “TNSNAMES.ORA” file is located in. An example connection string is:

CONNECTIONSTRINGNAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = i.p.add.re.ss)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
(SERVER = DEDICATED)
)
)

Using TOAD for Oracle With a Remote Oracle Installation

I couldn’t figure out how to connect to my remote Oracle install on a VMWare Solaris install using TOAD until I found this page at trap17. Turns out you need to install the Oracle Instant Client (or at least have it installed somewhere) with the correct environment variables for TOAD to pick up your Oracle install. I followed this guide and it worked straight away:

Here’s how you do it (Although I’ve done my best to present an accurate procedure, I can’t guarantee this will work for everyone. Messing around with your environmental variables is not for the weak, proceed with caution):

1) Prepare your mind for information that should be used once and then quickly discarded. Otherwise the following information may stick to your brain and take up valuable space. You are warned.

2) Install the oracle instant client. I don’t know why…I kinda wish I did. You probably can get away with installing just the basic package. Unzip the files to a directory, and then copy the path of this directory to the clipboard.

3) Add this directory to the PATH environmental variable. To do this, right click on My Computer, go to the Advanced tab, and click ‘Environment Variables’. Under System variables, find ‘Path’. Select it, and click edit. ctrl-v to paste the directory into the string, and say OK. e.g.:

Before: %SystemRoot%system32;%SystemRoot%;C:Program FilesATI TechnologiesATI Control Panel;
After: %SystemRoot%system32;%SystemRoot%;C:Program FilesATI TechnologiesATI Control Panel;C:instantclient;

4) In the same window, under ‘User variables for …’, Click the New button. Call the variable ‘TNS_ADMIN’. ctrl-v to paste the directory path into the ‘variable value’ field. Say OK, and close out the ‘My Computer’ properties windows.

5) Create a file called ‘TNSNAMES.ORA’ in your instant client installation directory. In this file designate the connection parameters for your database. Mine looked something like this:

CODE
DB_ALIAS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip.add.re.ss)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
(SERVER = DEDICATED)
)
)


replace the DB_ALIAS, ip.add.re.ss, port, and SID with your settings. If you don’t know what any of this means, good for you, you’ll live longer.

6) Reset your computer, fire up Toad, and then open a new connection. You should see DB_ALIAS as an option under the database field. Set your username and password, hit connect, and you should be in business.

I guess Toad looks in certain places for the drivers and configuration files it needs to connect to oracle. Setting the environmental variables tells Toad where to look. If you had a local install of Oracle I imagine you could fire up Toad and all the files it needed would already be in the right place, but you can follow these steps instead. Woot!