Oracle9i DBA Fundamentals 1

Course Length: 5 days

This class will develop the key fundamental skills necessary to be an Oracle DBA. Students will design and create a server using the Optimal Flexible Architecture (OFA), configure logical and physical structures, set up database and user security, add and administer users, and monitor and tune main server areas. Each student will create a toolkit of administration scripts for database management and tuning by the end of this class. This class will also address issues for DBAs having to support Oracle8i and Oracle9i databases.

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

Prerequisites: Introduction to Oracle8i or Introduction to Oracle9I or at least six months working in an Oracle technical environment. An understanding of relational database concepts, SQL and PL/SQL programming skills are required. A solid understanding of Oracle schema is recommended.

Understanding the Oracle9i Server Architecture

Responsibilities of a DBA
Managing Different Types of Databases
What is an Oracle Server?
Oracle Versioning
Server Architectural Overview
Operating System Environment
Important Subdirectories in ORACLE_HOME
Oracle Product Subdirectories
Oracle Instance Components
Database Files
Control Files
Redo Log Files
Oracle Block Sizes
Blocks, Extents, and Segments
Different Types of Segments
Tablespaces
Logical Structural Design
System Change Numbers
Different Versions of Oracle
Oracle8.0 - The Object-Relational Release
Oracle8 Size Ceilings
Oracle8i - The Internet Release
Oracle9i Release 1- The eBusiness Database
Oracle9i - The Object Relational OLAP
Database
Oracle Products and Architectures
Optimal Flexible Architecture
DBA Management Pack

Oracle Memory Structures and Processes

Oracle Instance Review
Database Buffer Cache
The LRU and Dirty ListsServer Processes and the Buffer Cache
DBWR and the Buffer Cache
MTTR and Checkpoint Intervals
Checkpoints
Determining When Checkpoints Occur
Methods for Controlling Check Pointing
Minimal I/O Recovery
Defining Multiple DB Cache Buffer Pools
Defining the Database Buffer Pools
(Oracle8i style)
Database Buffer Pool Parameters
(Oracle8i style)
The Buffer Pool Parameter
Defining Storage for Buffer Pools
Calculating the Hit Ratio for Buffer Pools
Calculating Buffer Pool Sizes
Latch Contention
Caching Tables
Calculating Cache Usage of Schema Objects
Redo Log Buffer Cache
Shared Pool Area
Large Pool Area
Large Pool Parameters
Java Pool Size
The Oracle JVM
Impacting Server Process Memory
Background Processes
Required Background Processes
Additional Background Processes

Server Processes and Executing SQL

Server Processes
Program Global Areas
User Global Area
Dedicated Server Architecture
Shared Server Architecture
The Shared Server Architecture
Deprecated Parameters
Shared Server Parameters
The Parse Phase
Execution Plan Phase
Fetch Phase
Processing DML Statements
Rule-based and Cost-based Optimizers
Tuning Parallel Query
Parallel DML
Parallel DML Restrictions

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_% Static Views
DBA_% Static Views
USER_% Static Views
Additional Views
V$ Dynamic Views
GV$ Dynamic Views
Useful Database Data Dictionary Views
Additional Data Dictionary Views

Starting and Shutting Down an Oracle Server

Database Utilities
New SQL*Plus Features
The Server Manager Tool
Specialized DBA Commands
Set and Show Commands
The Parameter File
Oracle Universal Installer
The Database Configuration Assistant
Setting up a Parameter File
Important Parameters for Database Creation
Starting an Oracle Server
The STARTUP command
Startup and Shutdown Privileges
Shutdown Steps
SHUTDOWN Command Options
ALTER DATABASE Command
Alert and Trace Files
Working with Data Dictionary Views
Dynamic versus Static Parameters
Which Parameters are Dynamic versus Static
Parameters

Creating an Oracle Instance and Database

