file=cmis565.htm; updated, May 22, 2013

 

COURSE OUTLINE - Southern Illinois University at Edwardsville - School of Business

CMIS 565 Oracle Database Administration - 3 Semester Hours Credit


Dr. Douglas Bock, Bldg. II, Room 2333, Office Phone: 618-650-2504, Office: Wednesday (3:30-5:45 pm) and by appointment.  E-mail: dbock@siue.edu; Course Web site: 
http://www.siue.edu/~dbock.

 

REQUIRED TEXTBOOK:  All textbook material references are online at the Oracle On-line Technical Reference Site.  If the system requests a Username and Password, you should create your own combination Username/Password.  There is no textbook to purchase for this course.

 

·         Oracle Database Documentation Library (11gR2) –the main link to documentation for Oracle 11g Release 2.

·         Oracle Database Administrator's Guide (11gR2)  

·         Oracle Database Security Guide (11gR2)

·         Oracle Database Concepts (11gR2)

·         Oracle Database Error Messages (11gR2)

·         Oracle Database Net Services Administrator's Guide (11gR2)

·         Oracle Database Backup and Recovery User’s Guide (11gR2)

·         Oracle Database SQL Language Reference (11gR2)

·         Oracle Database Installation Guide (11gR2) for Linux

·         Oracle Database Performance Tuning Guide (11gR2)

·         Oracle Data Guard Concepts and Administration (11gR2)

 


Reference Handouts

 

·         vi Editor - subset of helpful vi editor commands.

·         LINUX Commands - subset of helpful LINUX commands.

·         Oracle Product_User Profile - instructions on creating the tables to store this information.

 


DETAILED COURSE OUTLINE

 

To view the web notes and required readings that accompany class lectures, click the hyperlinks given.

 

Week 1, Session 1 – May 22

 

0.  Course Introduction.  Course objectives; Familiarization with the Oracle10g DBMS software; Database administrator duties. 

·         Web Notes: Module 0-Introduction (20 minutes).

·         Reading:  Oracle Database Administrator's Guide – Chapter 1:  Getting Started with Database Administration.

 

1.  Oracle Architecture.  Oracle server; Oracle Instance; Memory and physical structures; Server Process; Background Processes. 

·         Web Notes:  Module 1-Archtecture (1.5 hours).

·         Web Notes:  Module 1-1-Connecting to an SIUE Oracle Server.

·         PuTTY Color Settings – document provides guidance on setting the color configuration for this software.

·         Reading:  Oracle Database Concepts – Chapter 14: Memory Architecture.

·         Reading:  Oracle Database Concepts – Chapter 15:  Process Architecture.

 

2.  Oracle Server.  Overview; Database Installation; SQL Plus. 

·         Web Notes:  Module 2-Oracle Server (30 minutes).

·         Reading:  Oracle Database Concepts – Chapter 1: Introduction to Oracle Database (read this by skimming – most of the concepts in this reading will be covered in detail as the course proceeds – I will not lecture directly on many of the topics covered here during Module 2).

·         Reading:  Oracle Database Installation Guide – Chapter 1:  Overview of Oracle Database Installation.

·         Reading:  Oracle Database Concepts – Chapter 7: SQL.

 

Monday, May 27 is Memorial Day, a national holiday.  Classes are not held that day.

 

Week 2, Session 2 – May 29

 

3.  Startup and Shutdown of a Database.   Initialization parameter files including the pfile and spfile; Startup command and database states; Shutdown command; Log and Trace files. 

·         Web Notes:  Module 3-Database Startup (45 minutes).

·         Reading:  Oracle Database Administrator's Guide – Chapter 3: Starting Up and Shutting Down (skip the section on Quiescing a Database). 

·         Reading:  Oracle Database Concepts – Chapter 13: Database Instance.  Read the first two sections: Introduction to the Oracle Database Instance and Overview of Instance Startup and Shutdown.

·         Lab #1 Familiarizing with an Oracle Server (10 points) – submit the lab.

 

4.  Tablespaces and Datafiles.  Types of tablespaces; Creating tablespaces; Space management; Locally managed versus dictionary managed tablespaces; Undo tablespace; Temporary tablespace; Online versus Offline; Resizing tablespaces and datafiles; Adding datafiles; Moving datafiles; Dropping tablespaces. 

