O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

RMAN Recipes for Oracle Database 12c: A Problem-Solution Approach, Second Edition

Book Description

RMAN Recipes for Oracle Database 12c is an example-driven approach to the Oracle database administrator's #1 job responsibility:

  • Be able to recover the database.

Of all the things you are responsible for as 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.

Oracle's flagship database product fortunately implements a wide-ranging feature set to aid you in the all-important task of safeguarding against data loss. Recovery Manager, or RMAN, is at the heart of that feature set, and is the tool most-often used to initiate database backup and recovery operations. In this book, well-known authors and database experts Darl Kuhn, Sam Alapati, and Arup Nanda have created a set of examples encompassing the gamut of backup and recovery tasks that you might need to perform.

Sometimes, especially when the heat is on, a good example is what you need to get started towards a solution. RMAN Recipes for Oracle Database 12c delivers. It'll be the book you reach for when that dreaded call comes in at 3:00am some dreary morning. It'll be the book that lets you sleep at night knowing that no matter what transpires, that you've done your job well and can recover from any outage. RMAN Recipes for Oracle Database 12c 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

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

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

  • Automate backup and recovery tasks by writing scripts

  • Troubleshoot RMAN problems and optimize RMAN performance

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

Who this book is for

RMAN Recipes for Oracle Database 12c is aimed squarely at 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 Reviewers
  8. Acknowledgments
  9. Introduction
  10. CHAPTER 1: Backup and Recovery 101
    1. Types of Database Failures
    2. Oracle Backup and Recovery Concepts
    3. Recovery Types
    4. RMAN Architecture
    5. Benefits of Using RMAN
    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 Fast Recovery Area
    1. 3-1. Creating the Fast 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 Data File 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 Fast 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 Auto Backup Control File Directory and File Name
    6. 5-6. Specifying the Snapshot Control File’s Name 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 File Names
    14. 5-14. Generating File Names 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 12.x Client with Older Catalogs
  16. CHAPTER 7: Making Backups with RMAN
    1. Key Concepts
    2. 7-1. Specifying Backup Options
    3. 7-2. Backing Up the Control File
    4. 7-3. Backing Up the Server Parameter File
    5. 7-4. Backing Up Data Files
    6. 7-5. Backing Up Tablespaces
    7. 7-6. Making a Whole-Database Backup
    8. 7-7. Backing Up Archived Redo Logs
    9. 7-8. Backing Up Everything
    10. 7-9. Backing Up Recovery Files in the FRA
    11. 7-10. Performing Incremental Backups
    12. 7-11. Reducing Incremental Backup Time
    13. 7-12. Creating Multiple Backup Sets
    14. 7-13. Making Copies of Backup Sets
    15. 7-14. Making Copies of Image Copy Backups
    16. 7-15. Making Tape Copies of Disk-Based Image Copies
    17. 7-16. Excluding a Tablespace from a Backup
    18. 7-17. Skipping Read-Only, Offline, or Inaccessible Files
    19. 7-18. Encrypting RMAN Backups
    20. 7-19. Making a Compressed Backup
    21. 7-20. Parallelizing Backups
    22. 7-21. Making Faster Backups of Large Files
    23. 7-22. Specifying Backup Windows
    24. 7-23. Reusing RMAN Backup Files
    25. 7-24. Retaining Backups for a Long Time
    26. 7-25. Backing Up Only Those Files Previously Not Backed Up
    27. 7-26. Restarting Backups After a Crash
    28. 7-27. Updating Image Copies
    29. 7-28. Backing Up a Container Database
    30. 7-29. Backing Up the Root in a Container Database
    31. 7-30. Backing Up a Pluggable Database
    32. 7-31. Backing Up Tablespaces and Data Files in a PDB
  17. CHAPTER 8: Maintaining RMAN Backups and the Repository
    1. 8-1. Adding User-Made Backups to the Repository
    2. 8-2. Finding Data Files and Archive Logs that Need a Backup
    3. 8-3. Finding Data Files 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 Data Files, 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 Fast 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 Fast 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
    8. 10-8. Restoring the Control File Using DBMS_BACKUP_RESTORE
  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. Viewing Backup Files Needed for Restore
    5. 11-3. Verifying Integrity of Backup Files
    6. 11-4. Testing Media Recovery
    7. 11-5. Performing Database-Level Recovery
    8. 11-6. Performing Tablespace-Level Recovery
    9. 11-7. Performing Data File–Level Recovery
    10. 11-8. Restoring Data Files to Nondefault Locations
    11. 11-9. Performing Block-Level Recovery
    12. 11-10. Restoring Temporary Tablespaces
    13. 11-11. Forcing RMAN to Restore a File
    14. 11-12. Restoring from an Older Backup
    15. 11-13. Recovering Through Resetlogs
    16. 11-14. Restoring the spfile
    17. 11-15. Restoring Archived Redo Log Files
    18. 11-16. Deleting Archived Redo Log Files During Recovery
    19. 11-17. Recovering Data Files Not Backed Up
    20. 11-18. Restoring from Uncataloged Backup Pieces
    21. 11-19. Restoring Data Files Using DBMS_BACKUP_RESTORE
    22. 11-20. Restoring a Container Database and/or Its Associated Pluggable Databases
    23. 11-21. Restoring and Recovering to a Different Server
  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 Data Files
    13. 12-12. Performing Incomplete Recovery on a Pluggable Database
    14. 12-13. 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 Fast 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. 13-22. Recovering a Specific Table from Backup
    24. 13-23. Recovering a Partition
    25. 13-24. Recovering a Table into a Different Name
    26. 13-25. Recovering a Table into a Different Tablespace
    27. 13-26. Creating an Export Dump of a Table to be Recovered
  23. CHAPTER 14: Handling Online Redo Log Failures
    1. How Redo Logs Work
    2. 14-1. Determining a Course of Action
    3. 14-2. Restoring After Losing One Member of a 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 Database Files in a Duplicate Database
    2. 15-2. Specifying Alternative Names for OMF or ASM File Systems
    3. 15-3. Creating a Duplicate Database from RMAN Backups
    4. 15-4. Duplicating a Database Without Using RMAN Backups
    5. 15-5. Specifying Options for Network-based Active Database Duplication
    6. 15-6. Duplicating a Database with Several Directories
    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. Duplicating Pluggable Databases and Container Databases
    12. 15-12. Transporting Tablespaces on the Same Operating System Platform
    13. 15-13. Performing a Cross-Platform Tablespace Transport by Converting Files on the Source Host
    14. 15-14. Performing a Cross-Platform Tablespace Transport by Converting Files on the Destination Host
    15. 15-15. Transporting a Database by Converting Files on the Source Database Platform
    16. 15-16. Transporting Tablespaces to a Different Platform Using RMAN Backup Sets
    17. 15-17. Transporting a Database to a Different Platform Using RMAN Backup Sets
  25. CHAPTER 16: Tuning RMAN
    1. 16-1. Identifying RMAN Processes
    2. 16-2. Monitoring RMAN Job Progress
    3. 16-3. Measuring Backup Performance
    4. 16-4. Determining Whether Repository Queries Are a Bottleneck
    5. 16-5. Enabling SQL Tracing for an RMAN Session
    6. 16-6. Determining Whether Bottleneck Is Read or Write
    7. 16-7. Identifying I/O Bottlenecks
    8. 16-8. Improving Tape I/O Performance
    9. 16-9. Maximizing Throughput to Backup Device
    10. 16-10. Tuning Media Recovery
    11. 16-11. Slowing RMAN Down
    12. 16-12. Improving Performance Through Parallelism
    13. 16-13. Speeding Up Compressed Backup Sets
    14. 16-14. Improving Performance Using Incremental Features
    15. 16-15. Setting the Large Pool Size
  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
    17. 17-17. Automatically Determining Whether Backups Are Working
  27. CHAPTER 18: Using Oracle Secure Backup as a Media Management Layer
    1. A Quick Introduction
    2. 18-1. Installing Oracle Secure Backup on Linux
    3. 18-2. Verifying the MML Installation
    4. 18-3. Working with obtool
    5. 18-4. Using obtool in Noninteractive Mode
    6. 18-5. Configuring an OSB Virtual Device
    7. 18-6. Configuring RMAN to Work with Oracle Secure Backup
    8. 18-7. Configuring RMAN Access to the Oracle Secure Backup sbt Library
    9. 18-8. Managing Authorized OSB Accounts
    10. 18-9. Creating OSB Media Families for RMAN Backups
    11. 18-10. Creating an OSB Database Backup Storage Selector
    12. 18-11. Configuring OSB Parameters in RMAN
    13. 18-12. Making Oracle Database Backups Using Oracle Secure Backup
    14. 18-13. Restoring Using Oracle Secure Backup
    15. 18-14. Accessing RMAN Backup Data in Oracle Secure Backup
  28. CHAPTER 19: Performing Backup and Recovery with Enterprise Manager
    1. 19-1. Creating a Backup
    2. 19-2. Restoring and Recovering
    3. 19-3. Performing Routine RMAN Maintenance Tasks
    4. 19-4. Configuring a Recovery Catalog
    5. 19-5. Configuring the Recovery Settings
    6. 19-6. Running Custom RMAN Scripts
    7. 19-7. Configuring Backup Settings
    8. 19-8. 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. Changing Failure Status
  30. CHAPTER 21: Using RMAN on Windows
    1. 21-1. Understanding Oracle Database Architecture on Windows
    2. 21-2. Monitoring Threads on Windows
    3. 21-3. Identifying Oracle Home and SID on Windows
    4. 21-4. Setting Oracle Groups on Windows
    5. 21-5. Setting Path for Oracle Binaries on Windows
    6. 21-6. Managing Oracle via Management Console
    7. 21-7. Killing Oracle Processes in Windows
    8. 21-8. Choosing Oracle Homes
    9. 21-9. Copying Open Files in Windows
    10. 21-10. Connecting as sysdba Using OS Authentication
    11. 21-11. Simulating a Failure
    12. 21-12. Creating a Fast Recovery Area
    13. 21-13. Placing Data Files, Control Files, and Online and Archived Redo Log Files in the FRA
    14. 21-14. Switching Back from Image Copies
    15. 21-15. Using the Fast Recovery Area
    16. 21-16. Developing a Windows Batch File
    17. 21-17. Scheduling Windows Jobs
    18. 21-18. Transporting Tablespaces to/from Windows
    19. 21-19. Transporting an Entire Database to/from Windows
  31. CHAPTER 22: RMAN in an Oracle Data Guard Environment
    1. 22-1. Synchronizing a Physical Standby Database
    2. 22-2. Configuring Archive Log Deletion Policies on the Primary and Standby Databases
    3. 22-3. Instantiating a Standby Database with RMAN
    4. 22-4. Resynchronizing After Structural Changes on the Primary Database
    5. 22-5. Implementing Oracle’s Recommended Backup Strategy
    6. 22-6. Recovering from the Loss of Data Files
    7. 22-7. Recovering Data Files on the Primary Database
    8. 22-8. Recovering the Control File(s) of a Standby Database
    9. 22-9. Recovering File(s) for a Primary Database
    10. 22-10. Configuring RMAN on a Primary Database
    11. 22-11. Configuring RMAN in a Standby Database
    12. 22-12. Registering Databases in a Data Guard Environment
    13. 22-13. Maintaining RMAN Backups in a Data Guard Environment
    14. 22-14. Reinstating a Physical Standby Using RMAN Backups
  32. CHAPTER 23: RMAN and RAC
    1. 23-1. Specifying a Location for the Snapshot Controlfile
    2. 23-2. Parallelizing a Backup Across All Nodes in a Cluster (11.2 and Higher)
    3. 23-3. Parallelizing a Backup Across All Nodes in a Cluster (Below 11.2)
    4. 23-4. Restoring on Multiple Nodes
    5. 23-5. Backing Up a RAC Database to Tape
  33. CHAPTER 24: RMAN and ASM
    1. 24-1. Defining ASM Diskgroups as Backup Destinations
    2. 24-2. Specifying ASM Diskgroups as Archivelog Locations
    3. 24-3. Copying Files to or from ASM
  34. CHAPTER 25: RMAN in Exadata
    1. The Lay of the Land
    2. 25-1. Configuring RMAN on Exadata
    3. 25-2. Backing Up Inside the Exadata Rack
    4. 25-3. Backing Up Outside the Exadata Rack
    5. 25-4. Setting RMAN Buffer Size
    6. 25-5. Detecting Skipped Blocks
    7. 25-6. Tuning RMAN Write Performance on Exadata
  35. Index