OCP Oracle Database 11g New Features Exam Guide P1

Chia sẻ: Vong Phat | Ngày: | Loại File: PDF | Số trang:30

Thêm vào BST

Báo xấu

181
lượt xem 37
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCP Oracle Database 11g New Features Exam Guide P1

® 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 george_hoare@mcgraw-hill.com 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.)