Oracle Database 10g: Backup and Recovery Student Guide

D22057GC10 Production 1.0 June 2006 D46524

®

Authors

Copyright © 2006, Oracle. All rights reserved.

Donna Keesling Maria Billings

Disclaimer

Technical Contributors and Reviewers Christopher Andrews Tammy Bednar Tom Best Harald van Breederode Mary Jane Bryksa Marielle Canning Tim Chien Donna Cooksey Judy Ferstenberg Gerlinde Frenzen Joel Goodman Pete Jones Isabelle Marchand Sabiha Miri Manish Pawar Jim Spiller George Stabler Anthony Woodell Editor Daniel Milne Graphic Designer Satish Bettegowda Publisher Jobi Varghese

This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract. Trademark Notice Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Contents

1

Introduction Objectives 1-2 Course Objectives 1-3 What Is Backup and Recovery? 1-4 Performing Backup and Recovery Operations for an Oracle Database 1-5 Using Oracle Recovery Manager 1-6 Recovery Manager Components 1-7 Using Oracle Secure Backup 1-9 Overview of Oracle Backup Methods 1-10 When Do You Need to Use a Backup for Recovery? 1-12 Responding to User Error 1-13 Overview of Data Recovery 1-14 Oracle Database Architecture: Crash Recovery 1-15 Oracle Database Architecture: Recovery After Media Failure 1-16 Developing a Backup and Recovery Strategy for Your Oracle Database 1-17 Oracle Solutions for High Availability 1-18 Oracle High Availability Solutions for Unplanned Downtime 1-19 Summary 1-21

2

Configuring for Recoverability Objectives 2-2 Configuring Your Database for Backup and Recovery Operations 2-3 Configuring ARCHIVELOG Mode 2-4 Specifying a Backup Destination 2-5 Specifying a Retention Policy 2-6 Using a Flash Recovery Area 2-8 Defining a Flash Recovery Area 2-9 Defining Flash Recovery Area Using Enterprise Manager 2-10 Flash Recovery Area Space Management 2-11 Flash Recovery Area Space Usage 2-13 Monitoring the Flash Recovery Area 2-14 Benefits of Using a Flash Recovery Area 2-15 Summary 2-16 Practice Overview 2-17

iii

3

Using RMAN to Create Backups Objectives 3-2 Using RMAN to Create Backups 3-3 Backup Destinations 3-4 Configuring Persistent Settings for RMAN 3-5 Using Enterprise Manager to Configure RMAN Settings 3-6 Managing Persistent Settings 3-7 Configuring and Allocating Channels for Use in Backups 3-8 Configuring Backup Optimization 3-9 Creating Backup Sets 3-11 Creating Image Copies 3-12 BACKUP Command Options 3-14 Creating a Whole Database Backup 3-16 RMAN Backup Types 3-17 Fast Incremental Backup 3-19 Enabling Fast Incremental Backup 3-20 Monitoring Block Change Tracking 3-21 Creating an Oracle-Suggested Backup 3-22 Creating Duplexed Backup Sets 3-23 Creating Duplexed Backup Sets Using CONFIGURE BACKUP COPIES 3-24 Creating Duplexed Backup Sets Using BACKUP COPIES 3-25 Creating Backups of Backup Sets 3-26 Configuring Control File Autobackups 3-27 Using a Media Manager 3-29 Creating Proxy Copies 3-31 Managing Backups: Reporting 3-32 Using Enterprise Manager to View Backup Reports 3-34 Managing Backups: Crosschecking and Deleting 3-35 Using Enterprise Manager to Manage Backups 3-36 Summary 3-37 Practice Overview 3-38

4

Using RMAN to Perform Recovery Objectives 4-2 Using RMAN RESTORE and RECOVER Commands 4-3 Performing Recovery Using Enterprise Manager 4-4 Comparing Complete and Incomplete Recovery 4-5 Complete Recovery Following the Loss of Datafiles 4-6 Performing Complete Recovery: Loss of a Non-Critical datafile in ARCHIVELOG Mode 4-7

iv

Performing Complete Recovery: Loss of a System-Critical Datafile in ARCHIVELOG Mode 4-8 Recovery Using Incrementally Updated Backups 4-9 Using the Flash Recovery Area for Fast Recovery 4-10 Performing Restore and Recovery of a Database in NOARCHIVELOG Mode 4-11 Using Incremental Backups to Recover a Database in NOARCHIVELOG Mode 4-12 Restoring and Recovering the Database on a New Host 4-13 Preparing to Restore the Database to a New Host 4-14 Restoring the Database to a New Host 4-15 Performing Recovery with a Backup Control File 4-19 Restoring the Server Parameter File from the Control File Autobackup 4-20 Restoring the Control File from Autobackup 4-21 Performing Disaster Recovery 4-23 Summary 4-25 Practice Overview 4-26 5

Using Oracle Flashback Technologies Objectives 5-2 Using Oracle Flashback Technology 5-3 Flashback Drop and the Recycle Bin 5-4 Understanding the Recycle Bin 5-5 Restoring Tables from the Recycle Bin 5-6 Automatic Space Reclamation in the Recycle Bin 5-7 Manual Space Reclamation in the Recycle Bin 5-8 Bypassing the Recycle Bin 5-9 Querying the Recycle Bin 5-10 Querying Data in Dropped Tables 5-11 Using Flashback Technology to Query Data 5-12 Configuring Undo for Flashback 5-13 Flashback Query: Overview 5-14 Flashback Query: Example 5-15 Flashback Versions Query: Overview 5-16 Using Enterprise Manager to Perform Flashback Versions Query 5-17 Flashback Versions Query: Considerations 5-18 Flashback Transaction Query: Overview 5-19 Using Enterprise Manager to Perform Flashback Transaction Query 5-20 Flashback Transaction Query: Considerations 5-21 Flashback Table: Overview 5-22 Flashback Table 5-23 Enabling Row Movement on a Table 5-24 Performing Flashback Table 5-25 v

Flashback Table: Considerations 5-26 Flashback Database: Overview 5-27 Flashback Database Architecture 5-28 Configuring Flashback Database 5-29 Using Enterprise Manager to Configure Flashback Database 5-30 Flashback Database: Examples 5-31 Using Enterprise Manager to Perform Flashback Database 5-32 Flashback Database Considerations 5-35 Monitoring Flashback Database 5-36 Tuning Considerations for Flashback Database 5-39 Using Guaranteed Restore Points 5-41 Summary 5-42 Practice Overview 5-43 6

Using RMAN to Create a Duplicate Database Objectives 6-2 Using RMAN to Create a Duplicate Database 6-3 Using a Duplicate Database 6-4 Creating a Duplicate Database 6-5 Creating an Initialization Parameter File for the Auxiliary Instance 6-6 Specifying Parameters to Control File Naming 6-7 Starting the Instance in NOMOUNT Mode 6-9 Ensuring That Backups and Archived Redo Log Files Are Available 6-10 Allocating Auxiliary Channels 6-11 Using the RMAN DUPLICATE Command 6-12 Understanding the RMAN Duplication Operation 6-13 Specifying Options for the DUPLICATE Command 6-14 Using Enterprise Manager to Create a Duplicate Database 6-15 Using Enterprise Manager: Source Working Directory 6-16 Using Enterprise Manager: Select Destination 6-17 Using Enterprise Manager: Destination Options 6-18 Using Enterprise Manager: Review Page 6-19 Summary 6-20 Practice Overview 6-21

7

Performing Tablespace Point-in-Time Recovery Objectives 7-2 Tablespace Point-in-Time Recovery (TSPITR) Concepts 7-3 Tablespace Point-in-Time Recovery (TSPITR): Terminology 7-4 Tablespace Point-in-Time Recovery: Architecture 7-5 Understanding When to Use TSPITR 7-7

vi

Preparing for TSPITR 7-8 Determining the Correct Target Time 7-9 Determining the Tablespaces for the Recovery Set 7-10 Identifying Objects That Will Be Lost 7-11 Performing Basic RMAN TSPITR 7-12 Performing Fully Automated TSPITR 7-13 Using Enterprise Manager to Perform TSPITR 7-14 Understanding TSPITR Processing 7-15 Performing Customized RMAN TSPITR with an RMAN-Managed Auxiliary Instance 7-17

Performing RMAN TSPITR Using Your Own Auxiliary Instance 7-18 Troubleshooting RMAN TSPITR 7-19 Summary 7-21 Practice Overview 7-22 8

Using the RMAN Recovery Catalog Objectives 8-2 RMAN Repository Data Storage: Comparison of Options 8-3 Storing Information in the Recovery Catalog 8-4 Reasons to Use a Recovery Catalog 8-5 Creating the Recovery Catalog: Three Steps 8-6 Configuring the Recovery Catalog Database 8-7 Creating the Recovery Catalog Owner 8-8 Creating the Recovery Catalog 8-9 Managing Target Database Records in the Recovery Catalog 8-10 Registering a Database in the Recovery Catalog 8-11 Using Enterprise Manager to Register a Database 8-12 Unregistering a Target Database from the Recovery Catalog 8-14 Cataloging Additional Backup Files 8-15 Manually Resynchronizing the Recovery Catalog 8-16 Using RMAN Stored Scripts 8-17 Creating RMAN Stored Scripts 8-18 Executing RMAN Stored Scripts 8-19 Displaying RMAN Stored Script Information 8-20 Updating and Deleting RMAN Stored Scripts 8-21 Backing Up and Recovering the Recovery Catalog 8-22 Re-creating an Unrecoverable Recovery Catalog 8-23 Exporting and Importing the Recovery Catalog 8-24 Upgrading the Recovery Catalog 8-25 Dropping the Recovery Catalog 8-26 Summary 8-27 Practice Overview 8-28

vii

9

Monitoring and Tuning RMAN Objectives 9-2 Monitoring RMAN Sessions 9-3 Monitoring RMAN Job Progress 9-5 Monitoring RMAN Job Performance 9-7 Interpreting RMAN Message Output 9-8 Using the DEBUG Option 9-9 Understanding RMAN Code Layer Error Numbers 9-10 Interpreting RMAN Error Stacks 9-11 Tuning RMAN 9-12 RMAN Multiplexing 9-13 Allocating Disk Buffer: Example 9-14 Allocating Tape Buffers 9-15 Comparing Synchronous and Asynchronous I/O 9-16 Monitoring RMAN Job Performance 9-18 Asynchronous I/O Bottlenecks 9-19 Synchronous I/O Bottlenecks 9-20 Tape Backup Speed 9-21 Tape Subsystem Performance Rules 9-22 Controlling Tape Buffer Size with BLKSIZE 9-23 Channel Tuning 9-24 Tuning the BACKUP Command 9-25 Tuning RMAN Backup Performance 9-26 Setting LARGE_POOL_SIZE 9-27 Tuning RMAN Tape Streaming Performance Bottlenecks 9-28 Summary 9-30 Practice Overview 9-31

10 Oracle Secure Backup Overview Objectives 10-2 Oracle Secure Backup Tape Backup Management 10-3 What Is Oracle Secure Backup? 10-4 Oracle Secure Backup: Tape Management and Integration with Oracle Products 10-5

Client/Server Architecture Host Roles 10-6 Oracle Secure Backup for Centralized Tape Backup Management 10-7 Typical SAN Environment 10-8 Oracle Secure Backup Interface Options 10-9 Media Concepts: Overview 10-10 Backup Pieces and Backup Images 10-11 Media Management Expiration Policies 10-12 Oracle Secure Backup Media Family 10-14 viii

Tape Drives and Libraries 10-15 Virtual Tape Libraries (VTL) 10-17 Managing Data to Be Protected 10-18 Oracle Secure Backup Jobs 10-19 Securing Data and Access to the Backup Domain 10-20 Oracle Secure Backup: The Integration Advantage 10-22 Why Use Oracle Secure Backup? 10-23 Summary 10-24 11 Installing Oracle Secure Backup Objectives 11-2 Performing Preinstallation Tasks 11-3 Installation and Configuration of the Administrative Domain 11-4 Performing Installation Tasks 11-5 Administrative Server Installation: Example 11-6 Wizard-Based Installation on Windows 11-13 Oracle Secure Backup Interfaces 11-14 Integration with Enterprise Manager 11-15 Oracle Secure Backup Administrative Server Page 11-16 Configuring the Administrative Server in EM 11-17 Oracle Secure Backup Web Tool Home Page 11-18 Common obtool Commands 11-19 Configuring Oracle Secure Backup Users 11-20 Adding Oracle Secure Backup Users 11-21 Preauthorizing Access 11-23 Summary 11-24 Practice Overview 11-25 12 Using RMAN and Oracle Secure Backup Objectives 12-2 RMAN and Oracle Secure Backup Basic Process Flow 12-3 RMAN Database Backup to Tape 12-4 Database Backup Storage Selector 12-5 Defining Database Storage Selectors 12-7 Media Families and RMAN 12-8 Oracle Database Disk and Tape Backup Solution 12-9 Backing Up the Flash Recovery Area to Tape 12-10 Defining Retention for RMAN Backups 12-11 Backup Settings 12-12 Scheduling Backups with EM 12-13 Oracle-Suggested Backup to Tape 12-14 ix

RMAN and Oracle Secure Backup Job Execution 12-15 Managing Database Tape Backups 12-16 Performing Database Recovery 12-18 RMAN Automatic Failover to Previous Backup 12-19 Summary 12-20 Practice Overview 12-21 13 Backing Up File-System Files with Oracle Secure Backup Objectives 13-2 Backing Up File-System Files with Oracle Secure Backup 13-3 File-System Backups 13-4 Managing Media Families 13-5 Dataset Script: Examples 13-6 Creating Datasets 13-9 Creating Backup Windows 13-11 Creating Backup Schedules 13-13 Creating Backup Triggers 13-14 Previewing a Backup Trigger 13-16 Creating On-Demand Backup Requests 13-17 Submitting Backup Requests 13-19 Reviewing Jobs 13-20 Summary 13-21 Practice Overview 13-22 14 Restoring File-System Backups with Oracle Secure Backup Objectives 14-2 Browsing the Catalog for File-System Backup Data 14-3 Restoring File-System Data 14-4 Restoring File-System Files with Oracle Secure Backup 14-5 The Restore Page 14-6 Listing All File-System Backups of a Client 14-7 Creating a Catalog-Based Restore Request 14-8 Submitting Restore Requests 14-12 Summary 14-13 Practice Overview 14-14

x

15 Managing Oracle Secure Backup Security Objectives 15-2 Guarding Access and Data 15-3 Managing User Access Control 15-4 Predefined Classes and User Rights 15-5 Defining an Oracle Secure Backup User 15-8 Oracle Secure Backup User: OS Permissions 15-9 Preauthorization 15-11 Assigning Windows Account Information 15-12 Authentication 15-13 Leveraging Oracle Security Technology 15-14 Administrative Server Certificate Authority (CA) 15-16 Oracle Wallets 15-18 Encrypted Backups to Tape 15-20 Creating RMAN Encrypted Backups 15-21 Using Transparent Mode Encryption 15-22 Using Password Mode Encryption 15-24 Using Dual Mode Encryption 15-25 Restoring Encrypted Backups 15-26 Performing Encrypted Recovery 15-27 Summary 15-28 Practice Overview 15-29 16 Managing the Administrative Domain Objectives 16-2 Oracle Secure Backup Processes: Daemons 16-3 Managing Common Daemon Operations 16-5 Managing Policies and Defaults 16-6 Configuring Oracle Secure Backup Policies 16-7 Oracle Secure Backup: Backup Metadata Catalogs 16-8 Oracle Secure Backup: Directory Structure 16-10 Backing Up the Catalog 16-11 Adding Clients 16-12 Adding Media Servers 16-13 NAS Devices 16-15 Adding NDMP Media Servers 16-16 Adding Devices 16-17 Discovering Devices on NDMP Hosts 16-19 Managing Devices 16-20 Tape Library Properties 16-21 Tape Drive Properties 16-22 xi

Managing Volumes 16-23 Oracle Secure Backup Jobs 16-25 Managing Jobs 16-27 Viewing Job Properties and Transcripts 16-29 Suspending and Resuming Job Dispatching 16-31 Job Summaries 16-33 Displaying Log Files and Transcripts 16-34 Summary 16-35 Practice Overview 16-36 Appendix A: Practices Appendix B: Practice Solutions Appendix C: Oracle Secure Backup Additional Installation Topics Topics C-2 Windows Installation: Overview C-3 Stopping Tape Device Drivers C-4 Oracle Secure Backup Setup C-6 Service Startup Dialogs C-8 Service Login Dialog Box C-9 Oracle Secure Backup Installed Files C-10 Installed Files for Host Role: Administrative Server C-14 Installed Files for Host Role: Media Server C-16 Installed Files for Host Role: Client C-17 Specifying Installation Parameters C-18 Client Installation: Example C-20 Media Server Installation: Example C-21 Verifying Your Installation C-22 Uninstalling Oracle Secure Backup C-23 Summary C-25 Appendix D: Glossary

xii