Preparation for Creating a Database
Follow OFA Guidelines
Methods for Creating an Oracle Database
Steps for Creating a Database
Setup for Unix / Setup for NT
Removing Oracle Servers and Software
Optimal Flexible Architecture
OFA Directory Layouts
OFA Database Layouts
File Naming Conventions
The CREATE DATABASE Command
Creating a Database Script
What is Created?
Preinstalled Users and Preinstalled Roles
After Database Creation
Managing PL/SQL Packages
Troubleshooting Database Creation

Managing Tablespaces and Data Files

Logical and Physical Structures
Tablespaces
The SYSTEM Tablespace
CREATE TABLESPACE Command
CREATE TABLESPACE Clauses
Creating a Tablespace
Dictionary-Managed Space
Locally-Managed Tablespaces
ALTER TABLESPACE Command
Temporary Tablespaces
CREATE TEMPORARY TABLESPACE
Read-only Tablespaces
Viewing Active Transactions
Coalescing Dictionary-Managed Tablespaces
Using the ALTER TABLESPACE command
Taking Tablespaces ONLINE and OFFLINE
Tablespace Offline Options
Renaming a Data File
Adding More Space to a Database
Autoextending a Data File
DROP TABLESPACE Command
Converting Dictionary and Local Tablespaces
Migrating the Tablespace Management Type
Transportable Tablespaces
Steps to Transport a Tablespace
Data Dictionary Views

Managing Tables

Creating Tables
Creating Table Syntax
Create the SALE_TAB Table
Column Datatypes
Different Types of Tables
Global Temporary Tables
Clustered Tables
Database Block
Block Utilization Parameters
Row Migration and Chaining
Storage Clause Options and Parameters
Managing Extent Growth
Moving Tables
Removing Data From Tables
Renaming Tables
Modifying Column Definitions
Defining Columns to be Unused
Dropping Columns
Segment High Water Mark
Calculate the HWM of a Segment
Online Table Redefinitions
DBMS_REDEFINITION Package
Redefining a Table
Validate Structure Online
The TRUNCATE Command
Data Dictionary Views for Tables
Listing of Columns for Main Views

Oracle Indexing

Index Attributes
Index Types
B*Tree Indexes
Composite Indexes
Reverse Key Indexes
Bitmap Indexes
Cluster (B*Tree) Indexes
Index Organized Tables
Function-Based Indexes
Domain Indexes
Index Options
Index Maintenance
Analyzing the Index
Rebuilding the Index
Online Index Rebuilds
Index Organized Table High Availability
Features
Examples of IOT Online Operations
Coalescing the Index
Indexes on Primary Keys
Export and Import of Indexes
SQL*Loader and Indexes

Managing Constraints

Data Integrity
Different Types of Constraints
Column Constraints
Table Constraints
Constraint Names
Primary Key Constraints
Foreign Key Constraints
Unique Constraints
Not Null Constraints
Check Constraints
Viewing Constraint Information
Constraints can be Deferred
Enabling and Disabling Constraints
Creating a Deferred Constraint
Setting Deferred Constraints
Setting the RELY Flag
Additional Constraint Features
Explicit Index Definition
USING INDEX Syntax
Dropping or Disabling Constraints and Indexes
Reducing Locking on Foreign Key Tables
Caching Primary Key Values

Managing Rollback and Temporary Segments

Data Dictionary Views on Storage Structures
Rollback Segments
Private versus Public Rollback Segments
Typical Layout for Rollback Segments
Second Rollback Segment in SYSTEM
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
Recommendations for Rollback Segments
Temporary Segments
Tablespaces and Temporary Segments

Administration of Control and Redo Log Files

