Oracle Database Administration (HTML Format)

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

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.

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 (10gR2) � this is the main link to documentation for Oracle 10g Release 2.

�            Oracle Database Administrator's Guide (10gR2)  

�            Oracle Database Security Guide (10gR2)

�            Oracle Database Concepts (10gR2)

�            Oracle Database Error Messages (10gR2)

�            Oracle Database Backup and Recovery Basics (10gR2)

�            Oracle Database Net Services Administrator's Guide (10gR2)

�            PL/SQL User's Guide and Reference (10gR2)

�            Oracle Database SQL Reference (10gR2)

�            Oracle Enterprise Manager Concepts (10gR3)

�            Oracle Universal Installer and OPatch User's Guide (10gR2)

DETAILED COURSE OUTLINE

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

Week 1 � May 27

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:  Overview of Administering an Oracle Database.

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 8: Memory Architecture.
• Reading:  Oracle Database Concepts � Chapter 9:  Process Architecture.

2.  Oracle Server.  DBA Tools; Universal Installer; SQL Plus; Oracle Enterprise Manager. 

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

�         Reading:  Oracle Database Concepts � Chapter 1:  Introduction to the 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 Universal Installer Concepts Guide � Chapter 1:  Introduction to Oracle Universal Installer Basic Concepts.

�  Lab #1 Familiarizing with an Oracle Server (10 points).

Week 2 � June 3

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 12: Database and Instance Startup and Shutdown.

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 8: Managing Tablespaces and Chapter 9: 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 an Oracle Database (focus on the manual creation of a database).
• Lab #2 Creating a Database (30 points).

Week 3 � June 10

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.
•  Lab #3 Tablespace and Datafile Management (10 points).

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 5: 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 6: Managing the Redo Log  and Chapter 7: Managing Archived Redo Logs.
• Lab #4 Redo Log and Control File Multiplexing, Archive Redo Log Files, SPFile (10 points).

Week 4 � June 17

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 2: Data Blocks, Extents, and Segments.

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 10: Managing the Undo Tablespace.

Week 5 � June 24 � MIDTERM EXAMINATION

Week 6 � July 1

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 15: Managing Tables, Chapter 17: Managing Partitioned Tables, Chapter 18: Managing Clusters, and Chapter 19:  Managing Hash Clusters.
• Reading:  Oracle Database Concepts � Chapter 26:  Native Datatypes and Chapter 21: 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 16: Managing Indexes.
• Reading:  Oracle Database Concepts � Chapter 18:  Partitioned Tables and Indexes.
• Lab #5 � finish the lab.

Week 7 � July 8

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 5:  Authorization: Privileges, Roles, Profiles and Resource Limitations.

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 Security Guide � Chapter 10:  Administering Authentication.
• Reading:  Oracle Database Security Guide � Chapter 11:  Administering User Privileges, Roles, and Profiles.
• Lab # 6 Profiles, Users, Roles, and Privileges (20 points) � start the lab now.

Week 8 � July 15

15.  Synonyms, Sequences, and Views. 

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

• Reading:  Oracle Database Administrator's Guide � Chapter 20: Managing Views, Sequences, and Synonyms.
• Lab #7 Creating Synonyms, Sequences, and Views (10 points).

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: Backup and Recovery Overview and Chapter 2: Backup and Recovery Strategies.

�         Reading:  Oracle Database Concepts � Chapter 15:  Backup and Recovery.

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

�         Reading:  Oracle Database Backup and Recovery Basics � Chapter 3: Setting Up and Configuring Backup and Recovery, Chapter 4: Backing Up Databases Using Recovery Manager, Chapter 6: Performing Complete Restore and Recovery of Databases.

�         Lab #8 Backup and Recovery (10 points).

Week 9 � July 22

17.  Oracle Network Administration. 

• Web Notes:  Module 17-Network Administration (1 hour).
• Reading:  Oracle Net Services Administrator's Guide  � Chapter 1: Networking Challenges in the Internet Age, Chapter 2:  Quick Start to Oracle Net Connections, Chapter 3: Connectivity Concepts, Chapter 4: Configuration and Management Concepts, and Chapter 8: Configuring Naming Methods (only read the section on Configuring the Local Naming Method). 

18.  Database Tuning.

• Web Notes:  Module 18-Tuning (1 hour).
• Reading:  None.