File=module1-1-connecting_to_oracle.htm; updated: May 13, 2013
Module 1.1 Connecting to an SIUE Oracle Server Using PuTTY
This document is designed to assist you in getting started with using Oracle software provided by the Department of Computer Management and Information Systems. Topics covered:
§ Basic Information on Oracle software and the department servers.
§ Using PuTTY to create a secure telnet session.
§ Using WinSCP to create a secure copy (FTP) session.
§ Special information for CMIS 565 students.
The Department of Computer Management and Information Systems uses different Oracle databases to teach students about the use of Oracle software located on two database servers. These servers are named sobora1.isg.siue.edu and sobora2.isg.siue.edu.
· Both servers run the LINUX operating system.
· The IP addresses are 18.104.22.168 (sobora1) and 22.214.171.124 (sobora2).
The primary databases for student use on the sobora1 server are:
· ORACLE – this database supports students using the Oracle Developer Repository and Oracle Enterprise Manager.
· EMCMIS – this database stores the Enterprise Manager Repository. This repository can be used to manage databases such as those created by students in the CMIS565 course.
The primary databases for student use on the sobora2 server are:
· DBORCL – this is a general purpose database used by CMIS565 students. It also supports students learning the Structured Query Language (SQL). These classes are primarily CMIS450, 460, 470, 563, 564, and 570.
· Student Databases – each student group in CMIS565 will build their own Oracle database(s) on this server.
· EMCMIS – this database stores a second copy of the Enterprise Manager Repository.
Student accounts for your use in your course of enrollment will be distributed to you by your instructor in class.
Using PuTTY for a Secure Login Connection
Both On and Off Campus
In the past you may have connected remotely to a server or database through the use of Telnet. Unfortunately, Telnet does not provide a secure connection – information is transmitted in plain text and a computer hacker can steal a login/password combination. Additionally, to provide proper security, the Telnet software on CMIS departmental database servers is not enabled.
In order to connect to the operating system level on the one of our database servers, you must connect with a secure login session (termed SSH). You can use any SSH client to make your connection – if you don’t have one, we recommend a freeware product named PuTTY that is available by free download from the Internet. PuTTY is installed in the computer classrooms and laboratories in Founders Hall.
To connect to a departmental database using PuTTY you need two login accounts – one will enable you to connect to the computer at the operating system level. The second will enable you to connect to the actual database.
Operating System User Account
Both the sobora1 and sobora2 servers have been set up so that you can use your SIUE EID (your email account) to access the server, but ONLY during the term in which you are enrolled in a CMIS course requiring access to one of the databases. If you don’t remember your EID and password, you will need to go to the student services center at the Office of Information and Technology in the basement of the Communications Building to obtain information about your account. Alternatively, you can update your EID and password through the following online link: https://oitam.isg.siue.edu/~eid/cgi-bin/e-ID/
Database Login User Name and Password
Your instructor will distribute a database login user name and password in class. These login accounts are named USER001, USER002, USER003, etc (or some similar name). The database login user name is only active for the term in which you are enrolled.
Obtaining a Copy of PuTTY
PuTTY is a freeware
package that was developed Simon Tatham and is
supported by Simon and a small team based in
After the Download
Depending on how much of the PuTTY product you download, you may find that several products have been added to your system like the ones shown in the figure below. If you downloaded more than just the PuTTY.Exe file, or if you saved the file to a location other than the desktop, then you may wish to create a desktop shortcut to enable you to access PuTTY
Configuring PuTTY to Connect to a Server
The next figure shows you how to configure PuTTY to connect to the sobora2 server. This same approach applies to the sobora1 server.
· Double-click the PuTTY.EXE file to open the PuTTY Configuration window shown below – the Session category window should be selected.
· Host name – enter: sobora2.siue.edu or the IP address 126.96.36.199. If you fail to connect with one of these, you may try sobora2.isg.siue.edu.
· Port – 22.
· Protocol – select the SSH option.
· Saved Sessions – use this textbox to enter a name for the session. Click the Save button to save the session information. The next time you use PuTTY, you can simply double-click the session name in order to complete your connection to the server. In the figure shown below the session has been saved with the name SOBORA2. Saving the session name is optional – you do not have to save it.
· Click the Open button to complete the connection.
If you explore the various links under the Category list box you’ll discover that you can modify the appearance and other features to suit your personal preferences.
If you enter the IP address to make the connection, you will probably see an alert like the one shown in the next figure. Click the Yes button to confirm that you trust the sobora2 server as a host.
Logging in to the Operating System
When PuTTY begins to execute, you are provided a resizable window like the one shown below. Here is where you will initially enter your SIUE EID user name and password in order to connect to the LINUX operating system. If your login is a success, you will normally see a $ (dollar sign) as the LINUX prompt indicating the operating system is ready for you to type a command. This has been modified on the sobora2 server shown in this figure to display your SIUE EID plus the “@” symbol plus the full server name.
Remember that the LINUX operating system is case sensitive – so your computer account user name and password must be typed exactly in order to successfully connect.
Oracle System Identifier (Database Identifier)
When you login, the operating system prompts you to enter a value for the Oracle System Identifier (also called the Oracle SID). This is ABSOLUTELY CRITICAL! If you want to connect to the database named DBORCL, then you must type the word DBORCL as requested at the operating system prompt (all capital letters). The default value displayed for the ORACLE_SID is your SIUE EID, but you may need to change this in order to connect to the database desired. Think of it this way – you are specifying the name of the database to which you wish to connect – can you connect to a database named with your user name if that database doesn’t actually exist??? The answer is NO – so remember to type DBORCL some other value depending on the database identifier to which you need connection.
There’s a good reason for asking you the database name to which you need to connect – CMIS 565 students build their own database. They will name the database following guidelines distributed in the CMIS 565 class – none of their databases will be named DBORCL. If you are a CMIS 565 student desiring to connect to your database, then you must enter the name of YOUR database, not DBORCL.
Using SQL*Plus with PuTTY
The SQL*Plus software provided with the Oracle relational database management system is an extension of the ISO/ANSI standard structured query language. SQL*Plus enables you to work with an oracle database to write queries that retrieve information, statements that modify the structure of a database, and execute scripts that contain a series of SQL*Plus commands.
Oracle’s SQL*Plus software is used primarily to write database queries. To start using SQL*Plus you issue the name of the program at the LINUX operating system prompt. Note that SQL*Plus is entered in lower case (sqlplus) as shown in the series of commands shown below.
SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 27 15:27:14 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: dbock
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter your Oracle login user name and Oracle password (remember that these accounts will be distributed by your instructor in class). A successful login results in the display of the SQL> prompt. You have just successfully connected to the database and are now ready to use Oracle’s SQL*Plus software to type SQL queries.
If you are unable to connect to a specific database and receive a system message that Oracle is not available there are two possibilities: (1) the value for the Oracle_SID is not correct – go back to the above section and logout then login in again and type the correct name for ORACLE_SID, or (2) the database has been shutdown either inadvertently, the server is down, or the database is offline for system backup. You may send an email to your instructor to check the status of the database and server.
Setting the Number of Lines of Output from SQL*Plus
When you issue a command in SQL*Plus, such as a SELECT command, the database may return more lines of output than will fit onto the computer monitor screen. This will cause scrolling. One way to limit the output is to set the pagesize variable within SQL*Plus. You will probably want to automate this so that the page size is automatically set each time you connect to SQL*Plus, but how do you accomplish this?
Did you notice the message login.sql loaded in the above output listing? Each time you connect to SQL*Plus, Oracle checks to determine if you have a login.sql file in the current working directory (the current directory when you connect is your home directory unless you’ve changed it). If the file exists, then SQL*Plus processes the commands stored in this file during the load process.
There are two ways to create the file. You can create it with Windows Notepad (the file must be a plain text file so using Microsoft Word or Wordpad WILL NOT work), or you can use the LINUX vi editor to create the file. The file must be named login.sql, not login.sql.txt or some other name. The exact name must match or else SQL*Plus will not process the file. If you use Windows Notepad to create the file, you will need to transfer the file from your client computer to your assigned user directory on the server (either sobora1 or sobora2 depending on the server assigned for your class work) – the steps on using file transfer protocol (FTP) to transfer files to the server are covered later in this document.
The login.sql file needs to contain the following commands at a minimum. You may wish to experiment with the pagesize because your client computer may display more than 23 lines depending on the size font that you have set as your default within PuTTY.
prompt login.sql loaded.
pause 'More . . .'
set pagesize 23
set pause on
define_editor = "vi"
Changing Your Database Login User Name
You cannot change your SIUE EID except as required by SIUE – for CMIS databases, your SIUE EID is your LINUX login user name and password. However, you can change your database login password and you should do this when you first connect to a database such as DBORCL when using SQL*Plus in order to prevent other students from connecting to your account.
While connected to SQL*Plus, use the Alter User command to change your password within the database. A sample command is shown here. Replace USER001 with your assigned login name and new_password with the new password that you wish to use.
ALTER USER user001 IDENTIFIED BY new_password;
ALTER USER user061 IDENTIFIED BY my_secret_word1576;
Quitting SQL*Plus and the Database Server
Type the EXIT command to quit SQL*Plus. Type the EXIT command again to quit the database server and to close your PuTTY session.
Transferring Files (File Transfer Protocol – FTP) To/From a Database Server
You will often need to transfer files from your client computer to your assigned working directory on the sobora1 or sobora2 server. An example is the login.sql file described in the previous section. Another example might be a script file that you create that contains SQL code as a series of SQL*Plus statements. It is much easier to work with Windows Notepad to write SQL code than it is to use the vi editor provided with LINUX.
In order to transfer files you need to use client software that provides for secure copying of documents to and from your assigned database server – a secure form of FTP.
While you can use any secure SCP (secure copy) software, we recommend the WinSCP freeware product that is available from many different sites on the Internet. You can simply search on WinSCP and download and install the product to the client computer.
During installation of WinSCP you can select the user interface – we recommend a Norton Commander style of user interface as it is very easy to use.
Connecting to the SOBORA2 Server with WinSCP
When you first login with WinSCP, you will need to enter session information that the software needs in order to complete the connection. The figure shown below provides an example.
· Host name: sobora2.siue.edu
· Port number: 22
· User name: Your SIUE EID account login user name
· Password: Your SIUE EID account password.
· Protocol: Select the SFTP (allow SCP fallback).
· Save button: Click this to save the session entries.
· Login button: Click this to complete your connection.
After you connect to the server, you will find that the user interface (if you selected the Norton Commander interface) has two panels as shown in the next figure. The left panel displays file information for the client computer while the right panel displays file information for the server. You can explore the menus and shortcut buttons in order to discover how to change directories (folders) on the client and server computers.
Files are transferred from the client to the server (or the other way around) by simple drag-and-drop. Use the mouse to select a file to be transferred (copied) and drag the file from one panel to the other. This will cause a Copy window like the one shown below to display. The window has many options to enable you to specify whether to rename the file, transfer text or binary – I have had trouble using Automatic to let the software decide whether the file is text or binary—it doesn't seem to work consistently. So, when transferring plain text files that contain script code you wish to execute, you should select Text as a Transfer mode – see the figure below. Clicking the Copy button completes the transfer.
CMIS 565 Students
The remainder of this document pertains to students enrolled in CMIS 565 Oracle Database Administration.
Your DBA Privileges and Responsibilities
As a student in the database administration course, you are granted special privileges. Your login account is designated as a member of the dba group. This means you have all of the privileges authorized to a database administer as well as the responsibilities. You can create a database – you can also damage one. Each of you will be building a database and you must act with professionalism.
Operating System Environment Variables
When you use the Oracle RDBMS software through a secure SSH PuTTY connection, you will initially need to check that the operating system environment variables listed below have been established for your computer account. You will do this as part of one of your first graded lab assignments for the CMIS 565 course. These environment variables link the operating system and Oracle RDBMS software to the database that you will use (either DBORCL or a database system identifier of your own creation). Generally, your account will have these environment variables already set up, but you need to be familiar with them in order to understand some of the requirements for using Oracle when you leave the university environment.
You may check the environment variable settings by typing the UNIX env command at the operating system command prompt. The particular environment variables we are concerned with are:
· ORACLE_HOME – specifies the home subdirectory location of the Oracle RDBMS software.
· ORACLE_SID – specifies the name of the database to which you will connect. If you are connecting to the DBORCL database, then the ORACLE_SID value will be set to this value (all capital letters).
· ORACLE_TERM – specifies the type of computer terminal you are logged onto or which you are emulating from a PC.
· PATH – specifies the path for your account to various Oracle software server subdirectories. Programs and files on these subdirectories can be executed directly without specifying the subdirectory location as part of the execution command.
· LD_LIBRARY_PATH – specifies a path to the load library for your server account. It enables you to run various Oracle programs such as SQL*Plus without specifying the subdirectory location as part of the execution command.
· ORACLE_BASE – specifies the location of the base subdirectory for Oracle RDBMS software. This variable is optional.
You may set values for environment variables by entering them directly at the operating system command prompt. The commands shown below provide examples that show you how to set values for environment variables for the version of the LINUX shell that we are using. Do not type the information shown in brackets.
<Your specified Oracle Sid> [you will be assigned this name in class]
ORACLE_TERM=vt100 [This sets the terminal emulation for the
client to vt100 emulation]
The listing shown below gives a typical display as output from the env command. If you discover that an environment variable is wrong, then you can modify the .profile file that is located on your home directory for your server account. This can be accomplished by using a text editor such as vi to edit your .profile file, or by FTPing the file to your client computer, using Notepad to edit the file, then FTPing the file back to your Oracle home directory. ALWAYS MAKE A BACKUP COPY OF ANY SYSTEM FILE THAT YOU EDIT BEFORE EDITING IT.
SSH_CONNECTION=::ffff:188.8.131.52 2884 ::ffff:184.108.40.206 22
SSH_CLIENT=::ffff:220.127.116.11 2884 22
You can access your server home directory with the operating system command: cd $HOME. Note that this command must be typed in caps as shown. All environment variables are capitalized by convention. In order to refer to the contents of the variable, you preface the variable name with the $ symbol.
You can confirm you have a .profile file by listing the files on your home directory with the operating system command:
END OF NOTES.