book

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

by Darl Kuhn, Sam Alapati, Arup Nanda

July 2013

Intermediate to advanced

796 pages

21h 24m

English

Apress

Read now

Unlock full access

Types of Database FailuresOracle Backup and Recovery ConceptsRecovery TypesRMAN ArchitectureBenefits of Using RMANBackup and Recovery Best Practices

2-1. Connecting to Your Database2-2. Starting and Stopping Your Database2-3. Toggling Archivelog Mode2-4. Connecting to RMAN2-5. Backing Up Your Database2-6. Simulating a Failure2-7. Restoring and Recovering Your Database
3-1. Creating the Fast Recovery Area3-2. Writing Regular RMAN Backups to the FRA3-3. Freeing FRA Space in an Emergency3-4. Checking Space Usage in the FRA3-5. Expanding or Shrinking the FRA3-6. Configuring Archived Redo Logs to Go to FRA3-7. Using the Same FRA for Two Databases with the Same Name3-8. Placing a Control File in the FRA3-9. Placing Online Redo Log Files in FRA3-10. Sending Image Copies to the FRA3-11. Deleting Backup Sets from the FRA3-12. Deleting Archived Redo Logs from the FRA3-13. Reinstating a Damaged Data File from an Image Copy3-14. Switching Back from an Image Copy3-15. Backing Up the FRA to Tape3-16. Sizing the Fast Recovery Area
4-1. Starting the RMAN Client4-2. Issuing RMAN Commands4-3. Saving RMAN Output to a Text File4-4. Logging Command-Line RMAN Output4-5. Connecting to a Target Database from the RMAN Prompt4-6. Connecting to a Target Database from the Operating System Command Line4-7. Executing Operating System Commands from Within RMAN4-8. Scripting RMAN4-9. Executing RMAN Command Files4-10. Creating Dynamic Command Files4-11. Connecting to an Auxiliary Database4-12. Executing Multiple RMAN Commands as a Single Unit4-13. Issuing SQL Statements from the RMAN Client4-14. Starting and Shutting Down a Database with RMAN4-15. Checking the Syntax of RMAN Commands4-16. Hiding Passwords When Connecting to RMAN4-17. Identifying RMAN Server Sessions4-18. Dropping a Database Using the RMAN Client
5-1. Showing RMAN Configuration Settings5-2. Configuring RMAN5-3. Restoring Default Parameter Settings5-4. Enabling and Disabling Automatic Control File Backups5-5. Specifying the Auto Backup Control File Directory and File Name5-6. Specifying the Snapshot Control File’s Name and Location5-7. Specifying the Retention Period for RMAN History5-8. Configuring the Default Device Type5-9. Configuring the Default Backup Type5-10. Making Compressed Backup Sets the Default5-11. Configuring Multiple Backup Copies5-12. Skipping Previously Backed-Up Files5-13. Specifying Backup Piece File Names5-14. Generating File Names for Image Copies5-15. Tagging RMAN Backups5-16. Configuring Automatic Channels5-17. Manually Allocating RMAN Channels5-18. Allocating an RMAN Maintenance Channel5-19. Creating a Backup Retention Policy5-20. Configuring an Archived Redo Log Deletion Policy5-21. Limiting the Size of Individual Backup Pieces5-22. Configuring the Maximum Size of Backup Sets
6-1. Creating the Recovery Catalog6-2. Granting Restricted Access6-3. Connecting to the Catalog from the Command Line6-4. Connecting to the Catalog from the RMAN Prompt6-5. Registering Target Databases6-6. Unregistering a Database6-7. Cataloging Older Files6-8. Updating the Recovery Catalog6-9. Dropping the Recovery Catalog6-10. Merging Recovery Catalogs6-11. Moving the Recovery Catalog to Another Database6-12. Creating a High-Availability Recovery Catalog6-13. Viewing Backup Information6-14. Uncataloging RMAN Records6-15. Using a Release 12.x Client with Older Catalogs
Key Concepts7-1. Specifying Backup Options7-2. Backing Up the Control File7-3. Backing Up the Server Parameter File7-4. Backing Up Data Files7-5. Backing Up Tablespaces7-6. Making a Whole-Database Backup7-7. Backing Up Archived Redo Logs7-8. Backing Up Everything7-9. Backing Up Recovery Files in the FRA7-10. Performing Incremental Backups7-11. Reducing Incremental Backup Time7-12. Creating Multiple Backup Sets7-13. Making Copies of Backup Sets7-14. Making Copies of Image Copy Backups7-15. Making Tape Copies of Disk-Based Image Copies7-16. Excluding a Tablespace from a Backup7-17. Skipping Read-Only, Offline, or Inaccessible Files7-18. Encrypting RMAN Backups7-19. Making a Compressed Backup7-20. Parallelizing Backups7-21. Making Faster Backups of Large Files7-22. Specifying Backup Windows7-23. Reusing RMAN Backup Files7-24. Retaining Backups for a Long Time7-25. Backing Up Only Those Files Previously Not Backed Up7-26. Restarting Backups After a Crash7-27. Updating Image Copies7-28. Backing Up a Container Database7-29. Backing Up the Root in a Container Database7-30. Backing Up a Pluggable Database7-31. Backing Up Tablespaces and Data Files in a PDB
8-1. Adding User-Made Backups to the Repository8-2. Finding Data Files and Archive Logs that Need a Backup8-3. Finding Data Files Affected by Unrecoverable Operations8-4. Identifying Obsolete Backups8-5. Displaying Information About Database Files8-6. Listing RMAN Backups8-7. Listing Expired Backups8-8. Listing Only Recoverable Backups and Copies8-9. Listing Restore Points8-10. Listing Database Incarnations8-11. Updating the RMAN Repository After Manually Deleting Backups8-12. Synchronizing the Repository with the Actual Backups8-13. Deleting Backups8-14. Deleting Archived Redo Logs8-15. Deleting Obsolete RMAN Backups8-16. Changing the Status of an RMAN Backup Record8-17. Changing the Status of Archival Backups8-18. Testing the Integrity of an RMAN Backup8-19. Validating Data Files, Backup Sets, and Data Blocks
Approaches to Scripting9-1. Developing a Unix Shell Script for RMAN9-2. Scheduling a Unix Shell File9-3. Developing a Windows Batch File to Run RMAN9-4. Scheduling a Script in Windows via the GUI9-5. Changing the Schedule of a Batch Job in the Task Scheduler9-6. Scheduling in Windows from the Command Line9-7. Creating Local Stored Scripts9-8. Creating a Global Stored Script9-9. Updating Stored Scripts9-10. Commenting on Stored Scripts9-11. Displaying Stored Scripts9-12. Listing Stored Scripts9-13. Dropping Stored Scripts9-14. Executing a Global Script When a Local Script of the Same Name Exists9-15. Converting Stored Scripts to Files9-16. Creating or Replacing a Stored Script from a File9-17. Passing Parameters to Stored Scripts9-18. Creating a Parameterized Command File Script
10-1. Restoring Control File Using Fast Recovery Area10-2. Restoring Control File Using Recovery Catalog10-3. Determining the Database Identifier10-4. Restoring Control File with No Fast Recovery Area or Recovery Catalog10-5. Restoring Control File to Nondefault Location10-6. Restoring Lost Copy of Multiplexed Control File10-7. Re-creating the Control File10-8. Restoring the Control File Using DBMS_BACKUP_RESTORE
BackgroundIf You’re Still Awake . . .11-1. Determining How to Restore and Recover11-2. Viewing Backup Files Needed for Restore11-3. Verifying Integrity of Backup Files11-4. Testing Media Recovery11-5. Performing Database-Level Recovery11-6. Performing Tablespace-Level Recovery11-7. Performing Data File–Level Recovery11-8. Restoring Data Files to Nondefault Locations11-9. Performing Block-Level Recovery11-10. Restoring Temporary Tablespaces11-11. Forcing RMAN to Restore a File11-12. Restoring from an Older Backup11-13. Recovering Through Resetlogs11-14. Restoring the spfile11-15. Restoring Archived Redo Log Files11-16. Deleting Archived Redo Log Files During Recovery11-17. Recovering Data Files Not Backed Up11-18. Restoring from Uncataloged Backup Pieces11-19. Restoring Data Files Using DBMS_BACKUP_RESTORE11-20. Restoring a Container Database and/or Its Associated Pluggable Databases11-21. Restoring and Recovering to a Different Server
Background12-1. Determining Type of Incomplete Recovery12-2. Performing Time-Based Recovery12-3. Performing Log Sequence–Based Recovery12-4. Performing Cancel-Based Recovery12-5. Using LogMiner to Find an SCN12-6. Performing Change/SCN-Based Recovery12-7. Recovering to a Restore Point12-8. Restoring a Noarchivelog Mode Database12-9. Recovering to a Previous Incarnation12-10. Performing Tablespace Point-in-Time Recovery12-11. Recovering a Subset of Data Files12-12. Performing Incomplete Recovery on a Pluggable Database12-13. Troubleshooting Incomplete Recovery
Introducing Flashback13-1. Checking the Flashback Status of a Database13-2. Enabling Flashback on a Database13-3. Disabling Flashback on a Database13-4. Flashing Back a Database from RMAN13-5. Flashing Back a Database from SQL13-6. Finding Out How Far Back into the Past You Can Flash Back13-7. Estimating the Amount of Flashback Logs Generated at Various Times13-8. Estimating the Space Occupied by Flashback Logs in the Fast Recovery Area13-9. Creating Normal Restore Points13-10. Creating Guaranteed Restore Points13-11. Listing Restore Points13-12. Dropping Restore Points13-13. Recovering a Dropped Table13-14. Undropping a Table When Another Exists with the Same Name13-15. Undropping a Specific Table from Two Dropped Tables with the Same Name13-16. Checking the Contents of the Recycle Bin13-17. Restoring Dependent Objects of an Undropped Table13-18. Turning Off the Recycle Bin13-19. Clearing the Recycle Bin13-20. Querying the History of a Table Row (Flashback Query)13-21. Flashing Back a Specific Table13-22. Recovering a Specific Table from Backup13-23. Recovering a Partition13-24. Recovering a Table into a Different Name13-25. Recovering a Table into a Different Tablespace13-26. Creating an Export Dump of a Table to be Recovered
How Redo Logs Work14-1. Determining a Course of Action14-2. Restoring After Losing One Member of a Multiplexed Group14-3. Recovering After Loss of All Members of the INACTIVE Redo Log Group14-4. Recovering After Loss of All Members of the ACTIVE Redo Log Group14-5. Recovering After Loss of All Members of the CURRENT Redo Log Group
15-1. Renaming Database Files in a Duplicate Database15-2. Specifying Alternative Names for OMF or ASM File Systems15-3. Creating a Duplicate Database from RMAN Backups15-4. Duplicating a Database Without Using RMAN Backups15-5. Specifying Options for Network-based Active Database Duplication15-6. Duplicating a Database with Several Directories15-7. Duplicating a Database to a Past Point in Time15-8. Skipping Tablespaces During Database Duplication15-9. Duplicating a Database with a Specific Backup Tag15-10. Resynchronizing a Duplicate Database15-11. Duplicating Pluggable Databases and Container Databases15-12. Transporting Tablespaces on the Same Operating System Platform15-13. Performing a Cross-Platform Tablespace Transport by Converting Files on the Source Host15-14. Performing a Cross-Platform Tablespace Transport by Converting Files on the Destination Host15-15. Transporting a Database by Converting Files on the Source Database Platform15-16. Transporting Tablespaces to a Different Platform Using RMAN Backup Sets15-17. Transporting a Database to a Different Platform Using RMAN Backup Sets
16-1. Identifying RMAN Processes16-2. Monitoring RMAN Job Progress16-3. Measuring Backup Performance16-4. Determining Whether Repository Queries Are a Bottleneck16-5. Enabling SQL Tracing for an RMAN Session16-6. Determining Whether Bottleneck Is Read or Write16-7. Identifying I/O Bottlenecks16-8. Improving Tape I/O Performance16-9. Maximizing Throughput to Backup Device16-10. Tuning Media Recovery16-11. Slowing RMAN Down16-12. Improving Performance Through Parallelism16-13. Speeding Up Compressed Backup Sets16-14. Improving Performance Using Incremental Features16-15. Setting the Large Pool Size
17-1. Determining Where to Start17-2. Resolving Connection Permission Issues17-3. Handling Disk Space Issues17-4. Dealing with the RMAN-06059 Error17-5. Terminating RMAN Processes17-6. Diagnosing NLS Character Set Issues17-7. Logging RMAN Output17-8. Viewing RMAN Command History17-9. Enabling RMAN’s Debug Output17-10. Enabling Granular Time Reporting17-11. Working with Oracle Support17-12. Resolving RMAN Compatibility Issues17-13. Dealing with an ORA-19511 Error17-14. Dealing with an ORA-27211 Error17-15. Dealing with an ORA-04031 Error17-16. Managing Files in an ASM Environment17-17. Automatically Determining Whether Backups Are Working
A Quick Introduction18-1. Installing Oracle Secure Backup on Linux18-2. Verifying the MML Installation18-3. Working with obtool18-4. Using obtool in Noninteractive Mode18-5. Configuring an OSB Virtual Device18-6. Configuring RMAN to Work with Oracle Secure Backup18-7. Configuring RMAN Access to the Oracle Secure Backup sbt Library18-8. Managing Authorized OSB Accounts18-9. Creating OSB Media Families for RMAN Backups18-10. Creating an OSB Database Backup Storage Selector18-11. Configuring OSB Parameters in RMAN18-12. Making Oracle Database Backups Using Oracle Secure Backup18-13. Restoring Using Oracle Secure Backup18-14. Accessing RMAN Backup Data in Oracle Secure Backup
19-1. Creating a Backup19-2. Restoring and Recovering19-3. Performing Routine RMAN Maintenance Tasks19-4. Configuring a Recovery Catalog19-5. Configuring the Recovery Settings19-6. Running Custom RMAN Scripts19-7. Configuring Backup Settings19-8. Configuring Backup Policies
20-1. Listing Failures20-2. Getting Advice20-3. Repairing Failures20-4. Changing Failure Status
21-1. Understanding Oracle Database Architecture on Windows21-2. Monitoring Threads on Windows21-3. Identifying Oracle Home and SID on Windows21-4. Setting Oracle Groups on Windows21-5. Setting Path for Oracle Binaries on Windows21-6. Managing Oracle via Management Console21-7. Killing Oracle Processes in Windows21-8. Choosing Oracle Homes21-9. Copying Open Files in Windows21-10. Connecting as sysdba Using OS Authentication21-11. Simulating a Failure21-12. Creating a Fast Recovery Area21-13. Placing Data Files, Control Files, and Online and Archived Redo Log Files in the FRA21-14. Switching Back from Image Copies21-15. Using the Fast Recovery Area21-16. Developing a Windows Batch File21-17. Scheduling Windows Jobs21-18. Transporting Tablespaces to/from Windows21-19. Transporting an Entire Database to/from Windows
22-1. Synchronizing a Physical Standby Database22-2. Configuring Archive Log Deletion Policies on the Primary and Standby Databases22-3. Instantiating a Standby Database with RMAN22-4. Resynchronizing After Structural Changes on the Primary Database22-5. Implementing Oracle’s Recommended Backup Strategy22-6. Recovering from the Loss of Data Files22-7. Recovering Data Files on the Primary Database22-8. Recovering the Control File(s) of a Standby Database22-9. Recovering File(s) for a Primary Database22-10. Configuring RMAN on a Primary Database22-11. Configuring RMAN in a Standby Database22-12. Registering Databases in a Data Guard Environment22-13. Maintaining RMAN Backups in a Data Guard Environment22-14. Reinstating a Physical Standby Using RMAN Backups
23-1. Specifying a Location for the Snapshot Controlfile23-2. Parallelizing a Backup Across All Nodes in a Cluster (11.2 and Higher)23-3. Parallelizing a Backup Across All Nodes in a Cluster (Below 11.2)23-4. Restoring on Multiple Nodes23-5. Backing Up a RAC Database to Tape
24-1. Defining ASM Diskgroups as Backup Destinations24-2. Specifying ASM Diskgroups as Archivelog Locations24-3. Copying Files to or from ASM
The Lay of the Land25-1. Configuring RMAN on Exadata25-2. Backing Up Inside the Exadata Rack25-3. Backing Up Outside the Exadata Rack25-4. Setting RMAN Buffer Size25-5. Detecting Skipped Blocks25-6. Tuning RMAN Write Performance on Exadata