Oracle Database 10g Insider Solutions

Dr. Arun Kumar has 10+ years of experience in Oracle Database Administration, including multidimensional, decision support systems and ERP software. He has worked on a wide variety of projects and environments ranging from client-server, E-Business to managed services across database systems. John Kanagaraj is a Principal Consultant with DB Soft Inc. He has been working with various flavors of UNIX since '84 and with Oracle since '88, mostly as a Developer/DBA/Apps DBA and System Administrator. Richard Stroupe is President of TRS Consulting LLC, a Northern Virginia based consulting firm that specializes in Oracle development, administration and training for the Federal government.

Introduction.

    Who Should Read This Book

    What You Need to Use This Book

    How This Book Is Organized

    About the Book's Sample Code

    Conventions Used in This Book

I. GETTING STARTED WITH ORACLE DATABASE 10G.

1. Exploring Oracle Database 10g Architecture.

    Overview of New Features

      SYSAUX Tablespace

      Rename Tablespace Option

      Automatic Storage Management

      Temporary Tablespace Group

      BigFile Tablespace

      Cross-Platform Transportable Tablespaces

      Performance Management Using AWR

      Automatic Database Diagnostic Monitor (ADDM)

      DROP DATABASE Command

      Data Pump Utilities

    Processes Beneath the Hood

      Classification of Database Processes

    Memory Architecture

      System Global Area (SGA)

      Program Global Area (PGA)

      Memory Manager (MMAN)

    Automatic Undo Management

    SQL Advisors

    Oracle's Suite of Other Advisors

    Flash Recovery Area

    Flashback Operations

      Flashback Database

    Improved Oracle Enterprise Manager

    Grid Technology Unveiled

    Summary

2. Installing Oracle Database 10g.

    Installation Decisions

    Prerequisites

      Overview of Silent Installation

    Installing 10g on UNIX/Linux

      Installation Steps

      Platform-Independent Database Installation Questions

      Optimal Flexible Architecture (OFA)

      Common Steps for Installation

      Components to Access Other Databases and Applications

    Oracle Database 10g DBCA Overview

      Tracing the Database Configuration Assistant (DBCA)

    Installing 10g on Windows

    Companion CD Installation and Patches (All Platforms)

      Manual Database Installation Steps

    Installation Checklists

    Database and Binary Cloning

    Using Enterprise Manager for Cloning

    Migrating a Database from 8i or 9i

    Overview of Database Upgrade Assistant

      Upgrade Path for Standby Environments

      Database Upgrade Assistant in Silent Mode

      Using DBUA with Oracle 9i Physical Standby Configuration

      Using DBUA with Oracle 9i Logical Standby Configuration

    Applying Patches to Database Installations

    Uninstalling from a Failed Install

      Step 1: Identify All Database Instances

      Step 2: Remove the Database(s)

      Step 3:  Remove the Oracle Software

    Summary

3. Customizing Installation Options.

    OEM and Automatic Alerts

      OEM Preinstallation Requirements

      OEM Installation Process

      Control for Oracle Management Agents

      Automatic Alerts and Management

      Metric-Thresholds Management

      Mobile Management Using EM2Go

    XML DB for XML Data Processing

      Installing XML DB

    ConText and Other Options

      Manual Installation of Oracle Text

      Installation of Oracle Spatial

      Operations with Oracle UltraSearch

    Setting Up RAC

      Manual Installation of RAC Instance

      Installation of RAC Instance Using Data Guard

    Managing Backups with RMAN

      Important Points on RMAN During Database Upgrade

      Unregister a Target Database

      Glimpse of Oracle Database 10g Release 2

    Summary

