An Oracle Data Guard configuration requires that Oracle redo transport services be configured and monitored.
7.1 Introduction to Redo Transport Services
Redo transport services performs the automated transfer of redo data between members of an Oracle Data Guard configuration.
The following redo transport destinations are supported:
Oracle Data Guard standby databases
This guide describes how to create and manage physical, logical, and snapshot standby databases.
Archive log repository
This destination type is used for temporary offsite storage of archived redo log files. An archive log repository consists of an Oracle database instance and a physical standby control file. An archive log repository does not contain data files, so it cannot support role transitions.
The procedure used to create an archive log repository is identical to the procedure used to create a physical standby database, except for the copying of data files.
Oracle Streams downstream capture databases
See Oracle Streams Concepts and Administration for more information about Oracle Streams downstream capture databases.
Far sync instances
See Far Sync for more information about far sync instances.
Zero Data Loss Recovery Appliance (Recovery Appliance)
Each redo transport destination is individually configured to receive redo data via one of two redo transport modes:
The synchronous redo transport mode transmits redo data synchronously with respect to transaction commitment. A transaction cannot commit until all redo generated by that transaction has been successfully sent to every enabled redo transport destination that uses the synchronous redo transport mode.
Although there is no limit on the distance between a primary database and a
SYNCredo transport destination, transaction commit latency increases as network latency increases between a primary database and a
SYNCredo transport destination.
This transport mode is used by the Maximum Protection and Maximum Availability data protection modes described in Oracle Data Guard Protection Modes .
Synchronous redo transport is not supported for Zero Data Loss Recovery Appliance.
The asynchronous redo transport mode transmits redo data asynchronously with respect to transaction commitment. A transaction can commit without waiting for the redo generated by that transaction to be successfully sent to any redo transport destination that uses the asynchronous redo transport mode.
This transport mode is used by the Maximum Performance data protection mode described in Oracle Data Guard Protection Modes .
7.2 Configuring Redo Transport Services
Oracle databases must be configured before they can send and receive redo data. Part of the configuration process involves setting up redo transport security.
See the following topics:
These topics assume that you have a thorough understanding of the following:
Database administrator authentication
Database initialization parameters
Managing a redo log
Managing archived redo logs
Fast recovery areas
Oracle Net Configuration
7.2.1 Redo Transport Security
Redo transport uses Oracle Net sessions to transport redo data.
These redo transport sessions are authenticated using either the Secure Socket Layer (SSL) protocol or a remote login password file.
18.104.22.168 Redo Transport Authentication Using SSL
Secure Sockets Layer (SSL) is an industry standard protocol for securing network connections.
SSL uses RSA public key cryptography and symmetric key cryptography to provide authentication, encryption, and data integrity. SSL is automatically used for redo transport authentication between two Oracle databases if:
The databases are members of the same Oracle Internet Directory (OID) enterprise domain and that domain allows the use of current user database links.
FAL_SERVERdatabase initialization parameters that correspond to the databases use Oracle Net connect descriptors configured for SSL.
Each database has an Oracle wallet or a supported hardware security module that contains a user certificate with a distinguished name (DN) that matches the DN in the OID entry for the database.
Oracle Database Security Guide for more information about SSL
Oracle Database Enterprise User Security Administrator's Guide for more information about administering enterprise domains
Oracle Label Security Administrator's Guide for information about administering Oracle Internet Directory
22.214.171.124 Redo Transport Authentication Using a Password File
If the SSL authentication requirements are not met, then each database must use a remote login password file.
In an Oracle Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database. That copy is automatically refreshed whenever an administrative privilege (
SYSDBA, and so on) is granted or revoked, and after the password of any user with administrative privileges is changed. The only exception to this is far sync instances. Updated password files must still be manually copied to far sync instances because far sync instances receive redo, but do not apply it. Once the password file is up-to-date at the far sync instance the redo containing the password update at the primary is automatically propagated to any standby databases that are set up to receive redo from that far sync instance. The password file is updated on the standby when the redo is applied.
When a password file is used for redo transport authentication, the password of the user account used for redo transport authentication is compared between the database initiating a redo transport session and the target database. The password must be the same at both databases to create a redo transport session.
By default, the password of the
SYS user is used to authenticate redo transport sessions when a password file is used. The
REDO_TRANSPORT_USER database initialization parameter can be used to select a different user password for redo transport authentication by setting this parameter to the name of any user who has been granted the
SYSOPER privilege. For administrative ease, Oracle recommends that the
REDO_TRANSPORT_USER parameter be set to the same value on the redo source database and at each redo transport destination.
Oracle Database Administrator's Guide for more information creating and maintaining remote login password files
7.2.2 Configuring an Oracle Database to Send Redo Data
To specify a redo transport destination, use the
n database initialization parameter (where
n is an integer from 1 to 31).
There is a
n database initialization parameter (where
n is an integer from 1 to 31) that corresponds to each
n parameter. This parameter is used to enable or disable the corresponding redo destination. Table 7-1 shows the valid values that can be assigned to this parameter.
Table 7-1 LOG_ARCHIVE_DEST_STATE_n Initialization Parameter Values
This destination becomes enabled if communication to its associated destination fails.
A redo transport destination is configured by setting the
n parameter to a character string that includes one or more attributes. This section briefly describes the most commonly used attributes. See LOG_ARCHIVE_DEST_n Parameter Attributes for a full description of all
n parameter attributes.
SERVICE attribute, which is a mandatory attribute for a redo transport destination, must be the first attribute specified in the attribute list. The
SERVICE attribute is used to specify the Oracle Net service name used to connect to the redo transport destination. The service name must be resolvable through an Oracle Net naming method to an Oracle Net connect descriptor that matches the Oracle Net listener(s) at the redo transport destination. The connect descriptor must specify that a dedicated server connection be used, unless that is the default connection type for the redo transport destination.
Oracle Database Net Services Administrator's Guide for information about Oracle Net service names, connect descriptors, listeners, and network security
SYNC attribute specifies that the synchronous redo transport mode be used to send redo data to a redo transport destination.
ASYNC attribute specifies that the asynchronous redo transport mode be used to send redo data to a redo transport destination. The asynchronous redo transport mode is used if neither the
SYNC nor the
ASYNC attribute is specified.
NET_TIMEOUT attribute specifies how long the
LGWR process waits for an acknowledgement that redo data has been successfully received by a destination that uses the synchronous redo transport mode. If an acknowledgement is not received within
NET_TIMEOUT seconds, the redo transport connection is terminated and an error is logged.
Oracle recommends that the
NET_TIMEOUT attribute be specified whenever the synchronous redo transport mode is used, so that the maximum duration of a redo source database stall caused by a redo transport fault can be precisely controlled. See Monitoring Synchronous Redo Transport Response Time for information about monitoring synchronous redo transport mode response time.
Note:You could also set the database initialization parameter,
DATA_GUARD_SYNC_LATENCY, which is global for all synchronous standby destinations. It defines the maximum amount of time (in seconds) that the primary database may wait before disconnecting subsequent destinations after at least one synchronous standby has acknowledged receipt of the redo.
For example, suppose you have three synchronous standby destinations and you set
DATA_GUARD_SYNC_LATENCY to a value of 2. If the first standby acknowledges receipt of the redo immediately, then the primary database waits no longer than 2 seconds for the other two standbys to respond. If one or both respond within 2 seconds, then they are maintained as active destinations. Destinations that do not respond in time are marked as failed. In both cases the primary remains in zero data loss protection mode because one synchronous standby has acknowledged receipt of the redo. Any failed synchronous standbys are reconnected as normal after the number of seconds specified for the
REOPEN attribute have passed.
AFFIRM attribute is used to specify that redo received from a redo source database is not acknowledged until it has been written to the standby redo log. The
NOAFFIRM attribute is used to specify that received redo is acknowledged without waiting for received redo to be written to the standby redo log.
DB_UNIQUE_NAME attribute is used to specify the
DB_UNIQUE_NAME of a redo transport destination. The
DB_UNIQUE_NAME attribute must be specified if the
LOG_ARCHIVE_CONFIG database initialization parameter has been defined and its value includes a
DB_UNIQUE_NAME attribute is specified, its value must match one of the
DB_UNIQUE_NAME values in the
DG_CONFIG list. It must also match the value of the
DB_UNIQUE_NAME database initialization parameter at the redo transport destination. If either match fails, an error is logged and redo transport is not possible to that destination.
VALID_FOR attribute is used to specify when redo transport services transmits redo data to a redo transport destination. Oracle recommends that the
VALID_FOR attribute be specified for each redo transport destination at every site in an Oracle Data Guard configuration so that redo transport services continue to send redo data to all standby databases after a role transition, regardless of which standby database assumes the primary role.
REOPEN attribute is used to specify the minimum number of seconds between automatic reconnect attempts to a redo transport destination that is inactive because of a previous error.
COMPRESSION attribute is used to specify that redo data is transmitted to a redo transport destination in compressed form. Redo transport compression can significantly improve redo transport performance on network links with low bandwidth and high latency.
Redo transport compression is a feature of the Oracle Advanced Compression option. You must purchase a license for this option before using the redo transport compression feature.
The following example uses all of the
n attributes described in this section. A
DB_UNIQUE_NAME has been specified for both destinations, as has the use of compression. If a redo transport fault occurs at either destination, then redo transport attempts to reconnect to that destination, but not more frequently than once every 60 seconds.
DB_UNIQUE_NAME=BOSTON LOG_ARCHIVE_CONFIG='DG_CONFIG=(BOSTON,CHICAGO,HARTFORD)' LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) REOPEN=60 COMPRESSION=ENABLE DB_UNIQUE_NAME=CHICAGO' LOG_ARCHIVE_DEST_STATE_2='ENABLE' LOG_ARCHIVE_DEST_3='SERVICE=HARTFORD SYNC AFFIRM NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) REOPEN=60 COMPRESSION=ENABLE DB_UNIQUE_NAME=HARTFORD' LOG_ARCHIVE_DEST_STATE_3='ENABLE'
Configuration for Zero Data Loss Recovery Appliance (Recovery Appliance) is identical to configuration for any standby database. So in the preceding example, because Chicago is an
ASYNC destination, it could be either a standby database or a Recovery Appliance. (Synchronous redo transport is not supported for Recovery Appliance)
7.2.3 Configuring an Oracle Database to Receive Redo Data
Redo transport destination must be configured to receive and to archive redo data from a redo source database.
See the following:
126.96.36.199 Managing Standby Redo Logs
The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log. A standby redo log is used to store redo received from another Oracle database. Standby redo logs are structurally identical to redo logs, and are created and managed using the same SQL statements used to create and manage redo logs.
Redo received from another Oracle database via redo transport is written to the current standby redo log group by a remote file server (RFS) foreground process. When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, and the previously used standby redo log group is archived by an
n background process.
The process of sequentially filling and then archiving redo log file groups at a redo source database is mirrored at each redo transport destination by the sequential filling and archiving of standby redo log groups.
Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database. For administrative ease, Oracle recommends that all redo log files in the redo log at the redo source database and the standby redo log at a redo transport destination be of the same size.
The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. At the redo source database, query the
V$LOG view to determine how many redo log groups are in the redo log at the redo source database and query the
V$THREAD view to determine how many redo threads exist at the redo source database.
Perform the following query on a redo source database to determine the size of each log file and the number of log groups in the redo log:
SQL> SELECT GROUP#, BYTES FROM V$LOG;
Perform the following query on a redo destination database to determine the size of each log file and the number of log groups in the standby redo log:
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
If the redo source database is an Oracle Real Applications Cluster (Oracle RAC) or Oracle Real Application Clusters One Node (Oracle RAC One Node) database, query the
V$LOG view at the redo source database to determine how many redo threads exist and specify the corresponding thread numbers when adding redo log groups to the standby redo log.
The following sample SQL statements create a standby redo log at a database that is to receive redo from a redo source database that has two redo threads:
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
Whenever a redo log group is added to a primary database, a log group must also be added to the standby redo log of each standby database in the configuration. Otherwise, the standby database may become unsynchronized after a primary log switch, which could temporarily prevent a zero data loss failover or cause a primary database operating in maximum protection mode to shut down.
188.8.131.52 Cases Where Redo Is Written Directly To an Archived Redo Log File
Redo received by a standby database is written directly to an archived redo log file if a standby redo log group is not available or if the redo was sent to resolve a redo gap. When this occurs, redo is written to the location specified by the
LOCATION attribute of one
n parameter that is valid for archiving redo received from another database. The
n parameter that is used for this purpose is determined when the standby database is mounted, and this choice is reevaluated each time a
n parameter is modified.
7.3 Cascaded Redo Transport Destinations
A cascaded redo transport destination (also known as a terminal destination) receives primary database redo indirectly from a standby database rather than directly from a primary database.
A physical standby database that cascades primary database redo to one or more terminal destinations at the same time it is applying changes to its local database files is known as a cascading standby database.
With cascading, the overhead associated with performing redo transport is offloaded from a primary database to a cascading standby database.
A cascading standby database can cascade primary database redo to up to 30 terminal destinations.
A cascading standby database can either cascade redo in real-time (as it is being written to the standby redo log file) or non-real-time (as complete standby redo log files are being archived on the cascading standby).
Only physical standby databases can cascade redo.
Real-time cascading requires a license for the Oracle Active Data Guard option.
Non-real-time cascading is supported on destinations 1 through 10 only. (Real-time cascading is supported on all destinations.)
See Before You Patch or Upgrade the Oracle Database Software for information about how to handle cascaded redo transport destinations during an Oracle Database upgrade.
Also see the following topics:
7.3.1 Configuring a Terminal Destination
These steps describe how to configure a terminal destination.
- Select a physical standby database to configure as a cascading standby database.
- On the cascading standby database, configure the
FAL_SERVERdatabase initialization parameter with the Oracle Net alias of the primary database or of a standby database that receives redo directly from the primary database.
- On the cascading standby database, configure a
ndatabase initialization parameter for one or more terminal destinations. Configure the
SERVICEattribute of this destination with the Oracle Net alias of the terminal destination, and the
VALIDattribute to be valid for archival of the standby redo log while in the standby role.
If you specify
ASYNCtransport mode on destinations 1 through 10, then redo is shipped in real-time. If you do not specify a transport mode or you specify
SYNCon destinations 1 through 10, then redo is shipped in non-real-time. Destinations 11 through 31 operate only in
ASYNC(real-time) transport mode.
- At the terminal destination, configure the
FAL_SERVERdatabase initialization parameter with the Oracle Net alias of the cascading standby database or of another standby database that is directly connected to the primary database. Although it is also possible to specify the primary database, this would defeat the purpose of cascading, which is to reduce the redo transport overhead on the primary database.
- Example 7-1 shows some of the database initialization parameters used by the members of an Oracle Data Guard configuration that includes a primary database named
bostonthat sends redo to a local physical standby database named
boston2, which then cascades primary database redo to a remote physical standby database named
ndatabase initialization parameter could also be configured on database
bostonthat is valid for standby redo log archival to database
bostonis in the standby role. This would allow redo cascading to database
denverto continue if a switchover is performed between database
Example 7-1 Some of the Initialization Parameters Used When Cascading Redo
DB_UNIQUE_NAME=boston FAL_SERVER=boston2 LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,boston2,denver)' LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston' LOG_ARCHIVE_DEST_2='SERVICE=boston2 SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston2'
Cascading Physical Standby Database
DB_UNIQUE_NAME=boston2 FAL_SERVER=boston LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(boston,boston2,denver)' LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston2' LOG_ARCHIVE_DEST_2= 'SERVICE=denver VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver'
Cascaded Physical Standby Database
DB_UNIQUE_NAME=denver FAL_SERVER=boston2 LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,boston2,denver)' LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=denver'
For information about validating a configuration after you set up a cascading environment, see "Validating a Configuration".
7.3.2 Cascading Scenarios
A Data Guard configuration can be set up to cascade to a single physical standby or to multiple physical standbys.
See the following topics:
184.108.40.206 Cascading to a Physical Standby
These steps provide an example of cascading to a physical standby database.
In this scenario, you have a mission-critical primary database. This database has stringent performance and data protection requirements, so you have decided to deploy a local physical standby database to provide zero data loss protection and a remote, cascaded physical standby database to protect against regional disasters at the primary and local standby database sites.
You can achieve the objectives described above by performing the following steps:
- Create a physical standby database at a local site.
- Create a physical standby database at a site that is sufficiently remote to provide protection against regional disasters at the primary and local standby database sites.
- Configure the local standby database as a
SYNCredo transport destination of the primary database.
- Configure the remote physical standby database as a terminal destination of the local standby database.
220.127.116.11 Cascading to Multiple Physical Standbys
These steps provide an example of cascading to multiple physical standby databases.
In this scenario, you have a primary database in North America and you want to deploy three replicas of this database in Europe to support read-only reporting applications. For cost and performance reasons, you do not want to maintain network links from North America to each of your European sites.
You can achieve the objectives described above by performing the following steps:
- Create a network link between your North American site and one of your European sites.
- Create a physical standby database at each of your European sites.
- Open your physical standby databases in real-time query mode, as described in Opening a Physical Standby Database .
- Configure the physical standby database at the European endpoint of your transatlantic network link to cascade redo to your other European standby databases.
- Configure the other two physical standby databases as terminal destinations of the cascading standby database configured in step 4.
7.4 Data Protection Considerations for Cascading Standbys
When your configuration includes cascading standbys, each destination should have a
n parameter defined that points back to its source for use during a failover.
Real-time cascade enables a cascaded standby database to provide nearly the same level of data protection as any standby database that receives redo directly from a primary database using asynchronous redo transport. However, although redo is forwarded in real-time, the fact that there is a second network hop creates the potential for additional data loss if an outage prevents all redo from reaching the terminal destination.
7.5 Validating a Configuration
To validate an Oracle Data Guard configuration after you create it, query the
V$DATAGUARD_CONFIG view from any database in the configuration.
The view displays the unique database names defined with the
LOG_ARCHIVE_CONFIG initialization parameters.
Oracle Database Reference for more information about the
7.6 Monitoring Redo Transport Services
You can monitor redo transport status, as well as redo transport response time.
See the following topics:
7.6.1 Monitoring Redo Transport Status
You can query views to monitor redo transport status on a redo source database.
Take the following steps to monitor redo transport status on a redo source database.
- Perform the following query on the redo source database to determine the most recently archived sequence number for each thread:
SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG - > WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) - > GROUP BY THREAD#;
- Perform the following query on the redo source database to determine the most recently archived redo log file at each redo transport destination:
SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# - > FROM V$ARCHIVE_DEST_STATUS - > WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ# ------------------ ------ ---------------- ------------- /private1/prmy/lad VALID 1 947 standby1 VALID 1 947
The most recently archived redo log file should be the same for each destination. If it is not, a status other than
VALIDmay identify an error encountered during the archival operation to that destination.
- Perform a query at a redo source database to find out if an archived redo log file has been received at a particular redo transport destination. Each destination has an ID number associated with it. You can query the
DEST_IDcolumn of the
V$ARCHIVE_DESTview on a database to identify each destination's ID number.
Assume that destination 1 points to the local archived redo log and that destination 2 points to a redo transport destination. Perform the following query at the redo source database to find out if any log files are missing at the redo transport destination:
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM - > (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) - > LOCAL WHERE - > LOCAL.SEQUENCE# NOT IN - > (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND - > THREAD# = LOCAL.THREAD#); THREAD# SEQUENCE# --------- --------- 1 12 1 13 1 14
- Set the
LOG_ARCHIVE_TRACEdatabase initialization parameter at a redo source database and at each redo transport destination to trace redo transport progress. See Setting Archive Tracing for complete details and examples.
7.6.2 Monitoring Synchronous Redo Transport Response Time
V$REDO_DEST_RESP_HISTOGRAM view contains response time data for each redo transport destination.
The response time data is maintained for redo transport messages sent via the synchronous redo transport mode.
The data for each destination consists of a series of rows, with one row for each response time. To simplify record keeping, response times are rounded up to the nearest whole second for response times less than 300 seconds. Response times greater than 300 seconds are round up to 600, 1200, 2400, 4800, or 9600 seconds.
Each row contains four columns:
FREQUENCY column contains the number of times that a given response time has been observed. The
DURATION column corresponds to the response time. The
DEST_ID column identifies the destination. The
TIME column contains a timestamp taken when the row was last updated.
The response time data in this view is useful for identifying synchronous redo transport mode performance issues that can affect transaction throughput on a redo source database. It is also useful for tuning the
The next three examples show example queries for destination 2, which corresponds to the
LOG_ARCHIVE_DEST_2 parameter. To display response time data for a different destination, simply change the
DEST_ID in the query.
Perform the following query on a redo source database to display the response time histogram for destination 2:
SQL> SELECT FREQUENCY, DURATION FROM - > V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1;
Perform the following query on a redo source database to display the slowest response time for destination 2:
SQL> SELECT max(DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM - > WHERE DEST_ID=2 AND FREQUENCY>1;
Perform the following query on a redo source database to display the fastest response time for destination 2:
SQL> SELECT min( DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM - > WHERE DEST_ID=2 AND FREQUENCY>1;
The highest observed response time for a destination cannot exceed the highest specified
NET_TIMEOUT value specified for that destination, because synchronous redo transport mode sessions are terminated if a redo transport destination does not respond to a redo transport message within
7.6.3 Redo Gap Detection and Resolution
A redo gap occurs whenever redo transmission is interrupted.
When redo transmission resumes, redo transport services automatically detects the redo gap and resolves it by sending the missing redo to the destination.
The time needed to resolve a redo gap is directly proportional to the size of the gap and inversely proportional to the effective throughput of the network link between the redo source database and the redo transport destination. Redo transport services has two options that may reduce redo gap resolution time when low performance network links are used:
Redo Transport Compression
COMPRESSIONattribute of the
nparameter is used to specify that redo data be compressed before transmission to the destination.
Parallel Redo Transport Network Sessions
MAX_CONNECTIONSattribute of the
nparameter can be used to specify that more than one network session be used to send the redo needed to resolve a redo gap.
See LOG_ARCHIVE_DEST_n Parameter Attributes for more information about the
18.104.22.168 Manual Gap Resolution
In some situations, gap resolution cannot be performed automatically and it must be performed manually.
For example, redo gap resolution must be performed manually on a logical standby database if the primary database is unavailable.
Perform the following query at the physical standby database to determine if there is redo gap on a physical standby database:
SQL> SELECT * FROM V$ARCHIVE_GAP; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# ----------- ------------- -------------- 1 7 10
The output from the previous example indicates that the physical standby database is currently missing log files from sequence 7 to sequence 10 for thread 1.
Perform the following query on the primary database to locate the archived redo log files on the primary database (assuming the local archive destination on the primary database is
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND - > DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10; NAME -------------------------------------------------------------------------------- /primary/thread1_dest/arcr_1_7.arc /primary/thread1_dest/arcr_1_8.arc /primary/thread1_dest/arcr_1_9.arc
This query may return consecutive sequences for a given thread. In that case, there is no actual gap, but the associated thread was disabled and enabled within the time period of generating these two archived logs. The query also does not identify the gap that may exist at the tail end for a given thread. For instance, if the primary database has generated archived logs up to sequence 100 for thread 1, and the latest archived log that the logical standby database has received for the given thread is the one associated with sequence 77, then this query does not return any rows, although there is a gap for the archived logs associated with sequences 78 to 100.
Copy these log files to the physical standby database and register them using the
ALTER DATABASE REGISTER LOGFILE. For example:
SQL> ALTER DATABASE REGISTER LOGFILE - > '/physical_standby1/thread1_dest/arcr_1_7.arc'; SQL> ALTER DATABASE REGISTER LOGFILE - > '/physical_standby1/thread1_dest/arcr_1_8.arc'; SQL> ALTER DATABASE REGISTER LOGFILE - > '/physical_standby1/thread1_dest/arcr_1_9.arc';
V$ARCHIVE_GAP view on a physical standby database only returns the gap that is currently blocking Redo Apply from continuing. After resolving the gap, query the
V$ARCHIVE_GAP view again on the physical standby database to determine if there is another gap sequence. Repeat this process until there are no more gaps.
To determine if there is a redo gap on a logical standby database, query the
DBA_LOGSTDBY_LOG view on the logical standby database. For example, the following query indicates there is a gap in the sequence of archived redo log files because it displays two files for
THREAD 1 on the logical standby database. (If there are no gaps, then the query shows only one file for each thread.) The output shows that the highest registered file is sequence number 10, but there is a gap at the file shown as sequence number 6:
SQL> COLUMN FILE_NAME FORMAT a55 SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L - > WHERE NEXT_CHANGE# NOT IN - > (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#) - > ORDER BY THREAD#, SEQUENCE#; THREAD# SEQUENCE# FILE_NAME ---------- ---------- ----------------------------------------------- 1 6 /disk1/oracle/dbs/log-1292880008_6.arc 1 10 /disk1/oracle/dbs/log-1292880008_10.arc
Copy the missing log files, with sequence numbers 7, 8, and 9, to the logical standby system and register them using the
ALTER DATABASE REGISTER LOGICAL LOGFILE statement. For example:
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE - > '/disk1/oracle/dbs/log-1292880008_7.arc'; SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE - > '/disk1/oracle/dbs/log-1292880008_8.arc'; SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE - > '/disk1/oracle/dbs/log-1292880008_9.arc';
A query based on the
DBA_LOGSTDBY_LOG view on a logical standby database, as specified above, only returns the gap that is currently blocking SQL Apply from continuing. After resolving the gap, query the
DBA_LOGSTDBY_LOG view again on the logical standby database to determine if there is another gap sequence. Repeat this process until there are no more gaps.
7.6.4 Redo Transport Services Wait Events
You can use Oracle wait events to track redo transport wait time on a redo source database.
Table 7-2 lists several of these Oracle wait events, which are found in the
V$SYSTEM_EVENT dynamic performance view.
For a complete list of the Oracle wait events used by redo transport, see the Oracle Data Guard Redo Transport and Network Best Practices white paper on the Oracle Maximum Availability Architecture (MAA) home page at:
Table 7-2 Redo Transport Wait Events
LNS wait on ATTACH
Total time spent waiting for redo transport sessions to be established to all
LNS wait on SENDREQ
Total time spent waiting for redo data to be written to all
LNS wait on DETACH
Total time spent waiting for redo transport connections to be terminated to all
7.7 Tuning Redo Transport
You can optimize redo transport for best performance.
See the Oracle Data Guard Redo Transport and Network Configuration Best Practices white paper available on the Oracle Maximum Availability Architecture (MAA) home page at: