Oracle Database Administration – Lab #1
Familiarizing with an Oracle Server
This laboratory is to introduce you to a number of Oracle RDBMS topics, concepts, and facts.
1. Study the Connecting to the SIUE Oracle Server document from the course web site. Logon to your account by using either PuTTy for a secure login session or the iSQL interface—both of these are described in the Connecting to an SIUE Oracle Serverdocument link found on this class web site. After logging on, use the pwd operating system command to answer the following questions:
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)?
What is the name of the disk drive (which mount point a01, a02, a03, etc) is used for your home directory?
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.
What is the path of the ORACLE_HOME subdirectory?
3. Examine the files on your home directory.
Use the ls –al operating system command to list the files on your home directory.
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 Serverdocument. If you don’t want to use the vi editor, you can alternatively use file transfer protocol (FTP) the file to your client computer and use Notepad to alter the file, then FTP the file back to your home directory on the Oracle server.
You need to understand that these new environment variables do not take effect until you log off and log on again. In order to prove to yourself that you have correctly modified the file, log off, then log on again and use the env command to examine the value of the environment variables.
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.
Check to see if you have a login.sqlfile 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
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.
Set the operating system permission levels for this 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.
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 oracle 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 file and copy it to your home subdirectory (the LINUX/UNIX command is cp init.ora $HOME/init.ora). Examine the init.ora file on your home subdirectory and answer these questions.
b. What is the database block size specified when creating a database with this init.ora file?
How many control files are defined and what are their names by default?
What version of Oracle is the init.ora file set up to support for compatibility of features?
What is the name of the tablespace that supports UNDO management?
6. The ORACLE2 database consists of multiple datafiles and other files. Explore the ORACLE2 server to find the location of the oracle database files (HINT: start at /a01/oradata and search the directory tree).
Across how many disk drives is the ORACLE2 database is distributed?
List the files by disk drive, file name, file size, and date last modified for files that store the following information: system tablespace, undo tablespace, and online-redo logs.
