Oracle® Database Backup and Recovery Advanced User’s Guide 10g Release 1 (10.1) Part No. B10734-01 December 2003 Oracle Database Backup and Recovery Advanced User’s Guide, 10g Release 1 (10.1) Part No. B10734-01 Copyright © 2003 Oracle Corporation. All rights reserved. Primary Author: Antonio Romero Contributing Author: Lance Ashdown Contributors: Beldalker Anand, Tammy Bednar, Senad Dizdar, Muthu Olagappan, Francisco Sanchez, Steve Wertheimer Graphic Artist: Valarie Moore The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent and other intellectual and industrial property laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited. The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation. If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on behalf of the U.S. Government, the following notice is applicable: Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial computer software" and use, duplication, and disclosure of the Programs, including documentation, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs. Oracle is a registered trademark, and Oracle Store, Oracle7, Oracle8, Oracle9i, PL/SQL, and SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective owners. iii Contents Send Us Your Comments . xxi Preface xxiii Audience . xxiv Organization . xxiv Related Documentation xxvi Conventions . xxvii Documentation Accessibility xxx What’s New in Backup and Recovery? xxxiii Oracle Database Release 10g New Features in Backup and Recovery . xxxiv Part I Recovery Manager Advanced Architecture and Concepts 1 Recovery Manager Architecture About the RMAN Environment . 1-2 RMAN Session Architecture . 1-3 RMAN Command Line Client . 1-3 How RMAN Compiles and Executes Commands . 1-3 Issuing RMAN Commands . 1-4 RMAN Pipe Interface . 1-6 RMAN Repository 1-7 Storage of the RMAN Repository in the Recovery Catalog . 1-7 Storage of the RMAN Repository in the Control File . 1-10 iv Media Management 1-11 Performing Backup and Restore with a Media Manager . 1-11 Backup Solutions Program 1-12 2 RMAN Backups Concepts About RMAN Channels 2-2 Automatic and Manual Channel Allocation . 2-3 Automatic Channel Device Configuration and Parallelism . 2-4 Automatic Channel Default Device Types 2-5 Automatic Channel Naming Conventions 2-6 Automatic Channel Generic Configurations 2-7 Automatic Channel-Specific Configurations 2-8 Clearing Automatic Channel Settings . 2-8 Determining Channel Parallelism to Match Hardware Devices . 2-9 Channel Control Options for Manual and Automatic Channels . 2-10 Channel Failover . 2-11 About RMAN Backups 2-12 About Image Copies . 2-12 About Proxy Copies . 2-14 Storage of Backups on Disk and Tape . 2-15 Backups of Archived Logs . 2-15 Multiplexed Backup Sets . 2-16 Multiplexing by the Media Manager . 2-18 Manual Parallelization of Backups . 2-18 Multiple Copies of RMAN Backups . 2-20 Duplexed Backup Sets 2-20 Backups of Backup Sets 2-22 Backups of Image Copies . 2-24 RMAN Backup Options: Naming, Sizing, and Speed . 2-25 Filenames for Backup Pieces . 2-25 Filenames for Image Copies 2-26 Tags for RMAN Backups . 2-26 Size of Backup Pieces . 2-28 Number and Size of Backup Sets 2-30 I/O Read Rate of Backups . 2-31 v RMAN Backup Types 2-32 Incremental Backups 2-33 Control File and Server Parameter File Autobackups 2-38 How RMAN Performs Control File Autobackups 2-39 When RMAN Performs Control File Autobackups . 2-40 Backup Retention Policies 2-41 Recovery Window 2-43 Backup Redundancy 2-45 Batch Deletes of Obsolete Backups 2-46 Exempting Backups from the Retention Policy 2-47 Relationship Between Retention Policy and Flash Recovery Area Rules 2-48 Backup Optimization . 2-49 Backup Optimization Algorithm 2-49 Requirements for Enabling and Disabling Backup Optimization . 2-51 Effect of Retention Policies on Backup Optimization . 2-52 Restartable Backups . 2-54 Managing Backup Windows and Performance: BACKUP . DURATION . 2-55 Controlling RMAN Behavior when Backup Window Ends with PARTIAL . 2-55 Managing Backup Performance with MINIMIZE TIME and MINIMIZE LOAD 2-56 RMAN Backup Errors 2-57 Tests and Integrity Checks for Backups . 2-58 Detecting Physical and Logical Block Corruption . 2-59 Detection of Logical Block Corruption 2-59 Detection of Fractured Blocks During Open Backups 2-60 Backup Validation with RMAN . 2-60 3 RMAN Recovery Concepts Restoring Files with RMAN . 3-2 Mechanics of Restore Operations . 3-2 File Selection in Restore Operations 3-3 Restore Failover 3-4 Restore Optimization . 3-5 Datafile Media Recovery with RMAN . 3-5 RMAN Media Recovery: Basic Steps . 3-5 Mechanics of Recovery: Incremental Backups and Redo Logs 3-7 vi Incomplete Recovery 3-9 Tablespace Point-in-Time Recovery . 3-10 Block Media Recovery with RMAN 3-10 When Block Media Recovery Should Be Used . 3-11 Block Media Recovery When Redo Is Missing . 3-12 Database Duplication with RMAN . 3-13 Physical Standby Database Creation with RMAN 3-15 4 RMAN Maintenance Concepts RMAN Reporting 4-2 Using the RMAN LIST Command . 4-2 RMAN Reports 4-3 SHOW Command Output . 4-7 Crosschecks of RMAN Backups 4-7 Monitoring RMAN Through V$ Views 4-9 Correlating Server Sessions with RMAN Channels 4-10 Monitoring RMAN Job Progress 4-13 Monitoring RMAN Interaction with the Media Manager 4-16 Monitoring RMAN Job Performance . 4-17 Determining Which Datafiles Require Recovery . 4-17 Deletion of RMAN Backups . 4-18 Summary of RMAN Deletion Methods . 4-19 Removal of Backups with the DELETE Command . 4-20 Behavior of DELETE Command When the Repository and Media Do Not Correspond 4-22 Removal of Backups with the BACKUP . DELETE INPUT Command 4-23 CHANGE AVAILABLE and CHANGE UNAVAILABLE with RMAN Backups 4-24 Changing Retention Policy Status of RMAN Backups . 4-24 Part II Performing Advanced RMAN Backup and Recovery 5 Connecting to Databases with RMAN Starting RMAN Without Connecting to a Database 5-2 Connecting to a Target Database and a Recovery Catalog 5-2 Connecting to the Target Database and Recovery Catalog from the Command Line . 5-3 vii Connecting to the Target Database and Recovery Catalog from the RMAN Prompt . 5-3 Connecting to an Auxiliary Database . 5-4 Connecting to an Auxiliary Database from the Command Line . 5-4 Connecting to an Auxiliary Database from the RMAN Prompt . 5-4 Diagnosing Connection Problems . 5-5 Diagnosing Target and Auxiliary Database Connection Problems 5-5 Diagnosing Recovery Catalog Connection Problems . 5-5 Hiding Passwords When Connecting to Databases . 5-5 Sending RMAN Output Simultaneously to the Terminal and a Log File . 5-7 Executing RMAN Commands Through a Pipe . 5-7 Executing Multiple RMAN Commands In Succession Through a Pipe: Example . 5-8 Executing RMAN Commands In a Single Job Through a Pipe: Example 5-8 6 Configuring the RMAN Environment: Advanced Topics Configuring the Flash Recovery Area: Advanced Topics 6-2 Configuring Online Redo Log Creation in the Flash Recovery Area . 6-2 Configuring Control File Creation in the Flash Recovery Area 6-2 Archived Redo Log Creation in the Flash Recovery Area 6-3 RMAN File Creation in the Flash Recovery Area 6-4 Configuring RMAN to Make Backups to a Media Manager 6-5 Prerequisites for Using a Media Manager with RMAN . 6-5 Locating the Media Management Library: The SBT_LIBRARY Parameter . 6-6 Testing Whether the Media Manager Library Is Integrated Correctly . 6-7 Configuring Automatic Channels for Use with a Media Manager . 6-11 Configuring Automatic Channels 6-12 Configuring Parallelism for Automatic Channels . 6-12 Configuring a Generic Automatic Channel for a Device Type 6-13 Showing the Automatic Channel Configuration Settings 6-14 Configuring a Specific Channel for a Device Type 6-16 Clearing Channel and Device Settings 6-19 Configuring the Maximum Size of Backup Sets and Pieces 6-20 Configuring Backup Optimization 6-21 Configuring Backup Duplexing: CONFIGURE . BACKUP COPIES 6-22 Configuring Tablespaces for Exclusion from Whole Database Backups . 6-24 Configuring Auxiliary Instance Datafile Names: CONFIGURE AUXNAME 6-25 viii Setting the Snapshot Control File Location . 6-26 Default Location of the Snapshot Control File . 6-27 Viewing the Configured Location of the Snapshot Control File . 6-27 Setting the Location of the Snapshot Control File 6-28 Showing the Current Snapshot Control File Name . 6-28 Setting Up RMAN for Use with a Shared Server . 6-29 7 Making Backups with RMAN: Advanced Topics Configuring and Allocating Channels for Use in Backups 7-2 Configuring the Default Backup Type for Disk 7-3 Duplexing Backup Sets 7-3 Duplexing Backup Sets with CONFIGURE BACKUP COPIES . 7-3 Duplexing Backupsets with BACKUP . COPIES 7-4 Making Split Mirror Backups with RMAN . 7-5 Backing Up Backup Sets with RMAN 7-7 Backing Up Image Copies with RMAN . 7-8 Restarting and Optimizing RMAN Backups . 7-8 Backing Up Files Using Backup Optimization . 7-9 Restarting a Backup After It Partially Completes 7-9 Validating Backups with RMAN . 7-10 RMAN Backup Examples 7-11 Specifying the Device Type on the BACKUP Command: Example 7-12 Skipping Tablespaces when Backing Up a Database: Example . 7-12 Restarting a Backup: Example 7-13 Spreading a Backup Across Multiple Disk Drives: Example . 7-13 Backing Up a Large Database to Multiple File Systems: Example 7-14 Specifying the Size of Backup Sets: Example 7-15 Limiting the Size of Backup Pieces: Example . 7-16 Backing Up Archived Redo Logs in a Failover Scenario: Example . 7-17 Backing Up Archived Logs Needed to Recover an Online Backup: Example . 7-17 Backing Up and Deleting Multiple Copies of an Archived Redo Log: Example 7-18 Performing Differential Incremental Backups: Example 7-19 Performing Cumulative Incremental Backups: Example . 7-19 Determining How Channels Distribute a Backup Workload: Example . 7-20 Backing Up in NOARCHIVELOG Mode: Example . 7-20 ix Cataloging User-Managed Datafile Copies: Example . 7-21 Keeping a Long-Term Backup: Example 7-22 Optimizing Backups: Examples . 7-23 Handling Errors During Backups: Example . 7-26 8 Advanced RMAN Recovery Techniques Performing Database Point-In-Time Recovery . 8-2 Performing Point-in-Time Recovery with a Current Control File . 8-3 Point-in-Time Recovery to a Previous Incarnation 8-4 Performing Recovery with a Backup Control File . 8-6 Performing Recovery with a Backup Control File and a Recovery Catalog 8-7 Performing Recovery with a Backup Control File and No Recovery Catalog 8-8 Restoring the Database to a New Host . 8-11 Specifying Filenames When Restoring to a New Host . 8-12 Determining the SCN for Incomplete Recovery After Restore 8-13 Testing the Restore of a Database to a New Host: Scenario . 8-13 Performing Disaster Recovery 8-18 Performing Block Media Recovery with RMAN 8-21 Recovering Datablocks By Using All Available Backups . 8-21 Recovering Datablocks By Using Selected Backups 8-22 Recovering Blocks Listed in V$DATABASE_BLOCK_CORRUPTION . 8-23 RMAN Restore and Recovery Examples 8-24 Restoring Datafile Copies to a New Host: Example 8-24 Restoring When Multiple Databases in the Catalog Share the Same Name: Example 8-25 Recovering a Database in NOARCHIVELOG Mode: Example . 8-27 Recovering a Lost Datafile Without a Backup: Example 8-28 Transporting a Tablespace to a Different Database on the Same Platform: Example 8-29 9 Flashback Technology: Recovering from Logical Corruptions Oracle Flashback Technology: Overview . 9-2 Oracle Flashback Query: Recovering at the Row Level 9-3 Oracle Flashback Table: Returning Individual Tables to Past States . 9-4 Oracle Flashback Drop: Undo a DROP TABLE Operation 9-6 What is the Recycle Bin? 9-6 How Tables and Other Objects Are Placed in the Recycle Bin 9-6 x Naming Convention for Objects in the Recycle Bin 9-7 Viewing and Querying Objects in the Recycle Bin 9-8 Recycle Bin Capacity and Space Pressure . 9-9 Performing Flashback Drop on Tables in the Recycle Bin 9-10 Purging Objects from the Recycle Bin . 9-12 Privileges and Security . 9-14 Limitations and Restrictions on Flashback Drop . 9-15 Oracle Flashback Database: Alternative to Point-In-Time Recovery 9-15 Limitations of Flashback Database . 9-16 Requirements for Flashback Database . 9-17 Enabling Flashback Database 9-17 Sizing the Flash Recovery Area for Flashback Logs 9-18 Determining the Current Flashback Database Window . 9-19 Performance Tuning for Flashback Database . 9-19 Monitoring Flashback Database . 9-20 Running the FLASHBACK DATABASE Command from RMAN 9-21 Running the FLASHBACK DATABASE Command from SQL*Plus 9-22 Using Oracle Flashback Features Together in Data Recovery: Scenario 9-23 10 RMAN Tablespace Point-in-Time Recovery (TSPITR) Understanding RMAN TSPITR 10-1 RMAN TSPITR Concepts . 10-2 Deciding When to Use TSPITR . 10-4 Planning and Preparing for TSPITR . 10-6 Choosing the Right Target Time for TSPITR . 10-7 Determining the Recovery Set: Analyzing Data Relationships . 10-7 Identifying and Preserving Objects That Will Be Lost After TSPITR . 10-9 Performing Basic RMAN TSPITR . 10-10 Fully Automated RMAN TSPITR . 10-11 Performing Customized RMAN TSPITR with an RMAN-Managed Auxiliary Instance 10-13 Renaming TSPITR Recovery Set Datafiles with SET NEWNAME 10-14 Renaming TSPITR Auxiliary Set Datafiles 10-15 Using Image Copies for Faster TSPITR Performance 10-18 Customizing Initialization Parameters for the Automatic Auxiliary Instance 10-21 Performing RMAN TSPITR Using Your Own Auxiliary Instance . 10-22 [...]... restore, and recover Oracle databases s Perform maintenance on backups of database files To use this document, you need to know the following: s s s Relational database concepts and basic database administration as described in Oracle Database Concepts and the Oracle Database Administrator's Guide Basic backup and recovery concepts and strategies as described in Oracle Database Backup and Recovery Basics... Database Backup and Recovery Basics s Oracle Database Recovery Manager Reference s Oracle Database Utilities s http://www .oracle. com /database/ recovery You can access information about the Backup Solutions Program at http://otn .oracle. com/deploy/availability xxvi Many books in the documentation set use the sample schemas of the seed database, which is installed by default when you install Oracle Refer to Oracle. .. restore a database and how to use the SQL*Plus RECOVER command Chapter 16, "Making User-Managed Backups" This chapter describes how to use operating system command to back up database files and archived redo logs Chapter 17, "Performing User-Managed Database Flashback and Recovery" This chapter describes how to use the SQL*Plus FLASHBACK DATABASE and RECOVER commands Chapter 18, "Advanced User-Managed Recovery. .. the database creates more room automatically by deleting the minimum set of files from flash recovery area that are obsolete, backed up to tertiary storage, or redundant See Also: s s s Oracle Database Backup and Recovery Basics to learn how to set up the flash recovery area "Configuring the Flash Recovery Area: Advanced Topics" on page 6-2 Oracle Flashback Database With the FLASHBACK DATABASE command,... copies of the control file and online redo logs – Archived redo logs and flashback logs – RMAN backups – Files created by RESTORE and RECOVER commands Recovery components of the database interact with the flash recovery area to ensure that the database is completely recoverable using files in the flash recovery area The database manages the disk space in the flash recovery area, and when there is not sufficient... create and manage a recovery catalog Chapter 14, "Tuning Backup and Recovery" This chapter gives tips for improving RMAN backup and restore performance xxv Chapter 15, "Recovery Manager Troubleshooting" This chapter gives tips for diagnosing and responding to RMAN problems Part III, "Performing User-Managed Backup and Recovery" This section describes how to use operating system utilities to back up and. .. Scenario 15-31 UNKNOWN Database Name Appears in Recovery Catalog: Scenario 15-32 xiv Part III Performing User-Managed Backup and Recovery 16 Making User-Managed Backups Querying V$ Views to Obtain Backup Information Listing Database Files Before a Backup Determining Datafile Status for Online Tablespace Backups Making User-Managed Backups of the Whole Database Making... releases xxxiii Oracle Database Release 10g New Features in Backup and Recovery The new features for this release greatly increase the manageability of RMAN, making backup and recovery simpler and more performant s Flash Recovery Area A flash recovery area is a directory, file system, or Automatic Storage Management disk group that serves as the default storage area for files related to recovery Such files... RMAN Client and Running a Stored Script Restrictions on Stored Script Names Managing the Control File When You Use a Recovery Catalog Backing Up and Recovering the Recovery Catalog Backing Up the Recovery Catalog Restoring and Recovering the Recovery Catalog from Backup Re-Creating the Recovery Catalog Exporting and Importing the Recovery Catalog... user-supplied database objects and structures, column names, packages and classes, usernames and roles, program units, and parameter values Enter sqlplus to open SQL*Plus UPPERCASE monospace (fixed-width) font lowercase monospace (fixed-width) font Example Ensure that the recovery catalog and target database do not reside on the same disk You can back up the database by using the BACKUP command Query the . Oracle Database Backup and Recovery Advanced User’s Guide 10g Release 1 (10.1) Part No. B10734-01 December 2003 Oracle Database Backup and Recovery Advanced. Programs. Oracle is a registered trademark, and Oracle Store, Oracle7 , Oracle8 , Oracle9 i, PL/SQL, and SQL*Plus are trademarks or registered trademarks of Oracle