OCP:
Oracle 10g
Administration II
Study Guide
Doug Stuns
Tim Buterbaugh
Bob Bryla
SYBEX®

OCP:

Oracle 10

gAdministration II

Study Guide

4368FM.fm Page i Thursday, December 16, 2004 10:17 AM

4368FM.fm Page ii Thursday, December 16, 2004 10:17 AM

San Francisco • London

OCP:

Oracle 10

g

not limited to photocopy, photograph, magnetic, or other record, without the prior agreement and written per-
mission of the publisher.
Library of Congress Card Number: 2004094993
ISBN: 0-7821-4368-7
SYBEX and the SYBEX logo are either registered trademarks or trademarks of SYBEX Inc. in the United States
and/or other countries.
Screen reproductions produced with FullShot 99. FullShot 99 © 1991-1999 Inbit Incorporated. All rights
reserved. FullShot is a trademark of Inbit Incorporated.
The CD interface was created using Macromedia Director, COPYRIGHT 1994, 1997-1999 Macromedia Inc. For
more information on Macromedia and Macromedia Director, visit

http://www.macromedia.com

.
Internet screen shot(s) using Microsoft Internet Explorer 6.0 reprinted by permission from Microsoft Corporation.
SYBEX is an independent entity from Oracle Corporation and is not affiliated with Oracle Corporation in any
manner. This publication may be used in assisting students to prepare for an Oracle Certified Professional exam.
Neither Oracle Corporation nor SYBEX warrants that use of this publication will ensure passing the relevant
exam. Oracle is either a registered trademark or a trademark of Oracle Corporation in the United States and/or
other countries.
TRADEMARKS: SYBEX has attempted throughout this book to distinguish proprietary trademarks from
descriptive terms by following the capitalization style used by the manufacturer.
The author and publisher have made their best efforts to prepare this book, and the content is based upon final
release software whenever possible. Portions of the manuscript may be based upon pre-release versions supplied
by software manufacturer(s). The author and the publisher make no representation or warranties of any kind
with regard to the completeness or accuracy of the contents herein and accept no liability of any kind including
but not limited to performance, merchantability, fitness for any particular purpose, or any losses or damages of
any kind caused or alleged to be caused directly or indirectly from this book.
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1

. At Sybex, we’re
continually striving to meet the needs of individuals preparing for certification exams.
Good luck in pursuit of your Oracle certification!
Neil Edde
Publisher—Certification
Sybex, Inc.

4368FM.fm Page v Thursday, December 16, 2004 10:17 AM

Software License Agreement: Terms and Conditions

The media and/or any online materials accompanying
this book that are available now or in the future con-
tain programs and/or text files (the “Software”) to be
used in connection with the book. SYBEX hereby
grants to you a license to use the Software, subject to
the terms that follow. Your purchase, acceptance, or
use of the Software will constitute your acceptance of
such terms.
The Software compilation is the property of SYBEX
unless otherwise indicated and is protected by copy-
right to SYBEX or other copyright owner(s) as indi-
cated in the media files (the “Owner(s)”). You are
hereby granted a single-user license to use the Software
for your personal, noncommercial use only. You may
not reproduce, sell, distribute, publish, circulate, or
commercially exploit the Software, or any portion
thereof, without the written consent of SYBEX and the
specific copyright owner(s) of any component software
included on this media.

Owner(s).

Warranty

SYBEX warrants the enclosed media to be free of phys-
ical defects for a period of ninety (90) days after pur-
chase. The Software is not available from SYBEX in any
other form or media than that enclosed herein or posted
to

www.sybex.com

. If you discover a defect in the media
during this warranty period, you may obtain a replace-
ment of identical format at no charge by sending the
defective media, postage prepaid, with proof of pur-
chase to:
SYBEX Inc.
Product Support Department
1151 Marina Village Parkway
Alameda, CA 94501
Web:

http://www.sybex.com

After the 90-day period, you can obtain replacement
media of identical format by sending us the defective
disk, proof of purchase, and a check or money order for
$10, payable to SYBEX.


register it. Individual programs differ on details of trial
periods, registration, and payment. Please observe the
requirements stated in appropriate files.

Copy Protection

The Software in whole or in part may or may not be
copy-protected or encrypted. However, in all cases,
reselling or redistributing these files without authoriza-
tion is expressly forbidden except as specifically pro-
vided for by the Owner(s) therein.

4368FM.fm Page vi Thursday, December 16, 2004 10:17 AM