4. Setting Up Automatic Storage Management.

    Automatic Storage Management (ASM)

    Prerequisites for Using ASM

      ASM Architecture in a Nutshell

    Configuration Options

      Initialization Parameters for ASM Instances

      StartUp/Shutdown Commands on an ASM Instance

    Performing ASM Operations

      The CREATE DISK GROUP Command

      Other Operations on a Disk Group

    Avoiding ASM Pitfalls

      Unable to Connect to ASM Instance

      Non-ASM and ASM Database Migrations

      Discovering ASM Instances in a RAC Environment

      VIP Timeouts and VIP Failure on RAC

    When and Where to Use ASM

      ASM Improvements in Oracle Database 10g Release 2

    Summary

II. QUICK WINS USING ORACLE DATABASE 10G.

5. Using Automatic Memory Management.

    Introduction to Quick Wins

    What Is Automatic Shared Memory Management?

      SGA Sizing Considerations

    Understanding Memory Advisor

      Memory Advisors Through OEM

    Automated Maintenance Tasks

    Memory Management Tips

      Converting a Manual Memory-Managed Database to ASMM

      Tuning SGA_TARGET and SGA_MAX_SIZE

    Scaling for Database Expansion

    ASMM Improvements in 10g Release 2

    Summary

6. Monitoring with Automatic Statistics Collection.

    Automatic Statistics Collection Fundamentals

      Types of Database Statistics

      Database Metrics Revisited

      Statistics Levels and AWR Snapshots

    Collecting and Analyzing Statistics

      Preventing the Time Out of GATHER_STATS_JOB

      Cross-Checking Your Statistics and Ratios

      AWR Reports Generation

    Metrics Monitoring and Customization

    Using Database Alerts with Metrics

    Making the Most of Metrics and Statistics Collection

      Statistics Collection During Database Upgrades

    New Features in Oracle Database 10g Release 2

    Summary

7. Managing Automatic Undo.

    Using Automatic Undo Management

      How Undo Data Helps the Database

      The Significance of the UNDO_RETENTION Parameter

      Resumable Space Allocation

      Undo Management in Real Application Clusters

    Redo Log File Sizings

    Using Undo Advisor

      Undo Management Using OEM

      Making the Most of Undo Advisor

    Guaranteeing Undo Availability

    Undo Tips from the Trenches

    Automatic Undo Management Improvements in Release 2

    Summary

8. Leveraging Automatic Segment Management.

    Segment Management in Oracle Database 10g

      Data, Index, and Temporary Segments

    Making Segment Management Work for You

    Using Segment Advisor

      Invoking Segment Advisor from Enterprise Manager

      Invoking Segment Advisor from PL/SQL

      Reviewing Segment Advisor Results

    Intelligent Segment Resource Estimation

    Space-Management Tips and Tricks

      Limitations for Online Segment-Shrink Operations

      For Dictionary-Managed Tablespaces

    ASSM Improvements in Release 2

    Summary

9. Implementing Recovery Manager.

    RMAN Basics

      Difference Between RMAN and Hot Backup

      Advantages of RMAN over Hot Backup

      A Sample RMAN Backup Session

      RMAN Documentation

      Accessing RMAN

    New Features of RMAN

      Use of Flash Recovery Area

      Fast Incremental Backups

      Incrementally Updated Backups

      Recovery Through RESETLOGS

      RMAN Tuning

      Other RMAN Improvements

    Recovery Without Hurdles

      Backup Strategies

      Recovery Scenarios

    New Features in Oracle Database 10g Release 2

    Summary

III. Tuning Oracle Database 10g.

10. Adopting a New Approach to Tuning.

    A New Performance Philosophy

      Performance-Management Tools

      STATSPACK Shortfalls

    New and Improved Views

      V$METRIC, V$METRIC_HISTORY, V$METRICNAME, and V$METRICGROUP

      V$SERVICES, V$SERVICEMETRIC, and V$SERVICEMETRIC_HISTORY

      V$SERVICE_STATS

      V$SERVICE_EVENT

      V$EVENTMETRIC

      V$EVENT_HISTOGRAM

      V$FILEMETRIC and V$FILEMETRIC_HISTORY

      V$FILE_HISTOGRAM

      V$TEMP_HISTOGRAM

      V$ENQUEUE_STATISTICS

      V$OSSTAT

      Changes in Existing Views

      Changes in Other Views

    Time and Wait Model

    OEM and Tuning

      Navigating OEM Database Control

      Alerts and Metrics

      Advisors

    New Features in Oracle Database 10g Release 2

    Summary

