Creating a Logical Standby Database
Step 2 Add a disabled primary-key RELY constraint
5.2 Where to Send Redo Data
This section contains the following topics:
■ Destination Types
■ How to Send Redo Data
■ Setting Up Flash Recovery Areas
5.2.1 Destination Types
There are several types of destinations supported by redo transport services:
■ Oracle Data Guard standby databases
Standby database destinations can be either physical standby databases or logical standby databases. Section 1.1.2 discusses standby databases.
■ Archived redo log repository
This type of destination allows off-site archiving of redo data. An archive log repository is created by using a physical standby control file, starting the instance, and mounting the database. This database contains no datafiles and cannot be used for switchover or failover. This alternative is useful as a way of holding archived redo log files for a short period of time, perhaps a day, after which the log files can then be deleted. This avoids most of the storage and processing expense of another fully configured standby database.
Oracle recommends using an archived redo log repository for temporary storage of archived redo log files. This can be accomplished by configuring the repository destination for archiver-based transport (using the ARCH attribute on LOG_
ARCHIVE_DEST_n parameter) in a Data Guard configuration running in maximum performance mode. For a no data loss environment, you should use a fully configured standby database using the LGWR, SYNC, and AFFIRM transport settings in a Data Guard configuration and running in either maximum protection mode or maximum availability mode.
■ Oracle Streams real-time downstream capture database
Oracle Net Primary
Database
Online Redo Log Files
Archived Redo Log Files Redo Generation
Redo Transport Services
Standby Redo Log Files
Arcxhived Redo Log Files
Standby Database Log Apply Services
Where to Send Redo Data
This destination type allows Oracle Streams to configure a capture process on a remote downstream database. The Streams downstream capture process uses redo transport services to transfer redo data to the downstream database where a Streams capture process captures changes in the standby redo log files and archived redo log files on the remote destination. See Oracle Streams Concepts and Administration for more information.
■ Oracle Change Data Capture staging database
This destination type supports a Change Data Capture Asynchronous AutoLog configuration remotely at a staging database. Redo data is copied from the source database to the staging database using redo transport services. The Change Data Capture configuration captures changes from the redo data. See Oracle Database Data Warehousing Guide for more information.
For discussion purposes, this guide refers to the production database as a primary database and to archival destinations as standby databases (as defined in Section 1.1).
If you are using Oracle Change Data Capture, substitute the terms source and staging database for primary and standby database, respectively. If you are using Oracle Streams, substitute the terms source and downstream capture database for primary and standby database, respectively.
5.2.2 Configuring Destinations with the LOG_ARCHIVE_DEST_n Parameter
The LOG_ARCHIVE_DEST_n initialization parameter defines up to ten (where n = 1, 2, 3, ... 10) destinations, each of which must specify either the LOCATION or the SERVICE attribute to specify where to archive the redo data.
The LOCATION and SERVICE attributes describe either a local disk location or an Oracle Net service name that represents a standby destination to which redo transport services will transmit redo data. Specifying remote destinations with the SERVICE attribute allows Data Guard to maintain a transactionally consistent remote copy of the primary database for disaster recovery.
For every LOG_ARCHIVE_DEST_n initialization parameter that you define, specify a corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_
DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently on (enabled) or off (disabled).
Table 5–1 describes the LOG_ARCHIVE_DEST_STATE_n parameter attributes.
Example 5–1 provides an example of one destination with the LOCATION attribute.
Table 5–1 LOG_ARCHIVE_DEST_STATE_n Initialization Parameter Attributes
Attribute Description
ENABLE Redo transport services can transmit redo data to this destination. This is the default.
DEFER Redo transport services will not transmit redo data to this destination. This is a valid but unused destination.
ALTERNATE This destination is not enabled, but it will become enabled if communication to its associated destination fails.
RESET Functions the same as DEFER, but clears any error messages for the destination if it had previously failed.
Where to Send Redo Data
Figure 5–2 shows what this simple configuration, consisting of a single local
destination, would look like. The log writer process writes redo data to the online redo log file. As each online redo log file is filled, a log switch occurs and an ARCn process archives the filled online redo log file to an archived redo log file. The filled online redo log file is now available for reuse.
Figure 5–2 Primary Database Archiving When There Is No Standby Database
It is important to note that the configuration shown in Figure 5–2 does not include a standby database and thus does not provide disaster-recovery protection. To make this simple configuration into a Data Guard configuration that provides disaster recovery, add a standby database at a remote destination by specifying the SERVICE attribute.
Example 5–2 shows the initialization parameters that enable redo transport services to archive the online redo log on the local destination chicago and transmit redo data to a remote standby database with the Oracle Net service name boston. The example takes the default values for all of the other LOG_ARCHIVE_DEST_n attributes:
Example 5–2 Specifying a Remote Archiving Destination LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/'
LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_2='SERVICE=boston' LOG_ARCHIVE_DEST_STATE_2=ENABLE
These initialization parameters set up a Data Guard configuration that uses archiver (ARCn) processes to archive to both the local and remote destinations. This
configuration provides the maximum performance level of data protection.
Primary Database Transactions
LGWR
ARC0 Online Redo Log Files
Archived Redo Log Files
Where to Send Redo Data
Although you can create a Data Guard configuration by specifying only the LOCATION or the SERVICE attributes on the LOG_ARCHIVE_DEST_n parameter, you can
optionally specify more attributes to further define each destination’s behavior.
Chapter 14 provides reference information for all of the LOG_ARCHIVE_DEST_n parameter attributes.
5.2.2.1 Changing Destination Attributes
You can dynamically update most of the attribute values of the LOG_ARCHIVE_DEST_
n and the LOG_ARCHIVE_DEST_STATE_n parameters using the ALTER SYSTEM SET statement.
The modifications take effect after the next log switch on the primary database. For example, to defer redo transport services from transmitting redo data to the remote standby database named boston, issue the following statements on the primary database:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=boston 2> VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
5.2.2.2 Viewing Attribute with V$ARCHIVE_DEST
Query the V$ARCHIVE_DEST view to see current settings of the LOG_ARCHIVE_
DEST_n initialization parameter.
5.2.3 Setting Up Flash Recovery Areas
The Oracle database enables you to configure a disk area called a flash recovery area that is a directory or Oracle Storage Manager disk group that serves as the default storage area for files related to recovery.
To configure a flash recovery area, use the DB_RECOVERY_FILE_DEST initialization parameter. LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_
DEST (meaning that archived redo log files will be sent to the flash recovery area) if you create a recovery area and do not set any other local archiving destinations. (See Oracle Database Backup and Recovery Basics to configure the flash recovery area and Oracle Database Administrator's Guide for more information about Oracle Storage Manager and Oracle Managed Files.)
This section contains the following topics:
■ Using the LOG_ARCHIVE_DEST_10 Destination
■ Using Other LOG_ARCHIVE_DEST_n Destinations
■ Using the STANDBY_ARCHIVE_DEST Destination
■ Sharing a Flash Recovery Area Between Primary and Standby Databases Note: The filenames for archived redo log files stored in a flash recovery area are generated automatically by Oracle Managed Files (OMF); the filenames are not based on the format specified by the LOG_ARCHIVE_FORMAT initialization parameter.
Where to Send Redo Data
See Oracle Database Backup and Recovery Basics to configure flash recovery areas and Section 10.3.4 for information about setting up a deletion policy for archived redo log files in flash recovery areas.
5.2.3.1 Using the LOG_ARCHIVE_DEST_10 Destination
If a flash recovery area has been configured and no local destinations are defined, Data Guard implicitly uses the LOG_ARCHIVE_DEST_10 destination as the flash recovery area.
When the LOG_ARCHIVE_DEST_10 destination is used, Data Guard automatically uses the default values for all of the LOG_ARCHIVE_DEST_10 parameter attributes. To override the defaults, you can dynamically set the values for most of the attributes by explicitly specifying the LOG_ARCHIVE_DEST_10 parameter. For example, the following ALTER SYSTEM SET statement specifies several attributes on the LOG_
ARCHIVE_DEST_10 initialization parameter:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
When setting LOG_ARCHIVE_DEST_n attributes, the TEMPLATE attribute of a LOG_
ARCHIVE_DEST_n parameter will override all other specifications for the flash recovery area. If the TEMPLATE attribute is specified for a remote destination and that destination archives redo data to a flash recovery area, the archived redo log file will use the directory and file name specified by the TEMPLATE attribute.
5.2.3.2 Using Other LOG_ARCHIVE_DEST_n Destinations
You can explicitly set up one or more other LOG_ARCHIVE_DEST_n destinations to point to a flash recovery area. For example, you can optionally:
■ Configure destinations other than LOG_ARCHIVE_DEST_10
For example, an existing Data Guard configuration may have already used the LOG_ARCHIVE_DEST_10 destination for another purpose, or you may want to release the LOG_ARCHIVE_DEST_10 destination for other uses.
To configure another archival destination to point to the flash recovery area, you must specify the LOCATION=USE_DB_RECOVERY_FILE_DEST attribute to define the new destination. For example:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST ARCH MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
The implicit setting (for LOG_ARCHIVE_DEST_10 to use the flash recovery area) will be cleared.
■ Configure destinations in addition to LOG_ARCHIVE_DEST_10 destination for use after a role transition
For example, you can configure one destination to be valid for standby redo log archival when the database operates in the standby role and another destination to be valid for online redo log archival when the database operates in the primary role.
To configure a LOG_ARCHIVE_DEST_n destination in addition to LOG_ARCHIVE_
DEST_10, you must explicitly specify both destinations:
LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST ARCH MANDATORY REOPEN=5 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)'
LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST ARCH MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
How to Send Redo Data
5.2.3.3 Using the STANDBY_ARCHIVE_DEST Destination
On a physical standby database, you can define the STANDBY_ARCHIVE_DEST parameter to point to the flash recovery area. For example:
STANDBY_ARCHIVE_DEST='LOCATION=USE_DB_RECOVERY_FILE_DEST'
5.2.3.4 Sharing a Flash Recovery Area Between Primary and Standby Databases
You can share a flash recovery area between databases provided each database that shares the flash recovery area has a unique database name, specified with the DB_
UNIQUE_NAME initialization parameter.
The following examples show how to specify initialization parameters on the primary and standby databases that will share a flash recovery area in the /arch/oradata location. Although the DB_UNIQUE_NAME parameter is not specified in Example 5–3, it defaults to PAYROLL, which is the name specified for the DB_NAME initialization parameter.
Example 5–3 Primary Database Initialization Parameters for a Shared Recovery Area DB_NAME=PAYROLL
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' DB_RECOVERY_FILE_DEST='/arch/oradata'
DB_RECOVERY_FILE_DEST_SIZE=20G
Example 5–4 Standby Database Initialization Parameters for a Shared Recovery Area DB_NAME=PAYROLL
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' STANDBY_ARCHIVE_DEST='LOCATION=USE_DB_RECOVERY_FILE_DEST' DB_RECOVERY_FILE_DEST='/arch/oradata'
DB_RECOVERY_FILE_DEST_SIZE=5G
See Oracle Database Backup and Recovery Advanced User's Guide for more information about sharing a flash recovery area among multiple databases.