To Cathy, Brant, and Brea.
—Doug Stuns
To Jeaneanne and Gillian, the ladies that I love—and to Arren, Brandon, and
Nicholas, who help me watch over them.
—Tim Buterbaugh
To MC, CM, CR, ES—y’all rock.
—Bob Bryla

4368FM.fm Page vii Thursday, December 16, 2004 10:17 AM

Acknowledgments

First, I want say what a blessing it was to be able to write this book. I am very thankful for
the time and privilege. I am truly thankful to the Lord, my savior, for making this all possible.
Thanks to Mae and Jeff for direction and guidance throughout the writing of this book.
Sarah, your edits and suggestions greatly improved this book and made my job much easier.

Chapter 1

Configuring Recovery Manager 1

Chapter 2

Using Recovery Manager 37

Chapter 3

Recovering From Non-Critical Losses 85

Chapter 4

Database Recovery 107

Chapter 5

Understanding the Flashback Database 163

Chapter 6

Recovering from User Errors 195

Chapter 7

Handling Block Corruption 225

Chapter 8



Index 669

4368FM.fm Page ix Thursday, December 16, 2004 10:17 AM

4368FM.fm Page x Thursday, December 16, 2004 10:17 AM

Contents

Introduction xix
Assessment Test xxxi

Chapter 1 Configuring Recovery Manager 1

Exploring the Features and Components of RMAN 2
RMAN Usage Considerations 4
RMAN Repository and Control Files 6
RMAN Using the Recovery Catalog 7
Starting and Connecting to RMAN 13
Describing Media Management Layer 17
Describing Channel Allocation 17
Parameters and Persistent Settings for RMAN 20
Configuring RMAN Settings with Enterprise Manager 24
Describing Retention Policies 27
Configuring the Control File Autobackup 29
Summary 30
Exam Essentials 30
Review Questions 31
Answers to Review Questions 35


Scheduling a Backup Job 76

4368FM.fm Page xi Thursday, December 16, 2004 10:17 AM

xii

Contents

Summary 77
Exam Essentials 78
Review Questions 79
Answers to Review Questions 83

Chapter 3 Recovering From Non-Critical Losses 85

An Overview of Non-Critical Files 86
Creating a New Temporary Tablespace 87
Starting the Database with a Missing Tempfile 89
Altering the Default Temporary Tablespace for a Database 90
Re-creating Redo Log Files 90
Recovering an Index Tablespace 92
Re-creating Indexes 93
Recovering Read-Only Tablespaces 94
Re-creating the Password File 97
Summary 99
Exam Essentials 99
Review Questions 101
Answers to Review Questions 105

Chapter 4 Database Recovery 107

Contents

xiii

Using the Flash Recovery Area 168
Backing Up the Flash Recovery Area 171
Configuring the Flashback Database 172
Using the Flashback Database with RMAN 173
Monitoring the Flashback Database 175
Using the Flashback Database with Enterprise Manager 177
Configuring the Flashback Database with EM 177
Using the Flashback Database with EM 179
Monitoring the Flashback Database with EM 186
Summary 187
Exam Essentials 187
Review Questions 188
Answers to Review Questions 192

Chapter 6 Recovering from User Errors 195

An Overview of Flashback Technologies 196
Using Flashback Drop 197
Understanding the Recycle Bin 198
Limitations on Flashback Drop and the Recycle Bin 203
Using EM to Perform Flashback Dropped Tables 204
Using Flashback Versions Query 209
Using Flashback Transaction Query 213
Using Flashback Table 215
Summary 217
Exam Essentials 218


Chapter 8 Understanding Automatic Database Management 257

Using the Automatic Workload Repository (AWR) 259
AWR Statistics Collection Facility 259
Workload Repository 262
Active Session History 264
AWR Snapshots 266
AWR Baselines 269
Using AWR Views 270
Using AWR Reports 272
Managing Server-Generated Alerts 274
Threshold and Non-Threshold Alerts 275
Default Alerts 275
An Overview of the Alert Process 275
Using Enterprise Manager to Configure Thresholds 276
Setting Thresholds through PL/SQL 279
Viewing Server Alerts 281
Using Automatic Routine
Administration Tasks 281
Automatic Statistics Collection 282
Understanding the Advisory Framework 294
An Overview of the Advisors 295
Automatic Database Diagnostic Monitor (ADDM) 300
SQL Tuning Advisor 304
Summary 319
Exam Essentials 320
Review Questions 321
Answers to Review Questions 325


