file=cmis565.htm;
updated, April 30, 2012
COURSE OUTLINE - Southern
CMIS 565 Oracle Database
Administration - 3 Semester Hours Credit
Dr.
REQUIRED TEXTBOOK: All textbook material references are
online at the Oracle On-line Technical Reference Site. If the system requests a Username and
Password, use the following username and password created for this course:
Username: siueuser Password: rucjfsw. You can also
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 Users Guide (11gR2)
·
Oracle
Database SQL Language Reference (11gR2)
·
Oracle
Database Installation Guide (11gR2) for Linux
· Oracle Database Performance Tuning Guide (11gR2)
·
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 21
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.
Week 1,
Session 2 May 23
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).
Monday May 28 is Memorial Day, a national holiday. There will be no class held.
Week 2,
Session 1 May 30
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 3,
Session 1 June 4
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 3, Session 2 June 6 MIDTERM EXAMINATION
Week 4,
Session 1 June 11
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 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 4,
Session 2 June 13
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.
Week 5,
Session 1 June 18
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 Users Guide Chapter
8: RMAN Backup Concepts, Chapter
9: Backing Up the Database, Chapter
17: Performing Complete Database Recovery.
Week 5,
Session 2 June 20
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.
Week 6, Session 1 June 25 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:
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