·         Web Notes:  Module 4-Tablespaces and Datafiles (1 hour, 30 minutes).

·         Reading:  Oracle Database Administrator's Guide – Chapter 14: Managing Tablespaces and Chapter 15: Managing Datafiles and Tempfiles.

 

5.  Creating an Oracle Database.  Optimal Flexible Architecture (OFA); Oracle software and file locations; DBA authentication methods; Creating a database manually; Creating a database using Oracle Managed Files (OMF);  Post-database creation tasks. 

·         Web Notes:  Module 5-Create Database (1 hour).

·         Reading:  Oracle Database Administrator's Guide – Chapter 2: Creating and Configuring an Oracle Database (focus on the manual creation of a database).

 

Week 3, Session 3 – June 5

 

6.  Data Dictionary and Dynamic Performance Views.   Base tables and views; Using the data dictionary; Dynamic performance tables. 

·         Web Notes:  Module 6-Data Dictionary (30 minutes).

·         Reading:  Oracle Database Concepts – Chapter 7: The Data Dictionary and Dynamic Performance Views.

·         Lab #2 Creating a Database (30 points) – submit the lab.

·         Lab #3 Tablespace and Datafile Management (10 points) – submit the lab.

 

7.  The Control File.  Control file contents; Multiplexing the control file. 

·         Web Notes:  Module 7-Control File (30 minutes).

·         Reading:  Oracle Database Administrator's Guide – Chapter 11: Managing Control Files.

 

8.  Redo Log Files.  Using Redo Log files; Redo Log file structure; Switches and Checkpoints; Adding Groups; Adding Group Members; Dropping Redo Log files and Groups; Relocating and Renaming; Archiving. 

·         Web Notes:  Module 8-Redo Log (40 minutes)

·         Reading:  Oracle Database Administrator's Guide – Chapter 12: Managing the Redo Log  and Chapter 13: Managing Archived Redo Logs.

 

Week 4, Session 4 – June 12

 

9.  Storage Structures.  Segment types; Storage clauses; Extent allocation and De-allocation; Database block and block management; Block space usage. 

·         Web Notes:  Module 9-Storage Structures (1 hour, 15 minutes).

·         Reading:  Oracle Database Concepts – Chapter 12: Logical Storage Structures.

·          Lab #4 Redo Log and Control File Multiplexing, Archive Redo Log Files, SPFile (10 points) – submit the lab.

 

10.  Managing Undo Data.  Undo segment; Read consistency; Automatic undo management including: configuration, initialization parameters, the Undo tablespace, and altering, switching, and dropping Undo tablespaces; Sizing Undo tablespace; Obtaining undo segment information. 

·         Web Notes:  Module 10-Undo Management (1 hour, 15 minutes).

·         Reading:  Oracle Database Administrator's Guide – Chapter 16: Managing Undo.

 

Week 5, Session 5 – June 19 – MIDTERM EXAMINATION

 

Week 6, Session 6 – June 26

 

11.  Tables, Clusters, and Data Integrity.  Data types; ROWID; Row structure; Create table command; Setting PCTFREE and PCTUSED parameters; Row migration/chaining; Storage/block utilization parameters; Allocating extents; Table truncation; Dropping a table; Clusters—When to use both indexed and hashed clusters; creating, altering and dropping clusters; Obtaining table information; Constraint types; Primary key enforcement; Foreign key enforcement; Enabling constraints; Obtaining constraint information.

·         Web Notes:  Module 11-Tables and Clusters (2 hours).

·         Reading:  Oracle Database Administrator's Guide – Chapter 20: Managing Tables, Chapter 22: Managing Clusters, and Chapter 23:  Managing Hash Clusters.

·         Reading:  Oracle Database Concepts – Chapter 5: Data Integrity.

·        Lab #5 Tables, Indexes, Integrity Constraints, Clusters and Undo Tablespaces (40 points) – start the lab now.

 

