How to Connect to Oracle
By default Oracle listens to port 1521 for SQL*Plus connections. This is an unencrypted connection, meaning that all data is sent in clear text. Even in corporate settings, it may not be a good practice to connect directly to 1521 as someone could then look at the data you're sending and/or obtain the connection information to your database. This definitely not a recommended practice if you connect to 1521 over the internet.
The best way to get around this is to restrict access to port 1521 (which the build scripts do by default) and then leverage SSH tunnelling to connect to your database. There are several ways to do this.
There are already good articles about how to do this so it won't be covering in this document. For pre-4.1 installations view this article. For versions 4.1 and onwards SQL Developer has a new SSH Hosts tab dedicated to SSH tunnelling which is covered in this article.
There are some cases where you may to run an SQL*Plus script which is stored on your local machine. In order to run it you need to do two things: open an SSH tunnel that maps a local port to the server's port 1521, and then connect via SQL*Plus to that new local port.
To create the tunnel, open a new terminal window and run the following command. In this example it will map local port 1525 to remort port 1521.
ssh -L 1525:localhost:1521 giffy@vcentos
In another terminal window, connect to the Oracle database using the following command.
Note that it is connecting to
localhost on port 1525 and not directly to the server (port 1521 on
When doing SSH tunnelling, you may want to keep two different terminal windows open. If the tunnel drops, you can easily reconnect. If you don't want to have two windows open (or tabs if you use a good terminal app such as iTerm for Mac) then you can always suffix the open tunnel command with
Opening Port 1521
Though not recommending on public networks, you may want to open port 1521 for a direct connection to the database. This is ideal when using a Virtual Machine (VM) on your system with a local internal network. There are several ways to open port 1521 from the firewall.
If you haven't already run the build script, you can modify the
OOS_FIREWALL_ORACLE_YN setting to
config.sh. If you've already run the build script and installed Oracle then run the following commands as
firewall-cmd --zone=public --add-service=oracle --permanent firewall-cmd --reload
If you run into any issues it's probably because you used an older version (pre 0.2.0) of the build script that did not include the
oracle firewall configuration. To manually create it run:
cd /etc/firewalld/services cp /usr/lib/firewalld/services/http.xml . mv http.xml oracle.xml vi oracle.xml # Modify accordingly. The most important thing being the port, change from 80 to 1521
Then run the
firewall-cmd commands above to register the firewall changes.
You can now connect directly to the server by running:
SQLcl is the new command line interface that will replace SQL*Plus. This article describes how to install it on your local machine. You can use sqlcl then with the SQL Tunnelling technique covered above.
SQLcl is now an optional install. If installed you can run it locally on the machine, just like you would an
sqlplus command. Instead use
The default configuration of this project is to leave the TNS port (1521) closed. If this is the case, you will need to use an ssh tunnel to connect. This can be achieved either with your OS as described above, or by using the
sshtunnel command available in
If you haven't already generated an authentication key, you will want to do that first (on your client machine). This can be done with the command
ssh-keygen. The default key file should save to
$HOME/.ssh/id_rsa. Once that has been created, you will want to push that data to the server. This can be done with the command
ssh-copy-id (which will also allow you to ssh to the server without entering a password each time).
ssh-copy-id is utility that copies your public key to the server to the file $HOME/.ssh/authorized_keys where $HOME is the home directory of the user you are connecting to with your key file. If your system doesn't have
ssh-copy-id you just need to copy the contents of your public key file (typically $HOME/.ssh/id_rsa.pub) on your client machine to the file $HOME/.ssh/authorized_keys on the server. After copying the key, if you get the error:
Agent admitted failure to sign using the key. when trying to ssh to the machine, you may need to add the authentication key to the ssh agent. This can be done with the command
Once that has been completed, we will be able to use the
sshtunnel command in
sqlcl. For example, my servers IP address is 192.168.1.10; my key file is located at: /home/trent/.ssh/id_rsa.pub; the TNS port is 1521; and I want to map it to port 8888 on localhost. So, I launch
sqlcl with the
/nolog flag, and then run:
sshtunnel email@example.com -i /home/trent/.ssh/id_rsa -L 8888:localhost:1521.
Then I can connect to the database on localhost using the port 8888.
$ ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/home/trent/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/trent/.ssh/id_rsa. Your public key has been saved in /home/trent/.ssh/id_rsa.pub. The key fingerprint is: #output ommitted $ ssh-copy-id firstname.lastname@example.org /usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed /usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys email@example.com's password: Number of key(s) added: 1 Now try logging into the machine, with: "ssh 'firstname.lastname@example.org'" and check to make sure that only the key(s) you wanted were added. $ ./sql /nolog SQLcl: Release 220.127.116.11.177.0246 RC on Wed Aug 19 07:59:47 2015 Copyright (c) 1982, 2015, Oracle. All rights reserved. SQL> sshtunnel email@example.com -i /home/trent/.ssh/id_rsa -L 8888:localhost:1521 Passphrase for /home/trent/.ssh/id_rsa ************* SSH Tunnel connected SQL> connect oos_user/oracle@localhost:8888/xe Connected