Oracle® Database Administration

Summary

This course gives an introduction to the administration of an ORACLE® Relational Database and is based on ORACLE 8i, although the underlying principles may be applied to both Version 7 and Version 8.

Prerequisites

Attendance on the Introduction to SQL course or equivalent or extensive experience of another Relational Database, such as Informix or Sybase. It is also recommended that the student has several months hands on experience of the use of both SQL and SQL*Plus® and some knowledge of PL/SQL®. If this is not the case then an optional module of this course, SQL and PL/SQL Revision is recommended.

Length

4 days

Format

Instructor-led course, with many practical computer-based exercises.

Course Outline

Introduction
Introduction, Objectives, Course Schedule, Course Introductions, Pre-requisites, Bibliography, Delegate Responsibilities, Course Summary
System Architecture
Objectives, System Architecture, Database Versus Instance, The Instance, System Global Area, Database Buffer Cache, Redo Log Buffer, Shared Pool, Process Structures, System Monitor - SMON, Process Monitor - PMON, Database Writer - DBWR, Log Writer - LGWR, Checkpoint - CKPT, The Database Files, Initialisation File, Control File, Data File, Logging On the DBA, Redo Log File, The Archiver - ARCH, Noarchivelog Mode, Archivelog Mode, Automatic Archiving, How Oracle Works, Concurrency and Consistency, Rollback Segments, How Locking Works, Locking Mechanisms, Automatic Locking, Manual Locking
Server Management
Objectives, Database Management Tasks, What Tools are Available?, What is OTN - Oracle Technology Network?, How Do I Access Oracle Documentation?, What is Enterprise Manager?, What is SQL*Plus?, What is Server Manager?, Logging On the DBA, Starting SQL*Plus, Starting Server Manager, Database Startup, Database Shutdown, Shutting Down the Database, Connecting to the Database, Starting Up the Database for General Use, Starting Up the Database as Nomount, Mounting the Database, Opening the Database, Read Only Database, Abnormal Instance Shutdown, Using SQL*Plus in Oracle 8i, Starting Up a Restricted Session, Duplex Control Files, Modes of Mounting a Database with the Parallel Server
Space Management
Objectives, What is a Tablespace?, What is an Extent?, What is a Block?, Showing Parameters, What is a Segment?, Schema, Showing Tablespace Details, Creating a Tablespace, Space Considerations, Storage Parameters, Creating a Temporary Tablespace, Make a Tablespace Temporary, Make a Tablespace Permanent, Creating a Table in a Tablespace, Changing the Tablespace Status, Tablespace Full, Extending a Tablespace, Resizing a Tablespace, Managing Free Space, Space Allocation, The Space.sql Script, Removing an Object
Utilities
Objectives What is a Utility?, Export Utility, Using a Host String, Using Parameters, Import Utility, Using a Host String, Using Parameters, SQL*Loader Utility, Loading Data, The Control File, Running the Utility, Appending Data
Networking
Objectives, What is Client/Server?, What is Oracle Net8?, What is ODBC?, How Net8 Works, Naming Resolution, Client Configuration, Server Configuration, The Lsnrctl Utility
Simple Backup and Recovery
Objectives, What is a Backup?, What is Recovery?, Backup and Recovery Management Tasks, The Backup Process - Choosing a Method, Operating System Backup Without Archiving, The Parameter File, The Control Files, The Data Files, The Redo Log Files, The Backup, Advantages of Off-line Backup, Disadvantages of Off-line Backup, Control File Backup, The Recovery Process, Types of Failure, User Process Failure, Instance Failure, User Error, Statement Failure, Media Failure, Recovering a Control File, Recovering a Data File, Renaming a Recovered Data File, Other Failure, Backup Strategies
Complex Backup and Recovery
Objectives, What is a Complex Backup?, What is Complex Recovery?, Management Tasks, Operating System Backup With Archiving, Advantages of Online Backup, Disadvantages of Online Backup, Running in Archive Mode, Displaying Archive Status, Displaying Archive Status in SQL*Plus, Enable Automatic Archiving, Change Archive Status, Stop Automatic Archiving, Changing Log File Destination, The Offline Backup, Switching Log Files, The Online Backup Process, Control File Backup, Data File Backup, The Online Recovery Process - Types of Failure, Control File Restore, Media Failure, User Error
Rollback Segments and Redo Logs
Objectives, What is a Rollback Segment?, Public vs Private Rollback Segments, Creating a Rollback Segment, What is a Redo Log?, Online Redo Log Groups and Members, Viewing the Redo Log Groups, Creating Redo Log Groups, Switching Log Files, Dropping a Redo Log Group, Duplexing Redo Log Members, Resizing Redo Log Members, What is Log Minor? How to Use Log Miner, What is an SCN? Restrictions on Use, Set Parameter UTL_FILE_DIR, Creating the Log Minor Package, Creating the Dictionary File, Specifying the Log Files, Starting the Session, View the Log File Contents, Stopping the Session, Log Minor Workshop
Recovery Manager
Objectives, Backup Strategies, What is Recovery Manager? Types of Backup, The Recovery User, The Recovery Catalog, Invoking Recovery Manager, Allocating a Channel, Using RMAN with Files, Sample RMAN Command Files, Using the Catalog, Creating the RMAN User, Grant Permissions to the RMAN User, Creating The Recovery Catalog, Registering a Database, Invoking Recovery Manager using the Catalog, Lists, Reporting, Report Examples, Generating Backup Sets, Offline Backups, Online Backups, Online Tablespace Backups, Datafile Backups, Controlfile Backups, Archive Log File Backups, Recovering a Datafile, Recovering a Datafile after Media Failure, Point In Time Recovery, Stored Scripts, Incremental Backups, Cumulative Incremental Backups, Tags, Maintaining the Recovery Catalog, Registering existing Backups, Manually Adjusting Catalog Entries, Recovery Manager Workshop
User Management
Objectives, What is a User?, What is a Privilege?, What is a Role?, What is a Profile?, Creating a User, Granting Privileges, Combining Create and Grant, Creating a Role, Granting Privileges to a Role, Granting a Role to a User, Revoking Privileges, Creating a Profile, Giving a Profile to a User, Removing an Object, Killing a User Session, Syntax for Alter User, Changing the Password, Drop User, The OPS$ User Account, Database Administrator Authentication, Connecting as User SYS
Configuration and Tuning
Objectives, Configuration and Tuning, What is Configuration?, What is Tuning?, The Oracle Documentation, What to Configure?, Configuring the Operating System, Configuring Oracle, Sizing and Configuring Database Objects, Configuring Tables, Configuring Indexes, Configuring Rollback Segments, Configuring Sort Areas, Configuring Temporary Tablespaces, Configuring the Redo Log, What to Tune?, Where to Start?, Tuning Oracle, Tuning Memory, Tuning Disc I/O, Default Block Size, The Memory.sql Script, The Mereport.lst Report, Library Cache Reloads, Dictionary Cache Miss Rate, Buffer Hit Ratio, Sort Area Size Values, Non-Indexed Tables
Query Optimisation
Objectives, Query Optimisation, Creating The Tables, Explain Plan, Creating the PLAN_TABLE Table, Running EXPLAIN PLAN, Analyse the Execution Plan, Using SET AUTOTRACE, Collecting Statistics, Timing SQL Statements, Timing Areas, Create Index, Create Secondary Index, The Query Optimiser, Partitioned Tables, Creating a Partitioned Table, Index Only Tables, Creating an Index Only Table, Cost-Based Optimisation, Rule-Based Optimisation, Using Hints, How to Specify Hints, SQL Trace, Parameter File Settings, Enabling Tracing, Running TKPROF, TKPROF Output
Creating a Database
Objectives, Dropping a Database, Creating a Database, Database Creation Tasks, Create a New Parameter File, Starting Up the Database, Creating a New Database, Creating a Temporary Rollback Segment, Creating a Tablespace for Rollback Segments, Creating the Other Tablespaces, Put the Temporary Rollback Segment Online, Alter Defaults for System User, Creating the Real Rollback Segments, Offline the Temporary Rollback Segment, SQL Install Files, Product User Profiles, The HELP Text, Scott/Tiger Tables, Testing the Database

Hardware and Software Requirements

Although Windows 95/98/ME may be used for the course, to obtain the maximum benefit each student should have access to a PC running Windows NT Workstation or Windows 2000 Professional.

For ORACLE 8, each PC should have at least 500Mb of free space on the C drive and a CDROM. However, the PCs do NOT have to be on a network.

If ORACLE 8i is to be used, each PC must be running Windows NT/2000 and should have at least 96Mb of RAM, 1Gb of free space on the C drive and a CDROM. For this version of Oracle, the PCs DO have to be on a network.

Note to Oracle licensees

Your Oracle license may not allow the use of the software for providing training to people outside your organisation. Contact Oracle if you are planning to provide third-party training and have any doubts about your license in this regard.

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 3

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 4

Notice: Undefined variable: REMOTE_USER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Oracle and Java are registered trademarks of Oracle and/or its affiliates. PL/SQL and SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Microsoft is either a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. MVS is a registered trademark of the International Business Machines Corp. in the U.S. or other countries or both. Motif, OSF/1, UNIX, and the "X Device" are registered trademarks and IT DialTone and The Open Group are trademarks of The Open Group in the U.S. and other countries. All other trademarks are the property of their respective owners.