11. Effectively Using the Automatic Workload Repository.

    Introducing the AWR

      AWR Collection

      Comparison to STATSPACK

      AWR Storage and Reporting

      Accessing the AWR Snapshots

    Customizing the AWR

      AWR Dependencies

      Using the OEM to Customize the AWR

      Using the SQL Interface to AWR

    Performing Baselining

      Capturing Baselines

      Dropping Baselines

    Active Session History

      Session-State Information

      Using Session-State Information

      The Connection to Active Session History

      Size of Active Session History Buffers

      An Example of Spot Analysis Using ASH

      Using OEM to Obtain Active Session History

      Dumping ASH Buffer Contents

      The Downside of ASH

    New Features in Oracle Database 10g Release 2

    Summary

12. Effectively Using the Automatic Database Diagnostic Monitor.

    Introduction to ADDM

      The Goal of ADDM

      Problem Areas Handled by ADDM

      Using ADDM with ASH

      Nonproblematic Areas

      Helpful ADDM Views

    Interfacing to ADDM

      Using OEM for ADDM

      Using SQL to Get to ADDM

    Making Sense of ADDM Data

      Finding 1: SQL Statements Consuming Significant Time

      Finding 2: SQL Statements Waiting for Locks

      Finding 3: Session Management

      Other Findings

      Expected I/O Response Time

      Cross-Verification from the Advisor Views

    New Features in Oracle Database 10g Release 2

    Summary

13. Effectively Using the SQL Advisors.

    Automating the SQL Advisors

      The SQL Tuning Advisor

      The SQL Access Advisor

    Using the Advisors for Tuning

      OEM Interface to the SQL Tuning Advisor

      OEM Interface to the SQL Access Advisor

      APIs to the SQL Tuning Advisor

    Avoiding Advisor Pitfalls

    New Features in Oracle Database 10g Release 2

    Summary

14. Influencing the Cost Based Optimizer.

    New Optimizer Improvements

      RBO and CBO: A Comparison

      What the Optimizer Does

      The Relevance of Object Statistics

      The Relevance of System Statistics

      The Relevance of Dictionary Statistics

      Dynamic Sampling

      Table and Index Monitoring

      Parameters Influencing the Optimizer

      Cardinality, Selectivity, and Column Usage

      Tracing the Optimizer

    SQL Profiles

    Effective Use of Histograms

    New Features in Oracle Database 10g Release 2

    Summary

IV. Scaling and Availability with Oracle Database 10g.

15. Utilizing Oracle Database 10g Real Applications Clusters.

    Oracle 10g RAC Overview

    Cluster Ready Services

      Cluster Ready Services Architecture

      Installing Cluster Ready Services

      Troubleshooting the CRS Install

    Service Workload Management

    10g RAC Installation and Migration Enhancements

      Local Versus Shared Install

      Installing the 10g ORACLE_HOME

      Adding a Node to or Removing a Node from an Existing ORACLE_HOME

      Migrating from a Previous Release

    10g RAC Manageability Enhancements

      Oracle Cluster Registry

      New Support for 10g Utilities

      Managing RAC with 10g Enterprise Manager

    Automatic Storage Management for Cluster Management

      Managing ASM with 10g RAC

      ASM Maximum File Size Limitation with 10g RAC

    Third-Party Cluster Management Support

    Limited Rolling Upgrade with OPATCH

    10g RAC Tips for Better Use

      Cluster Interconnect

      SGA Components

      Tablespaces

      Tables and Indexes

      Function-Dependent Routing

      Object Partitioning

      Running 10g RAC with 9i RAC in Same Environment

      CRS and ASM

    Advanced RAC Features in Oracle Database 10g Release 2

    Summary

