Oracle Database 11g – Underground Advice for Database Administrators电子书

Oracle Database 11g–Underground Advice for Database Administrators

Table of Contents

Oracle Database 11g—Underground Advice for Database Administrators

Credits

About the author

About the reviewers

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. When to Step Away from the Keyboard

Protecting and defending

Choosing your tools

Graphic-based, command-line Oracle tools and usage

Staying away from dinosaurs

Insisting on help

What does a DBA do all day?

Prioritizing tasks—daily, weekly, monthly, quarterly, or yearly

Daily

Weekly

Monthly

Quarterly

Yearly

SLAs: Why isn't the database down anymore?

Avoiding major blunders

Summary

2. Maintaining Oracle Standards

Adapting to constant change

Database concepts

Multiple ORACLE_HOME(s)

Keeping the environment clean

Oracle's Optimal Flexible Architecture (OFA)

11g differences in the OFA standard

XWINDOWS and GUI displays

Automating day-to-day tasks

DBMS_SCHEDULER

OS cron utility executing a scheduled task on a Unix server

OEM Console plus the Intelligent Agent

11g Diagnosability Framework

Advisors and checkers

Missing temp file resolution

Environmental variables and scripting

Guidelines for scripting

Separating the configuration file

Host commands relative location

Separating the variable part of the script into its own configuration file

Don't hardcode values; reference a configuration file and password file at runtime

Putting variables at the top of the script with curly braces

Moving functions to a centralized file to be reused

Validating the use of the script

Using SQL to generate code

Helpful Unix commands

Reducing operating system differences with common tools

Configuration management, release management, and change control

Configuration management

Using OCM in disconnected mode with masking

Mass deployment utility

Release management

DBA issues with patching

Applying a patch without integrating MOS with OCM

Using the new patch plan functionality with OCM installed and uploaded to MOS

Change control

Where, when, and who to call for help

My Oracle Support

Documentation library

Summary

3. Tracking the Bits and Bytes

Dump block

Demonstration of data travel path

Location of trace files

Running dump block SQL statements

Identifying files and blocks

Legend for Trace Files:

Tracking the SCN through trace files

Single Row Insert

Commit of a single row insert

Single row update and corresponding undo block

Commit of a single row update

Oracle's RDBMS Log Miner utility

Turn on archivelog mode

Add supplemental logging

Identification key logging

Table-level supplemental logging

Flash(back) Recovery Area (FRA)

Automatic Undo Management (AUM)

Identifying data in undo segments by flashing back to timestamp

When to use Log Miner

Identifying the data needed to restore

SCN, timestamp, or log sequence number

Pseudo column ORA_ROWSCN

Flashback Transaction Query and Backout

Enabling flashback logs

Flashback Table

Flashback Transaction Query with pseudo columns

Flashback Transaction Backout

Using strings to remove binary components

Summary

4. Achieving Maximum Uptime

Maximum Availability Architecture (MAA)

Downtime—planned or unplanned

MAA with commodity hardware: Case study

Optimizing Oracle Database High Availability

To archive or not to archive, you pick the mode

Multiple archive destinations

Moving the archive destination in an emergency

Using a different disk device or disk mount

Monitoring all hard drive space and Archivelog space

Database compatibility parameter with spfile, pfile management

Dealing with storage—RAID, SAME, ASM, and OMF

RAID—Redundant Arrays of Inexpensive Disks

SAME—Stripe and Mirror Everything

ASM—Automatic Storage Management

Recommendations for implementing ASM

Mirrored files—control files and online redo logs

Autoextending data files

Auditing, log files, and max dump file size

What is currently being audited?

Auditing Session Activity

Other logs to monitor

Data dictionary healthcheck

SQL*Net hardening, tuning, and troubleshooting

Troubleshooting

What can go wrong?

Grid Control High Availability and Disaster Recovery

Recommended installation for GC 10.2.0.5+

Why should I install a separate database?

Cookbook for silent install and configuring later

Migrating GC repositories

Transportable tablespace migrations

Keeping the repository highly available

Repository backups, restores, or imports

MAA—repository on a physical standby database

OMS and agents' high availability

Cloning Management agents

GC at a very large site

Summary

5. Data Guard and Flashback

Physical, snapshot, and logical standbys

Physical standby database

Snapshot standby database

Logical standby database

Commodity hardware and mixed environments

What is Data Guard broker?

What controls the Data Guard broker?

Which tool is best?

Start with the default configuration—maximum performance

Utilizing multiple standby sites

Protection modes and real-time apply

Maximum performance (default)

Maximum performance recommendations

Maximum availability

