Oracle Database 10g Insider Solutions

Description

Oracle Database 10g Insider Solutions is a must-have reference guide for all Oracle professionals. It provides much-needed information on best practices, tips, and techniques in debugging, installation, deployment, and tuning of the Oracle 10g database. You can draw upon the experience and knowledge of these authors to find creative ways to put your Oracle database to work. The authors will also address common and not-so-common installation concerns, database administration, development, and tuning. Oracle Database 10g Insider Solutions will bring you the insider information that you can't get anywhere else.

Back Cover

Oracle Database 10g Insider Solutions is a must-have reference guide for all Oracle professionals. It provides much-needed information on best practices, tips, and techniques in debugging, installation, deployment, and tuning of the Oracle 10g database. You can draw upon the experience and knowledge of these authors to find creative ways to put your Oracle database to work. The authors will also address common and not-so-common installation concerns, database administration, development, and tuning. Oracle Database 10g Insider Solutions will bring you the insider information that you can't get anywhere else.

Contents

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.

Author
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.