6 Redo Transport Services

6.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.

6.2.2 Configuring an Oracle Database to Send Redo Data

This section describes how to configure an Oracle database to send redo data to a redo transport destination.

The LOG_ARCHIVE_DEST_n database initialization parameter (where n is an integer from 1 to 31) is used to specify the location of a local archive redo log or to specify a redo transport destination. This section describes the latter use of this parameter.

There is a LOG_ARCHIVE_DEST_STATE_n database initialization parameter (where n is an integer from 1 to 31) that corresponds to each LOG_ARCHIVE_DEST_n parameter. This parameter is used to enable or disable the corresponding redo destination. Table 6-1 shows the valid values that can be assigned to this parameter.

A redo transport destination is configured by setting the LOG_ARCHIVE_DEST_n parameter to a character string that includes one or more attributes. This section briefly describes the most commonly used attributes. See Chapter 15 for a full description of all LOG_ARCHIVE_DEST_n parameter attributes.

The 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.

The SYNC attribute is used to specify that the synchronous redo transport mode be used to send redo data to a redo transport destination.

The ASYNC attribute is used to specify that the asynchronous redo transport mode be used to send redo data to a redo transport destination. The asynchronous redo transport mode will be used if neither the SYNC nor the ASYNC attribute is specified.

The NET_TIMEOUT attribute is used to specify how long the LGWR process will block waiting 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 Section 6.4.2 for information about monitoring synchronous redo transport mode response time.

The 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.

The 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 DG_CONFIG list.

If the 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 will not be possible to that destination.

The 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 a Data Guard configuration so that redo transport services will continue to send redo data to all standby databases after a role transition, regardless of which standby database assumes the primary role.

The 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.

The 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 LOG_ARCHIVE_DEST_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, redo transport will attempt 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'

6.2.3 Configuring an Oracle Database to Receive Redo Data

This section describes how to configure a redo transport destination to receive and to archive redo data from a redo source database.

The following topics are discussed:

• Creating and Managing a Standby Redo Log

• Configuring Standby Redo Log Archival

• Cases Where Redo Is Written Directly To an Archived Redo Log File

SQL> SELECT GROUP#, BYTES FROM V$LOG;

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M;
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 500M;
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog3.rdo') 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 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;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;

LOG_ARCHIVE_DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive
VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
LOG_ARCHIVE_DEST_STATE_2=ENABLE

6.3.1 Configuring a Cascaded Destination

Perform the following steps to configure a cascaded destination:

1. Select a physical standby database to configure as a cascading standby database.

2. On the cascading standby database, configure the FAL_SERVER attribute with the Oracle Net alias of the primary database or of a standby database that receives redo directly from the primary database.

3. On the cascading standby database, configure a LOG_ARCHIVE_DEST_n database initialization parameter for the cascaded destination. Configure the SERVICE attribute of this destination with the Oracle Net alias of the cascaded destination, and the VALID attribute to be valid for archival of the standby redo log while in the standby role.

   If the SYNC or ASYNC redo transport attributes are specified, they are ignored.

4. At the cascaded destination, configure the FAL_SERVER database 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.

5. Example 6-1 shows some of the database initialization parameters used by the members of a Data Guard configuration that includes a primary database named boston that sends redo to a local physical standby database named boston2, which then cascades primary database redo to a remote physical standby database named denver.

   Note that a LOG_ARCHIVE_DEST_n database initialization parameter could also be configured on database boston that is valid for standby redo log archival to database denver when database boston is in the standby role. This would allow redo cascading to database denver to continue if a switchover is performed between database boston and database boston2.

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'

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'
 

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'

6.3.2 Data Protection Considerations

Oracle recommends that a standby database primarily intended for disaster recovery purposes receive redo data directly from the primary database. This will result in the highest level of data protection. A cascaded standby database can be used as a second line of defense, but by definition it will always have received less primary database redo than a standby database that is receiving redo directly from the primary.

6.3.3 Cascading Scenarios

This section describes two typical cascading scenarios

• Cascading to a Physical Standby

• Cascading to Multiple Physical Standbys

6.4.1 Monitoring Redo Transport Status

This section describes the steps used to monitor redo transport status on a redo source database.

Step 1   Determine the most recently archived redo log file.

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#;

Step 2   Determine the most recently archived redo log file at each redo transport destination.

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 VALID may identify an error encountered during the archival operation to that destination.

Step 3   Find out if archived redo log files have been received at a redo transport destination.

A query can be performed 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_ID column of the V$ARCHIVE_DEST view 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

Step 4   Trace the progression of redo transmitted to a redo transport destination.

Set the LOG_ARCHIVE_TRACE database initialization parameter at a redo source database and at each redo transport destination to trace redo transport progress. See Appendix F for complete details and examples.

6.4.2 Monitoring Synchronous Redo Transport Response Time

The V$REDO_DEST_RESP_HISTOGRAM view contains response time data for each redo transport destination. This 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, DURATION, DEST_ID, and TIME.

The 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 NET_TIMEOUT attribute.

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;

6.4.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

  The COMPRESSION attribute of the LOG_ARCHIVE_DEST_n parameter is used to specify that redo data be compressed before transmission to the destination.

• Parallel Redo Transport Network Sessions

  The MAX_CONNECTIONS attribute of the LOG_ARCHIVE_DEST_n parameter can be used to specify that more than one network session be used to send the redo needed to resolve a redo gap.

See Chapter 15, "LOG_ARCHIVE_DEST_n Parameter Attributes" for more information about the COMPRESSION and MAX_CONNECTIONS attributes.

SQL> SELECT * FROM V$ARCHIVE_GAP;

    THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
-----------  -------------  --------------
          1              7              10

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

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';

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

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';

6.4.4 Redo Transport Services Wait Events

Table 6-2 lists several of the Oracle wait events used to track redo transport wait time on a redo source database. These wait events 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:

http://www.oracle.com/goto/maa