16. Utilizing 10g Physical and Logical Standby with Data Guard.

    Oracle 10g Data Guard Overview

    General Improvements with Oracle 10g Data Guard

      Real Time Apply

      Flashback Database Support

      Redo Transmission Authentication and Encryption

      Role-Based Destinations

      Improved Data Guard Configuration Management

      Improved Data Guard STARTUP Command

      Improved Data Guard Archiving

    Logical Standby Improvements

      Zero Outage for Instantiation

      Support for Redo Log Files

      The PREPARE TO SWITCHOVER Command

      Support for SQL Apply Services

      Rolling Database Upgrades with SQL Apply

      Enhanced Logical Standby Commands and Views

    Data Guard Broker Improvements

      Broker Support for RAC Databases

      Enhancements to the Enterprise Manager Data Guard GUI

      New 10g Broker Commands

    Applying Patch Sets with Data Guard in Place

      Patching a Physical Database

      Patching a Logical Database

    New Features in Oracle Database 10g Release 2

      Automatic Failover

      Faster Redo Apply Failover

      Flashback Database Across Switchovers

      Asynchronous Redo Transmission

      SQL Apply

    Summary

17. Effectively Using Flashback Technologies.

    Brief Overview of Flashback Technology

    Flashback Database

      Flashback Database Architecture

      Configuring Flashback Database

      Managing Flashback Database

      Flashback Database in Action

      Using Flashback Database with Data Guard

      Troubleshooting Flashback Database

    Flashback Table

      Configuring Flashback Table

      Flashback Table in Action

      Troubleshooting Flashback Table

    Flashback Drop with Recycle Bin

      Oracle Recycle Bin

      Flashback Drop in Action

      Troubleshooting Flashback Drop

      Disabling Flashback Drop

    Flashback Versions Query

      Flashback Versions Query in Action

      Troubleshooting Flashback Versions Query

    Flashback Transaction Query

      Flashback Transaction Query in Action

    Advanced Flashback Features in Oracle Database 10g Release 2

      Flashback Restore Points

      Flashback Across Global Database Changes

      Additional 10g R2 Views

    Summary

18. Leveraging Grid Technology Features.

    What Exactly Is the Grid?

      A Familiar Example: The Electricity Grid

      Similarities in the Computing Grid

      Grid Computing: A Little History

    Oracle's Version of the Grid

    Managing the Grid

    Summary

V. USING ORACLE DATABASE 10G UTILITIES AND OTHER ADVANCED FEATURES.

19. Maximizing Data Movement with Oracle Data Pump.

    Oracle 10g Data Pump Enhancements

      Oracle 10g Data Pump Concepts

      Data Pump Architecture

      Data Pump Process Flow

      Data Pump Export and Import Utilities

      Diagnosing Data Pump Issues with TRACE

      Managing Data Pump Jobs

    External Table Enhancements

    Advanced Data Pump Features in Oracle Database 10g Release 2

    Summary

20. USING 10G SQL#PLUS AND ISQL#PLUS.

    10g SQL*Plus Enhancements

      DEFINE Enhancements

      SPOOL Enhancements

    10g iSQL*Plus Enhancements

    iSQL*Plus Environment

    iSQL*Plus Workspace

    Advanced iSQL*Plus and SQL*Plus Features in Oracle Database 10g Release 2

    Summary

21. Making the Most of Oracle Database 10g.

    A Collection of Useful Features

    DML/DDL Features

    Network Features

      New Features in Oracle Database 10g Release 2

    Security Features

      Access Control Features

    Resource Manager and Scheduler

      Job Management Using the Scheduler

      Overview of the Resource Manager

    New Database Initialization Parameters

    Oracle Streams Enhancements

      Streams Architecture

      Streams Administrator

      Streams Downstream Capture

      Streams-Enhanced RAC Support

      Other Streams Enhancements

    MetaLink Integration with Oracle Database 10g

    Oracle Database 10g EM Patch Cache

    Oracle Applications 11i with Oracle Database 10g

    Summary

Index.