Database Courses:
Oracle9i DBA Fundamentals I Training Course

This course 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. The course will also address issues for DBAs having to support Oracle8i and Oracle9i databases.

Delegates should have attended either the Introduction to Oracle8i or Introduction to Oracle9i course or have 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.

Topics covered on the 5 day Oracle9i DBA Fundamentals I course

Understanding the Oracle9i Server Architecture

• Responsibilities of a DBA
• Changing DBA Environments
• 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, Control, and Redo Log Files
• Logical Storage Structures
• 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
• Oracle Ceilings
• Additional Database Limits
• Oracle8i - The Internet Release
• Oracle9i Release 1- The eBusiness Database
• Oracle9i Release 2- The Object-Relational OLAP Database
• Oracle Products and Architectures
• DBA Management Pack
• Understanding your Oracle Server Layout

Oracle Memory Structures and Processes

• Oracle Instance Review
• Database Buffer Cache
• The LRU and Dirty Lists
• Server Processes and the Buffer Cache
• DBWR and the Buffer Cache
• MTTR and Checkpoint intervals
• Checkpoints
• V$INSTANCE_RECOVERY View
• MTTR Advisor
• 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 and Buffer Pool Sizes
• Latch Contention
• Caching Tables
• Calculating Cache Usage of Schema Objects
• Redo Log Buffer Cache
• Shared and Large Pool Area
• Java Pool Size
• The Oracle JVM
• Impacting Server Process Memory
• Background and Required Background Processes
• Additional Background Processes
• Listing Processes

Server Processes and Executing SQL

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

Using the Data Dictionary

• What is the Oracle Data Dictionary?
• Base Tables, Static Views, and Dynamic Views in the Data Dictionary
• ALL_% Static Views
• DBA_% Static Views
• USER_% Static Views
• USER_% Static Views
• V$ Dynamic Views
• GV$ Dynamic Views
• GV$ Dynamic Views
• Database Data Dictionary Views

Starting and Shutting Down an Oracle Server

• Database Utilities
• The Server Manager Tool (Obsolete in Oracle9i)
• SQL*Plus Features and Commands
• Additional 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
• The SHUTDOWN Command
• ALTER DATABASE Command
• Alert and Trace Files
• Working with Data Dictionary Views
• Dynamic versus Static Parameters

Server Parameter File

• Definition of the Server Parameter File (SPFILE)
• Operation of the SPFILE
• 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
• Defining Automatic PGA Memory Management
• SQL Work Areas versus the PGA Aggregate Target
• Setting the PGA Target Memory Size
• Displaying Workarea Information
• Using a PGA Workarea
• Dynamic SGA
• Granules
• Changing the Database Buffer Cache
• V$BUFFER_POOL
• Changing the Shared Pool Size
• Database Buffer Cache Parameters
• Multiple Database Buffer Caches
• Deprecated and Advisory Parameters
• V$DB_CACHE_ADVICE

Managing Tablespaces and Data Files

• Review Logical and Physical Structure Relationships
• The SYSTEM Tablespace
• CREATE TABLESPACE
• Locally-Managed Tablespaces
• Temporary Tablespaces
• Temporary Segments
• Database Default Temporary Tablespace
• Creating a Default Temporary Tablespace
• Read-only Tablespaces
• Viewing Active Transactions
• Coalescing Dictionary Managed Tablespaces
• Using the ALTER TABLESPACE command
• Taking Tablespaces ONLINE and OFFLINE
• Renaming a Data File
• Adding More Space to a Database
• Autoextending a Data File
• DROP TABLESPACE Command
• Dictionary-Managed Tablespaces
• Dictionary versus Locally Managed Tablespaces
• Converting Dictionary and Local Tablespaces
• Migrating the Tablespace Management Type
• Data Dictionary Views

Managing Rollback Segments

• Managing Before-Image Data
• Rollback Segments
• Private versus Public Rollback Segments
• Second Rollback Segment in SYSTEM
• Sizing Rollback Segments
• The OPTIMAL Parameter
• CREATE ROLLBACK SEGMENT Command
• ALTER/DROP ROLLBACK SEGMENT Commands
• Using the SET TRANSACTION Command
• Data Dictionary Views for Rollback Segments