Maximum availability recommendations

Maximum protection and recommendations

Database states

Manual failover with physical standby

Manual failover with DGMGRL

Flashback and guaranteed restore points

Possible testing/recovery scenarios for Flashback and Data Guard

Lost-write detection using a physical standby database

Corruption, patch reversal, upgrades, or testing scenarios

Reinstate failed primary scenario

Troubleshooting the logical standby

Options for resolving errors that stop the SQL Apply process

How to skip a single transaction

Active Data Guard and RMAN

Other Data Guard notes and features

Summary

6. Extended RMAN

Recovery goals determine backup configuration

Backup types and the default configuration

Backup incremental levels

Full backup

Logical backup

Oracle's suggested backup: What is missing?

Controlfiles—an important part of backup and recovery

How often should backups occur?

Default configuration details

Oracle's recommended backup strategy

Issues with incremental merge backups

Restore and recovery comparison

Recommendations for Incremental Merge backup

Calculating the FRA disk space needed

Catalog versus controlfile RMAN recordkeeping and retention policies

RMAN stored script and substitution variables

Retention policies: Recovery window or redundancy?

Not needed (OBSOLETE) versus not found (EXPIRED)

What if I want to keep certain backups?

Corruption detection

Physical corruption

Logical corruption

Commands and utilities that detect corruption

DBVERIFY

RMAN VALIDATE or BACKUP VALIDATE command

CTAS

Export utility or Data Pump

Which utility should be used?

What should I do if corruption is detected?

Data Recovery Adviser

What does RMAN backup, restore, and recover?

Possible interruptions to the recovery process

What doesn't RMAN backup, restore, and recover?

Online redo: Key to consistency

User-managed backups

What do I do before starting a restore and recovery?

Find the most recent controlfile backup

Find the backup you want to restore

Restoring the controlfile

Restoring the database

Full recovery

Point-in-Time Recovery

Verifying that the recovery is complete

Simplified recovery through resetlogs

RMAN cloning and standbys—physical, snapshot, or logical

Clones, DBIDs, and incarnations

Creating a cloned database

Post-cloning tasks

Creating a standby database

Physical standby

Scheduled maintenance/cataloging of archivelogs

Rolling forward a standby using incremental

Rolling incremental for monthly updates to data warehouses

The DBMS_BACKUP_RESTORE package

Summary

7. Migrating to 11g: A Step-Ordered Approach

Oracle net services

Client compatibility (SQL*Net, JDBC, ODBC)

RMAN binary, virtual/catalog, and database

Grid Control—database repository and agents

ASM, CFS, and RDBMS within an Oracle Grid infrastructure

Recommended order of migration

Installation of major versions, maintenance releases, and patches

Release installation

PatchSet installation—cloned ORACLE_HOME

Database upgrade methods

How long does the database upgrade take?

Database Upgrade Assistant (DBUA)

RMAN

Using RMAN as part of a manual upgrade process

Downgrading with RMAN

Transportable Tablespaces (TTS)

Preparatory steps for TTS migrations

Using TTS for upgrades

TTS cookbook

Recreating an unrecoverable database with TTS

Using TTS to add skipped read-only tablespaces during duplication

Using TTS to merge two ASM databases into one

Sharing read-only tablespaces between different databases with TTS

Cross-platform migrations with a transportable database

Physical and/or snapshot standbys

Failing back to original version

Transient logical standby: Rolling upgrades with minimal downtime

Export/import or data pump migration

Character set selection—UTF8

Post-11g upgrade tasks

Summary

8. 11g Tuning Tools

Hardware load testing and forecasting

Orion—Oracle I/O numbers calibration tool

Calibrate I/O

jMeter

Monitoring hidden or underlying problems

Proactive monitoring

Automatic Diagnostic Database Monitor (ADDM)

Automatic Workload Repository

Active Session History (ASH)

SQL Advisors

STATSPACK

Reactive diagnostic and tracing tools

Bind peeking and Adaptive Cursor Sharing

Gathering statistics

Comparing statistics

Restoring statistics history

Knowing what needs to be tuned

Tuning a single query

SQL Plan Management (SPM)

SQL Management Base

Tracing and diagnostic events

What is an event ?

When should I set an event?

What are the different event levels?

Specific Trace events for performance problems

Interpreting the resulting Event Trace file

Upgrading the Optimizer

Capturing and backing up execution plans and statistics

SQL Tuning Sets

Stored Outlines

Capturing and backing up Optimizer Statistics

Upgrade the database to 11g

Capturing new execution plans and new statistics

Evolving or verifying new plans that execute better than the 10g versions

Summary

Index