®
OCP Oracle Database 11g:
New Features for
Administrators
Exam Guide (Exam 1Z0-050)
This page intentionally left blank
®
OCP Oracle Database 11g:
New Features for
Administrators
Exam Guide (Exam 1Z0-050)
Sam Alapati
This publication and CD-ROM may be used in assisting students to prepare for the
OCP Oracle Database 11g: New Features for Administrators exam. Neither Oracle
Corporation nor The McGraw-Hill Companies warrant that use of this publication
and CD-ROM will ensure passing the relevant exam.
New York Chicago San Francisco Lisbon London Madrid
Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2008 by The McGraw-Hill Companies, Inc. All rights reserved. Manufactured in the United States of America. Except as
permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or
by any means, or stored in a database or retrieval system, without the prior written permission of the publisher.
0-07-164372-9
The material in this eBook also appears in the print version of this title: 0-07-149682-3.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked
name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the
trademark. Where such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate
training programs. For more information, please contact George Hoare, Special Sales, at
[email protected] or (212)
904-4069.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the
work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve
one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon,
transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may
use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work
may be terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS
TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK,
INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE,
AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED
WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not
warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or
error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless
of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information
accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, spe-
cial, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been
advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim
or cause arises in contract, tort or otherwise.
DOI: 10.1036/0071496823
Professional
Want to learn more?
We hope you enjoy this
McGraw-Hill eBook! If
you’d like more information about this book,
its author, or related books and websites,
please click here.
To the twins, Nina and Nicholas.
ABOUT THE AUTHOR
Sam Alapati (Irvine, TX) is an experienced Oracle DBA who holds the Oracle OCP
DBA (11g) certification and the Hewlett-Packard UNIX System Administrator
certification. He currently manages Oracle databases at the Boy Scouts of America’s
national office in Irving, Texas. Previously, Sam worked for AMR Holdings (Sabre)
and the Blanch Company in Dallas. Sam was a Senior Principal Consultant for
Oracle Corporation in New York, working with NBC and Lehman Brothers. In
addition to being a professional Oracle database administrator, Sam has also taught
Oracle DBA classes for many students and college-level courses at Kansas State
University, University of Texas at Austin, and Rutgers University.
About the Technical Editor
April Wells (Austin, TX) is an experienced Oracle DBA who holds multiple OCP
DBA certifications. She currently manages Oracle databases and Oracle data
warehouses at NetSpend Corporation in Austin, Texas. Previously, April worked
for Oracle Corporation in Austin as on-site support at Dell; at Corporate Systems
in Amarillo, Texas; and at US Steel in Pennsylvania and Minnesota.
CONTENTS AT A GLANCE
1 Installing, Upgrading, and Change Management ................. 1
2 Diagnosing and Resolving Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
3 Intelligent Infrastructure and Database Security . . . . . . . . . . . . . . . . . . . 147
4 Automatic SQL Tuning and SQL Plan Management . . . . . . . . . . . . . . . 213
5 Automatic Storage Management and Partitioning
Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
6 Performance Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
7 RMAN and Flashback Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
8 Oracle SecureFiles and Miscellaneous New Features ................. 449
About the CD-ROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
vii
For more information about this title, click here
CONTENTS
Acknowledgments .................................... xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi
1 Installing, Upgrading, and Change Management ..... 1
Installing Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Changes in the Optimal Flexible Architecture . . . . . . . . . . . . 3
Automatic Diagnostic Repository . . . . . . . . . . . . . . . . . . . . . . 5
Changes in the Installation Options . . . . . . . . . . . . . . . . . . . . 6
New Database Components . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Role and Privilege Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
An Oracle Database 11g Installation . . . . . . . . . . . . . . . . . . . . 8
New Features in Database Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Summary of New Initialization Parameters . . . . . . . . . . . . . . . 10
DBCA Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Using the DBCA to Create a Database . . . . . . . . . . . . . . . . . . 13
Upgrading to Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Moving Data as Part of the Upgrade . . . . . . . . . . . . . . . . . . . . 19
New Privileges for Storage Management . . . . . . . . . . . . . . . . . 20
Upgrading to Oracle Database 11g . . . . . . . . . . . . . . . . . . . . . . 20
Exercise 1-1 Scripts to Run for Upgrading a Database . . . . 31
Real Application Testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Database Replay . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Exercise 1-2 Procedure for Capturing and Replaying
a Database Workload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
The SQL Performance Analyzer . . . . . . . . . . . . . . . . . . . . . . . 51
Exercise 1-3 Testing SQL Performance Following a Database
Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
2 Diagnosing and Resolving Problems . . . . . . . . . . . . . . . . 77
Automatic Diagnostic Repository . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
The Structure of the ADR . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
ADRCI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
viii
Contents ix
Incidents and Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Incident Packaging Service . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Exercise 2-1 Creating an Incident Package
with ADRCI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Support Workbench . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Viewing Critical Error Alerts . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Examining Problem Details . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Collecting Additional Diagnostic Data . . . . . . . . . . . . . . . . . . 98
Creating a Service Request . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Packaging and Uploading Diagnostic Data . . . . . . . . . . . . . . . 100
Tracking the Service Request . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Implementing Repairs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Closing Incidents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Generating a User-Created Problem . . . . . . . . . . . . . . . . . . . . 103
Viewing the Alert Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Health Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Running a Health Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Exercise 2-2 Running a Health Check with Input
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Viewing the Health Monitor Reports . . . . . . . . . . . . . . . . . . . 110
SQL Repair Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Using the Support Workbench . . . . . . . . . . . . . . . . . . . . . . . . 111
Using the DBMS_SQLDIAG Package . . . . . . . . . . . . . . . . . . 111
Exercise 2-3 Exporting a SQL Patch to Another
Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Data Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Repair Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Using RMAN to Manage the Data Recovery Advisor . . . . . . 119
Proactive Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Managing the DRA with Database Control . . . . . . . . . . . . . . 130
New Parameter to Detect Database Corruption . . . . . . . . . . . 131
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
x OCP Oracle Database 11g: New Features for Administrators Exam Guide
3 Intelligent Infrastructure and Database Security . . . . . . 147
Enhancements in AWR Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Managing Baseline Templates . . . . . . . . . . . . . . . . . . . . . . . . . 149
Renaming a Baseline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Setting AWR Baseline Metric Thresholds . . . . . . . . . . . . . . . . 152
Moving Window AWR Baselines . . . . . . . . . . . . . . . . . . . . . . . 155
Managing the New Baseline Features . . . . . . . . . . . . . . . . . . . 156
Controlling Automated Maintenance Tasks . . . . . . . . . . . . . . . . . . . . . 158
Predefined Maintenance Windows . . . . . . . . . . . . . . . . . . . . . 159
Managing the Automatic Maintenance Tasks . . . . . . . . . . . . . 159
Implementing Automatic Maintenance Tasks . . . . . . . . . . . . . 163
Configuring Resource Allocation for Automatic Tasks . . . . . . 163
I/O Calibration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Exercise 3-1 Calibrating I/O Resources . . . . . . . . . . . . . . . 164
Database Resource Manager New Features . . . . . . . . . . . . . . . . . . . . . . 167
Per Session I/O Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Pre-Created Mixed Workload Resource Plan . . . . . . . . . . . . . 172
Using New Oracle Scheduler Features . . . . . . . . . . . . . . . . . . . . . . . . . 173
Lightweight Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Exercise 3-2 Creating an Array of Regular Scheduler Jobs 177
Remote External Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Scheduler Support for Data Guard . . . . . . . . . . . . . . . . . . . . . . 184
Security Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Secure Password Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Configuring Fine-Grained Access to Network Services . . . . . 190
Encrypting Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Creating the Oracle Wallet . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Creating an Encrypted Tablespace . . . . . . . . . . . . . . . . . . . . . . 196
Restrictions on Tablespace Encryption . . . . . . . . . . . . . . . . . . 198
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
4 Automatic SQL Tuning and SQL Plan Management . . . 213
Automatic SQL Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
SQL Profiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Contents xi
Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
The Automatic SQL Tuning Process . . . . . . . . . . . . . . . . . . . . 217
Exercise 4-1 Using Enterprise Manager to Access
the SQL Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Interpreting Automatic SQL Tuning Reports . . . . . . . . . . . . . 223
Data Dictionary Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
SQL Plan Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Capturing SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . 227
Fixed SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
SQL Plan Baseline Attributes . . . . . . . . . . . . . . . . . . . . . . . . . 235
Managing SQL Plan Baselines . . . . . . . . . . . . . . . . . . . . . . . . . 237
The SQL Management Base . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Managing SPM with the Enterprise Manager . . . . . . . . . . . . . 240
SQL Access Advisor Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
New Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Partitioning Recommendations . . . . . . . . . . . . . . . . . . . . . . . . 242
Publish Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Running a SQL Access Advisor Job Using PL/SQL . . . . . . . . 243
Using Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Using the Cursor Cache to Get SQL Access Advisor
Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
5 Automatic Storage Management and Partitioning
Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Automatic Storage Management New Features . . . . . . . . . . . . . . . . . . 264
ASM Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
ASM Fast Mirror Resync . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
ASM Preferred Mirror Read . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
ASM Scalability and Performance Enhancements . . . . . . . . . 272
New SYSASM Privilege . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
ASM Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Changing ASM Disk Group Attributes . . . . . . . . . . . . . . . . . . 279
New Manageability Options for Commands . . . . . . . . . . . . . . 280
xii OCP Oracle Database 11g: New Features for Administrators Exam Guide
Exercise 5-1 Using the md_backup and md_restore
Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Partitioning Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Interval Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
System Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Virtual Column-Based Partitioning . . . . . . . . . . . . . . . . . . . . . 298
Reference Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Composite Partitioning Enhancements . . . . . . . . . . . . . . . . . . 307
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
6 Performance Enhancements . . . . . . . . . . . . . . . . . . . . . . 321
ADDM Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
ADDM for Real Application Clusters . . . . . . . . . . . . . . . . . . . 322
New DBMS_ADDM Package . . . . . . . . . . . . . . . . . . . . . . . . . 324
Automatic Memory Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328
SGA, PGA, and the MEMORY_TARGET Parameter . . . . . . 329
Exercise 6-1 Using Automatic Memory Management . . . . 331
Monitoring Automatic Memory Management . . . . . . . . . . . . 332
DBCA and Automatic Memory Management . . . . . . . . . . . . 333
Enhancements in Optimizer Statistics Collection . . . . . . . . . . . . . . . . 334
Statistics Preferences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Partitioned Tables and Incremental Statistics . . . . . . . . . . . . . 337
New Sampling Technique . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Deferred Statistics Publishing . . . . . . . . . . . . . . . . . . . . . . . . . 338
Extended Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Result Cache Memory Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Managing the Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Caching SQL Results with a Result_Cache Hint . . . . . . . . . . 348
Using the DBMS_RESULT_CACHE Package . . . . . . . . . . . . 350
Using Dynamic Performance Views . . . . . . . . . . . . . . . . . . . . . 353
The SQL Query Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . 354
The PL/SQL Function Result Cache . . . . . . . . . . . . . . . . . . . . 357
The Client Query Result cache . . . . . . . . . . . . . . . . . . . . . . . . 359
Adaptive Cursor Sharing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
Contents xiii
How Adaptive Cursor Sharing Works . . . . . . . . . . . . . . . . . . . 364
Monitoring Adaptive Cursor Sharing . . . . . . . . . . . . . . . . . . . 365
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
7 RMAN and Flashback Enhancements . . . . . . . . . . . . . . . 383
RMAN Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Active (Network-Based) Database Duplication . . . . . . . . . . . 384
Parallel Backup and Restore of Large Files . . . . . . . . . . . . . . . 392
Archival (Long-Term) Backups . . . . . . . . . . . . . . . . . . . . . . . . 394
Fast Incremental Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Improved Block Media Recovery Performance . . . . . . . . . . . . 398
New Persistent Configuration Parameters . . . . . . . . . . . . . . . . 401
Backup Failover to Non-Flash Recovery Areas . . . . . . . . . . . . 404
Recovery Catalog Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Merging Recovery Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Virtual Private Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
New Flashback-Related Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Flashback Data Archive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Flashback Transaction Backout . . . . . . . . . . . . . . . . . . . . . . . . 427
Exercise 7-1 Using the TRANSACTION_BACKOUT
Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
8 Oracle SecureFiles and Miscellaneous New Features . . . 449
Oracle SecureFiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Enabling SecureFiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
Capabilities of SecureFiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
Storage Options for SecureFiles . . . . . . . . . . . . . . . . . . . . . . . . 453
Creating SecureFiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
Managing and Monitoring SecureFiles . . . . . . . . . . . . . . . . . . 456
Migrating to SecureFiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
xiv OCP Oracle Database 11g: New Features for Administrators Exam Guide
Online Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
Locking Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
Minimal Invalidation of Dependent Objects . . . . . . . . . . . . . . 460
Creating a Parameter File from Memory . . . . . . . . . . . . . . . . . 461
Hot Patching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462
Miscellaneous New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Invisible Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Shrinking Temporary Tablespaces . . . . . . . . . . . . . . . . . . . . . . 465
Tablespace Option for Creating Temporary Tables . . . . . . . . . 467
PL/SQL and Java Automatic Native Compilation . . . . . . . . . 467
Exercise 8-1 Setting Up a PL/SQL Program Unit for
Native Compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
OLTP Table Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Direct NFS Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 480
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490
Appendix About the CD-ROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
Installing and Running MasterExam . . . . . . . . . . . . . . . . . . . . 491
MasterExam . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Electronic Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Removing Installation(s) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Technical Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
LearnKey Technical Support . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
ACKNOWLEDGMENTS
I wish to acknowledge the excellent technical editing of the book by April Wells. Thanks to
April’s careful and expert technical vetting, I’ve been able to avoid errors and improve the
quality of the presentation.
In the last few years, over the course of writing three books for Oracle Press,
Timothy Green, Senior Acquisitions Editor, has also become my friend. Tim’s
sagacious replies to queries as well as excellent counsel and encouragement have
been extremely helpful. I also thank the excellent help and support provided by
the Acquisitions Coordinator, Jennifer Housh.
I appreciate the terrific job done by Laura Stone, the Project Editor, in seeing the
chapters through all the editing and production stages with great patience, skill, and
cheer. Laura has shown great alacrity and exceptional understanding at various stages
of the manuscript to keep the workflow moving steadily. Exceptional copy editing by
Nancy Rapoport has significantly enhanced the style and presentation of the contents
of the book. I’m also grateful to the great proofreading by Susie Elkind. I’m lucky to
have friends at work such as Myra Riggs and Dabir Haider. Myra always finds time to
talk about just about anything, and I can always count on her help and advice. Dabir
has been a source of help and support ever since he joined us last year. I couldn’t have
taken care of business without the kind help of Leticia Salazar, who has gone out of
her way to help me in numerous ways throughout the last year—thanks, Leticia! My
buddy Mark Potts has been a true friend over the years and I’m grateful to him for all
his help. My colleagues at work, Lance Parkes and Rob Page, are always helpful, and I
acknowledge their kindness as well. I’m fortunate to work with David Jeffress and Dave
Campbell, two exceptional managers, and thanks go out to both of them. I would also
like to express my appreciation for Dan Nelson and Debra Kendrew for their friendship
and caring.
Finally, I’d like to extend my profound thanks to both of my families. I’m grateful to
Mom; Dad; and my two brothers, Hari Hara Prasad and Siva Sankara Prasad; for their
love, affection, and support. Thanks also to Aruna, Vanaja, Ashwin, Teja, Aparna,
and Soumya for their love and kindness over the years. I wish to acknowledge the
enormous debt I owe to my wife, Valerie, who had to fill in the void left by my absence
from the home front during the writing of this book. Finally, thanks to Shannon and
the twins, Nina and Nicholas, who always love it when we get to look at the first
copies of a newly printed book!
xv
INTRODUCTION
O racle Database 11g offers several highly powerful enhancements to the management
of Oracle databases. Oracle databases are the leading commercial databases in the
world today, and it’s a great challenge as well as a matter of pride to certify in the latest
flagship offering of the Oracle Corporation. For more information about the Oracle certification
exams, requirements for certification, or recertification, please visit Oracle’s certification web site
(http://www.oracle.com/education/certification).
This book will help you in your endeavor to upgrade your Oracle OCP DBA
certification to the latest level—Oracle Database 11g. It is a study guide for Oracle
Database administrators who plan to take the OCP Oracle Database 10g: New Features
for Administrators Exam Guide (Exam 1Z0-050). This exam is also known commonly
as the DBA “Upgrade” exam because you can upgrade to the latest Oracle Database 11g
OCP certification from the Oracle10g OCP certification. In order to take this exam,
Oracle Corporation must have already certified you as an Oracle10g OCP.
If your goal is to update your credentials, you can do so by diligently working through
this book, doing all the exercises and practice tests. However, your goal should ideally
reach beyond passing the exam. Ultimately, you’ll need to use the new skills you learn
in your present job, and the only way to do so is by clearly understanding the nuances of
the various enhancements offered by the Oracle Database 11g release. To this end, I’ve
designed this book as much more than a simple exam guide, by carefully introducing the
topics and discussing them in sufficient detail to provide you with a solid understanding.
Some of you, I’m sure, are tempted to just learn the minimum necessary to pass the
upgrade exam. However, I urge you to delve deeper into the new release by testing
the new features on your own and exploring the many new capabilities of the Oracle
Database 11g release. In general, Oracle certifications are voluntary, in the sense that
in most cases they are not prerequisites for a job working with Oracle products. The
real value of certification comes in the mastery of a wide range of capabilities of the
Oracle database software. As the first chapter shows you, it is easier to install the new
Oracle software than ever before. Therefore, you really don’t have any excuses for not
installing the Oracle Database 11g software on your workstation or a test server and
exploring all the new features. I’ve found that testing the various features is the best
way to understand and remember the nuances of the new features, including the usage
of the new commands and SQL statements that help implement the features.
In This Book
This book is organized to serve as an in-depth review for the OCP Oracle Database
11g: New Features for Administrators Exam for Oracle professionals who already are
certified OCPs. Each chapter covers a major aspect of the exam; all the OCP official
certification objectives are carefully covered in the book.
xvi
Introduction xvii
On the CD-ROM
The CD-ROM contains the entire contents of the book in electronic form, as well as
two practice tests that simulate the real Oracle Database 11g OCP certification test.
For more information on the CD-ROM, please see the appendix.
Exam Readiness Checklist
At the end of this introduction, you will find an Exam Readiness Checklist. I
constructed this table to allow you to cross-reference the official exam objectives with
the certification objectives as I present and cover them in this book. The checklist
also allows you to gauge your level of expertise on each objective at the outset of your
studies. This should allow you to check your progress and make sure you spend the
time you need on more difficult or unfamiliar sections. I’ve provided a reference for
each objective exactly as Oracle Corporation presents it, including the section of the
study guide that covers that objective, as well as a chapter and page reference.
In Every Chapter
This book includes a set of chapter components that call your attention to important
items, reinforce important points, and provide helpful exam-taking hints. Take a
look at what you’ll find in every chapter:
■ Exam Watch notes call attention to information about, and potential pitfalls
in, the exam. As I mentioned earlier, I took the new OCP exam and received
my certification. I took the Beta exam in December, 2008—it had over 170
questions, so I know something about what you will go through!
■ Exercises are interspersed throughout the chapters, and they allow you to
get the hands-on experience you need in order to pass the exams. They help
you master skills that are likely to be an area of focus on the exam. Don’t
just read through the exercises; they are hands-on practice that you should
be comfortable completing. Learning by doing is an effective way to increase
your competency with a product.
■ On the Job notes describe the issues that come up most often in real-world
settings. They provide a valuable perspective on certification- and product-
related topics. They point out common mistakes and address questions that
have arisen from on-the-job discussions and experience.
■ Inside the Exam sections toward the end of each chapter are designed to
anticipate what the exam will emphasize. I provide pointers regarding key
topics to focus on, based on my own exam experience. You can get a leg up
xviii OCP Oracle Database 11g: New Features for Administrators Exam Guide
on how to respond to actual exam questions by focusing extra attention on
the contents referred to in the Inside the Exam sections.
■ The Certification Summary is a succinct review of the chapter and a
restatement of salient points regarding the exam.
✓ ■ The Two-Minute Drill at the end of every chapter is a checklist of the main
points of the chapter. You can use it for a quick, last-minute review before
the test.
Q&A ■ The Self Test offers questions similar to those found on the certification
exam. The answers to these questions, as well as explanations of the answers,
can be found at the end of each chapter. By taking the Self Test after
completing each chapter, you’ll reinforce what you’ve learned from that
chapter, while becoming familiar with the structure of the exam questions.
■ The Lab Questions at the end of the Self Test sections offer a unique and
challenging question format that, in order to answer correctly, require the
reader to understand multiple chapter concepts. These questions are more
complex and more comprehensive than the other questions, as they test your
ability to take all the knowledge you have gained from reading the chapter
and apply it to complicated, real-world situations.
Some Pointers
Once you’ve finished reading this book, set aside some time to do a thorough review.
You might want to return to the book several times and make use of all the methods
it offers for reviewing the material:
■ Reread all the Two-Minute Drills or have someone quiz you. You also can
use the drills as a way to do a quick cram before the exam. You might want
to make some flash cards out of 3 × 5 index cards that have the Two-Minute
Drill material on them.
■ Reread all the Exam Watch notes. Remember that these notes are based on
the OCP exam that I took and passed. I’ve tried to draw your attention to
what you should expect—and what you should be on the lookout for.
■ Retake the Self Tests. It is a good idea to take the test right after you’ve read
the chapter because the questions help reinforce what you’ve just learned.
However, it’s an even better idea to go back later and do all the questions
in the book in one sitting. Pretend that you’re taking the live exam. (When
you go through the questions the first time, you should mark your answers
on a separate piece of paper. That way, you can run through the questions
as many times as you need to until you feel comfortable with the material.)
Copyright by webtailieu.net