File=cmis565_lab1.htm; May 21, 2013


Oracle Database Administration – Lab #1

 Familiarizing with an Oracle Server (10 points)


This laboratory is to introduce you to a number of Oracle RDBMS topics, concepts, and facts.  Complete the exercises given below on an individual basis and submit this for grading.  The purpose of the laboratory is to cause you to explore an Oracle database and server.


Use Notepad, Wordpad, or Microsoft Word to document your solution to the tasks in the laboratory. 

·         Label your answer to each clearly by typing the number/letter of the question (example 2a) in bold print.

·         Show each command you execute and the computer's response (example response:  File Saved, etc.).  If you don't show the commands you execute, then your answer will be counted as wrong.

·         Print your output using 10 point Courier or Courier New font (so the output lines up and is easy to read).

·         Format your output as necessary to make it easy to read.  Do not submit for grading anything you would not submit to your boss for review in an actual work environment, i.e. in other words, do not turn in sloppy work that is difficult for me to grade.   


Tasks for this Lab


1.  Study the Connecting to an SIUE Oracle Server document available by hyperlink from the course web site.  Logon to your SIUE EID account on the SOBORA2 server by using PuTTy for a secure login session.   After logging on, use the pwd operating system command to answer the following questions:

a.    What is your home directory (include the path) (hint: if you’re not on your home directory, you can use the cd $HOME command to navigate to your home directory)? List your answer in your lab report.

b.    What is the name of the disk drive (which mount point – u01, u02, etc) that is used for your home directory?  List your answer in your lab report.


2.  In order to use Oracle software your operating system account must have a path entry that points to the location of the Oracle executable files.  The location of these files is in the bin subdirectory of the ORACLE_HOME subdirectory.  The variable ORACLE_HOME is defined as a public environment variable.  You can examine the environment variables with the env operating system command. 

a.    What is the path of the ORACLE_HOME subdirectory? List your answer in your lab report.

b.    What is the ORACLE_HOME value?  List your answer in your lab report.


3. Examine the files on your home directory.

a.    Use the ls –al operating system command to list the files on your home directory.  Produce a list of the files located on your home directory in your lab report.  List your answer in your lab report.

b.    Use the vi editor to alter the file named .profile (dot profile) that is stored on your home directory to define (add) environment variable definitions for the ORACLE_TERM and LD_LIBRARY_PATH variables.  These variables are explained in the Connecting to an SIUE Oracle Server document.  If you don’t want to use the vi editor, you can alternatively use file transfer protocol (FTP) to transfer the file to your client computer and use Notepad to alter the file, and then FTP the file back to your home directory on the Oracle server.  IMPORTANT! Before you alter the file, make a backup copy (name it something like .profile_backup) in case you damage the file and need to start over.

c.    If the ORACLE_HOME value in question #2 is not /u01/app/oracle/product/11.2.0/dbhome_1, then use the vi editor and correct it to this value.  You will need to edit the  .profile file.  Print a copy of the contents of your .profile file as part of your lab report.  You need to understand that these new environment variables do not take effect until you log off and log on again. 

d.    Log off, then log on again.  Now use the env command to examine the value of the environment variables.  List the output produced by the env command in your lab report. 


4.  One way to automate everyday tasks for Oracle is to create a login.sql file that will contain commands that Oracle reads and remembers every time you start Oracle. 

a.    Check to see if you have a login.sql file on your home directory.  The file should contain the commands listed below at a minimum.  

prompt login.sql loaded.
define _editor="vi"
set pause 'More...'
set pause on

set pagesize 23 <you can use a larger number if you desire>


If you do not have a login.sql file on your home directory, create one.  You may use the vi editor or Windows Notepad to create the file—if you use Notepad, you will need to FTP the file to your home directory on the Oracle server.

b.    Set the operating system permission levels for the login.sql file using the operating system command:  chmod 755 login.sql (all in lower case letters).  If you are not familiar with the vi editor or LINUX/UNIX commands, obtain a copy of the help sheets on these from the Reference Handouts section of the course home page syllabus.  Use the ls –al command to display the system permission settings for the login.sql file and add this output to your lab report.


5.  Move to the ORACLE_HOME subdirectory.  Examine the other subdirectories under ORACLE_HOME.  Look in the dbs subdirectory that stores the PFILE for the DBORCL database.  The example PFILE is named init.ora.   The init.ora file is a template that you can use to create your own PFILE. 

a.    Locate the init.ora and initDBORCL.ora files and copy both of these files to your home subdirectory (the LINUX/UNIX command to copy a file is cp init.ora $HOME/init.ora).  Save the files for a later lab.  Examine the initDBORCL.ora file on your home subdirectory and answer these questions.

b.    What is the database block size specified for the DBORCL database with this init.ora file?

c.    How many control files are defined and what are their names?

d.    What version of Oracle is this database set to support for compatibility of features?

e.    What is the name of the tablespace that supports UNDO management?


6.  The DBORCL database consists of multiple datafiles and other files.  Explore the server to find the location of the DBORCL database files (HINT:  start at /u01/oradata and search the directory tree). 

a.    Across how many disk drives is the DBORCL database is distributed? 

b.    List ONLY the files by disk drive, file name, file size, and date last modified that store the following information:  system tablespace, undo tablespace, and online-redo logs.