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 options
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 options
pe07$
: Note how the UNIX/Linux
username is also the password here:
pe07$ sqlplus jsmith/jsmith@//dbs3.cs.umb.edu/dbs3 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$
pe07$ mysql -u joe -D joedb -p
Password:xxxxx
mysql> exit
Bye
pe07$
To access the database systems dbs3.cs.umb.edu and pe07.cs.umb.edu, 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.cs.umb.edu) on pe07.cs.umb.edu 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.
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.
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:
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 H2 (h)? o user: xxxxxx password: xxxxxx 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-8.0.4.jar;. JdbcCheckup Please enter information to test connection to the database Using Oracle (o), MySql (m) or HSQLDB (h)? m user: xxxxx password: xxxx 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.
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.
sqlplus
scott/tiger1@//localhost:1521/dbs3