file=cmis565.htm; updated, May 22, 2013
COURSE OUTLINE - Southern
CMIS 565 Oracle Database Administration - 3 Semester Hours Credit
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.
· 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).
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)
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.
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; ClustersWhen 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 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 Administrator's Guide Chapter 27: Managing Resources with Oracle Database Resource Manager.
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).
· Web Notes: Module 16b-Recovery Manager (1 hour).
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.
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