12.  Indexes; Partitioned Tables and Indexes.  B-tree indexes; Bitmap indexes; Create index command; Index storage parameters; Allocating/deallocating index storage space; Rebuilding indexes; Coalescing indexes; Dropping indexes; Obtaining index information; Partitioning methods; Partitioning indexes; Partitioning to improve performance.

·         Web Notes:  Module 12-Indexes (1 hour).

·         Reading:  Oracle Database Administrator's Guide – Chapter 21: Managing Indexes.

·         Reading:  Oracle Database Concepts – Chapter 4:  Partitions, Views, and Other Schema Objects.  Read only the section Overview of Partitions

 

Week 7, Session 7 – July 3

 

13.  Profiles and Resources.  Profile creation; Profile alteration; Dropping a profile; Enabling and setting resource limits; Password protection, history, locking, expiration, aging, and history; Obtaining password and resource information. 

·         Web Notes: Module 13-Profiles and Resources (30 minutes).

·         Reading:  Oracle Database Security Guide – Chapter 2: Securing the Database Installation, Chapter 3:  Securing Oracle Database User Accounts.

·         Reading:  Oracle Database Administrator's Guide – Chapter 27:  Managing Resources with Oracle Database Resource Manager.

·         Lab #5 Tables, Indexes, Integrity Constraints, Clusters and Undo Tablespaces (40 points) – submit the lab.

 

14.  User Accounts, Privileges, and Roles.  User schema; Create user command; User authentication; User tablespace quotas; Dropping a user; Obtaining user information; System privileges; Granting system privileges; SYSDBA and SYSOPER privileges; Revoking system privileges; Object privileges; Granting object privileges; Revoking object privileges; Obtaining privilege information; What are roles; Managing roles; Create role command; Modifying roles; Assigning roles; Default roles; Revoking roles; Removing roles; Obtaining role information.

·         Web Notes:  Module 14-1 User Accounts (1 hour).

·         Web Notes:  Module 14-2 Privileges (1 hour).

·         Web Notes:  Module 14-3 Roles (1 hour).

·         Reading:  Oracle Database Concepts – Chapter 17 Topics for Database Administrators.  Read only the section Overview of Database Security – this section briefly covers privilege and role authorization.

·         Reading:  Oracle Database Security Guide – Chapter 4:  Managing User Privileges.

·         Lab # 6 Profiles, Users, Roles, and Privileges (20 points) – start the lab now.

 

Thursday, July 4 is Independence Day, a national holiday.  Classes are not held that day.

 

Week 8, Session 8 – July 10

 

15.  Synonyms, Sequences, and Views. 

·        Web Notes: Module 15-Synonyms, Sequences, and Views (1 hour).

·         Reading:  Oracle Database Administrator's Guide – Chapter 24: Managing Views, Sequences, and Synonyms.

·         Lab # 6 Profiles, Users, Roles, and Privileges (20 points) – submit the lab.

 

16.  Backup and Recovery.  Logical Backups (exporting and importing); Physical backups (cold and hot backups); Implementation of backup procedures; Integration of backup procedures.

·         Web Notes: Module 16-Backup and Recovery (2.5 hours).

·         Reading:  Oracle Backup and Recovery Basics – Chapter 1: Introduction to Backup and Recovery and Chapter 2: Getting Started with RMAN.

·         Web Notes: Module 16b-Recovery Manager (1 hour).

·         Reading:  Oracle Database Backup and Recovery User’s Guide – Chapter 8: RMAN Backup Concepts, Chapter 9: Backing Up the Database, Chapter 17: Performing Complete Database Recovery.

 

Week 9, Session 9 – July 17

 

17.  Oracle Network Administration.

·         Web Notes:  Module 17-Network Administration (1 hour).

·         Reading:  Oracle Net Services Administrator's Guide – Chapter 1: Introducing Oracle Net Services, Chapter 6: Quick Start to Oracle Net Services, Chapter 7: Managing Oracle Net Services, Chapter 8: Configuring Naming Methods (focus on the section Configuring the Local Naming Method). 

·         Lab #7 Creating Synonyms, Sequences, and Views (10 points) – submit the lab.

·         Lab #8 Backup and Recovery (10 points) – submit the lab.

 

18.  Database Tuning.

·         Web Notes:  Module 18-Tuning (1 hour).