Tablespace Management

• Automatic Undo Management
• Creating an Undo Tablespace
• Automatic Mode for Managing Undo Space
• Automatic Undo Initialization Parameters
• The UNDO_RETENTION Parameter
• 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
• Undo versus Rollback Segments
• Multiple Block Size Support
• Setting up Multiple Block Sizes

Segment Management

• High Throughput Systems
• 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

Managing Tables

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

Managing Indexes

• Index Types
• B*-tree Indexes
• Index Attributes
• Maintaining Indexes
• Composite Indexes
• Skip Scans
• Reverse Key Indexes
• Bitmap Indexes
• Cluster (B*Tree) Indexes
• Index Organized Tables
• Function-Based Indexes
• Domain Indexes
• Index Maintenance
• Analyzing, Rebuilding, and Coalescing the Index
• Online Index Rebuilds
• Index Organized Table High Availability Features
• Bitmap Join Indexes
• Indexes on Primary Keys
• Export and Import of Indexes
• SQL*Loader and Indexes

Managing Constraints

• Data Integrity
• Inline and Out-of-Line Constraints
• Constraint Names
• Primary and Foreign Key Constraints
• Unique, Not Null, and Check Constraints
• Viewing Constraint Information
• Enabling and Disabling Constraints
• Creating and Setting Deferred Constraints
• Setting the RELY Flag
• Explicit Index Definition
• USING INDEX Syntax
• Dropping or Disabling Constraints and Indexes
• Reducing Locking on Foreign Key Tables
• Caching Primary Key Values

Administration of Control Files

• Control File Contents
• Working with Control Files
• Multiplexing Control Files
• The CREATE CONTROLFILE Command
• Log Sequence Numbers
• Reviewing Checkpoints
• Instance Recovery
• Managing Checkpointing
• Multiple DBWR IO Processes
• DB_BLOCK_CHECKING
• DB_BLOCK_CHECKSUM
• FAST_START_PARALLEL_ROLLBACK

Administration of Redo Log Files

• Review of Redo Log Files
• Writing to Redo Log Files
• Adding and Clearing Online Redo Log Files
• Dropping and Renaming Online Redo Log Files
• Archivelog Mode
• Setting up Archiving
• Mandatory or Optional Destinations
• Managing Archive Destinations
• Dynamic Archive Parameters
• ARCHIVE LOG LIST Command
• Data Dictionary Views for Redo Log Files

Oracle Managed Files

• Why Use Oracle Managed Files?
• The Mechanism of OMF
• OMF Data Files and Naming Conventions
• O9iR2 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

Managing Users and Profiles

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

Security Administration

• System Privileges
• Roles
• Viewing System Privileges
• Privileges and the User Group PUBLIC
• Revoking System Privileges and Roles
• Object Privileges
• Database Authentication
• Setting up a Role
• Parent versus Child Roles
• Working with Default Roles
• Secure Application Role
• Enabling and Disabling Roles
• Guidelines and Standards for Roles

Additional Security Features

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

Oracle Utilities and Managing Data

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

Creating an Oracle Instance and Database

• Preparation, Methods, and Steps for Creating a Database
• Requirements for a Minimal Database
• Setup for UNIX and NT
• Removing Oracle Servers and Software
• Things to Consider When Creating a Database
• Optimal Flexible Architecture
• Follow OFA Guidelines
• OFA Directory Layouts
• File Naming Conventions
• The CREATE DATABASE Command
• Creating a Oracle9i Database Script
• What is Created?
• Preinstalled Users and Roles
• After Database Creation
• Managing PL/SQL Packages
• Troubleshooting Database Creation

   (excl.VAT)

PTR Associates Limited

21A Peach Street, Wokingham, Berkshire, RG40 1XJ, United Kingdom
Tel: 0118 979 4000 Fax: 0118 979 4035
Email: training@ptr.co.uk