Control File Contents
Working with Control Files
Multiplexing Control Files
The CREATE CONTROLFILE Command
Review of Redo Log Files
Writing to Redo Log Files
Log Sequence Numbers
Reviewing Checkpoints
Reviewing Checkpoint Events
Instance Recovery
Optimize Checkpointing
Controlling DBWR Activity
Multiple DBWR IO Processes
DB_BLOCK_CHECKING
DB_BLOCK_CHECKSUM
FAST_START_PARALLEL_ROLLBACK
Adding and Clearing Online Redo Log Files
Dropping Online Redo Log Files
Renaming Online Redo Log Files
Setting up Archiving
Mandatory or Optional Destinations
Managing Archive Destinations
Changing the ArchiveLog Mode
Dynamic Archive Parameters
ARCHIVE LOG LIST Command
Data Dictionary Views for Redo Log Files

Managing Users and Profiles

Creating Users
CREATE USER Command
Assigning Quotas to Users
ALTER USER Command
Protecting the System Tablespace
Creating Profiles
Database Resource Limits
Security Guidelines

Security Administration

System Privileges
Roles
Granting Privileges
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
Parent versus Child Roles
Working with Default Roles
Enabling and Disabling Roles
Guidelines and Standards for Roles
Additional Security Features

Advanced Security Features

Enhanced Security
Password Management
Password Resource Limits
Password Complexity
Setting up Password Management
Defining a Profile with Password Limits
Defining a Profile for a User
Information about Password Management
Data Encryption
Encryption: Random Key Generation

Server Parameter File

Definition of the Server Parameter File
(SPFILE)
Operation of the SPFILE
Steps for Creating an SPFILE
Changing Parameters in the SPFILE
Backing up the SPFILE
Gathering Information About Parameters
SPFILE FAQs

Dynamic Memory Management

Setting up SQL Work Areas
Setting the PGA Target Memory Size
Defining Automatic PGA Memory
Management
Displaying Workarea Information
Using a PGA Workarea
Dynamic SGA
Granules
V$BUFFER_POOL
Changing the Database Buffer Cache
Changing the Shared Pool Size
Database Buffer Cache Parameters
Deprecated Parameters
Advisory Parameter
V$DB_CACHE_ADVICE

Segment Management

Automatic Segment-Space Management
Creating Tablespaces Using Automatic
Segment-Space Management
Space Management
Creating Objects in Automatic Segment-Space
Managed Tablespaces
Space Deallocation With Automatic Segment-
Space Management
The DBMS_SPACE Package
The SPACE_USAGE Parameters
Space Deallocation Statements
Viewing Space Usage Information
Bitmap Join Indexes
Multiple Bitmap Join Indexes

Oracle-Managed Files

Database Temporary Tablespace
Creating a Default Temporary Tablespace
Why Use Oracle Managed Files?
The Mechanism of OMF
OMF Data Files
OMF Naming Conventions
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
Using OMF in Database Creation
OMF and Online Redo Log Files
OMF, Data Files, and Tablespaces
Renaming OMF Data Files
OMF and Control Files

Oracle Utilities and Managing Data

Oracle Utilities
The Export Utility
Export Modes
Export Parameters
Import Utility
Import Parameters
Rebuilding a Database
Logical Backups
Using Multiple Export Dump Files
Using a Query Filter on Export
Transportable Tablespaces
New 9i Features in Oracle Utilities
Exporting Statistics
Importing Statistics
New Parameters for Export and Import
Tablespace Level Exports
Direct-Load Inserts
SQL*Loader
Conventional versus Direct Path Loads
SQL*Loader Parameters
The Control File
DBVERIFY
Loadjava and Dropjava

Tablespace Management

Automatic Undo Management
Creating an Undo Tablespace
Automatic Mode for Managing Undo Space
Automatic Undo Initialization Parameters
The UNDO_RETENTION Parameter
Creating an Undo Tablespace
Defining an Undo Tablespace When Creating
a Database
Determining the Size of the Undo Tablespace
Data File
Dropping an Undo Tablespace
Switching Undo Tablespaces
Data Dictionary Views for Undo Information
Multiple Block Size Support
Setting up Multiple Block Sizes
Information About Block Sizes