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. 
1.  Oracle Architecture.  Oracle server; Oracle Instance; Memory and physical structures; Server Process; Background Processes. 
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.

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. 
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. 
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. 
Week 3 – June 10

6.  Data Dictionary and Dynamic Performance Views.   Base tables and views; Using the data dictionary; Dynamic performance tables. 
7.  The Control File.  Control file contents; Multiplexing the control file. 
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. 
Week 4 – June 17

9.  Storage Structures.  Segment types; Storage clauses; Extent allocation and De-allocation; Database block and block management; Block space usage. 
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. 
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.
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.
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. 
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.
Week 8 – July 15

15.  Synonyms, Sequences, and Views. 
·        Web Notes: Module 15-Synonyms, Sequences, and Views (1 hour).
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. 
18.  Database Tuning.
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
free counters
Website Worth