Skip Headers
Oracle® Data Guard Concepts and Administration
10
g
Release 2 (10.2)
Part Number B14239-01
Home
Book List
Index
Master Index
Feedback
Next
View PDF
Contents
List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documentation
Conventions
What's New in Oracle Data Guard?
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
Log Apply Services
1.2.3
Role Transitions
1.3
Data Guard Broker
1.3.1
Using Oracle Enterprise Manager
1.3.2
Using the Data Guard Command-Line Interface
1.4
Data Guard Protection Modes
1.5
Data Guard and Complementary Technologies
1.6
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.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
2.5
Online Redo Logs, Archived Redo Logs, and Standby Redo Logs
2.5.1
Online Redo Logs and Archived Redo Logs
2.5.2
Standby Redo Logs
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
Create a Password File
3.1.3
Configure a Standby Redo Log
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 Data Files
3.2.2
Create a Control File for the Standby Database
3.2.3
Prepare an Initialization 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
Create a New Password File
4.2.4.3
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
Redo Transport Services
5.1
Introduction to Redo Transport Services
5.2
Where to Send Redo Data
5.2.1
Destination Types
5.2.2
Configuring Destinations with the LOG_ARCHIVE_DEST_n Parameter
5.2.2.1
Changing Destination Attributes
5.2.2.2
Viewing Attribute with V$ARCHIVE_DEST
5.2.3
Setting Up Flash Recovery Areas
5.2.3.1
Using the LOG_ARCHIVE_DEST_10 Destination
5.2.3.2
Using Other LOG_ARCHIVE_DEST_n Destinations
5.2.3.3
Using the STANDBY_ARCHIVE_DEST Destination
5.2.3.4
Sharing a Flash Recovery Area Between Primary and Standby Databases
5.3
How to Send Redo Data
5.3.1
Using Archiver Processes (ARCn) to Archive Redo Data
5.3.1.1
Initialization Parameters That Control ARCn Archival Behavior
5.3.1.2
ARCn Archival Processing
5.3.2
Using the Log Writer Process (LGWR) to Archive Redo Data
5.3.2.1
LOG_ARCHIVE_DEST_n Attributes for LGWR Archival Processing
5.3.2.2
LGWR SYNC Archival Processing
5.3.2.3
LGWR ASYNC Archival Processing
5.3.3
Providing for Secure Redo Data Transmission
5.4
When Redo Data Should Be Sent
5.4.1
Specifying Role-Based Destinations with the VALID_FOR Attribute
5.4.2
Specify Unique Names for Primary and Standby Databases
5.5
What to Do If Errors Occur
5.5.1
Retrying the Archival Operation
5.5.2
Using an Alternate Destination
5.5.3
Controlling the Number of Retry Attempts
5.6
Setting Up a Data Protection Mode
5.6.1
Choosing a Data Protection Mode
5.6.1.1
Maximum Protection Mode
5.6.1.2
Maximum Availability Mode
5.6.1.3
Maximum Performance Mode
5.6.2
Setting the Data Protection Mode of a Data Guard Configuration
5.7
Managing Log Files
5.7.1
Specifying Alternate Directory Locations for Archived Redo Log Files
5.7.2
Reusing Online Redo Log Files
5.7.3
Managing Standby Redo Log Files
5.7.3.1
Determining If a Standby Redo Log File Group Configuration Is Adequate
5.7.3.2
Adding Standby Redo Log Members to an Existing Group
5.7.4
Planning for Growth and Reuse of the Control Files
5.7.4.1
Sizing the Disk Volumes that Contain the Control Files
5.7.4.2
Specifying the Reuse of Records in the Control File
5.7.5
Sharing a Log File Destination Among Multiple Standby Databases
5.8
Managing Archive Gaps
5.8.1
When Is an Archive Gap Discovered?
5.8.2
How Is a Gap Resolved?
5.8.3
Using the Fetch Archive Log (FAL) to Resolve Archive Gaps
5.8.4
Manually Determining and Resolving Archive Gaps
5.9
Verification
5.9.1
Monitoring Log File Archival Information
5.9.2
Monitoring the Performance of Redo Transport Services
5.9.2.1
ARCn Process Wait Events
5.9.2.2
LGWR SYNC Wait Events
5.9.2.3
LGWR ASYNC Wait Events
6
Log Apply Services
6.1
Introduction to Log Apply Services
6.2
Log Apply Services Configuration Options
6.2.1
Using Real-Time Apply to Apply Redo Data Immediately
6.2.2
Specifying a Time Delay for the Application of Archived Redo Log Files
6.2.2.1
Using Flashback Database as an Alternative to Setting a Time Delay
6.3
Applying Redo Data to Physical Standby Databases
6.3.1
Starting Redo Apply
6.3.2
Starting Real-Time Apply
6.3.3
Stopping Log Apply Services
6.3.4
Monitoring Log Apply Services on Physical Standby Databases
6.4
Applying Redo Data to Logical Standby Databases
6.4.1
Starting SQL Apply
6.4.2
Starting Real-time Apply
6.4.3
Stopping Log Apply Services on a Logical Standby Database
6.4.4
Monitoring Log Apply Services on Logical Standby Databases
7
Role Transitions
7.1
Introduction to Role Transitions
7.1.1
Preparing for a Role Transition (Failover or Switchover)
7.1.2
Choosing a Target Standby Database for a Role Transition
7.1.3
Switchovers
7.1.4
Failovers
7.2
Role Transitions Involving Physical Standby Databases
7.2.1
Switchovers Involving a Physical Standby Database
7.2.2
Failovers Involving a Physical Standby Database
7.3
Role Transitions Involving Logical Standby Databases
7.3.1
Switchovers Involving a Logical Standby Database
7.3.2
Failovers Involving a Logical Standby Database
7.4
Using Flashback Database After a Role Transition
7.4.1
Using Flashback Database After a Switchover
7.4.2
Using Flashback Database After a Failover
8
Managing a Physical Standby Database
8.1
Starting Up and Shutting Down a Physical Standby Database
8.1.1
Starting Up a Physical Standby Database
8.1.2
Shutting Down a Physical Standby Database
8.2
Opening a Standby Database for Read-Only or Read/Write Access
8.2.1
Assessing Whether or Not to Open a Standby Database
8.2.2
Opening a Physical Standby Database for Read-Only Access
8.3
Managing Primary Database Events That Affect the Standby Database
8.3.1
Adding a Data File or Creating a Tablespace
8.3.1.1
When STANDBY_FILE_MANAGEMENT Is Set to AUTO
8.3.1.2
When STANDBY_FILE_MANAGEMENT Is Set to MANUAL
8.3.2
Dropping Tablespaces and Deleting Data Files
8.3.2.1
When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL
8.3.2.2
Using DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
8.3.3
Using Transportable Tablespaces with a Physical Standby Database
8.3.4
Renaming a Data File in the Primary Database
8.3.5
Adding or Dropping Online Redo Log Files
8.3.6
NOLOGGING or Unrecoverable Operations
8.4
Recovering Through the OPEN RESETLOGS Statement
8.5
Monitoring the Primary and Standby Databases
8.5.1
Alert Log
8.5.2
Dynamic Performance Views (Fixed Views)
8.5.3
Monitoring Recovery Progress
8.5.3.1
Monitoring the Process Activities
8.5.3.2
Determining the Progress of Redo Apply
8.5.3.3
Determining the Location and Creator of the Archived Redo Log Files
8.5.3.4
Viewing Database Incarnations Before and After OPEN RESETLOGS
8.5.3.5
Viewing the Archived Redo Log History
8.5.3.6
Determining Which Log Files Were Applied to the Standby Database
8.5.3.7
Determining Which Log Files Were Not Received by the Standby Site
8.5.4
Monitoring Log Apply Services on Physical Standby Databases
8.5.4.1
Accessing the V$DATABASE View
8.5.4.2
Accessing the V$MANAGED_STANDBY Fixed View
8.5.4.3
Accessing the V$ARCHIVE_DEST_STATUS Fixed View
8.5.4.4
Accessing the V$ARCHIVED_LOG Fixed View
8.5.4.5
Accessing the V$LOG_HISTORY Fixed View
8.5.4.6
Accessing the V$DATAGUARD_STATUS Fixed View
8.6
Tuning the Log Apply Rate for a Physical Standby Database
9
Managing a Logical Standby Database
9.1
Overview of the SQL Apply Architecture
9.1.1
Various Considerations for SQL Apply
9.1.1.1
Transaction Size Considerations
9.1.1.2
Pageout Considerations
9.1.1.3
Restart Considerations
9.1.1.4
DML Apply Considerations
9.1.1.5
DDL Apply Considerations
9.2
Views Related to Managing and Monitoring a Logical Standby Database
9.2.1
DBA_LOGSTDBY_EVENTS View
9.2.2
DBA_LOGSTDBY_LOG View
9.2.3
V$LOGSTDBY_STATS View
9.2.4
V$LOGSTDBY_PROCESS View
9.2.5
V$LOGSTDBY_PROGRESS View
9.2.6
V$LOGSTDBY_STATE View
9.2.7
V$LOGSTDBY_STATS View
9.3
Monitoring a Logical Standby Database
9.3.1
Monitoring SQL Apply Progress
9.3.2
Automatic Deletion of Log Files
9.4
Customizing a Logical Standby Database
9.4.1
Using Real-Time Apply On the Logical Standby Database
9.4.2
Customizing Logging of Events in the DBA_LOGSTDBY_EVENTS View
9.4.3
Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects
9.4.4
Setting up a Skip Handler for a DDL Statement
9.4.5
Modifying a Logical Standby Database
9.4.5.1
Performing DDL on a Logical Standby Database
9.4.5.2
Modifying Tables That Are Not Maintained by SQL Apply
9.4.6
Adding or Re-Creating Tables On a Logical Standby Database
9.5
Managing Specific Workloads In the Context of a Logical Standby Database
9.5.1
Importing a Transportable Tablespace to the Primary Database
9.5.2
Using Materialized Views
9.5.3
How Triggers and Constraints Are Handled on a Logical Standby Database
9.5.4
Recovering Through the OPEN RESETLOGS Statement
9.6
Tuning a Logical Standby Database
9.6.1
Create a Primary Key RELY Constraint
9.6.2
Gather Statistics for the Cost-Based Optimizer
9.6.3
Adjust the Number of Processes
9.6.3.1
Adjusting the Number of APPLIER Processes
9.6.3.2
Adjusting the Number of PREPARER Processes
9.6.4
Adjust the Memory Used for LCR Cache
9.6.5
Adjust How Transactions are Applied On the Logical Standby Database
10
Using RMAN to Back Up and Restore Files
10.1
Backup Procedure
10.1.1
Using Disk as Cache for Tape Backup
10.1.2
Performing Backups Directly to Tape
10.2
Effect of Switchovers, Failovers, and Control File Creation on Backups
10.2.1
Recovery from Loss of Data Files on the Primary Database
10.2.2
Recovery from Loss of Data Files on the Standby Database
10.2.3
Recovery from the Loss of a Standby Control File
10.2.4
Recovery from the Loss of the Primary Control File
10.2.5
Recovery from the Loss of an Online Redo Log File
10.2.6
Incomplete Recovery of the Database
10.3
Additional Backup Situations
10.3.1
Standby Databases Too Geographically Distant to Share Backups
10.3.2
Standby Database Does Not Contain Data Files, Used as a FAL Server
10.3.3
Standby Database File Names Are Different than Primary Database
10.3.4
Deletion Policy for Archived Redo Log Files In Flash Recovery Areas
10.3.4.1
Reconfiguring the Deletion Policy After a Role Transition
10.3.4.2
Viewing the Current Deletion Policy
11
Using SQL Apply to Upgrade Oracle Database
11.1
Benefits of a Rolling Upgrade Using SQL Apply
11.2
Requirements to Perform a Rolling Upgrade Using SQL Apply
11.3
Figures and Conventions Used in the Upgrade Instructions
11.4
Prepare to Upgrade
11.5
Upgrade the Databases
12
Data Guard Scenarios
12.1
Setting Up and Verifying Archival Destinations
12.1.1
Configuring a Primary Database and a Physical Standby Database
12.1.2
Configuring a Primary Database and a Logical Standby Database
12.1.3
Configuring Both Physical and Logical Standby Databases
12.1.4
Verifying the Current VALID_FOR Attribute Settings for Each Destination
12.2
Choosing the Best Available Standby Database for a Role Transition
12.2.1
Example: Best Physical Standby Database for a Failover
12.2.2
Example: Best Logical Standby Database for a Failover
12.3
Configuring a Logical Standby Database to Support a New Primary Database
12.3.1
When the New Primary Database Was Formerly a Physical Standby Database
12.3.2
When the New Primary Database Was Formerly a Logical Standby Database
12.4
Using Flashback Database After a Failover
12.4.1
Flashing Back a Failed Primary Database into a Physical Standby Database
12.4.2
Flashing Back a Failed Primary Database into a Logical Standby Database
12.4.3
Flashing Back a Logical Standby Database to a Specific Applied SCN
12.5
Using Flashback Database After Issuing an Open Resetlogs Statement
12.5.1
Flashing Back a Physical Standby Database to a Specific Point-in-Time
12.5.2
Flash Back a Logical Standby Database After Flashing Back the Primary
12.6
Using a Physical Standby Database for Read/Write Testing and Reporting
12.7
Using RMAN Incremental Backups to Roll Forward a Physical Standby Database
12.7.1
Physical Standby Database Lags Far Behind the Primary Database
12.7.2
Physical Standby Database Has Nologging Changes On a Subset of Data Files
12.7.3
Physical Standby Database Has Widespread Nologging Changes
12.8
Using a Physical Standby Database with a Time Lag
12.8.1
Establishing a Time Lag on a Physical Standby Database
12.8.2
Failing Over to a Physical Standby Database with a Time Lag
12.8.3
Switching Over to a Physical Standby Database with a Time Lag
12.9
Recovering From a Network Failure
12.10
Recovering After the NOLOGGING Clause Is Specified
12.10.1
Recovery Steps for Logical Standby Databases
12.10.2
Recovery Steps for Physical Standby Databases
12.10.3
Determining If a Backup Is Required After Unrecoverable Operations
12.11
Resolving Archive Gaps Manually
12.11.1
What Causes Archive Gaps?
12.11.1.1
Creation of the Standby Database
12.11.1.2
Shutdown of the Standby Database When the Primary Database Is Open
12.11.1.3
Network Failure Prevents Transmission of Redo
12.11.2
Determining If an Archive Gap Exists
12.11.3
Manually Transmitting Log Files in the Archive Gap to the Standby Site
12.11.4
Manually Applying Log Files in the Archive Gap to the Standby Database
12.12
Creating a Standby Database That Uses OMF or ASM
Part II Reference
13
Initialization Parameters
14
LOG_ARCHIVE_DEST_n Parameter Attributes
AFFIRM and NOAFFIRM
ALTERNATE
ARCH and LGWR
DB_UNIQUE_NAME
DELAY
DEPENDENCY
LOCATION and SERVICE
MANDATORY and OPTIONAL
MAX_CONNECTIONS
MAX_FAILURE
NET_TIMEOUT
NOREGISTER
REOPEN
SYNC and ASYNC
TEMPLATE
VALID_FOR
VERIFY
15
SQL Statements Relevant to Data Guard
15.1
ALTER DATABASE Statements
15.2
ALTER SESSION Statements
16
Views Relevant to Oracle Data Guard
Part III Appendixes
A
Troubleshooting Data Guard
A.1
Common Problems
A.1.1
Standby Archive Destination Is Not Defined Properly
A.1.2
Renaming Data Files with the ALTER DATABASE Statement
A.1.3
Standby Database Does Not Receive Redo Data from the Primary Database
A.1.4
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 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 Because User Sessions Are Still Active
A.4.4
Switchover Fails with the ORA-01102 Error
A.4.5
Redo Data Is Not Applied After Switchover
A.4.6
Roll Back After Unsuccessful Switchover and Start Over
A.5
What to Do If SQL Apply Stops
A.6
Network Tuning for Redo Data Transmission
A.7
Slow Disk Performance on Standby Databases
A.8
Log Files Must Match to Avoid Primary Database Shutdown
A.9
Troubleshooting a Logical Standby Database
A.9.1
Recovering from Errors
A.9.1.1
DDL Transactions Containing File Specifications
A.9.1.2
Recovering from DML Failures
A.9.2
Troubleshooting SQL*Loader Sessions
A.9.3
Troubleshooting Long-Running Transactions
A.9.4
Troubleshooting ORA-1403 Errors with Flashback Transactions
B
Upgrading 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
C
Data Type and DDL Support on a Logical Standby Database
C.1
Data Type Considerations
C.1.1
Supported Data Types in a Logical Standby Database
C.1.2
Unsupported Data Types in a Logical Standby Database
C.2
Storage Type Considerations
C.2.1
Support Storage Types
C.2.2
Unsupported Storage Type
C.3
PL/SQL Supplied Packages Considerations
C.3.1
Supported PL/SQL Supplied Packages
C.3.2
Unsupported PL/SQL Supplied Packages
C.4
Unsupported Tables, Sequences, and Views
C.5
Skipped SQL Statements on a Logical Standby Database
C.6
DDL Statements Supported by a Logical Standby Database
D
Data Guard and Real Application Clusters
D.1
Configuring Standby Databases in a Real Application Clusters Environment
D.1.1
Setting Up a Multi-Instance Primary with a Single-Instance Standby
D.1.2
Setting Up a Multi-Instance Primary with a Multi-Instance Standby
D.2
Configuration Considerations in a Real Application Clusters Environment
D.2.1
Format for Archived Redo Log Filenames
D.2.2
Archive Destination Quotas
D.2.3
Data Protection Modes
D.2.4
Role Transitions
D.2.4.1
Switchovers
D.2.4.2
Failovers
D.3
Troubleshooting
D.3.1
Switchover Fails in a Real Application Clusters Configuration
D.3.2
Avoiding Downtime in Real Application Clusters During a Network Outage
E
Cascaded Destinations
E.1
Configuring Cascaded Destinations
E.1.1
Configuring Cascaded Destinations for Physical Standby Databases
E.1.2
Configuring Cascaded Destinations for Logical Standby Databases
E.2
Role Transitions with Cascaded Destinations
E.2.1
Standby Databases Receiving Redo Data from a Physical Standby Database
E.2.2
Standby Databases Receiving Redo Data from a Logical Standby Database
E.3
Examples of Cascaded Destinations
E.3.1
Local Physical Standby and Cascaded Remote Physical Standby
E.3.2
Local Physical Standby and Cascaded Remote Logical Standby
E.3.3
Local and Remote Physical Standby and Cascaded Local Logical Standby
E.3.4
Consolidated Reporting with Cascaded Logical Standby Destinations
E.3.5
Temporary Use of Cascaded Destinations During Network Upgrades
F
Creating a Standby Database with Recovery Manager
F.1
Preparing to Use RMAN to Create a Standby Database
F.1.1
About Standby Database Preparation Using RMAN
F.1.2
Creating the Standby Control File with RMAN
F.1.3
Naming the Standby Database Data Files When Using RMAN
F.1.4
Naming the Standby Database Log Files When Using RMAN
F.2
Creating a Standby Database with RMAN: Overview
F.2.1
RMAN Standby Creation Without Recovery
F.2.2
RMAN Standby Creation with Recovery
F.3
Setting Up the Standby Database
F.3.1
Setting Up a Standby Database When Files Are Not Oracle Managed Files
F.3.2
Setting Up a Standby Database When All Files Are Oracle Managed Files
F.3.3
Setting Up a Standby Databases When a Subset of Files Are Oracle Managed Files
F.4
Creating a Standby Database with the Same Directory Structure
F.4.1
Creating the Standby Database Without Performing Recovery
F.4.2
Creating the Standby Database and Performing Recovery
F.5
Creating a Standby Database with a Different Directory Structure
F.5.1
Naming Standby Database Files with DB_FILE_NAME_CONVERT
F.5.1.1
Creating the Standby Database Without Performing Recovery
F.5.1.2
Creating the Standby Database and Performing Recovery
F.5.2
Naming Standby Database Files with SET NEWNAME
F.5.2.1
Creating the Standby Database Without Performing Recovery
F.5.2.2
Creating the Standby Database and Performing Recovery
F.5.3
Naming Standby Database Files with CONFIGURE AUXNAME
F.5.3.1
Creating the Standby Database Without Performing Recovery
F.5.3.2
Creating the Standby Database and Performing Recovery
F.6
Creating a Standby Database on the Local Host
F.7
Creating a Standby Database with Image Copies
F.7.1
Overview
F.7.2
When Copies and Data Files Use the Same Names
F.7.3
When Copies and Data Files Use Different Names
F.7.3.1
Creating the Standby Database Without Performing Recovery
F.7.3.2
Creating the Standby Database and Performing Recovery
F.8
Usage Scenario
G
Setting Archive Tracing
G.1
LOG_ARCHIVE_TRACE Initialization Parameter
G.2
Determining the Location of the Trace Files
G.2.1
Setting the LOG_ARCHIVE_TRACE Initialization Parameter
G.2.2
Choosing an Integer Value
Index
