Setup needed for access to our database servers on dbs3.cs.umb.edu and pe07.cs.umb.edu, for CS634, spring 2018

For security reasons, you cannot login to dbs3. Thus you will need to ssh to pe07.cs.umb.edu and then use sqlplus to access Oracle on dbs3.
For example, the sample user scott has password tiger1, and a table named emp. Here we use sqlplus running on pe07.cs.umb.edu to access the database on dbs3 by adding "@//dbs3.cs.umb.edu/dbs3" to the usual sqlplus command:

Note: see "Shortcut way" below to avoid typing the long command every time.

pe07$ sqlplus
        scott/tiger1@//dbs3.cs.umb.edu/dbs3

        

        SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 22 15:45:19 2016

        

        Copyright (c) 1982, 2014, Oracle.  All rights reserved.

        

        Last Successful login time: Fri Aug 19 2016 14:19:03 -04:00

        

        Connected to:

        Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
        Production

        With the Partitioning, OLAP, Advanced Analytics and Real Application
        Testing opions

        

        SQL> select count(*) from emp;

        

          COUNT(*)

        ----------

                14

        

        SQL> exit

        Disconnected from Oracle Database 12c Enterprise Edition Release
        12.1.0.2.0 - 6bit Production

        With the Partitioning, OLAP, Advanced Analytics and Real Application
        Testing opions

        pe07$

To avoid showing your login information on the command line (which is also shown by the "ps al" command to others), do the following:

 pe07$ sqlplus /nolog 

        

        SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 30 13:54:13 2016

         

        Copyright (c) 1982, 2014, Oracle. All rights reserved. 

        

        SQL> connect scott/tiger1@//dbs3.cs.umb.edu/dbs3 

        Connected.

SQL> select count(*) from emp;

        

          COUNT(*)

        ----------

                14

SQL> exit

        Disconnected from Oracle Database 12c Enterprise Edition Release
        12.1.0.2.0 - 6bit Production

        With the Partitioning, OLAP, Advanced Analytics and Real Application
        Testing opions

        pe07$

Example with student user jsmith: Note how the UNIX/Linux username is also the password here:

pe07$
          sqlplus jsmith/jsmith@//dbs3.cs.umb.edu/dbs3

Shortcut way: add the TWO_TASK environment variable to your .profile file in your login directory to specify our default remote Oracle host:

export
          TWO_TASK=dbs3.cs.umb.edu/dbs3

Then you can use "sqlplus" alone and be prompted for username and password.

Oracle Accounts

Once you have run apply for a cs630 or cs636 account (even if you are in cs430 or cs436 officially), you are listed for an Oracle account. I will create the accounts and will be the (one and only) Oracle DBA for dbs3. Thus if you believe the Oracle system is not working properly, please email me (eoneil@cs.umb.edu) right away. If you forget your Oracle password, you should be able to get it reset by the UNIX operators in S-3-158 using a special Oracle account set up for that purpose. This is a new functionality for operators, however, and may not be available at the start of the term.

JDBC access to Oracle and mysql from pe07 and other Linux hosts at cs.umb.edu

JDBC for Oracle is available at port 1521 on dbs3.cs.umb.edu, and its "sid" is dbs3. You can test a JDBC connection to your own Oracle account by running JdbcCheckup after copying the jdbc directory from your class home page to your own homework directory. Note that when logged in on pe07 or other host at cs.umb.edu, you are inside the firewall, so you can contact Oracle's JDBC service directly at dbs3.cs.umb.edu, port 1521, and don't need a tunnel as you will at home, discussed later in this file.

cp -r /data/htdocs/cs634/jdbc cs634

Then cd to your own jdbc directory, and:

pe07$ javac JdbcCheckup.java

        pe07$ java -cp ojdbc6.jar:. JdbcCheckup

        Picked up _JAVA_OPTIONS: -Djava.security.egd=file:/dev/../dev/urandom

        Please enter information to test connection to the database

        Using Oracle (o), MySql (m) or HSQLDB (h)? o

        user: xxxxx

        password: yyyyy (same as xxxxx unless you changed it)

        use canned Oracle connection string (y/n): y

        host: dbs3.cs.umb.edu

        port (often 1521): 1521

        sid (site id): dbs3

        using connection string: jdbc:oracle:thin:@dbs3.cs.umb.edu:1521:dbs3

        Connecting to the database...connected.

        Hello World!

        Your JDBC installation is correct.

Similarly, for mysql (the jar version, here 43, may change)

pe07$ java -cp  mysql-connector-java-5.1.43-bin.jar:.
        JdbcCheckup

        Picked up _JAVA_OPTIONS: -Djava.security.egd=file:/dev/../dev/urandom

        Please enter information to test connection to the database

        Using Oracle (o), MySql (m) or HSQLDB (h)? m

        user: xxxxx

        password: yyyyy

        use canned MySql connection string (y/n): y

        host: localhost

        port (often 3306): 3306

        using connection string: jdbc:mysql://localhost:3306/eoneil1db

        Connecting to the database...connected.

        Hello World!

        Your JDBC installation is correct.

Home machine tunnel setups needed for using JDBC from home

