RMAN Recipes for Oracle Database 11g:A Problem-Solution Approach docx

Books for professionals by professionals®

RMAN Recipes for Oracle Database 11g:

A Problem-Solution Approach

Dear Reader,

RMAN is the tool of choice for Oracle database backup and recovery. RMAN

contains core features that aren’t available with other backup and recovery

solutions. Furthermore, Oracle continues to integrate RMAN with other products

such as Enterprise Manager, RAC, ASM, and Data Guard. If you are a DBA in an

Oracle shop, then it’s vital that you know how to use RMAN effectively. Your

job depends on it.

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

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

problem. Failure is not an option.

This book is unique in that it contains answers for almost any RMAN backup

and recovery problem that you’re likely to encounter. We tackle all scenarios,

from simple to complex. Each recipe title is an indexed entry to a particular

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

explain why features work like they do.

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,

secure, and ensure the availability of your company’s databases.

Sincerely,

Darl Kuhn, Sam Alapati, Arup Nanda

US $59.99

Shelve in

Databases/Oracle

User level:

Beginner–Intermediate

Kuhn,

Nanda

Alapati, Oracle RMAN Recipes

The EXPERT’s VOIce® In oracle

RMAN Recipes

for Oracle Database 11g

A Problem-Solution Approach

Darl Kuhn, Sam Alapati, and Arup Nanda

Companion

eBook Available

THE APRESS ROADMAP

Expert Oracle

Database Architecture

RMAN Recipes for

Oracle Database 11g

Expert Oracle Database

10g Administration

Practical

RMAN for the

Busy DBA

Practical

RMAN for the

Busy DBA

An example-based approach to backing

up and recovering your Oracle database.

this print for content only—size & color not accurate spine = 1.3237" 704 page count

 CYAN

MAGENTA

 YELLOW

BLACK

 PANTONE 123 C

Darl Kuhn, coauthor of

Oracle RMAN

Pocket Reference

ISBN-13: 978-1-59059-851-1

ISBN-10: 1-59059-851-2

9 781590 598511

5 5 9 9 9 www.apress.com

SOURCE CODE ONLINE

Companion eBook

See last page for details

on $10 eBook version

Sam Alapati, author of

Expert Oracle Database

10g Administration

Arup Nanda, author of

Oracle 11g New Features

Series on Oracle

Technology Network

Darl Kuhn, Sam Alapati, and Arup Nanda

RMAN Recipes for

Oracle Database 11g

A Problem-Solution Approach

8512Ch00CMP4 7/27/07 6:21 AM Page i

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 [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 510-549-5930, fax 510-549-5939, 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.com in 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 ii

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 iii

8512Ch00CMP4 7/27/07 6:21 AM Page iv

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 v

8512Ch00CMP4 7/27/07 6:21 AM Page vi

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 vii

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

viii ■CONTENTS

8512Ch00CMP4 7/27/07 6:21 AM Page viii

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

■CONTENTS ix

8512Ch00CMP4 7/27/07 6:21 AM Page ix

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

x ■CONTENTS

8512Ch00CMP4 7/27/07 6:21 AM Page x

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

■CONTENTS xi

8512Ch00CMP4 7/27/07 6:21 AM Page xi

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

xii ■CONTENTS

8512Ch00CMP4 7/27/07 6:21 AM Page xii

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

■CONTENTS xiii

8512Ch00CMP4 7/27/07 6:21 AM Page xiii