Oracle RMAN Recipes

704 Pages·2007·6.75 MB·English

cyan yelloW
MaGenTa Black
panTone 123 c
Books for professionals By professionals® The eXperT’s Voice® in oracle
Companion
eBook Available
RMAN Recipes for Oracle Database 11g:
PPrraaccttiiccaall
A Problem-Solution Approach O RRMMAANN ffoorr tthhee
BBuussyy DDBBAA
Dear Reader, r
RMAN is the tool of choice for Oracle database backup and recovery. RMAN a
Darl Kuhn, coauthor of contains core features that aren’t available with other backup and recovery
Oracle RMAN solutions. Furthermore, Oracle continues to integrate RMAN with other products c
Pocket Reference such as Enterprise Manager, RAC, ASM, and Data Guard. If you are a DBA in an RMAN Recipes
Oracle shop, then it’s vital that you know how to use RMAN effectively. Your l
job depends on it. e
This recipe book provides you with focused solutions for the gamut of RMAN
backup and recovery tasks. We know from hard experience that sometimes all

you need is an easy-to-find, clear example showing how a feature works. This is
R
especially true when you have a critical issue that is causing database downtime.

In those situations, people expect you to earn your keep and quickly solve the Oracle Database 11g
problem. Failure is not an option. M for
This book is unique in that it contains answers for almost any RMAN backup
Sam Alapati, author of
and recovery problem that you’re likely to encounter. We tackle all scenarios,
Expert Oracle Database
10g Administration from simple to complex. Each recipe title is an indexed entry to a particular A
problem. In the recipe you’ll find the solution and a detailed explanation of
how it works. You won’t be shown merely how to parrot RMAN commands. We
N
explain why features work like they do. A Problem-Solution Approach
If your company uses Oracle technology, then RMAN should be a key piece
of your data protection strategy. As a DBA, you’re the one responsible for making
it work. We hope that you’ll use this book to fully maximize RMAN to protect, R
secure, and ensure the availability of your company’s databases.
Sincerely, e
Arup Nanda, author of
Darl Kuhn, Sam Alapati, Arup Nanda
Oracle 11g New Features c An example-based approach to backing
Series on Oracle
Technology Network up and recovering your Oracle database.
i
Companion eBook p
THE APRESS ROADMAP
e
Expert Oracle Database RMAN Recipes for Expert Oracle
10g Administration Oracle Database 11g Database Architecture
s
See last page for details
on $10 eBook version
Darl Kuhn, Sam Alapati, Arup Nanda
SOURCE CODE ONLINE ISBN-13: 978-1-59059-851-1 NA and
www.apress.com ISBN-10: 1-59059-851-2 alaK
55999 npu
dah
atn
US $59.99 i,,
Shelve in
Databases/Oracle
User level:
9 781590 598511
Beginner–Intermediate
this print for content only—size & color not accurate spine = 1.3237" 704 page count

8512Ch00CMP4 7/27/07 6:21 AM Page i
RMAN Recipes for
Oracle Database 11g
A Problem-Solution Approach
Darl Kuhn, Sam Alapati, and Arup Nanda
8512Ch00CMP4 7/27/07 6:21 AM Page ii
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 [phone], e-mail [email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone [phone], fax [phone], e-mail [email protected], 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.comin the Source Code/
Download section. You will need to answer questions pertaining to this book in order to successfully
download the code.
8512Ch00CMP4 7/27/07 6:21 AM Page iii
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
8512Ch00CMP4 7/27/07 6:21 AM Page iv
8512Ch00CMP4 7/27/07 6:21 AM Page v
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
8512Ch00CMP4 7/27/07 6:21 AM Page vi
8512Ch00CMP4 7/27/07 6:21 AM Page vii
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
8512Ch00CMP4 7/27/07 6:21 AM Page viii
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