·         Reading:  Oracle Database Performance Tuning Guide – Chapter 1: Performance Tuning Overview.

 

19. Data Guard.

·         Web Notes:  Data Guard Architecture and Administration (2 hour).

·         Reading:  Oracle Data Guard Concepts and Administration (1 hour) – Chapter 1. Introduction to Oracle Data Guard.

 

Week 10, Session 10 – July 24 (6:00-7:40 PM) – FINAL EXAMINATION

 


 

PREREQUISITE:  CMIS 564 Database Management Systems, equivalent credit, or instructor permission.  You must understand the Structured Query Language (SQL).

 

COURSE DESCRIPTION:  Seminar in Oracle Database Administration including database creation, maintenance, backup, recovery, and user account administration.

 

SUPPLEMENTARY TEXTS:   None.

 

COURSE OBJECTIVES:

  • Review basic responsibilities of the Database Administrator.
  • Learn the basic components of an Oracle Instance and Oracle Architecture.
  • Learn the approved Oracle Optimal Flexible Architecture.
  • Given a conceptual data model based on the Entity-Relationship Model, develop an appropriate physical database design for an Oracle database.
  • Gain hands-on experience with Oracle for a LINUX operating system environment including: database creation, database administration of user accounts, roles, privileges, profiles, tables, synonyms, indexes, clusters, views, and database triggers .
  • Learn backup and recovery procedures for Oracle.
  • Learn the various system tables and views and query them to determine various status indicators for the database using SQL.
  • Learn the basics of Undo Management.
  • Familiarize with concepts in Database Tuning.
  • Familiarize with concepts in Data Guard

 

EXAMINATIONS, QUIZZES, AND COMPUTER LABORATORY ASSIGNMENTS:  There will be two examinations, midterm (100 points) and final (100 points) for a total of 200 points

 

Scheduled or unannounced quizzes are not planned; however, you are expected to attend class every day.  If the class as a whole requires motivation to study assigned material, unannounced quizzes will be given and the quiz points will be added to the course total of available points.

 

Computer Laboratory assignments are as indicated in the detailed course schedule given above.  Some assignments will be completed in class.  If you do not have sufficient time to complete assignments in class, you are expected to complete them outside of class prior to the beginning of the next class period. 

 

Lab assignments submitted late, but within one additional class period of the assigned due date will receive an automatic deduction of 10% (1 letter grade) of the points available for the assignment.  Assignments are due at the beginning of the class period as scheduled in the detailed course schedule, or as announced in class; thus, if you use any part of that class period to complete an assignment, then the assignment will be considered to be late.  Additional point deductions will be made for errors in the assignment.  Assignments completed more than one class meeting after the due date will receive no credit; however, all assignments must be submitted prior to the final examination.  Total points for computer laboratory assignments is 140 points.

 

Total points available for the course including examinations and laboratories is 340 points.

 

GRADING SCALE:  Straight scale 90%=A, 80%=B, 70%=C, 60%=D, below 60% is failing.

 

PLAGIARISM POLICY:   The university policy on plagiarism will be enforced to the fullest extent.  If you are caught cheating on any assignment or examination, you may be failed in the course and reported to the Vice Chancellor for Academic Affairs for other disciplinary action.  Click here to obtain copy of this policy or obtain a copy at the office of the Dean for Academic Affairs in Rendleman Hall. 

 

INCOMPLETE POLICY:  Grades of Incomplete will not be given unless some act beyond your control takes place which materially affects your ability to complete the course, for example, severe illness, emotional trauma due to a death in the family, or accident.  If you get behind on course work, you will be graded on that portion of the course work that is completed or you can withdraw from the course.

 

COURSE SCHEDULE AND ASSIGNMENTS:   The amount of material covered in any given week will vary depending on the depth of in-class discussion that is generated by the material.  I prefer staying with a topic until everyone is satisfied with their understanding of the material as opposed to moving onward leaving some students confused about the material.  I have divided the material into sessions, but the material we cover may not correspond exactly to a session if the class discussion takes longer than anticipated.  Try to stay ahead in the readings about one session.  Bring questions to class.

 

ATTENDANCE:  Attendance is mandatory for all scheduled classes. 

 


End of Syllabus