Using EM Database Control with ASM Disk Groups 355
Using RMAN to Perform Database Migration to ASM 357
Summary 357
Exam Essentials 358
Review Questions 360
Answers to Review Questions 365

Chapter 10 Understanding Globalization Support 367

An Overview of Globalization Support 368
Globalization Support Features 369
Globalization Support Architecture 370
Supporting Multilingual Applications 372
Using Unicode in a Multilingual Database 374
Using NLS Parameters 376
Setting NLS Parameters 376
Prioritizing NLS Parameters 388
Using NLS Views 391
Using Datetime Datatypes 395

DATE

Datatype

395
TIMESTAMP

Datatype

400

xvi

Contents

Validating Changes 430
Submitting the Pending Area 431
Clearing the Pending Area 432
Resource Consumer Groups 432
Managing Resource Consumer Groups 433
Resource Plans 443
Creating Simple Resource Plans 444
Creating Complex Resource Plans 447
Creating Resource Sub-Plans 448
Modifying Resource Plans 449
Deleting Resource Plans 450
Resource Plan Directives 450
Creating Resource Plan Directives 451
Putting the Pieces Together 460
Creating the Elements 460
Summary 469
Exam Essentials 470
Review Questions 471
Answers to Review Questions 475

Chapter 12 Using the Scheduler to Automate Tasks 477

Scheduler Overview 478
Scheduler Architecture 479
The Job Table 480
The Job Coordinator 480


Using Scheduler Programs 497
Program Attributes 497
Creating Programs 498
Dropping Programs 500
Using Schedules 500
Schedule Attributes 501
Creating Schedules 501
Setting Repeat Intervals 502
Testing Repeat Intervals 504
Using Scheduler Windows 507
Creating Windows 507
Opening and Closing Windows 508
Window Logging 509
Purging Logs 511
Using Scheduler Views 511
Summary 513
Exam Essentials 514
Review Questions 516
Answers to Review Questions 521

Chapter 13 Monitoring and Managing Storage 523

Monitoring Tablespace Storage 525
Space Usage Monitoring 525
Editing Thresholds with Enterprise Manager
Database Control 526
Using

DBMS_SERVER_ALERT


xviii

Contents

Summary 587
Exam Essentials 588
Review Questions 589
Answers to Review Questions 594

Chapter 14 Securing the Oracle Listener, Diagnostic
Sources, and Memory 597

Securing the Oracle Listener 598
An Overview of the TNS Listener 599
Managing the Listener Remotely 600
Setting the Listener Password 601
Controlling Database Access 604
Using Listener Logging 606
Removing Unneeded External Procedure Services 608
Creating a Separate Listener for External Procedures 611
Diagnostic Sources 615
Using the Oracle Alert Log 616
Using Server-Generated Alerts 625
Using Oracle Trace Files 628
Automatic Memory Management 630
Oracle Memory Usage 631
Summary 639
Exam Essentials 640
Review Questions 642

DBA resources remains higher than others during weak economic times.
This book is intended to help you pass the Oracle Database 10

g

: Administration II Exam,
which will establish your credentials as an Oracle Certified Professional (OCP). The OCP cer-
tification is a prerequisite for obtaining an Oracle Certified Master (OCM) certification. Using
this book and a practice database, you can learn the necessary skills to pass the 1Z0-043 Oracle
Database 10

g

: Administration II exam.

Why Become Oracle Certified?

The number one reason to become an OCP is to gain more visibility and greater access to the
industry’s most challenging opportunities. Oracle certification is the best way to demonstrate
your knowledge and skills in Oracle database systems.
Certification is proof of your knowledge and shows that you have the skills required to support
Oracle core products. The Oracle certification program can help a company to identify proven
performers who have demonstrated their skills and who can support the company’s investment
in Oracle technology. It demonstrates that you have a solid understanding of your job role and the
Oracle products used in that role.
OCPs are among the best paid in the IT industry. Salary surveys consistently show the OCP
certification to yield higher salaries than other certifications, including Microsoft, Novell, and
Cisco.
So whether you are beginning your career, changing your career, or looking to secure your
position as a DBA, this book is for you!


Certified Professional (OCP), which builds on and requires
OCA certification. To obtain OCP certification, you must attend an approved Oracle Uni-
versity hands-on class and pass the 1Z0-043 Oracle Database 10

g

: Administration II exam in
a proctored setting.


