RMAN Recipes for Oracle Database 11g P1

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

Thêm vào BST

Báo xấu

92
lượt xem 17
download

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

What skills set the database administrator (DBA) apart from other technologists? Of the many responsibilities laid upon a DBA, which cannot be performed by someone else? Adding database accounts? Creating tables and indexes? Installing and configuring databases? Optimizing the database and the applications that access and manipulate it?

Chủ đề:

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

Lưu

Nội dung Text: RMAN Recipes for Oracle Database 11g P1

The eXperT’s Voice ® in oracle Practical RMAN for the Busy DBA RMAN Recipes for Oracle Database 11g A Problem-Solution Approach An example-based approach to backing up and recovering your Oracle database. Darl Kuhn, Sam Alapati, and Arup Nanda
RMAN Recipes for Oracle Database 11g A Problem-Solution Approach Darl Kuhn, Sam Alapati, and Arup Nanda
RMAN Recipes for Oracle Database 11g: A Problem-Solution Approach Copyright © 2007 by Darl Kuhn, Sam Alapati, Arup Nanda All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-59059-851-1 ISBN-10 (pbk): 1-59059-851-2 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Jonathan Gennick Technical Reviewer: Bernard Lopuz Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Jonathan Gennick, Jason Gilmore, Jonathan Hassell, Chris Mills, Matthew Moodie, Jeffrey Pepper, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Project Manager: Richard Dal Porto Copy Edit Manager: Nicole Flores Copy Editor: Kim Wimpsett Assistant Production Director: Kari Brooks-Copony Production Editor: Lori Bring Compositor: Diana Van Winkle, Van Winkle Design Group Proofreader: Dan Shaw Indexer: Broccoli Information Management Artist: Diana Van Winkle, Van Winkle Design Group Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders-ny@springer-sbm.com, or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600, Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, or visit http://www.apress.com. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com in the Source Code/ Download section. You will need to answer questions pertaining to this book in order to successfully download the code.
To Heidi, Lisa, and Brandi. —Darl Kuhn To my wife Valerie; for her enormous support and sacrifice. —Sam Alapati To Anu and Anish. —Arup Nanda
Contents at a Glance Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix ■CHAPTER 1 Backup and Recovery 101 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 ■CHAPTER 2 Jump-Starting RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 ■CHAPTER 3 Using the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 ■CHAPTER 4 Using RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 ■CHAPTER 5 Configuring the RMAN Environment . . . . . . . . . . . . . . . . . . . . . 113 ■CHAPTER 6 Using the Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 ■CHAPTER 7 Making Backups with RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 ■CHAPTER 8 Maintaining RMAN Backups and the Repository . . . . . . . . . 225 ■CHAPTER 9 Scripting RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 ■CHAPTER 10 Restoring the Control File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 ■CHAPTER 11 Performing Complete Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . 313 ■CHAPTER 12 Performing Incomplete Recovery . . . . . . . . . . . . . . . . . . . . . . . . 359 ■CHAPTER 13 Performing Flashback Recovery . . . . . . . . . . . . . . . . . . . . . . . . . 385 ■CHAPTER 14 Handling Online Redo Log Failures . . . . . . . . . . . . . . . . . . . . . . 427 ■CHAPTER 15 Duplicating Databases and Transporting Data . . . . . . . . . . . 443 ■CHAPTER 16 Tuning RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 ■CHAPTER 17 Troubleshooting RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517 ■CHAPTER 18 Using a Media Management Layer . . . . . . . . . . . . . . . . . . . . . . . 545 ■CHAPTER 19 Performing Backup and Recovery with Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583 ■CHAPTER 20 Using the Data Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . . 611 ■CHAPTER 21 Using RMAN on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623 ■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645 v
Contents Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix ■CHAPTER 1 Backup and Recovery 101 ................................1 Types of Database Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Oracle Backup and Recovery Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Backup Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Recovery Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 RMAN Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Backup and Recovery Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 ■CHAPTER 2 Jump-Starting RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2-1. Connecting to Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2-2. Starting and Stopping Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 2-3. Toggling Archivelog Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 2-4. Connecting to RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 2-5. Backing Up Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 2-6. Simulating a Failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 2-7. Restoring and Recovering Your Database . . . . . . . . . . . . . . . . . . . . . . . . 35 ■CHAPTER 3 Using the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . 39 3-1. Creating the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 3-2. Writing Regular RMAN Backups to the FRA . . . . . . . . . . . . . . . . . . . . . . . 41 3-3. Freeing FRA Space in an Emergency . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 3-4. Checking Space Usage in the FRA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 3-5. Expanding or Shrinking the FRA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 3-6. Configuring Archived Redo Logs to Go to FRA . . . . . . . . . . . . . . . . . . . . . 53 3-7. Using the Same FRA for Two Databases with the Same Name . . . . . . . . . 55 3-8. Placing a Control File in the FRA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 3-9. Placing Online Redo Log Files in FRA . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 3-10. Sending Image Copies to the FRA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 3-11. Deleting Backup Sets from the FRA . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 vii
viii ■CONTENTS 3-12. Deleting Archived Redo Logs from the FRA . . . . . . . . . . . . . . . . . . . . . . 73 3-13. Reinstating a Damaged Datafile from an Image Copy . . . . . . . . . . . . . . 74 3-14. Switching Back from an Image Copy . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 3-15. Backing Up the FRA to Tape . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 3-16. Sizing the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 ■CHAPTER 4 Using RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 4-1. Starting the RMAN Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 4-2. Issuing RMAN Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 4-3. Saving RMAN Output to a Text File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 4-4. Logging Command-Line RMAN Output . . . . . . . . . . . . . . . . . . . . . . . . . . 93 4-5. Connecting to a Target Database from the RMAN Prompt . . . . . . . . . . . . 94 4-6. Connecting to a Target Database from the Operating System Command Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 4-7. Executing Operating System Commands from Within RMAN . . . . . . . . . . 96 4-8. Scripting RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 4-9. Executing RMAN Command Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 4-10. Creating Dynamic Command Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 4-11. Connecting to an Auxiliary Database . . . . . . . . . . . . . . . . . . . . . . . . . . 102 4-12. Executing Multiple RMAN Commands As a Single Unit . . . . . . . . . . . . . 103 4-13. Issuing SQL Statements from the RMAN Client . . . . . . . . . . . . . . . . . . 104 4-14. Starting and Shutting Down a Database with RMAN . . . . . . . . . . . . . . . 106 4-15. Checking the Syntax of RMAN Commands . . . . . . . . . . . . . . . . . . . . . 107 4-16. Hiding Passwords When Connecting to RMAN . . . . . . . . . . . . . . . . . . . 109 4-17. Identifying RMAN Server Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 4-18. Dropping a Database using the RMAN Client . . . . . . . . . . . . . . . . . . . . 112 ■CHAPTER 5 Configuring the RMAN Environment . . . . . . . . . . . . . . . . . . . . 113 5-1. Showing RMAN Configuration Settings . . . . . . . . . . . . . . . . . . . . . . . . . 113 5-2. Configuring RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 5-3. Restoring Default Parameter Settings . . . . . . . . . . . . . . . . . . . . . . . . . . 117 5-4. Enabling and Disabling Automatic Control File Backups . . . . . . . . . . . . . 118 5-5. Specifying the Autobackup Control File Directory and Filename . . . . . . . 120 5-6. Specifying the Snapshot Control Filename and Location. . . . . . . . . . . . . 121 5-7. Specifying the Retention Period for RMAN History . . . . . . . . . . . . . . . . . 122 5-8. Configuring the Default Device Type . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 5-9. Configuring the Default Backup Type . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 5-10. Making Compressed Backup Sets the Default . . . . . . . . . . . . . . . . . . . 126 5-11. Configuring Multiple Backup Copies . . . . . . . . . . . . . . . . . . . . . . . . . . 127 5-12. Skipping Previously Backed Up Files . . . . . . . . . . . . . . . . . . . . . . . . . 129 5-13. Specifying Backup Piece Filenames . . . . . . . . . . . . . . . . . . . . . . . . . . 133 5-14. Generating Filenames for Image Copies . . . . . . . . . . . . . . . . . . . . . . . 134
■CONTENTS ix 5-15. Tagging RMAN Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 5-16. Configuring Automatic Channels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 5-17. Manually Allocating RMAN Channels . . . . . . . . . . . . . . . . . . . . . . . . . . 140 5-18. Allocating an RMAN Maintenance Channel . . . . . . . . . . . . . . . . . . . . . 142 5-19. Creating a Backup Retention Policy . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 5-20. Configuring an Archived Redo Log Deletion Policy . . . . . . . . . . . . . . . . 145 5-21. Limiting the Size of Individual Backup Pieces . . . . . . . . . . . . . . . . . . . 146 5-22. Configuring the Maximum Size of Backup Sets . . . . . . . . . . . . . . . . . . 146 ■CHAPTER 6 Using the Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 6-1. Creating the Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 6-2. Granting Restricted Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 6-3. Connecting to the Catalog from the Command Line . . . . . . . . . . . . . . . . 157 6-4. Connecting to the Catalog from the RMAN Prompt . . . . . . . . . . . . . . . . 159 6-5. Registering Target Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 6-6. Unregistering a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 6-7. Cataloging Older Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 6-8. Updating the Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164 6-9. Dropping the Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 6-10. Merging Recovery Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 6-11. Moving the Recovery Catalog to Another Database . . . . . . . . . . . . . . . 170 6-12. Creating a High-Availability Recovery Catalog . . . . . . . . . . . . . . . . . . . 170 6-13. Viewing Backup Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 6-14. Uncataloging RMAN Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 6-15. Using a Release 11.x Client with Older Catalogs . . . . . . . . . . . . . . . . . . 173 ■CHAPTER 7 Making Backups with RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Backup Sets and Image Copies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 RMAN Backup Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178 Types of Files That RMAN Can Back Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 RMAN Backup Destinations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 7-1. Specifying Backup Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 7-2. Backing Up the Control File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 7-3. Backing Up the Server Parameter File . . . . . . . . . . . . . . . . . . . . . . . . . . 185 7-4. Backing Up Datafiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185 7-5. Backing Up Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 7-6. Making a Whole-Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 7-7. Backing Up Archived Redo Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 7-8. Backing Up Everything . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 7-9. Backing Up Flash Recovery Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 7-10. Performing Incremental Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 7-11. Reducing Incremental Backup Time . . . . . . . . . . . . . . . . . . . . . . . . . . 198
x ■CONTENTS 7-12. Creating Multiple Backup Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 7-13. Making Copies of Backup Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 7-14. Making Copies of Image Copy Backups . . . . . . . . . . . . . . . . . . . . . . . . 203 7-15. Making Tape Copies of Disk-Based Image Copies . . . . . . . . . . . . . . . . 204 7-16. Excluding a Tablespace from a Backup . . . . . . . . . . . . . . . . . . . . . . . . 205 7-17. Skipping Read-Only, Offline, or Inaccessible Files . . . . . . . . . . . . . . . . . 206 7-18. Encrypting RMAN Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 7-19. Making a Compressed Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 7-20. Parallelizing Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 7-21. Making Faster Backups of Large Files . . . . . . . . . . . . . . . . . . . . . . . . . 212 7-22. Specifying Backup Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214 7-23. Reusing RMAN Backup Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 7-24. Retaining Backups for a Long Time . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 7-25. Backing Up Only Those Files Previously Not Backed Up . . . . . . . . . . . . 218 7-26. Restarting Backups After a Crash . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219 7-27. Updating Image Copies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 ■CHAPTER 8 Maintaining RMAN Backups and the Repository . . . . . . . 225 8-1. Adding User-Made Backups to the Repository . . . . . . . . . . . . . . . . . . . . 226 8-2. Finding Datafiles and Archivelogs That Need a Backup . . . . . . . . . . . . . 227 8-3. Finding Datafiles Affected by Unrecoverable Operations . . . . . . . . . . . . 229 8-4. Identifying Obsolete Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 8-5. Displaying Information About Database Files . . . . . . . . . . . . . . . . . . . . . 232 8-6. Listing RMAN Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233 8-7. Listing Expired Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 8-8. Listing Only Recoverable Backups and Copies . . . . . . . . . . . . . . . . . . . . 237 8-9. Listing Restore Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237 8-10. Listing Database Incarnations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238 8-11. Updating the RMAN Repository After Manually Deleting Backups . . . . . 239 8-12. Synchronizing the Repository with the Actual Backups . . . . . . . . . . . . 240 8-13. Deleting Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 8-14. Deleting Archived Redo Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 8-15. Deleting Obsolete RMAN Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248 8-16. Changing the Status of an RMAN Backup Record . . . . . . . . . . . . . . . . 249 8-17. Changing the Status of Archival Backups . . . . . . . . . . . . . . . . . . . . . . 250 8-18. Testing the Integrity of an RMAN Backup . . . . . . . . . . . . . . . . . . . . . . . 251 8-19. Validating Datafiles, Backup Sets, and Data Blocks. . . . . . . . . . . . . . . . 252 ■CHAPTER 9 Scripting RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 Approaches to Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 9-1. Developing a Unix Shell Script for RMAN . . . . . . . . . . . . . . . . . . . . . . . . 259 9-2. Scheduling a Unix Shell File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
■CONTENTS xi 9-3. Developing a Windows Batch File to Run RMAN . . . . . . . . . . . . . . . . . . 267 9-4. Scheduling a Script in Windows via the GUI . . . . . . . . . . . . . . . . . . . . . . 272 9-5. Changing the Schedule of a Batch Job in the Task Scheduler . . . . . . . . . 275 9-6. Scheduling in Windows from the Command Line . . . . . . . . . . . . . . . . . . 276 9-7. Creating Local-Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277 9-8. Creating a Global-Stored Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 9-9. Updating Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 9-10. Commenting on Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282 9-11. Displaying Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 9-12. Listing Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284 9-13. Dropping Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285 9-14. Executing a Global Script When a Local Script of the Same Name Exists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 9-15. Converting Stored Scripts to Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 9-16. Creating or Replacing a Stored Script from a File . . . . . . . . . . . . . . . . . 287 9-17. Passing Parameters to Stored Scripts . . . . . . . . . . . . . . . . . . . . . . . . . 288 9-18. Creating a Parameterized Command File Script . . . . . . . . . . . . . . . . . . 291 ■CHAPTER 10 Restoring the Control File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 10-1. Restoring Control File Using Flash Recovery Area . . . . . . . . . . . . . . . . 296 10-2. Restoring Control File Using Recovery Catalog . . . . . . . . . . . . . . . . . . . 298 10-3. Determining the Database Identifier . . . . . . . . . . . . . . . . . . . . . . . . . . 300 10-4. Restoring Control File with No Flash Recovery Area or Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 10-5. Restoring Control File to Nondefault Location . . . . . . . . . . . . . . . . . . . 307 10-6. Restoring Lost Copy of Multiplexed Control File . . . . . . . . . . . . . . . . . . 308 10-7. Re-creating the Control File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310 ■CHAPTER 11 Performing Complete Recovery . . . . . . . . . . . . . . . . . . . . . . . . . 313 Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 If You’re Still Awake... . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 11-1. Determining How to Restore and Recover . . . . . . . . . . . . . . . . . . . . . . 318 11-2. Previewing Backups Needed for Restore . . . . . . . . . . . . . . . . . . . . . . . 321 11-3. Verifying Integrity of Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323 11-4. Testing Media Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326 11-5. Performing Database-Level Recovery . . . . . . . . . . . . . . . . . . . . . . . . . 327 11-6. Performing Tablespace-Level Recovery . . . . . . . . . . . . . . . . . . . . . . . . 329 11-7. Performing Datafile-Level Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . 330 11-8. Restoring Datafiles to Nondefault Locations . . . . . . . . . . . . . . . . . . . . 332 11-9. Performing Block-Level Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334 11-10. Recovering Read-Only Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . 337 11-11. Restoring Temporary Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
xii ■CONTENTS 11-12. Forcing RMAN to Restore a File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338 11-13. Restoring from an Older Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 11-14. Recovering Through Resetlogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 11-15. Restoring the Spfile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 11-16. Restoring Archived Redo Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . 345 11-17. Recovering Datafiles Not Backed Up . . . . . . . . . . . . . . . . . . . . . . . . . 347 11-18. Deleting Archived Redo Log Files During Recovery . . . . . . . . . . . . . . 349 11-19. Restoring from Uncataloged Backup Pieces in Oracle Database 10g and Newer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350 11-20. Restoring from Uncataloged Backup Pieces in Oracle9i Database and Older . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351 ■CHAPTER 12 Performing Incomplete Recovery . . . . . . . . . . . . . . . . . . . . . . . 359 Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 12-1. Determining Type of Incomplete Recovery . . . . . . . . . . . . . . . . . . . . . . 362 12-2. Performing Time-Based Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 12-3. Performing Log Sequence–Based Recovery . . . . . . . . . . . . . . . . . . . . 364 12-4. Performing Cancel-Based Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . 366 12-5. Using LogMiner to Find an SCN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 12-6. Performing Change/SCN-Based Recovery . . . . . . . . . . . . . . . . . . . . . . 370 12-7. Recovering to a Restore Point . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 12-8. Restoring a Noarchivelog Mode Database . . . . . . . . . . . . . . . . . . . . . . 373 12-9. Recovering to a Previous Incarnation . . . . . . . . . . . . . . . . . . . . . . . . . . 374 12-10. Performing Tablespace Point-in-Time Recovery . . . . . . . . . . . . . . . . . 377 12-11. Recovering a Subset of Datafiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 12-12. Troubleshooting Incomplete Recovery . . . . . . . . . . . . . . . . . . . . . . . . 382 ■CHAPTER 13 Performing Flashback Recovery . . . . . . . . . . . . . . . . . . . . . . . . 385 Introducing Flashback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385 13-1. Checking the Flashback Status of a Database . . . . . . . . . . . . . . . . . . . 387 13-2. Enabling Flashback on a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 387 13-3. Disabling Flashback on a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 390 13-4. Flashing Back a Database from RMAN . . . . . . . . . . . . . . . . . . . . . . . . 390 13-5. Flashing Back a Database from SQL . . . . . . . . . . . . . . . . . . . . . . . . . . 397 13-6. Finding Out How Far Back into the Past You Can Flash Back . . . . . . . . 400 13-7. Estimating the Amount of Flashback Logs Generated at Various Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 13-8. Estimating the Space Occupied by Flashback Logs in the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 13-9. Creating Normal Restore Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404 13-10. Creating Guaranteed Restore Points . . . . . . . . . . . . . . . . . . . . . . . . . 405 13-11. Listing Restore Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
■CONTENTS xiii 13-12. Dropping Restore Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407 13-13. Recovering a Dropped Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407 13-14. Undropping a Table When Another Exists with the Same Name . . . . . 409 13-15. Undropping a Specific Table from Two Dropped Tables with the Same Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411 13-16. Checking the Contents of the Recycle Bin . . . . . . . . . . . . . . . . . . . . . 412 13-17. Restoring Dependent Objects of an Undropped Table . . . . . . . . . . . . . 414 13-18. Turning Off the Recycle Bin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417 13-19. Clearing the Recycle Bin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418 13-20. Querying the History of a Table Row (Flashback Query) . . . . . . . . . . . 420 13-21. Flashing Back a Specific Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422 ■CHAPTER 14 Handling Online Redo Log Failures . . . . . . . . . . . . . . . . . . . . . 427 How Redo Logs Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 14-1. Determining How to Restore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430 14-2. Restoring After Losing One Member of the Multiplexed Group . . . . . . . 433 14-3. Recovering After Loss of All Members of the INACTIVE Redo Log Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436 14-4. Recovering After Loss of All Members of the ACTIVE Redo Log Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439 14-5. Recovering After Loss of All Members of the CURRENT Redo Log Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441 ■CHAPTER 15 Duplicating Databases and Transporting Data . . . . . . . . . 443 15-1. Renaming Files in a Duplicate Database . . . . . . . . . . . . . . . . . . . . . . . 444 15-2. Creating a Duplicate Database on the Same Host . . . . . . . . . . . . . . . . 450 15-3. Duplicating a Database Without Any RMAN Backups . . . . . . . . . . . . . . 456 15-4. Creating a Duplicate Database on a Remote Host with the Same File Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 15-5. Duplicating a Database with Several Directories . . . . . . . . . . . . . . . . . 464 15-6. Creating a Standby Database on a New Host . . . . . . . . . . . . . . . . . . . . 465 15-7. Duplicating a Database to a Past Point in Time . . . . . . . . . . . . . . . . . . 468 15-8. Skipping Tablespaces During Database Duplication . . . . . . . . . . . . . . . 469 15-9. Duplicating a Database with a Specific Backup Tag . . . . . . . . . . . . . . . 470 15-10. Resynchronizing a Duplicate Database . . . . . . . . . . . . . . . . . . . . . . . 471 15-11. Transporting Tablespaces on the Same OS Platform . . . . . . . . . . . . . 472 15-12. Transporting Tablespaces Across Different Operating System Platforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477 15-13. Transporting an Entire Database to a Different Platform . . . . . . . . . . . 480 15-14. Transporting a Database by Converting Datafiles on the Target Platform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
xiv ■CONTENTS ■CHAPTER 16 Tuning RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 16-1. Identifying RMAN Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493 16-2. Measuring Backup Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494 16-3. Monitoring RMAN Job Progress . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498 16-4. Identifying I/O Bottlenecks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500 16-5. Improving Tape I/O Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504 16-6. Maximizing Throughput to Backup Device . . . . . . . . . . . . . . . . . . . . . . 505 16-7. Setting Large Pool Memory Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507 16-8. Tuning Media Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508 16-9. Tuning Crash Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509 16-10. Slowing RMAN Down . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512 16-11. Improving Performance Through Parallelism . . . . . . . . . . . . . . . . . . . 514 16-12. Improving Performance Using Incremental Features . . . . . . . . . . . . . 515 ■CHAPTER 17 Troubleshooting RMAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517 17-1. Determining Where to Start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517 17-2. Resolving Connection Permission Issues . . . . . . . . . . . . . . . . . . . . . . . 519 17-3. Handling Disk Space Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521 17-4. Dealing with the RMAN-06059 Error . . . . . . . . . . . . . . . . . . . . . . . . . . 523 17-5. Terminating RMAN Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525 17-6. Diagnosing NLS Character Set Issues . . . . . . . . . . . . . . . . . . . . . . . . . 527 17-7. Logging RMAN Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528 17-8. Viewing RMAN Command History . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530 17-9. Enabling RMAN’s Debug Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532 17-10. Enabling Granular Time Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . 534 17-11. Working with Oracle Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536 17-12. Resolving RMAN Compatibility Issues . . . . . . . . . . . . . . . . . . . . . . . . 536 17-13. Dealing with an ORA-19511 Error . . . . . . . . . . . . . . . . . . . . . . . . . . . 537 17-14. Dealing with an ORA-27211 Error . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 17-15. Dealing with an ORA-04031 Error . . . . . . . . . . . . . . . . . . . . . . . . . . . 540 17-16. Managing Files in an ASM Environment . . . . . . . . . . . . . . . . . . . . . . . 541 ■CHAPTER 18 Using a Media Management Layer . . . . . . . . . . . . . . . . . . . . . . 545 Using Oracle Secure Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546 18-1. Configuring RMAN Access to the Oracle Secure Backup sbt Library . . . 548 18-2. Managing Authorized OSB Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . 549 18-3. Creating OSB Media Families for RMAN Backups . . . . . . . . . . . . . . . . . 551 18-4. Creating an OSB Database Backup Storage Selector . . . . . . . . . . . . . . 552 18-5. Configuring OSB Parameters in RMAN . . . . . . . . . . . . . . . . . . . . . . . . . 553 18-6. Backing Up Using Oracle Secure Backup . . . . . . . . . . . . . . . . . . . . . . . 555 18-7. Restoring Using Oracle Secure Backup . . . . . . . . . . . . . . . . . . . . . . . . 556
■CONTENTS xv 18-8. Accessing RMAN Backup Data in Oracle Secure Backup . . . . . . . . . . . 557 Using Veritas NetBackup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558 18-9. Installing the NetBackup Agent for Oracle. . . . . . . . . . . . . . . . . . . . . . . 558 18-10. Maintaining Policies for the RMAN Backups . . . . . . . . . . . . . . . . . . . . 560 18-11. Scheduling NetBackup RMAN Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . 562 18-12. Defining Client Databases in NetBackup. . . . . . . . . . . . . . . . . . . . . . . 562 18-13. Checking for NetBackup Files on Tape . . . . . . . . . . . . . . . . . . . . . . . . 563 18-14. Configuring NetBackup Parameters in RMAN . . . . . . . . . . . . . . . . . . . 565 18-15. Backing Up Using NetBackup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566 18-16. Restoring Using NetBackup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567 Using EMC NetWorker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569 18-17. Configuring EMC NetWorker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570 18-18. Installing EMC NetWorker Module for Oracle . . . . . . . . . . . . . . . . . . . 572 18-19. Backing Up Using the EMC NetWorker Module for Oracle . . . . . . . . . . 574 18-20. Restoring Using the EMC NetWorker Module for Oracle . . . . . . . . . . . 577 18-21. Uninstalling the EMC NetWorker Module for Oracle . . . . . . . . . . . . . . 578 18-22. Verifying the MML Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 579 ■CHAPTER 19 Performing Backup and Recovery with Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583 19-1. Getting Started with RMAN and Enterprise Manager . . . . . . . . . . . . . . 583 19-2. Setting Up a Credentialed OS User . . . . . . . . . . . . . . . . . . . . . . . . . . . 587 19-3. Creating a Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588 19-4. Restoring and Recovering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593 19-5. Viewing Backup Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598 19-6. Performing Routine RMAN Maintenance Tasks . . . . . . . . . . . . . . . . . . 599 19-7. Configuring a Recovery Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600 19-8. Configuring Instance Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602 19-9. Configuring the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . 602 19-10. Configuring Restore Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603 19-11. Running Custom RMAN Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604 19-12. Configuring Backup Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608 19-13. Configuring Backup Policies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609 ■CHAPTER 20 Using the Data Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . 611 20-1. Listing Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612 20-2. Getting Advice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614 20-3. Repairing Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615 20-4. Using the Data Recovery Advisor Through Enterprise Manager . . . . . . 617 20-5. Changing Failure Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621
xvi ■CONTENTS ■CHAPTER 21 Using RMAN on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623 Oracle on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623 Oracle Architecture on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623 Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625 Threads, Not Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628 Oracle Home and SID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631 Oracle Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632 Location of Oracle Binaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635 Managing Oracle Through the Management Console . . . . . . . . . . . . . . . . . . 636 Killing the Threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639 Copying Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640 RMAN Recipes for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640 21-1. Connecting As sysdba Using OS Authentication . . . . . . . . . . . . . . . . . . 640 21-2. Simulating a Failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 21-3. Creating a Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 21-4. Placing Datafiles, Control Files, and Online and Archived Redo Log Files in the FRA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 21-5. Switching Back from Image Copies . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 21-6. Using the Flash Recovery Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641 21-7. Developing a Windows Batch File . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642 21-8. Scheduling Windows Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642 21-9. Transporting Tablespaces to/from Windows . . . . . . . . . . . . . . . . . . . . . 642 21-10. Transporting an Entire Database to/from Windows . . . . . . . . . . . . . . . 642 ■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
Foreword W hat skills set the database administrator (DBA) apart from other technologists? Of the many responsibilities laid upon a DBA, which cannot be performed by someone else? Adding database accounts? Creating tables and indexes? Installing and configuring databases? Opti- mizing the database and the applications that access and manipulate it? All of these tasks are regularly performed by people who do not consider themselves data- base administrators. They consider themselves to be programmers/analysts, to be application developers, or to be managers and directors, and they do all these things just to be able to move forward with their own jobs. Most application developers know how to run the Oracle Universal Installer—it’s just another graphical application, and accepting all the default choices is a perfectly valid way to get the job done these days. Adding database accounts? That’s easy! Granting database privileges? Just give ’em dba or sysdba and no more problems! Creating tables and indexes? C’mon, that’s more of a developer’s job than the DBA’s job, isn’t it? Tuning Oracle databases is mostly about crafting efficient SQL statements, and although this job often falls to DBAs, it is best handled by the developers and programmers who write the SQL in the first place. Although many of these duties are correctly assigned to a DBA, they are not a hallmark of the job. Think about the people flying airliners. With the degree of automation in aircraft cockpits now, it can be argued (with a lot of merit) that the planes can fly themselves, from take-off through navigated flight to touchdown. So, what are the pilots for? If something goes wrong with the plane, you want the best pilots at the controls of that plane. That’s because when things go wrong, they go wrong in a hurry, and it takes somebody who knows exactly what all that PlayStation gadgetry is really controlling in that cockpit, and it takes somebody who can intelligently take control and land the thing safely when dozens of lights are flashing and dozens of alarms are buzzing. It’s not too hard to justify the presence of pilots on airplanes in the end. Likewise, 50 years ago, at the dawn of the American space program, a debate was under- way then, as there is now: should space flights be manned or unmanned? There were good arguments in favor of the latter. The first astronauts weren’t human—they were dogs and chimps. When humans were finally included, the spacecraft engineers assured them they were redundant; they were just “spam in a can” went the gallows humor. But it didn’t take long to prove those people wrong. The presence of a well-trained and comprehensively knowledgeable pilot in the spacecraft has proven its worth, time and time again. A classic example is the final two minutes of the historic Apollo 11 moon landing, when Neil Armstrong looked out the window of the Eagle lunar module and realized that their auto- mated descent, controlled from Houston via computer, was dropping them into a boulder field. Only a few hundred meters from the lunar surface, Armstrong flipped the controls to manual and pushed the lunar module higher, seeking a more viable landing site. While Houston nervously and repeatedly queried for status, Armstrong calmly replied, “Hold, Houston,” until, with only 30 seconds of fuel remaining, he set the lunar module down and declared that the Eagle had landed. xvii
xviii ■FOREWORD That why we have human astronauts. This is what sets “spam in a can” apart from a pilot. This is why airliners, although heavily automated, have highly trained pilots at the controls. And that brings us back to database administrators…I hope! What sets a DBA apart from an ambitious programmer or a developer doing what needs to be done to move forward? It is the ability to prepare for trouble and recover from it. Database recovery in the event of failure or mishap is the most vital skill in a DBA’s toolkit. The Oracle RDBMS has been around now for about 30 years. The internal mechanisms for backup and recovery have changed very little in the past 20 years. Of course there have been enhancements, but the mechanism for basic “hot” or online backups has changed very little. However, it is the mechanism for restore and recovery that took a great leap forward 10 years ago, when Oracle Recovery Manager (RMAN) was introduced with Oracle 8. In a world where misnomers abound, Recovery Manager is quite aptly named. The focus of the product is not on automating backups, but rather on automating the steps of restore and recovery as much as possible. Much of the early reluctance to adopt RMAN came about not from any failings in the product, but rather from disappointment that the product did not make the job of performing backups any easier. Since backups are the operation that DBAs see most often, what RMAN does for recovery operations was not fully appreciated. As I teach people how to use RMAN, I attempt to stress the mind-set that RMAN is not just about performing backups. Rather, it is about “feeding” the RMAN recovery catalog. Backups are not ends in themselves but simply entries in the recovery catalog used by RMAN during restore and recovery operations. If a DBA considers it their duty to feed the recovery catalog with backup operations and other maintenance such as cross-checks, then you have someone who is truly preparing for the eventuality, not just the remote possibility, of restore and recovery. Someone understands the tool and is not just applying a different tool to bang in nails the same old way. The knowledge and capability to recover a database from catastrophic failure is what separates a real DBA from someone who found the installer or who knows how to do the clickety-clickety thing in Oracle Enterprise Manager—and not just once, by luck, but knows how to use RMAN to its full advantage in order to work around those confusing and mislead- ing error messages and to verify backups and maintain and protect the recovery catalog(s) so as to virtually guarantee recoverability, each and every time. It is this protective mind-set, liberally seasoned with caution and pessimism, that sepa- rates DBAs from other technologists. Systems administrators and network administrators have much the same tendencies, but only databases administrators are made responsible for never losing data. Systems and networks can be made redundant, and if they fail, it is only a matter of bringing them back to service, but data loss is forever and is never forgiven. Years ago, I worked with a very no-nonsense vice president. She didn’t want to know the details of my job and rightly so. She simply stated, very clearly, “Failures happen, but don’t ever tell me that you could not recover my data.” Message received. This book was written by seasoned professionals who have been using RMAN since its inception. They have recognized that RMAN can be confusing, and they think everyone should not have to go through the same learning curve in order to arrive at the same conclu- sions. So they have gathered together their best practices and tried-and-true procedures and compiled them into this wonderful book. If you are an Oracle database administrator, this could very well be the most important book you read. Technology books are famous for becoming shelfware, pristine and unopened