Oracle8i Database Administration

Course Length: 5 days

This course will teach students the fundamental skills required to manage Oracle databases. Students will learn database administration skills by designing, setting up, configuring, and managing their own Oracle8i server. By the end of the class, each student will have gone through the main tasks necessary to manage Oracle8i databases. New Oracle8i features such as the Oracle8i JVM, locally-managed tablespaces, partitioned tables, global and local indexes, index-organized tables, function-based indexes, object-relational features, the large pool and the Java pool will be covered.

Audience: Database administrators, application developers, and system administrators.

Prerequisites: Oracle for Application Developers. Previous working experience with Oracle is recommended. A solid understanding of SQL and PL/SQL is required. A base understanding of the operating system used for class (UNIX or NT) is needed as well.

Understanding the Oracle8i Server Architecture

Responsibilities of a DBA
What is an Oracle Server?
Oracle Versioning
Server Architectural Overview
Operating System Environment
Important Subdirectories in ORACLE_HOME
Oracle Instance Components
Database and Control Files
Redo Log Files
Logical Storage Structures
Blocks, Extents and Segments
Tablespaces
Logical Structural Design
System Change Numbers
Oracle8i JVM
Aurora and MTS
Oracle8.0- The Object-Relational Release
Oracle8 Size Ceilings
Oracle8i- The Internet Release
Oracle Architectures

Oracle Memory Structures and Processes

Data Buffer cache
The LRU and Dirty Lists
Server Processes and the Buffer Cache
DBWR and the Buffer Cache
Checkpoints
Determining When Checkpoints Occur
Defining Multiple DB Cache Buffer Pools
Database Buffer Pool Initialization Parameters
Defining Storage for Buffer Pools
Calculating the Hit Ratio for Buffer Pools
Latch Contention
Caching Tables
Calculating Cache usage of Schema Objects
Redo Log Buffer Cache
Shared Pool Area
Java Pool Size
Sort Enhancements
Background Processes

Server Processes and Executing SQL

Program Global Areas
The Parse and Fetch Phase
Processing DML Statements
Rule and Cost-based Optimizers
Tuning Parallel Query
Parallel DML
Using the Data Dictionary
What is the Oracle Data Dictionary?
Base Tables and Static Views in the Data Dictionary
Dynamic Views in the Data Dictionary
ALL_%, DBA_%, USER_% Views
V$ and GV$ Views

Starting and Shutting Down an Oracle Server

Database Utilities
New SQL*Plus Features in Oracle 8i
The Server Manager Tool
Specialized DBA Commands
Set and Show Commands
The Parameter File
Oracle Installer
The Database Configuration Assistant
Setting up a Parameter File
Important Parameters for Database Creation
Starting an Oracle Server
Shutdown Steps
Alert and Trace Files
Database Limits
Dynamic versus Static Parameters

Managing Tablespaces and Datafiles

Logical and Physical Structures
The SYSTEM Tablespace
CREATE TABLESPACE Command
Dictionary-Managed Space
Locally-Managed Tablespaces
ALTER TABLESPACE Command
Temporary Tablespaces
CREATE TEMPORARY TABLESPACE
Read-only Tablespaces
Coalescing Dictionary Managed Tablespaces
Taking Tablespaces ONLINE and OFFLINE
Renaming a Data File
Adding More Space to a Database
Autoextending a Data File
DROP TABLESPACE Command
Converting Dictionary and Local Tablespaces
Transportable Tablespace

Managing Tables

Creating Tables
Column Datatypes
Physical Properties
Temporary and Clustered Tables
Block Utilization
Row Migration and Chaining
Storage Clause Options
Moving and Removing Tables
Defining Columns to be Used
Dropping Columns
The TRUNCATE Command

Oracle8i Indexing

Index Attributes and Types
B*Tree Indexes
Composite Indexes
Reverse Key Indexes
Bitmap Indexes
Index Organized Tables
Function Based Indexes
Domain Indexes
Index Options and Maintenance
Analyzing and Rebuilding the Index
Indexes on Primary Keys
Export and Import of Indexes
SQL*Loader and Indexes

Managing Constraints

Data Integrity
Column Constraints
Table Constraints
Constraint Names
Primary and Foreign Key Constraints
Unique and Not Null Constraints
Check Constraints
Enabling and Disabling Constraints

Introduction to Partitioning