To access the database systems dbs3.cs.umb.edu and pe07, themselves inside the firewall, from a system outside the firewall, we need a “tunnel” that uses the SSH protocol’s ability to provide a secure connection to a port that is not directly accessible for security reasons. For example, we want to access port 1521 on dbs3 to talk to Oracle using JDBC, but this port is blocked by the firewall. So, instead we connect to port 22 (SSH’s port, which is not blocked by the firewall for users.cs.umb.edu or pe07) on pe07.cs.umb.edu (for example) and arrange that SSH make a connection for us inside the firewall to port 1521 on dbs3, and then move the data back and forth. For more info, see IBM article on tunneling.

For your home Windows machine

See PuttyTunnels.html for instructions on using putty to set up tunnels from home to our database hosts.

On your home Linux or MacOSX machine (in Terminal):

First get a Terminal window for Mac or a shell window for Linux to do the following commands. Use ssh to set up the needed tunnels to dbs3, port 1521, and pe07, port 3306: These just hang, logged in to pe07.

Replace ‘user’ here with your cs.umb.edu Linux username and answer the password prompt with your cs.umb.edu Linux password. These commands will “hang”, so open another shell/Terminal window to continue working.

      ssh -N -L1521:dbs3.cs.umb.edu:1521 user@pe07.cs.umb.edu
      ssh -N -L3333:pe07.cs.umb.edu:3306 user@pe07.cs.umb.edu
      or both at once:
      ssh -N -L1521:dbs3.cs.umb.edu:1521 -L3333:pe07.cs.umb.edu:3306 user@pe07.cs.umb.edu

These work on Mac OSX v 10.9.5 on a desktop system. If you are still having trouble, please report your OSX version number and type of Mac.

Testing the tunnels with a browser

Browse to localhost:1521 with Chrome and see "No Data Received". This means it did connect, just didn't get any data from the server.

Try localhost:12345 or another random port to see "This webpage is not available", meaning it couldn't connect.

Try localhost:3333 with Chrome and see a small download happen. It had to connect to do that.

Testing the tunnels with telnet (or similarly, putty or ssh)

telnet is an classic tool that was used before ssh was needed: it just connects your keyboard and screen to a TCP stream connection, plain and simple. It can be used to test to see if a port on a host is open, among other things. Try “telnet” at the command line to see if it’s available on your system, and if not, optionally install it as follows:

Windows: telnet is usually there, but if not, see Install telnet client.
Linux: telnet should be available in the shell window.
Mac: telnet should be there in Terminal’s command line.

Test mysql tunnel: telnet localhost 3333, or ssh localhost:3333, or putty to host localhost, port 3333

Test Oracle tunnel: telnet localhost 1531, or ssh localhost:1531, or putty to host localhost, port 1531

If successful, telnet or ssh or putty will take over the window and show you anything sent by the server. Anything you type will be sent to the server, except for the telnet escape sequence control-]. We don’t want to confuse the server with garbage, so type the escape sequence immediately, followed by quit, or kill the window. To see what happens with a port that’s not in active use, try “telnet localhost 12345” (or ssh localhost:12345 or putty ...) or some other random number.

Testing the tunnels with JdbcCheckup: from Windows, Linux or MacOSX

Once we have the tunnels working, the database ports show up as localhost:1521 and localhost:3333. We are using a local port 3333 to avoid any locally running mysql databases, which would normally use the local 3306 port. Similarly, if you have a local Oracle server running, you will need to use a different local port for tunneled Oracle.

java -classpath ojdbc6.jar;. JdbcCheckup    (use colon instead of semicolon on Linux/MacOSX)
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? o
user: xxxxxx
password: xxxxxx
use canned Oracle connection string (y/n): y
host: localhost
port (often 1521): 1521
sid (site id): dbs3
using connection string:
jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.

java -classpath mysql-connector-java-5.1.39-bin.jar;.
JdbcCheckup
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? m
user: xxxxx 
password: xxxx
use canned MySql connection string (y/n): y
host: localhost
port (often 3306): 3333
using connection string:
jdbc:mysql://localhost:3333/xxxxxdb
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.

Sqlplus on your home machine (optional, only for the adventurous)

You can download and install the same "instant client" software we are using on pe07 to access dbs3's Oracle database right from your own system.

Visit http://www.oracle.com/technetwork/database/features/instant-client/index-097480.htm and choose your platform, and obtain an Oracle web account if necessary (following its prompt to do so)
Find the list of downloads, and download and unzip one of the first two, plus the third (jdbc) and fourth (sqlplus).
Follow the instructions on the above-linked page.
Make sure your tunnels are working, so Oracle's port appears at localhost:1521.
In a new shell/command window, try sqlplus scott/tiger1@//localhost:1521/dbs3 Either cd to the directory with the executable, or add it to your path.
Since 1521 is the default, you can omit it.

Note that sqlldr is not included in client installation. We have added it to pe07 by following instructions here, modified for version 12.1. But if you load your tables while logged in on pe07, you shouldn't need sqlldr on your development system.

Note that once you are using port 1521 for a local Oracle, you can't use it for tunneling to dbs3's port, so use another local port for the tunnel, or bring down your local Oracle when you want to tunnel to dbs3's.

Oracle SQL Developer on your home machine (optional) after getting sqlplus to work

1. Visit http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html and choose your platform. For 64-bit Windows, use the version with the JDK embedded. For other platforms, get Java 8 working first.

2. Download and unzip. Run from the resulting directory or add to your path.

3. Create a new Connection, for example for user scott, password tiger1, hostname localhost, port 1521, sid dbs3, and leave the boxes below the sid blank.

4. Double click the resulting Connection icon, and explore the resulting catalog tree.