cmis565_lab2.htm; updated May 27, 2013


Oracle Database Administration – Lab #2

Creating a Database (30 points)


Situation:  As part of your work this term you will implement an Oracle database in a LINUX/UNIX operating system environment.  This database will store data for a limited customer order-entry database. 


You may elect to complete the laboratory individually or you may team up with 1 other laboratory partner.  If you team up with a partner, you select your own partner and you will be graded as a group.  Groups only need to submit one copy of each required laboratory report and need only create a single database that the group will share for the remainder of the course.  Groups may also elect to create more than one database, one for each group member.  The database(s) created will support your work on future laboratories, so it is essential that you complete this laboratory successfully.


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 (example 2a) in bold print.

·         Show each command you execute and the computer's response (example response:  Database Altered, Tablespace Created, etc.).  If you don't show the commands you execute, then your answer will be counted as wrong.  Some questions do not require you to execute any commands – your report lab does not need to provide any output for those questions.

·         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.   


Note:  -1 point for not using the proper front and formatting your output professionally.

Tasks for this Lab


General Information


DATABASE LOCATION: Your database must be Oracle Flexible Architecture (OFA) compliant.  Each student is allocated disk space on three disk drives (mount points) named /u01, /u02, and /u03.  On each disk drive is a student directory with your SIUE EID account name, e.g., if your EID is dbock, you will have space allocated on /u01/student/dbock.


Initially your database will have five tablespaces – one each for SYSTEM, SYSAUX, UNDO01, TEMP, and USERS and so it will need five files to store these tablespaces, one per tablespace as a minimum.  Your database will also include redo log files, control files, and an init.ora file.  You will comply with OFA guidelines for naming all files associated with your database, e.g., USER301system01.dbf, USER301sysaux01.dbf, initUSER301.ora, etc. – note that OFA requires incorporating the database name within the name of each file that is part of the database.


POSSIBLE ERRORS:   While creating your database you will create various files (as named above).  If you make an error and create the file in the wrong location, do not attempt to use an operating system copy command (cp) to copy the file to a new location - this WILL NOT work and will corrupt your database. 


You can only move datafiles using the procedure specified the set of notes named "Module 4—Tablespaces and Datafiles" - if you corrupt your database, you will have to redo this lab in order to create a new, working database. 


If you have to redo the lab, you must ensure the database instance is shutdown (the command SHUTDOWN ABORT should accomplish this), and then use operating system commands to delete all of the database files that you created (but not the init.ora file) before you  start over in your efforts to create your database.




1.    Logon to your account (2 points total).  When you logon, you will be at the home directory location for your account.  For example, if your account name is dbockstd, then you will be at location:  /u01/student/dbockstd. 

a.    Use the cd .. operating system command to move up the subdirectory tree to the next higher subdirectory.  Check the security permission settings on your account (use the ls -al | more operating system command).  The default setting is usually 700 as shown below.


drwx------   2 dbockstd dba  4096 May 20 14:34 dbockstd


b.    The permission settings need to be changed to 775.  If the permission settings are not 775, use the chmod 775 dbockstd (substitute your username) command to set the permissions to 775.  This is to allow other members of the dba group to write to your subdirectory.  This is necessary because Oracle is also a system user and is a member of the dba group.  Oracle must write to your subdirectory when creating a database.  Do this for each of your subdirectories on the /u01, /u02, and /u03 disk drives.

c.    Create a directory named oradata inside your $HOME directory on each of the disk drives (/u01 through /u03).  The command is mkdir oradata.  Set the permissions to 775 for each of the oradata directories.

2.  Set the ORACLE_SID and LD_LIBRARY variable values (2 points).

a.    Return to your home directory (cd $HOME).  In class you were assigned an account name to use for your ORACLE_SID, e.g. if your account is USER301, your ORACLE_SID will be set to USER301.  This will also be the value for the DB_NAME parameter in your init.ora file. Ensure your ORACLE_SID value is currect.  Use the env command to list the values of all environment variables to check that you have set the ORACLE_SID correctly.





When you completed Lab 1, you set various environment variables such as the LD_LIBRARY in your .profile file so that the environment variables are set automatically while working with the database you will create.  Ensure that these variable values are correct by screening the output of the env command. 

b.    Currently our student logon scripts ask you to enter the ORACLE_SID during logon.  You can ensure that the ORACLE_SID is correct by setting the value in the .profile file.  You can accomplish this by adding operating system commands to set this environment variable (see above) in the .profile file.  List the contents of the .profile file in your lab report. 

c.    Now, logoff and login again.  Use the operating system env command to verify the value of ORACLE_SID.  Remember that if you alter your .profile file, you must exit Oracle and logon again for the changes to take effect.

3.  Create the init.ora parameter file (pfile) (3 points total).

