Oracle Database 11g Underground Advice for Database Administrators

348 Pages·2010·6.85 MB·English

by  April Sims

Oracle Database 11g–
Underground Advice for
Database Administrators
Beyond the basics
A real-world DBA survival guide for Oracle 11g
database implementations

April C. Sims
BIRMINGHAM - MUMBAI
Oracle Database 11g—Underground Advice for
Database Administrators
Beyond the basics
Copyright © 2010 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of
the information presented. However, the information contained in this book is sold
without warranty, either express or implied. Neither the author, Packt Publishing,
nor its dealers or distributors will be held liable for any damages caused or alleged to
be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: April 2010
Production Reference: 1010410
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-849680-00-4
www.packtpub.com
Cover Image by Tina Negus ([email protected])
Credits
Author Editorial Team Leader
April C. Sims Akshara Aware
Reviewers Project Team Leader
Philip Rice Lata Basantani
Charles Schultz
Lei Zeng Project Coordinator
Jovita Pinto
Acquisition Editor
James Lumsden Indexer
Hemangini Bari
Development Editor
Dhwani Devater Graphics
Geetanjali Sawant
Technical Editors
Akash Johari Proofreader
Aaron Nash
Pallavi Kachare
Ajay Shanker
Production Coordinator
Shantanu Zagade
Copy Editor
Lakshmi Menon
Cover Work
Shantanu Zagade
About the author
April C. Sims, after many career changes, has finally found a field that inspires,
frustrates, and enthralls her at the same time—administering Oracle Databases. A
previous career as a teacher lead her to continue teaching others about Oracle, as she
considers the first year as a DBA to be the most critical. April is an Oracle Certified
Professional 8i, 9i, and 10g with an MBA from the University of Texas at Dallas. She
is an active technical presenter at regional Oracle Events, IOUG COLLABORATE,
and Oracle OpenWorld.
She is a lead DBA at Southern Utah University, a 4 year regional university based
in Cedar City, UT. Also known as Festival City, USA, it is home to 17 major events,
including the Utah Summer Games and the Utah Shakespearean Festival. It is a
beautiful place surrounded by National Parks.
April is also a Contributing Editor for IOUG "SELECT" Journal, which is a quarterly
technical magazine for the Independent Oracle Users Group. She is also a contributor
to ORACLE SECURITY Step-by-Step A Survival Guide for Oracle Security Version 1.0 by
SANS Press, 2003.
I want to thank the most inspiring person that I have ever met
during my Oracle career—my boss Jeanette Ormond. A big thanks
to John Kanagaraj and IOUG SELECT for getting me started on
my publishing career. Thanks to my Mom, Dad, and the rest of the
huge family as part of my upbringing. They were responsible for the
competitive spirit and stick-to-it nature that allowed me to finish this
book. Thanks to my husband Loyd for the nights spent in front of
the computer instead of cooking dinner. Thanks to everyone at Packt
Publishing and especially the team of technical reviewers who did
their best to keep me from making a fool out of myself.
About the reviewers
Philip Rice has been in the computer field since 1980 and began working
with Oracle in 1991. He is now an Oracle DBA for campus enterprise systems
at the University of California Santa Cruz, where the school mascot is a banana
slug. Philip has done presentations on the RMAN topic for User Groups at regional
and national levels.
Charles Schultz has worked at the University of Illinois since 1998 as an
Oracle Database Administrator supporting various central administration services,
including the University-wide ERP, where he specializes in rooting out performance
issues. While Charles's main focus is on Oracle, he has dabbled in Sybase, MySQL,
and MS SQL Server. Charles values the rich resources of the user community and
teaches Oracle classes at a community college.
When not logged into a server, Charles can be found on a volleyball court, eating up
a Sci-fi book, playing with his family, or engaging the community on social issues,
and he has been known to be up in the wee hours playing video games.
Lei Zeng is a seasoned DBA with over ten years of hands-on experience in Oracle
database management and administration on various platforms. She has worked
with many mission-critical databases, including both OLTP databases and data
warehouses up to terabytes. She is an OCP in Oracle 8i, 9i, 10g, 11g, and also a
certified system administrator on HP-UX, Sun Solaris platforms. She has special
interests in areas such as RAC, Data Guard, Stream, database upgrade, migration,
and performance tuning. For the recent years, Lei has become an active contributor
to IOUG's SELECT journal. Currently, she works for Yahoo! and can be reached at
[email protected].
I would like to thank the author, April C. Sims, who worked
diligently on this book to present it to the Oracle DBA community.
This book provides wonderful guidance which will ensure a more
successful DBA career.
Table of Contents
Preface 1
Chapter 1: When to Step Away from the Keyboard 9
Protecting and defending 9
Choosing your tools 11
Graphic-based, command-line Oracle tools and usage 12
Staying away from dinosaurs 14
Insisting on help 14
What does a DBA do all day? 15
Prioritizing tasks—daily, weekly, monthly, quarterly, or yearly 16
SLAs: Why isn't the database down anymore? 18
Avoiding major blunders 19
Summary 21
Chapter 2: Maintaining Oracle Standards 23
Adapting to constant change 23
Database concepts 24
Multiple ORACLE_HOME(s) 24
Keeping the environment clean 25
Oracle's Optimal Flexible Architecture (OFA) 26
11g differences in the OFA standard 27
XWINDOWS and GUI displays 28
Automating day-to-day tasks 28
DBMS_SCHEDULER 29
OS cron utility executing a scheduled task on a Unix server 30
OEM Console plus the Intelligent Agent 31
11g Diagnosability Framework 32
Advisors and checkers 33
Missing temp file resolution 34
Table of Contents
Environmental variables and scripting 39
Guidelines for scripting 40
Separating the configuration file 40
Separating the variable part of the script into its own configuration file 44
Don't hardcode values; reference a configuration file and password file at runtime 45
Putting variables at the top of the script with curly braces 47
Moving functions to a centralized file to be reused 47
Validating the use of the script 48
Using SQL to generate code 48
Helpful Unix commands 51
Reducing operating system differences with common tools 52
Configuration management, release management, and change control 53
Configuration management 53
Using OCM in disconnected mode with masking 54
Mass deployment utility 55
Release management 56
DBA issues with patching 58
Applying a patch without integrating MOS with OCM 60
Using the new patch plan functionality with OCM installed and uploaded to MOS 60
Change control 61
Where, when, and who to call for help 62
My Oracle Support 63
Documentation library 64
Summary 67
Chapter 3: Tracking the Bits and Bytes 69
Dump block 70
Demonstration of data travel path 73
Location of trace files 74
Running dump block SQL statements 75
Identifying files and blocks 78
Tracking the SCN through trace files 80
Oracle's RDBMS Log Miner utility 84
Turn on archivelog mode 86
Add supplemental logging 89
Identification key logging 89
Table-level supplemental logging 89
Flash(back) Recovery Area (FRA) 90
Automatic Undo Management (AUM) 92
Identifying data in undo segments by flashing back to timestamp 92
When to use Log Miner 94
Identifying the data needed to restore 95
SCN, timestamp, or log sequence number 95
Pseudo column ORA_ROWSCN 96
Flashback Transaction Query and Backout 96
[ ii ]
Table of Contents
Enabling flashback logs 97
Flashback Table 98
Flashback Transaction Query with pseudo columns 100
Flashback Transaction Backout 100
Using strings to remove binary components 101
Summary 103
Chapter 4: Achieving Maximum Uptime 105
Maximum Availability Architecture (MAA) 106
Downtime—planned or unplanned 107
MAA with commodity hardware: Case study 109
Optimizing Oracle Database High Availability 111
To archive or not to archive, you pick the mode 112
Multiple archive destinations 113
Moving the archive destination in an emergency 114
Using a different disk device or disk mount 114
Monitoring all hard drive space and Archivelog space 114
Database compatibility parameter with spfile, pfile management 115
Dealing with storage—RAID, SAME, ASM, and OMF 118
RAID—Redundant Arrays of Inexpensive Disks 119
SAME—Stripe and Mirror Everything 119
ASM—Automatic Storage Management 120
Mirrored files—control files and online redo logs 122
Autoextending data files 124
Auditing, log files, and max dump file size 125
What is currently being audited? 126
Auditing Session Activity 128
Other logs to monitor 130
Data dictionary healthcheck 131
SQL*Net hardening, tuning, and troubleshooting 132
Troubleshooting 135
What can go wrong? 136
Grid Control High Availability and Disaster Recovery 137
Recommended installation for GC 10.2.0.5+ 138
Why should I install a separate database? 139
Cookbook for silent install and configuring later 139
Migrating GC repositories 142
Transportable tablespace migrations 142
Keeping the repository highly available 143
Repository backups, restores, or imports 144
MAA—repository on a physical standby database 145
OMS and agents' high availability 146
Cloning Management agents 146
GC at a very large site 147
Summary 148
[ iii ]