Managing Large Tables with Oracle7
Oracle8 Size Ceilings
Oracle Data Storage
What is Partitioning?
Creating a Range Partitioned Table
The Partition Key
Partition Syntax
Range Partitioning
Advantages of Partitioning
Accessing a Partitioned Table
Using MAXVALUE
Partitioning Rules to Follow
Partition Extended Table Name Syntax
Restrictions on Partition Extended Name
Multi-Column Partition Keys
Inserting into a Multi-Column Partition Key
Modifying Partition Key Values
Setting Physical Attributes for Partitions
Hash and Composite Partitioning
Partition Pruning
Partition Wise Joins
Composite Objects
Global Prefixed Indexes
Local Partitioned Indexes
Local Non-Prefixed Indexes
List Partitioning in Oracle9i

Managing Rollback and Temporary Segments

Database Block
Block Utilization Parameters
Data Dictionary Views on Storage Structures
Rollback Segments
Private versus Public Rollback Segments
Typical Layout for Rollback Segments
Space Usage
Sizing Rollback Segments
The OPTIMAL Parameter
CREATE ROLLBACK SEGMENT Command
ALTER/DROP ROLLBACK SEGMENT
Using the SET TRANSACTION Command
Data Dictionary Views for Rollback Segments
Tablespaces and Temporary Segments

Administration of Control and Redo Log Files

Control File Contents
Working with Control Files
Multiplexing Control Files
Log Sequence Numbers
Checkpoints
Instance Recovery
Optimize Checkpointing
Controlling DBWR Activity
Multiple DBWR IO Processes
Adding and Clearing Online Redo Log Files
Dropping Online Redo Log Files
Renaming Online Redo Log Files
Setting up Archiving in Oracle8
ARCHIVE LOG LIST command
Data Dictionary Views for Redo Log Files

Managing Users and Profiles

Create and Alter Users
Creating Profiles
Database Resource Limits

Security Administration

System privileges
Roles
Granting and Viewing System Privileges
Granting Object Privileges
Privileges and the User Group PUBLIC
Revoking System Privileges and Roles
Object Privileges
Database Authentication
Predefined Roles
Setting up a Role
Guidelines and standards for roles

Advanced Security Features

Enhanced Security
Password Management
Password Resource Limits and Complexity
Defining a Profile with Password Limits
Defining a Profile for a User
Data Encryption
Implementing a Security Policy
Implementing an Application Context
Unique and Shared Schemas
Enterprise User Management
Oracle Wallets
Radius Authentication Dial-In User Service
Oracle Security Management
N-tier Authentication/Authorization

Object-Relational Concepts

Object-Relational Databases
New Object-Relational Features
New Oracle8i Object-Relational Features
New Oracle Data Types
Abstract Data Types
Data Dictionary Views
CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User-Defined Datatypes
Working with Object Tables
Object Identifiers
Defining and Updating a REF Values
INSERTING REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
LOBS

Object Methods

Create Type Body Command
Executing Object Methods
Different Types of Methods
MAP versus ORDER Method
Static Methods
Differences between Packages and Objects
DDL Type Commands
Objects and Nulls

Collections

Records
Creating a PL/SQL Record
PL/SQL2 Tables
PL/SQL8 Tables
CAST Operator
Operator "THE"
The TABLE Operator

Advanced Cost-Based Optimizer Features

Cost-Based Optimizer Enhancements
Generating Statistics
Stored Outlines

Oracle Utilities and Managing Data

Oracle Utilities
The Export Utility
Export Modes
Export Parameters
Import Utility and Parameters
Rebuilding a Database
Logical Backups
Direct-Load Inserts
SQL*Loader
Conventional versus Direct Path Loads
The Control File
DBVERIFY
Loadjava and Dropjava

Introduction to Performance Tuning

Main Components to Tune
Monitoring a System
Understanding Different Types of Applications
Memory Buffers
Sizing the Shared Pool Area
Calculating the Data Block Cache Hit Ratio
Application Tuning
Rule and Cost Based Optimizers
Running EXPLAIN PLAN
AUTOTRACE
Setting up SQL Trace
Running TKPROF
What is Statspack?

Creating an Oracle Instance and Database

Follow OFA Guidelines
Installing Oracle
Steps for Creating a Database
Setup for Unix and NT
Removing Oracle Servers
Creating a Database Script
What is Created?
Preinstalled Users and Roles
Troubleshooting Database and Creation