a.    (1 point)  Create a parameter file named init<username>.ora (for example, if your user account is USER301, then name the file initUSER301.ora).   This file needs to be stored to your $HOME directory.  Edit the CONTROL_FILES parameter in your initUSER301.ora file.  Naming your control files and their location through the CONTROL_FILES parameter causes these files to be created when you create the database.  Initially you should set the CONTROL_FILES parameter to create ONLY TWO control files named USER301control01.ctl and USER301control02.ctl (replace USER301 with your own user account) – later you will create a third control file as part of another laboratory assignment.  The system will establish an appropriate size for these files. 

b.    (1 point).  Ensure that you create the directories and set directory permissions for other directory locations required for your database within your assigned disk space – do not write to the DBORCL database’s trace file locations.  You can determine what those directories are by studying the notes supplied for this section of the course.  Ensure that the directory locations are entered correctly in your initUSER301.ora file.

c.    Do NOT include the parameter for the remote_login_passwordfile=EXCLUSIVE  parameter as you have not yet created an Oracle Password File for your database.


4.  Create a script that will create the database including the SYSTEM, SYSAUX, TEMP, and UNDO01 tablespaces (5 points).

a.    Create a script that includes the CREATE DATABASE command.  You must manually create your database.  Use the guidelines in Module 5 notes to specify the redo log files and sizes for these tablespaces.  The script should set passwords that you select for the SYS and SYSTEM special user accounts as part of your CREATE DATABASE.  Print a copy of the script as part of your lab report.


5.  Connect to SQL*Plus and startup your database instance in nomount mode.  Execute the script to create the database (11 points total).

a.    During execution of the script, you will receive no feedback from the SOBORA2 server – you’ll have to wait to see if you receive the Database Created message.  Display any feedback messages indicating successful creation of the database in your lab report.

b.    After execution, check to ensure that your database files (tablespace datafiles, control files, and redo log files) were created.  Show the operating system commands that you used to list the database files along with the operating system response to those commands in your lab report.

c.    Check the alert_sid.log file (e.g., alert_USER301.log) to ensure that no errors occurred during database creation.  List the location of your alert_sid.log in your lab report.


6.  Run the Scripts to create the data dictionary.

a.    You must be connected as the user SYS to run the scripts—this will ensure that the data dictionary is owned by the user SYS.  The scripts should not be run unattended.  Each script may take anywhere from 3 to 30 minutes to run, depending on the load on the server.  Do not show the entire output of the scripts.  Just show the last line of output displayed by the system when the script finishes running in your lab report.


$ sqlplus /nolog

SQL> connect sys as sysbda

SQL> Password:  <press the enter key here>

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql


b.   Verify that the objects in the data dictionary are valid.  The following query returns any invalid objects.


SELECT owner, object_name, object_type

FROM dba_objects

WHERE status = 'INVALID'

ORDER BY owner, object_type, object_name;


If any objects are invalid, shutdown your Oracle Instance, then delete all of the files you created that comprise your database and try again to create the database.


7.  Running the Product User Profile (1 point total).

a.    Build the user profiles that Oracle will use on LINUX for your database.  You must be connected as the user SYSTEM when you run the script.


SQL> connect system/<password>

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql


b.  Show the last line of output displayed by the system when the script finishes running in your lab report.  Use the Exit command to return to SQLPLUS.


8.  Create the USERS Tablespace (1 point).

a.    Connect as the user SYS.

b.    Create a USERS tablespace that is 5M in size.  Remember to place this tablespace to meet OFA guidelines.


9.  Create a DBA user (2 points total).

a.    Connect as the user SYS.

b.    It is an unacceptable practice to manage an Oracle database by using the users SYS or SYSTEM.  You need a DBA account for your database.  Create a DBA user named userXXX where you use your account name for the account that will be the DBA for your database.  This account is created while connected to SQLPLUS.  The command is:





  1. Create a second DBA user account that I as your instructor can use to grade your work.  My DBA account must be named dbock and a password of "password".
  2. (1 point) Examine the system table named sys.dba_roles and list all of the roles shown in the table.  Grant the CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE roles listed to your DBA user account and to the dbock user account.  Since the DBA will create other users, you need for the DBA accounts to have the admin option necessary to grant these roles to other users.  An example grant command is:




e.    Logoff SQLPLUS and reconnect as the user dbock.  Confirm that dbock has DBA privileges by executing a DESC DBA_TABLESPACES command.  If the view is described, then the user dbock works.  If the view is not described, run the synonyms SQL script named catdbsyn.sql (located where the other scripts are located). 

f.     Logoff SQLPLUS and reconnect as the DBA user that you created for yourself (the equivalent to USER301).  If necessary, run the synonyms SQL script named catdbsyn.sql again, this time as the DBA user you created.


10.  Connect as the user SYS and shutdown the database (-3 points if you forget to shutdown). 


Do not leave your database running between laboratory sessions.