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

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

by Darl Kuhn, Sam Alapati, Arup Nanda
Released July 2013
Publisher(s): Apress
ISBN: 9781430248361

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

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

    Product information

    • Title: RMAN Recipes for Oracle Database 12c: A Problem-Solution Approach, Second Edition
    • Author(s): Darl Kuhn, Sam Alapati, Arup Nanda
    • Release date: July 2013
    • Publisher(s): Apress
    • ISBN: 9781430248361