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

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

by Darl Kuhn, Sam Alapati, Arup Nanda
Released August 2007
Publisher(s): Apress
ISBN: 9781590598511

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

It's sometimes said that the true job of an Oracle database administrator can be summed up in one, essential skill: to be able to recover your database.

There's much wisdom in that statement. Of all the things you are responsible for as a database administrator, nothing is more important than the data itself. Like it or not, the fearsome responsibility of protecting your organization's most critical data falls squarely upon your shoulders:

  • Lose that data and your company could fail.

  • Lose that data and you could be out of a job.

Scared? You should be. But there's help. You are not alone. Oracle experts Darl Kuhn, Sam Alapati, and Arup Nanda have come together in this book to show you the power of Recovery Manager, or RMAN, which is Oracle's backup and recovery tool of choice. RMAN Recipes for Oracle Database 11g helps you take advantage of all that RMAN has to offer by providing easy-to-understand solutions to business-critical backup and recovery operations. This handy guide:

  • Demystifies the steps required to protect your business data

  • Provides ready-made and step-by-step solutions to simple and complex backup, restore, and recovery operations

  • Is task-oriented, making it easy for you to find the solution to pressing backup and recovery problems

  • Respects your time by providing example-based solutions, not endless reading material

When the pressure's on, you don't have time for long discussions of theory. This book gets right to the point with quick and easy-to-read, step-by-step solutions that can help you backup and recover your data with confidence.

What you'll learn

Oracle Database experts Darl Kuhn, Sam Alapati, and Arup Nanda show you how to

  • Reliably back up and recover your database using Oracle's Recovery Manager

  • Utilize new Oracle Database 11g features such as the Data Recovery Advisor

  • Let Oracle Database manage your backup files via the Flash Recovery Area

  • Automate backup and recovery tasks by writing scripts

  • Troubleshoot RMAN problems and optimize RMAN performance

  • Interface with a Media Management Layer to manage your backup tape library

  • Recover from the loss of a control file, loss of an online redo log, and other (hopefully!) unusual situations

Who this book is for

Oracle database administrators responsible for database backup and recovery operations.

