The Ultimate Connection Guide

Database systems course - Tel Aviv University

Contents:

Database connecting in general 1

Creating TAU database account 2

Oracle Express Edition (XE) database. 2

Oracle Client software: 2

SQL*Plus, 2

SQLDeveloper 3

Setting up an SSH Tunnel 6

Database connecting in general

In order to connect to a database you have mainly two options:

1. Connecting to an Oracle Express Database you have installed on your home computer
2. Connect to the Oracle Enterprise Database already installed in TAU Computer Sciences Labs.

You can connect to both databases using various database clients, such as SQL*Plus and SQLDeveloper, that can be installed on your home computer as well.

In the next sections we will describe some of the connection options with the various tools. For these descriptions we will be using the following details:

Connection option	User	Password	SID (service ID)	Host	Port	Comments (don’t forget to…)
From home client to home database	hr	hr	xe	Localhost (default)	1521 (default)	1. Install XE database 2. Install client software
	System	As specified during the installation.
Connect from home client to TAU database	Your TAU user	Your TAU password	csodb	Localhost	1555 (defined by ssh)	1. Install client software 2. Create TAU database account 3. Build SSH tunnel
Connect from TAU lab to TAU database	Your TAU user	Your TAU password	csodb	orasrv	1521 (default)	1. ssh to orasrv 2. Create TAU database account

Creating TAU database account

If this is your first time to login to Oracle you will need to create your account. Please follow this procedure:

- Invoke SQL*Plus (see in the following sections how to do that) you will need to login using this account:

Username: Tau_Dummy
Password: oracle

- Run the following command with your preferred username:
exec user_creation('YOUR_PREFERRED_USERNAME');

- Congratulations, you are done! You can restart SQL * Plus and log with your new username (the password is the same as the username).
Note that you will be asked to change your password on your first login.

Oracle Express Edition (XE) database

Tutorial on installation and connection

http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm

Download product from:

http://www.oracle.com/technology/software/products/database/xe/index.html

Oracle Client software:

SQL*Plus,

From home

This is a part of the Oracle Express Edition database in <oracle home>app\oracle\product\10.2.0\server\BIN

To run it double-click on sqlplus.exe

In TAU CS lab (or “Putty” from home)

Before you can run SQL*Plus you need to:

- Connect to orasrv.. Just type the following:
ssh orasrv (enter your password if required..)

- Set a few variables. Again, type the following:
setenv ORACLE_BASE /usr/local/lib/Oracle-Db/app/
setenv ORACLE_HOME /usr/local/lib/Oracle-Db/app/ODb11g
setenv ORACLE_SID csodb
set path=( $ORACLE_HOME/bin $path )

- Run SQL *Plus. (Note that the username and password for “Tau_Dummy” is set here):
sqlplus Tau_Dummy/oracle@orasrv/csodb

- Run SQL*Plus (you know what to do next..)
sqlplus /nolog

To connect with your account, just type “conn YOURUSERNAME” from within SQL*Plus.

SQLDeveloper

Tutorial on installation and connection

http://st-curriculum.oracle.com/tutorial/SQLDeveloper/html/tutorialhome.htm

Download product from:

http://www.oracle.com/technology/software/products/sql/index.html

Example for using in TAU lab

Log in to your account

1. Open a terminal

2. Type the command "sqldeveloper &" in the command prompt

3. The first time you run the program you will be asked if you would like to migrate from previous releases – answer “No”

4. Also, you are welcome to download any suggested updates.

NOTE: The following settings are for creating a connection within TAU. To connect from home via tunneling you will need to change the hostname and port according to the table at the start of this page (also look at the following tunneling section)

The first thing you need to do is to create a connection to the database:

1. In the connections tab, right click the connections icon and select: “New Connection”

2. Type in a connection name, username & password, leave the role unchanged

3. In the basic tab enter the following values:

· Hostname = orasrv

· Port = 1521

· SID = csodb

4. Test the connection and if succeeded the press connect

Setting up an SSH Tunnel

The idea behind creating a tunnel is that your client would ‘think’ that the server is at localhost:1555, but the tunnel established from your machine (localhost) to nova.cs.tau.ac.il would redirect the requests to orasrv:1521

How to do that?

Run the latest version of Putty (note that old versions might not connect, so download the latest - this was done with 0.60) .

Set it to point to Nova (nova.cs.tau.ac.il on port 22) by ssh

We now define the tunnel:

- Select “Connection à SSH à Tunnels” from the category tree.

- Enter the following:
Source port: 1555 (or something else you want..)
Destination: orasrv:1521

Now hit the “Add” button. Your screen should look something like this:

Now let’s just save the settings.. Go back to “Session” category, and your preferred name (in the picture “TAU - Oracle Tunneling”) and click save.

That’s it! Don’t forget to actual connect to nova by entering your unix username and password..

The following image is for using SQLDeveloper with the tunnel: