Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Data Guard?

• Oracle Database 11g Release 2 (11.2.0.3) New Features in Oracle Data Guard
• New Features in Oracle Data Guard 11.2
• New Features in Oracle Data Guard 11.1

Part I Concepts and Administration

1 Introduction to Oracle Data Guard

• 1.1 Data Guard Configurations
  • 1.1.1 Primary Database
  • 1.1.2 Standby Databases
  • 1.1.3 Configuration Example
• 1.2 Data Guard Services
  • 1.2.1 Redo Transport Services
  • 1.2.2 Apply Services
  • 1.2.3 Role Transitions
• 1.3 Data Guard Broker
  • 1.3.1 Using Oracle Enterprise Manager Grid Control
  • 1.3.2 Using the Data Guard Command-Line Interface
• 1.4 Data Guard Protection Modes
• 1.5 Client Failover
• 1.6 Data Guard and Complementary Technologies
• 1.7 Summary of Data Guard Benefits

2 Getting Started with Data Guard

• 2.1 Standby Database Types
  • 2.1.1 Physical Standby Databases
  • 2.1.2 Logical Standby Databases
  • 2.1.3 Snapshot Standby Databases
• 2.2 User Interfaces for Administering Data Guard Configurations
• 2.3 Data Guard Operational Prerequisites
  • 2.3.1 Hardware and Operating System Requirements
  • 2.3.2 Oracle Software Requirements
• 2.4 Standby Database Directory Structure Considerations

3 Creating a Physical Standby Database

• 3.1 Preparing the Primary Database for Standby Database Creation
  • 3.1.1 Enable Forced Logging
  • 3.1.2 Configure Redo Transport Authentication
  • 3.1.3 Configure the Primary Database to Receive Redo Data
  • 3.1.4 Set Primary Database Initialization Parameters
  • 3.1.5 Enable Archiving
• 3.2 Step-by-Step Instructions for Creating a Physical Standby Database
  • 3.2.1 Create a Backup Copy of the Primary Database Datafiles
  • 3.2.2 Create a Control File for the Standby Database
  • 3.2.3 Create a Parameter File for the Standby Database
  • 3.2.4 Copy Files from the Primary System to the Standby System
  • 3.2.5 Set Up the Environment to Support the Standby Database
  • 3.2.6 Start the Physical Standby Database
  • 3.2.7 Verify the Physical Standby Database Is Performing Properly
• 3.3 Post-Creation Steps

4 Creating a Logical Standby Database

• 4.1 Prerequisite Conditions for Creating a Logical Standby Database
  • 4.1.1 Determine Support for Data Types and Storage Attributes for Tables
  • 4.1.2 Ensure Table Rows in the Primary Database Can Be Uniquely Identified
• 4.2 Step-by-Step Instructions for Creating a Logical Standby Database
  • 4.2.1 Create a Physical Standby Database
  • 4.2.2 Stop Redo Apply on the Physical Standby Database
  • 4.2.3 Prepare the Primary Database to Support a Logical Standby Database
    • 4.2.3.1 Prepare the Primary Database for Role Transitions
    • 4.2.3.2 Build a Dictionary in the Redo Data
  • 4.2.4 Transition to a Logical Standby Database
    • 4.2.4.1 Convert to a Logical Standby Database
    • 4.2.4.2 Adjust Initialization Parameters for the Logical Standby Database
  • 4.2.5 Open the Logical Standby Database
  • 4.2.6 Verify the Logical Standby Database Is Performing Properly
• 4.3 Post-Creation Steps

5 Data Guard Protection Modes

• 5.1 Data Guard Protection Modes
• 5.2 Setting the Data Protection Mode of a Primary Database

6 Redo Transport Services

• 6.1 Introduction to Redo Transport Services
• 6.2 Configuring Redo Transport Services
  • 6.2.1 Redo Transport Security
    • 6.2.1.1 Redo Transport Authentication Using SSL
    • 6.2.1.2 Redo Transport Authentication Using a Password File
  • 6.2.2 Configuring an Oracle Database to Send Redo Data
    • 6.2.2.1 Viewing Attributes With V$ARCHIVE_DEST
  • 6.2.3 Configuring an Oracle Database to Receive Redo Data
    • 6.2.3.1 Creating and Managing a Standby Redo Log
    • 6.2.3.2 Configuring Standby Redo Log Archival
    • 6.2.3.3 Cases Where Redo Is Written Directly To an Archived Redo Log File
• 6.3 Cascaded Redo Transport Destinations
  • 6.3.1 Configuring a Cascaded Destination
  • 6.3.2 Data Protection Considerations
  • 6.3.3 Cascading Scenarios
    • 6.3.3.1 Cascading to a Physical Standby
    • 6.3.3.2 Cascading to Multiple Physical Standbys
• 6.4 Monitoring Redo Transport Services
  • 6.4.1 Monitoring Redo Transport Status
  • 6.4.2 Monitoring Synchronous Redo Transport Response Time
  • 6.4.3 Redo Gap Detection and Resolution
    • 6.4.3.1 Manual Gap Resolution
  • 6.4.4 Redo Transport Services Wait Events
• 6.5 Tuning Redo Transport

7 Apply Services

• 7.1 Introduction to Apply Services
• 7.2 Apply Services Configuration Options
  • 7.2.1 Using Real-Time Apply to Apply Redo Data Immediately
  • 7.2.2 Specifying a Time Delay for the Application of Archived Redo Log Files
    • 7.2.2.1 Using Flashback Database as an Alternative to Setting a Time Delay
• 7.3 Applying Redo Data to Physical Standby Databases
  • 7.3.1 Starting Redo Apply
  • 7.3.2 Stopping Redo Apply
  • 7.3.3 Monitoring Redo Apply on Physical Standby Databases
• 7.4 Applying Redo Data to Logical Standby Databases
  • 7.4.1 Starting SQL Apply
  • 7.4.2 Stopping SQL Apply on a Logical Standby Database
  • 7.4.3 Monitoring SQL Apply on Logical Standby Databases

8 Role Transitions

• 8.1 Introduction to Role Transitions
  • 8.1.1 Preparing for a Role Transition
  • 8.1.2 Choosing a Target Standby Database for a Role Transition
  • 8.1.3 Switchovers
  • 8.1.4 Failovers
  • 8.1.5 Role Transition Triggers
• 8.2 Role Transitions Involving Physical Standby Databases
  • 8.2.1 Performing a Switchover to a Physical Standby Database
  • 8.2.2 Performing a Failover to a Physical Standby Database
• 8.3 Role Transitions Involving Logical Standby Databases
  • 8.3.1 Performing a Switchover to a Logical Standby Database
  • 8.3.2 Performing a Failover to a Logical Standby Database
• 8.4 Using Flashback Database After a Role Transition
  • 8.4.1 Using Flashback Database After a Switchover
  • 8.4.2 Using Flashback Database After a Failover

9 Managing Physical and Snapshot Standby Databases

• 9.1 Starting Up and Shutting Down a Physical Standby Database
  • 9.1.1 Starting Up a Physical Standby Database
  • 9.1.2 Shutting Down a Physical Standby Database
• 9.2 Opening a Physical Standby Database
  • 9.2.1 Real-time query
    • 9.2.1.1 Monitoring Apply Lag in a Real-time Query Environment
    • 9.2.1.2 Configuring Apply Lag Tolerance in a Real-time Query Environment
    • 9.2.1.3 Forcing Redo Apply Synchronization in a Real-time Query Environment
    • 9.2.1.4 Real-time Query Restrictions
    • 9.2.1.5 Automatic Repair of Corrupt Data Blocks
    • 9.2.1.6 Manual Repair of Corrupt Data Blocks
    • 9.2.1.7 Tuning Queries on a Physical Standby Database
    • 9.2.1.8 Adding Temp Files to a Physical Standby Database
• 9.3 Primary Database Changes That Require Manual Intervention at a Physical Standby
  • 9.3.1 Adding a Datafile or Creating a Tablespace
    • 9.3.1.1 Using the STANDBY_FILE_MANAGEMENT Parameter with Raw Devices
    • 9.3.1.2 Recovering from Errors
  • 9.3.2 Dropping Tablespaces and Deleting Datafiles
    • 9.3.2.1 Using DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
  • 9.3.3 Using Transportable Tablespaces with a Physical Standby Database
  • 9.3.4 Renaming a Datafile in the Primary Database
  • 9.3.5 Add or Drop a Redo Log File Group
  • 9.3.6 NOLOGGING or Unrecoverable Operations
  • 9.3.7 Refresh the Password File
  • 9.3.8 Reset the TDE Master Encryption Key
• 9.4 Recovering Through the OPEN RESETLOGS Statement
• 9.5 Monitoring Primary, Physical Standby, and Snapshot Standby Databases
  • 9.5.1 Using Views to Monitor Primary, Physical, and Snapshot Standby Databases
    • 9.5.1.1 V$DATABASE
    • 9.5.1.2 V$MANAGED_STANDBY
    • 9.5.1.3 V$ARCHIVED_LOG
    • 9.5.1.4 V$LOG_HISTORY
    • 9.5.1.5 V$DATAGUARD_STATUS
    • 9.5.1.6 V$ARCHIVE_DEST
• 9.6 Tuning Redo Apply
• 9.7 Managing a Snapshot Standby Database
  • 9.7.1 Converting a Physical Standby Database into a Snapshot Standby Database
  • 9.7.2 Using a Snapshot Standby Database
  • 9.7.3 Converting a Snapshot Standby Database into a Physical Standby Database

10 Managing a Logical Standby Database

• 10.1 Overview of the SQL Apply Architecture
  • 10.1.1 Various Considerations for SQL Apply
    • 10.1.1.1 Transaction Size Considerations
    • 10.1.1.2 Pageout Considerations
    • 10.1.1.3 Restart Considerations
    • 10.1.1.4 DML Apply Considerations
    • 10.1.1.5 DDL Apply Considerations
    • 10.1.1.6 Password Verification Functions
• 10.2 Controlling User Access to Tables in a Logical Standby Database
• 10.3 Views Related to Managing and Monitoring a Logical Standby Database
  • 10.3.1 DBA_LOGSTDBY_EVENTS View
  • 10.3.2 DBA_LOGSTDBY_LOG View
  • 10.3.3 V$DATAGUARD_STATS View
  • 10.3.4 V$LOGSTDBY_PROCESS View
  • 10.3.5 V$LOGSTDBY_PROGRESS View
  • 10.3.6 V$LOGSTDBY_STATE View
  • 10.3.7 V$LOGSTDBY_STATS View
• 10.4 Monitoring a Logical Standby Database
  • 10.4.1 Monitoring SQL Apply Progress
  • 10.4.2 Automatic Deletion of Log Files
• 10.5 Customizing a Logical Standby Database
  • 10.5.1 Customizing Logging of Events in the DBA_LOGSTDBY_EVENTS View
  • 10.5.2 Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects
  • 10.5.3 Setting up a Skip Handler for a DDL Statement
  • 10.5.4 Modifying a Logical Standby Database
    • 10.5.4.1 Performing DDL on a Logical Standby Database
    • 10.5.4.2 Modifying Tables That Are Not Maintained by SQL Apply
  • 10.5.5 Adding or Re-Creating Tables On a Logical Standby Database
• 10.6 Managing Specific Workloads In the Context of a Logical Standby Database
  • 10.6.1 Importing a Transportable Tablespace to the Primary Database
  • 10.6.2 Using Materialized Views
  • 10.6.3 How Triggers and Constraints Are Handled on a Logical Standby Database
  • 10.6.4 Using Triggers to Replicate Unsupported Tables
  • 10.6.5 Recovering Through the Point-in-Time Recovery Performed at the Primary
  • 10.6.6 Running an Oracle Streams Capture Process on a Logical Standby Database
• 10.7 Tuning a Logical Standby Database
  • 10.7.1 Create a Primary Key RELY Constraint
  • 10.7.2 Gather Statistics for the Cost-Based Optimizer
  • 10.7.3 Adjust the Number of Processes
    • 10.7.3.1 Adjusting the Number of APPLIER Processes
    • 10.7.3.2 Adjusting the Number of PREPARER Processes
  • 10.7.4 Adjust the Memory Used for LCR Cache
  • 10.7.5 Adjust How Transactions are Applied On the Logical Standby Database
• 10.8 Backup and Recovery in the Context of a Logical Standby Database

11 Using RMAN to Back Up and Restore Files

• 11.1 About RMAN File Management in a Data Guard Configuration
  • 11.1.1 Interchangeability of Backups in a Data Guard Environment
  • 11.1.2 Association of Backups in a Data Guard Environment
  • 11.1.3 Accessibility of Backups in a Data Guard Environment
• 11.2 About RMAN Configuration in a Data Guard Environment
• 11.3 Recommended RMAN and Oracle Database Configurations
  • 11.3.1 Oracle Database Configurations on Primary and Standby Databases
  • 11.3.2 RMAN Configurations at the Primary Database
  • 11.3.3 RMAN Configurations at a Standby Database Where Backups are Performed
  • 11.3.4 RMAN Configurations at a Standby Where Backups Are Not Performed
• 11.4 Backup Procedures
  • 11.4.1 Using Disk as Cache for Tape Backups
    • 11.4.1.1 Commands for Daily Tape Backups Using Disk as Cache
    • 11.4.1.2 Commands for Weekly Tape Backups Using Disk as Cache
  • 11.4.2 Performing Backups Directly to Tape
    • 11.4.2.1 Commands for Daily Backups Directly to Tape
    • 11.4.2.2 Commands for Weekly Backups Directly to Tape
• 11.5 Registering and Unregistering Databases in a Data Guard Environment
• 11.6 Reporting in a Data Guard Environment
• 11.7 Performing Backup Maintenance in a Data Guard Environment
  • 11.7.1 Changing Metadata in the Recovery Catalog
  • 11.7.2 Deleting Archived Logs or Backups
  • 11.7.3 Validating Recovery Catalog Metadata
• 11.8 Recovery Scenarios in a Data Guard Environment
  • 11.8.1 Recovery from Loss of Datafiles on the Primary Database
  • 11.8.2 Recovery from Loss of Datafiles on the Standby Database
  • 11.8.3 Recovery from Loss of a Standby Control File
  • 11.8.4 Recovery from Loss of the Primary Control File
  • 11.8.5 Recovery from Loss of an Online Redo Log File
  • 11.8.6 Incomplete Recovery of the Primary Database
• 11.9 Additional Backup Situations
  • 11.9.1 Standby Databases Too Geographically Distant to Share Backups
  • 11.9.2 Standby Database Does Not Contain Datafiles, Used as a FAL Server
  • 11.9.3 Standby Database File Names Are Different From Primary Database
• 11.10 Using RMAN Incremental Backups to Roll Forward a Physical Standby Database
  • 11.10.1 Steps for Using RMAN Incremental Backups

12 Using SQL Apply to Upgrade the Oracle Database

• 12.1 Benefits of a Rolling Upgrade Using SQL Apply
• 12.2 Requirements to Perform a Rolling Upgrade Using SQL Apply
• 12.3 Figures and Conventions Used in the Upgrade Instructions
• 12.4 Performing a Rolling Upgrade By Creating a New Logical Standby Database
• 12.5 Performing a Rolling Upgrade With an Existing Logical Standby Database
• 12.6 Performing a Rolling Upgrade With an Existing Physical Standby Database

13 Data Guard Scenarios

• 13.1 Configuring Logical Standby Databases After a Failover
  • 13.1.1 When the New Primary Database Was Formerly a Physical Standby Database
  • 13.1.2 When the New Primary Database Was Formerly a Logical Standby Database
• 13.2 Converting a Failed Primary Into a Standby Database Using Flashback Database
  • 13.2.1 Flashing Back a Failed Primary Database into a Physical Standby Database
  • 13.2.2 Flashing Back a Failed Primary Database into a Logical Standby Database
  • 13.2.3 Flashing Back a Logical Standby Database to a Specific Applied SCN
• 13.3 Using Flashback Database After Issuing an Open Resetlogs Statement
  • 13.3.1 Flashing Back a Physical Standby Database to a Specific Point-in-Time
  • 13.3.2 Flashing Back a Logical Standby Database to a Specific Point-in-Time
• 13.4 Recovering After the NOLOGGING Clause Is Specified
  • 13.4.1 Recovery Steps for Logical Standby Databases
  • 13.4.2 Recovery Steps for Physical Standby Databases
  • 13.4.3 Determining If a Backup Is Required After Unrecoverable Operations
• 13.5 Creating a Standby Database That Uses OMF or Oracle ASM
• 13.6 Recovering From Lost-Write Errors on a Primary Database
• 13.7 Converting a Failed Primary into a Standby Database Using RMAN Backups
  • 13.7.1 Converting a Failed Primary into a Physical Standby Using RMAN Backups
  • 13.7.2 Converting a Failed Primary into a Logical Standby Using RMAN Backups
• 13.8 Changing the Character Set of a Primary Without Re-Creating Physical Standbys

Part II Reference

14 Initialization Parameters

15 LOG_ARCHIVE_DEST_n Parameter Attributes

• AFFIRM and NOAFFIRM
• ALTERNATE
• COMPRESSION
• DB_UNIQUE_NAME
• DELAY
• LOCATION and SERVICE
• MANDATORY
• MAX_CONNECTIONS
• MAX_FAILURE
• NET_TIMEOUT
• NOREGISTER
• REOPEN
• SYNC and ASYNC
• TEMPLATE
• VALID_FOR

16 SQL Statements Relevant to Data Guard

• 16.1 ALTER DATABASE Statements
• 16.2 ALTER SESSION Statements
• 16.3 ALTER SYSTEM Statements

17 Views Relevant to Oracle Data Guard

Part III Appendixes

A Troubleshooting Data Guard

• A.1 Common Problems
  • A.1.1 Renaming Datafiles with the ALTER DATABASE Statement
  • A.1.2 Standby Database Does Not Receive Redo Data from the Primary Database
  • A.1.3 You Cannot Mount the Physical Standby Database
• A.2 Log File Destination Failures
• A.3 Handling Logical Standby Database Failures
• A.4 Problems Switching Over to a Physical Standby Database
  • A.4.1 Switchover Fails Because Redo Data Was Not Transmitted
  • A.4.2 Switchover Fails Because SQL Sessions Are Still Active
  • A.4.3 Switchover Fails with the ORA-01102 Error
  • A.4.4 Redo Data Is Not Applied After Switchover
  • A.4.5 Roll Back After Unsuccessful Switchover and Start Over
• A.5 Problems Switching Over to a Logical Standby Database
  • A.5.1 Failures During the Prepare Phase of a Switchover Operation
    • A.5.1.1 Failure While Preparing the Primary Database
    • A.5.1.2 Failure While Preparing the Logical Standby Database
  • A.5.2 Failures During the Commit Phase of a Switchover Operation
    • A.5.2.1 Failure to Convert the Original Primary Database
    • A.5.2.2 Failure to Convert the Target Logical Standby Database
• A.6 What to Do If SQL Apply Stops
• A.7 Network Tuning for Redo Data Transmission
• A.8 Slow Disk Performance on Standby Databases
• A.9 Log Files Must Match to Avoid Primary Database Shutdown
• A.10 Troubleshooting a Logical Standby Database
  • A.10.1 Recovering from Errors
    • A.10.1.1 DDL Transactions Containing File Specifications
    • A.10.1.2 Recovering from DML Failures
  • A.10.2 Troubleshooting SQL*Loader Sessions
  • A.10.3 Troubleshooting Long-Running Transactions
  • A.10.4 Troubleshooting ORA-1403 Errors with Flashback Transactions

B Upgrading and Downgrading Databases in a Data Guard Configuration

• B.1 Before You Upgrade the Oracle Database Software
• B.2 Upgrading Oracle Database with a Physical Standby Database in Place
• B.3 Upgrading Oracle Database with a Logical Standby Database in Place
• B.4 Modifying the COMPATIBLE Initialization Parameter After Upgrading
• B.5 Downgrading Oracle Database with No Logical Standby in Place
• B.6 Downgrading Oracle Database with a Logical Standby in Place

C Data Type and DDL Support on a Logical Standby Database

• C.1 Datatype Considerations
  • C.1.1 Supported Datatypes in a Logical Standby Database
    • C.1.1.1 Compatibility Requirements
  • C.1.2 Unsupported Datatypes in a Logical Standby Database
• C.2 Support for Transparent Data Encryption (TDE)
• C.3 Support for Tablespace Encryption
• C.4 Support For Row-level Security and Fine-Grained Auditing
  • C.4.1 Row-level Security
  • C.4.2 Fine-Grained Auditing
  • C.4.3 Skipping and Enabling PL/SQL Replication
• C.5 Oracle Label Security
• C.6 Oracle E-Business Suite
• C.7 Supported Table Storage Types
• C.8 Unsupported Table Storage Types
• C.9 PL/SQL Supplied Packages Considerations
  • C.9.1 Supported PL/SQL Supplied Packages
  • C.9.2 Unsupported PL/SQL Supplied Packages
  • C.9.3 Handling XML and XDB PL/SQL Packages in Logical Standby
    • C.9.3.1 The DBMS_XMLSCHEMA Schema
    • C.9.3.2 The DBMS_XMLINDEX Package
    • C.9.3.3 Dealing With Unsupported PL/SQL Procedures
    • C.9.3.4 Manually Compensating for Unsupported PL/SQL
    • C.9.3.5 Proactively Compensating for Unsupported PL/SQL
    • C.9.3.6 Compensating for Ordering Sensitive Unsupported PL/SQL
• C.10 Unsupported Tables
• C.11 Skipped SQL Statements on a Logical Standby Database
• C.12 DDL Statements Supported by a Logical Standby Database
  • C.12.1 DDL Statements that Use DBLINKS
  • C.12.2 Replication of AUD$ and FGA_LOG$ on Logical Standbys
• C.13 Distributed transactions and XA Support
• C.14 Support for SecureFiles LOBs
• C.15 Character Set Considerations

D Data Guard and Oracle Real Application Clusters

• D.1 Configuring Standby Databases in an Oracle RAC Environment
  • D.1.1 Setting Up a Multi-Instance Primary with a Single-Instance Standby
  • D.1.2 Setting Up Oracle RAC Primary and Standby Databases
    • D.1.2.1 Configuring an Oracle RAC Standby Database to Receive Redo Data
    • D.1.2.2 Configuring an Oracle RAC Primary Database to Send Redo Data
• D.2 Configuration Considerations in an Oracle RAC Environment
  • D.2.1 Format for Archived Redo Log Filenames
  • D.2.2 Data Protection Modes
  • D.2.3 Role Transitions
    • D.2.3.1 Switchovers
• D.3 Troubleshooting
  • D.3.1 Switchover Fails in an Oracle RAC Configuration

E Creating a Standby Database with Recovery Manager

• E.1 Prerequisites
• E.2 Overview of Standby Database Creation with RMAN
  • E.2.1 Purpose of Standby Database Creation with RMAN
  • E.2.2 Basic Concepts of Standby Creation with RMAN
    • E.2.2.1 Active Database and Backup-Based Duplication
    • E.2.2.2 DB_UNIQUE_NAME Values in an RMAN Environment
    • E.2.2.3 Recovery of a Standby Database
    • E.2.2.4 Standby Database Redo Log Files
    • E.2.2.5 Password Files for the Standby Database
• E.3 Using the DUPLICATE Command to Create a Standby Database
  • E.3.1 Creating a Standby Database with Active Database Duplication
  • E.3.2 Creating a Standby Database with Backup-Based Duplication

F Setting Archive Tracing

• F.1 Setting the LOG_ARCHIVE_TRACE Initialization Parameter
• F.2 Choosing an Integer Value

Index