Table of contents

  1. Title Page
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. Foreword
  6. About the Authors
  7. About the Technical Reviewer
  8. Acknowledgments
    1. From Darl
    2. From Sam
    3. From Arup
  9. Introduction
    1. Audience
    2. Using This Book
    3. What This Book Covers
    4. Conventions Used in This Book
    5. Comments and Questions
    6. Contacting the Authors
  10. CHAPTER 1: Backup and Recovery 101
    1. Types of Database Failures
    2. Oracle Backup and Recovery Concepts
    3. Backup Types
    4. Recovery Types
    5. RMAN Architecture
    6. Backup and Recovery Best Practices
  11. CHAPTER 2: Jump-Starting RMAN
    1. 2-1. Connecting to Your Database
    2. 2-2. Starting and Stopping Your Database
    3. 2-3. Toggling Archivelog Mode
    4. 2-4. Connecting to RMAN
    5. 2-5. Backing Up Your Database
    6. 2-6. Simulating a Failure
    7. 2-7. Restoring and Recovering Your Database
  12. CHAPTER 3: Using the Flash Recovery Area
    1. 3-1. Creating the Flash Recovery Area
    2. 3-2. Writing Regular RMAN Backups to the FRA
    3. 3-3. Freeing FRA Space in an Emergency
    4. 3-4. Checking Space Usage in the FRA
    5. 3-5. Expanding or Shrinking the FRA
    6. 3-6. Configuring Archived Redo Logs to Go to FRA
    7. 3-7. Using the Same FRA for Two Databases with the Same Name
    8. 3-8. Placing a Control File in the FRA
    9. 3-9. Placing Online Redo Log Files in FRA
    10. 3-10. Sending Image Copies to the FRA
    11. 3-11. Deleting Backup Sets from the FRA
    12. 3-12. Deleting Archived Redo Logs from the FRA
    13. 3-13. Reinstating a Damaged Datafile from an Image Copy
    14. 3-14. Switching Back from an Image Copy
    15. 3-15. Backing Up the FRA to Tape
    16. 3-16. Sizing the Flash Recovery Area
  13. CHAPTER 4: Using RMAN
    1. 4-1. Starting the RMAN Client
    2. 4-2. Issuing RMAN Commands
    3. 4-3. Saving RMAN Output to a Text File
    4. 4-4. Logging Command-Line RMAN Output
    5. 4-5. Connecting to a Target Database from the RMAN Prompt
    6. 4-6. Connecting to a Target Database from the Operating System Command Line
    7. 4-7. Executing Operating System Commands from Within RMAN
    8. 4-8. Scripting RMAN
    9. 4-9. Executing RMAN Command Files
    10. 4-10. Creating Dynamic Command Files
    11. 4-11. Connecting to an Auxiliary Database
    12. 4-12. Executing Multiple RMAN Commands As a Single Unit
    13. 4-13. Issuing SQL Statements from the RMAN Client
    14. 4-14. Starting and Shutting Down a Database with RMAN
    15. 4-15. Checking the Syntax of RMAN Commands
    16. 4-16. Hiding Passwords When Connecting to RMAN
    17. 4-17. Identifying RMAN Server Sessions
    18. 4-18. Dropping a Database using the RMAN Client
  14. CHAPTER 5: Configuring the RMAN Environment
    1. 5-1. Showing RMAN Configuration Settings
    2. 5-2. Configuring RMAN
    3. 5-3. Restoring Default Parameter Settings
    4. 5-4. Enabling and Disabling Automatic Control File Backups
    5. 5-5. Specifying the Autobackup Control File Directory and Filename
    6. 5-6. Specifying the Snapshot Control Filename and Location
    7. 5-7. Specifying the Retention Period for RMAN History
    8. 5-8. Configuring the Default Device Type
    9. 5-9. Configuring the Default Backup Type
    10. 5-10. Making Compressed Backup Sets the Default
    11. 5-11. Configuring Multiple Backup Copies
    12. 5-12. Skipping Previously Backed Up Files
    13. 5-13. Specifying Backup Piece Filenames
    14. 5-14. Generating Filenames for Image Copies
    15. 5-15. Tagging RMAN Backups
    16. 5-16. Configuring Automatic Channels
    17. 5-17. Manually Allocating RMAN Channels
    18. 5-18. Allocating an RMAN Maintenance Channel
    19. 5-19. Creating a Backup Retention Policy
    20. 5-20. Configuring an Archived Redo Log Deletion Policy
    21. 5-21. Limiting the Size of Individual Backup Pieces
    22. 5-22. Configuring the Maximum Size of Backup Sets
  15. CHAPTER 6: Using the Recovery Catalog
    1. 6-1. Creating the Recovery Catalog
    2. 6-2. Granting Restricted Access
    3. 6-3. Connecting to the Catalog from the Command Line
    4. 6-4. Connecting to the Catalog from the RMAN Prompt
    5. 6-5. Registering Target Databases
    6. 6-6. Unregistering a Database
    7. 6-7. Cataloging Older Files
    8. 6-8. Updating the Recovery Catalog
    9. 6-9. Dropping the Recovery Catalog
    10. 6-10. Merging Recovery Catalogs
    11. 6-11. Moving the Recovery Catalog to Another Database
    12. 6-12. Creating a High-Availability Recovery Catalog
    13. 6-13. Viewing Backup Information
    14. 6-14. Uncataloging RMAN Records
    15. 6-15. Using a Release 11.x Client with Older Catalogs
  16. CHAPTER 7: Making Backups with RMAN
    1. Backup Sets and Image Copies
    2. RMAN Backup Modes
    3. Types of Files That RMAN Can Back Up
    4. RMAN Backup Destinations
    5. 7-1. Specifying Backup Options
    6. 7-2. Backing Up the Control File
    7. 7-3. Backing Up the Server Parameter File
    8. 7-4. Backing Up Datafiles
    9. 7-5. Backing Up Tablespaces
    10. 7-6. Making a Whole-Database Backup
    11. 7-7. Backing Up Archived Redo Logs
    12. 7-8. Backing Up Everything
    13. 7-9. Backing Up Flash Recovery Files
    14. 7-10. Performing Incremental Backups
    15. 7-11. Reducing Incremental Backup Time
    16. 7-12. Creating Multiple Backup Sets
    17. 7-13. Making Copies of Backup Sets
    18. 7-14. Making Copies of Image Copy Backups
    19. 7-15. Making Tape Copies of Disk-Based Image Copies
    20. 7-16. Excluding a Tablespace from a Backup
    21. 7-17. Skipping Read-Only, Offline, or Inaccessible Files
    22. 7-18. Encrypting RMAN Backups
    23. 7-19. Making a Compressed Backup
    24. 7-20. Parallelizing Backups
    25. 7-21. Making Faster Backups of Large Files
    26. 7-22. Specifying Backup Windows
    27. 7-23. Reusing RMAN Backup Files
    28. 7-24. Retaining Backups for a Long Time
    29. 7-25. Backing Up Only Those Files Previously Not Backed Up
    30. 7-26. Restarting Backups After a Crash
    31. 7-27. Updating Image Copies
  17. CHAPTER 8: Maintaining RMAN Backups and the Repository
    1. 8-1. Adding User-Made Backups to the Repository
    2. 8-2. Finding Datafiles and Archivelogs That Need a Backup
    3. 8-3. Finding Datafiles Affected by Unrecoverable Operations
    4. 8-4. Identifying Obsolete Backups
    5. 8-5. Displaying Information About Database Files
    6. 8-6. Listing RMAN Backups
    7. 8-7. Listing Expired Backups
    8. 8-8. Listing Only Recoverable Backups and Copies
    9. 8-9. Listing Restore Points
    10. 8-10. Listing Database Incarnations
    11. 8-11. Updating the RMAN Repository After Manually Deleting Backups
    12. 8-12. Synchronizing the Repository with the Actual Backups
    13. 8-13. Deleting Backups
    14. 8-14. Deleting Archived Redo Logs
    15. 8-15. Deleting Obsolete RMAN Backups
    16. 8-16. Changing the Status of an RMAN Backup Record
    17. 8-17. Changing the Status of Archival Backups
    18. 8-18. Testing the Integrity of an RMAN Backup
    19. 8-19. Validating Datafiles, Backup Sets, and Data Blocks
  18. CHAPTER 9: Scripting RMAN
    1. Approaches to Scripting
    2. 9-1. Developing a Unix Shell Script for RMAN
    3. 9-2. Scheduling a Unix Shell File
    4. 9-3. Developing a Windows Batch File to Run RMAN
    5. 9-4. Scheduling a Script in Windows via the GUI
    6. 9-5. Changing the Schedule of a Batch Job in the Task Scheduler
    7. 9-6. Scheduling in Windows from the Command Line
    8. 9-7. Creating Local-Stored Scripts
    9. 9-8. Creating a Global-Stored Script
    10. 9-9. Updating Stored Scripts
    11. 9-10. Commenting on Stored Scripts
    12. 9-11. Displaying Stored Scripts
    13. 9-12. Listing Stored Scripts
    14. 9-13. Dropping Stored Scripts
    15. 9-14. Executing a Global Script When a Local Script of the Same Name Exists
    16. 9-15. Converting Stored Scripts to Files
    17. 9-16. Creating or Replacing a Stored Script from a File
    18. 9-17. Passing Parameters to Stored Scripts
    19. 9-18. Creating a Parameterized Command File Script
  19. CHAPTER 10: Restoring the Control File
    1. 10-1. Restoring Control File Using Flash Recovery Area
    2. 10-2. Restoring Control File Using Recovery Catalog
    3. 10-3. Determining the Database Identifier
    4. 10-4. Restoring Control File with No Flash Recovery Area or Recovery Catalog
    5. 10-5. Restoring Control File to Nondefault Location
    6. 10-6. Restoring Lost Copy of Multiplexed Control File
    7. 10-7. Re-creating the Control File
  20. CHAPTER 11: Performing Complete Recovery
    1. Background
    2. If You're Still Awake...
    3. 11-1. Determining How to Restore and Recover
    4. 11-2. Previewing Backups Needed for Restore
    5. 11-3. Verifying Integrity of Backups
    6. 11-4. Testing Media Recovery
    7. 11-5. Performing Database-Level Recovery
    8. 11-6. Performing Tablespace-Level Recovery
    9. 11-7. Performing Datafile-Level Recovery
    10. 11-8. Restoring Datafiles to Nondefault Locations
    11. 11-9. Performing Block-Level Recovery
    12. 11-10. Recovering Read-Only Tablespaces
    13. 11-11. Restoring Temporary Tablespaces
    14. 11-12. Forcing RMAN to Restore a File
    15. 11-13. Restoring from an Older Backup
    16. 11-14. Recovering Through Resetlogs
    17. 11-15. Restoring the Spfile
    18. 11-16. Restoring Archived Redo Log Files
    19. 11-17. Recovering Datafiles Not Backed Up
    20. 11-18. Deleting Archived Redo Log Files During Recovery
    21. 11-19. Restoring from Uncataloged Backup Pieces in Oracle Database 10g and Newer
    22. 11-20. Restoring from Uncataloged Backup Pieces in Oracle9i Database and Older
  21. CHAPTER 12: Performing Incomplete Recovery
    1. Background
    2. 12-1. Determining Type of Incomplete Recovery
    3. 12-2. Performing Time-Based Recovery
    4. 12-3. Performing Log Sequence–Based Recovery
    5. 12-4. Performing Cancel-Based Recovery
    6. 12-5. Using LogMiner to Find an SCN
    7. 12-6. Performing Change/SCN-Based Recovery
    8. 12-7. Recovering to a Restore Point
    9. 12-8. Restoring a Noarchivelog Mode Database
    10. 12-9. Recovering to a Previous Incarnation
    11. 12-10. Performing Tablespace Point-in-Time Recovery
    12. 12-11. Recovering a Subset of Datafiles
    13. 12-12. Troubleshooting Incomplete Recovery
  22. CHAPTER 13: Performing Flashback Recovery
    1. Introducing Flashback
    2. 13-1. Checking the Flashback Status of a Database
    3. 13-2. Enabling Flashback on a Database
    4. 13-3. Disabling Flashback on a Database
    5. 13-4. Flashing Back a Database from RMAN
    6. 13-5. Flashing Back a Database from SQL
    7. 13-6. Finding Out How Far Back into the Past You Can Flash Back
    8. 13-7. Estimating the Amount of Flashback Logs Generated at Various Times
    9. 13-8. Estimating the Space Occupied by Flashback Logs in the Flash Recovery Area
    10. 13-9. Creating Normal Restore Points
    11. 13-10. Creating Guaranteed Restore Points
    12. 13-11. Listing Restore Points
    13. 13-12. Dropping Restore Points
    14. 13-13. Recovering a Dropped Table
    15. 13-14. Undropping a Table When Another Exists with the Same Name
    16. 13-15. Undropping a Specific Table from Two Dropped Tables with the Same Name
    17. 13-16. Checking the Contents of the Recycle Bin
    18. 13-17. Restoring Dependent Objects of an Undropped Table
    19. 13-18. Turning Off the Recycle Bin
    20. 13-19. Clearing the Recycle Bin
    21. 13-20. Querying the History of a Table Row (Flashback Query)
    22. 13-21. Flashing Back a Specific Table
  23. CHAPTER 14: Handling Online Redo Log Failures
    1. How Redo Logs Work
    2. 14-1. Determining How to Restore
    3. 14-2. Restoring After Losing One Member of the Multiplexed Group
    4. 14-3. Recovering After Loss of All Members of the INACTIVE Redo Log Group
    5. 14-4. Recovering After Loss of All Members of the ACTIVE Redo Log Group
    6. 14-5. Recovering After Loss of All Members of the CURRENT Redo Log Group
  24. CHAPTER 15: Duplicating Databases and Transporting Data
    1. 15-1. Renaming Files in a Duplicate Database
    2. 15-2. Creating a Duplicate Database on the Same Host
    3. 15-3. Duplicating a Database Without Any RMAN Backups
    4. 15-4. Creating a Duplicate Database on a Remote Host with the Same File Structure
    5. 15-5. Duplicating a Database with Several Directories
    6. 15-6. Creating a Standby Database on a New Host
    7. 15-7. Duplicating a Database to a Past Point in Time
    8. 15-8. Skipping Tablespaces During Database Duplication
    9. 15-9. Duplicating a Database with a Specific Backup Tag
    10. 15-10. Resynchronizing a Duplicate Database
    11. 15-11. Transporting Tablespaces on the Same OS Platform
    12. 15-12. Transporting Tablespaces Across Different Operating System Platforms
    13. 15-13. Transporting an Entire Database to a Different Platform
    14. 15-14. Transporting a Database by Converting Datafiles on the Target Platform
  25. CHAPTER 16: Tuning RMAN
    1. 16-1. Identifying RMAN Processes
    2. 16-2. Measuring Backup Performance
    3. 16-3. Monitoring RMAN Job Progress
    4. 16-4. Identifying I/O Bottlenecks
    5. 16-5. Improving Tape I/O Performance
    6. 16-6. Maximizing Throughput to Backup Device
    7. 16-7. Setting Large Pool Memory Size
    8. 16-8. Tuning Media Recovery
    9. 16-9. Tuning Crash Recovery
    10. 16-10. Slowing RMAN Down
    11. 16-11. Improving Performance Through Parallelism
    12. 16-12. Improving Performance Using Incremental Features
  26. CHAPTER 17: Troubleshooting RMAN
    1. 17-1. Determining Where to Start
    2. 17-2. Resolving Connection Permission Issues
    3. 17-3. Handling Disk Space Issues
    4. 17-4. Dealing with the RMAN-06059 Error
    5. 17-5. Terminating RMAN Processes
    6. 17-6. Diagnosing NLS Character Set Issues
    7. 17-7. Logging RMAN Output
    8. 17-8. Viewing RMAN Command History
    9. 17-9. Enabling RMAN's Debug Output
    10. 17-10. Enabling Granular Time Reporting
    11. 17-11. Working with Oracle Support
    12. 17-12. Resolving RMAN Compatibility Issues
    13. 17-13. Dealing with an ORA-19511 Error
    14. 17-14. Dealing with an ORA-27211 Error
    15. 17-15. Dealing with an ORA-04031 Error
    16. 17-16. Managing Files in an ASM Environment
  27. CHAPTER 18: Using a Media Management Layer
    1. Using Oracle Secure Backup
    2. 18-1. Configuring RMAN Access to the Oracle Secure Backup sbt Library
    3. 18-2. Managing Authorized OSB Accounts
    4. 18-3. Creating OSB Media Families for RMAN Backups
    5. 18-4. Creating an OSB Database Backup Storage Selector
    6. 18-5. Configuring OSB Parameters in RMAN
    7. 18-6. Backing Up Using Oracle Secure Backup
    8. 18-7. Restoring Using Oracle Secure Backup
    9. 18-8. Accessing RMAN Backup Data in Oracle Secure Backup
    10. Using Veritas NetBackup
    11. 18-9. Installing the NetBackup Agent for Oracle
    12. 18-10. Maintaining Policies for the RMAN Backups
    13. 18-11. Scheduling NetBackup RMAN Jobs
    14. 18-12. Defining Client Databases in NetBackup
    15. 18-13. Checking for NetBackup Files on Tape
    16. 18-14. Configuring NetBackup Parameters in RMAN
    17. 18-15. Backing Up Using NetBackup
    18. 18-16. Restoring Using NetBackup
    19. Using EMC NetWorker
    20. 18-17. Configuring EMC NetWorker
    21. 18-18. Installing EMC NetWorker Module for Oracle
    22. 18-19. Backing Up Using the EMC NetWorker Module for Oracle
    23. 18-20. Restoring Using the EMC NetWorker Module for Oracle
    24. 18-21. Uninstalling the EMC NetWorker Module for Oracle
    25. 18-22. Verifying the MML Installation
  28. CHAPTER 19: Performing Backup and Recovery with Enterprise Manager
    1. 19-1. Getting Started with RMAN and Enterprise Manager
    2. 19-2. Setting Up a Credentialed OS User
    3. 19-3. Creating a Backup
    4. 19-4. Restoring and Recovering
    5. 19-5. Viewing Backup Reports
    6. 19-6. Performing Routine RMAN Maintenance Tasks
    7. 19-7. Configuring a Recovery Catalog
    8. 19-8. Configuring Instance Recovery
    9. 19-9. Configuring the Flash Recovery Area
    10. 19-10. Configuring Restore Points
    11. 19-11. Running Custom RMAN Scripts
    12. 19-12. Configuring Backup Settings
    13. 19-13. Configuring Backup Policies
  29. CHAPTER 20: Using the Data Recovery Advisor
    1. 20-1. Listing Failures
    2. 20-2. Getting Advice
    3. 20-3. Repairing Failures
    4. 20-4. Using the Data Recovery Advisor Through Enterprise Manager
    5. 20-5. Changing Failure Status
  30. CHAPTER 21: Using RMAN on Windows
    1. Oracle on Windows
    2. Oracle Architecture on Windows
    3. Services
    4. Threads, Not Processes
    5. Oracle Home and SID
    6. Oracle Groups
    7. Location of Oracle Binaries
    8. Managing Oracle Through the Management Console
    9. Killing the Threads
    10. Copying Files
    11. RMAN Recipes for Windows
    12. 21-1. Connecting As sysdba Using OS Authentication
    13. 21-2. Simulating a Failure
    14. 21-3. Creating a Flash Recovery Area
    15. 21-4. Placing Datafiles, Control Files, and Online and Archived Redo Log Files in the FRA
    16. 21-5. Switching Back from Image Copies
    17. 21-6. Using the Flash Recovery Area
    18. 21-7. Developing a Windows Batch File
    19. 21-8. Scheduling Windows Jobs
    20. 21-9. Transporting Tablespaces to/from Windows
    21. 21-10. Transporting an Entire Database to/from Windows
  31. Index

Product information

  • Title: RMAN Recipes for Oracle Database 11g: A Problem-Solution Approach
  • Author(s): Darl Kuhn, Sam Alapati, Arup Nanda
  • Release date: August 2007
  • Publisher(s): Apress
  • ISBN: 9781590598511