The third and highest tier is the Oracle 10

g

Certified Master (OCM), which builds on and
requires OCP certification. To obtain OCM certification, you must attend advanced-level
classes and take a two-day, hands-on practical exam.
The material in this book addresses only the Administration II exam. Other Sybex books—
which can be found at

http://www.sybex.com

—can help students new to the DBA world
prepare for the OCA exam 1Z0-042 Oracle Database 10

g

: Administration I. You can also get
information on the Oracle upgrade exam, Oracle Database 10


The Oracle 10

g

Administrator Certified Associate (OCA) certification is a streamlined, entry-
level certification for the database administration track and is required to advance toward the
more senior certification tiers. This certification requires you to pass one exam that demon-
strates your knowledge of Oracle basics:


1Z0-042 Oracle Database 10

g

: Administration I

4368Intro.fm Page xx Thursday, December 16, 2004 10:22 AM

Introduction

xxi

Oracle Database 10

g

Administrator Certified Professional

The OCP tier of the database administration track challenges you to demonstrate your enhanced


Oracle Database 10

g

: Introduction to SQL


Oracle Database 10

g

: New Features for Administrators


Oracle Database 10

g

: Program with PL/SQL
If you already have your OCP in 9

i

or earlier and have elected to take the upgrade path, you
are not required to take the Oracle University class to obtain your OCP for Oracle 10

g

.


g

Upgrade Paths

Existing Oracle Professionals can upgrade their certification in several ways:


An Oracle9

i

OCP can upgrade to 10

g

certification by passing the 1Z0-040 Oracle Data-
base 10g: New Features for Administrators exam.


An Oracle8

i

OCP can upgrade directly to 10

g

by passing the 1Z0-045 Oracle Database 10



certification to 10

g

with the 1Z0-040 Oracle Database 10

g

: New Features
for Administrators exam.

Oracle Database 10

g

Administrator Special Accreditations

New to the Oracle certification program are the Oracle Database 10

g

Administrator Special
Accreditation programs. These accreditations formally recognize the specialized knowledge of
OCPs, in particular database administration areas such as high availability, security, and 10

gGrid Control. OCPs who pass one of these special accreditation exams receive a certificate that

Using Globalization Support

Customize language-dependent behavior for the database and individual sessions.
Specify different linguistic sorts for queries.
Use datetime datatypes.
Query data using case-insensitive and accent-insensitive searches.
Obtain Globalization support configuration information.

Oracle Database 10

g

DBA Assessment

Oracle also provides an optional (and free) prerequisite to all of the proctored exams—the Oracle
Database 10

g

DBA Assessment online exam:


1Z0-041 Oracle Database 10

g

: DBA Assessment
This exam evaluates your proficiency with basic administration and management of an Oracle 10

g


CONFIGURE

.
Manage RMAN’s persistent settings.
Start RMAN utility and allocate channels.

Using Recovery Manager

Use the RMAN

BACKUP

command to create backup sets and image copies.
Enable block change tracking.
Manage the backups and image copies taken with RMAN with the

LIST

and

REPORTcommands.

Diagnostic Sources

Use the alert log and database trace files for diagnostic purposes.
View alerts using Enterprise Manager (EM).

Flashback Database

Determine which flashback technology to use for each recovery situation.
Configure and use Flashback Database.
Monitor the Flashback Database.
Use the Enterprise Manager Recovery Wizard to flashback database.
Manage (or maintain) the Flash Recovery Area.

Recovering from User Errors
Recover a dropped table using Flashback technology.
Perform a Flashback table operation.
Manage the Recycle Bin.
Recover from user errors using Flashback Versions Query.
Perform transaction-level recovery using Flashback Transaction Query.
Dealing with Database Corruption
Define block corruption and list its causes and symptoms.
Detect database corruptions using the following utilities: ANALYZE, DBVERIFY.
Detect database corruptions using the DBMS_REPAIR package.
Implement the DB_BLOCK_CHECKING parameter to detect corruptions.
Repair corruptions using RMAN.
Automatic Database Management
Use the Database Advisors to gather information about your database.
Use the SQL Tuning Advisor to improve database performance.
Use automatic undo retention tuning.
Monitoring and Managing Storage
Tune redo writing and archiving operations.
Issue statements that can be suspended upon encountering space condition errors.
Reduce space-related error conditions by proactively managing tablespace usage.
Reclaim wasted space from tables and indexes using the segment shrink functionality.
Estimate the size of new tables and indexes.