Data Guard optimal network bandwidth Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard - Determining Optimal Network Bandwidth

In a Data Guard environment, archived redo logs from the primary database are transferred to standby sites using Oracle Net. To ensure a timely delivery of archived redo log files, a suitable network connection between the primary site and all remote archival destinations must be provided.

Determining Optimal Network Bandwidth

The issue of having an optimal network bandwidth becomes more crucial when the primary database is running in high data protection modes. As mentioned in the previous chapters, the high data protection modes, MAXIMUM PROTECTION and MAXIMUM AVAILABILITY, require a synchronous transfer of redo records. In these cases, a transaction on the primary database is not complete until at least one Oracle instance participating in the data protection mode receives redo records. It is not recommended that a Oracle instance be configured to participate in a high data protection mode connected using a WAN.

In order to calculate the optimal network bandwidth for a Data Guard environment, it will be necessary to determine the amount of data the Oracle Net will carry in the busy period. In other words, the amount of redo generated on the primary database during the busy period will have to be known. Oracle provides several methods for determining the rate of redo generation during peak time. For example, the output of STATSPACK, an Oracle supplied package to monitor performance of the database, can be used to get the amount of redo generated per second.

In this text, the information stored in the view v$archived_log will be used to find the largest archived redo log file created over a period of few days. That file size will be used to estimate the volume of data that may be carried over Oracle Net to standby sites during peak activity periods. The script, Network_BW.sql, can be used to determine the optimal bandwidth for the network based on size of archived redo log files.

Tuning of Data Guard Configuration

Determining Optimal Network Bandwidth

The script is based on assumption that the primary database has been running for last n days where n is an input parameter to the script. It determines the size of largest archived redo log file and the average size of archived redo log files in the last n days. If the largest file is five times or more larger than the average file size, the average file size is used to do the further calculations. 

This assumes that the largest archived redo log file in the last n days has been a result of a heavy, but temporary, transaction on the database.  If no such phenomenon exists, the largest file will be used.

The script assumes an overhead of approximately 25% data in transporting data over Net. These overheads are mostly related to network information required to carry packets from source to destination. The output of the script is the bandwidth of the network in Megabits per second that is required for timely transfer of archived redo logs using Oracle Net.

It is recommended to execute this script using n=8 and n=32. These two values of n will take care of extra redo logs generated by any weekly or monthly batch jobs.

Select
   Max(SizeOfFile) MaxSize,
   Avg(SizeOfFile) AvgSize,
   Ceil((Decode(Sign(Max(SizeOfFile)-5*Avg(SizeOfFile)),1,
   Avg(SizeOfFile)*(1+0.25),  
   Max(SizeOfFile)*(1+0.25))*8)/(3600*1024*1024)) BandWidth_Mbps
From (
     Select
       to_char(COMPLETION_TIME,'YYYY-MON-DD HH24') Completion_Hour,
       Sum(BLOCK_SIZE*(BLOCKS+1)) SizeOfFile
     From
       V$ARCHIVED_LOG
     See code depot for full scripts
     And
       Dest_id = 1
   Group By
      to_char(COMPLETION_TIME,'YYYY-MON-DD HH24')
);

Determining Optimal Network Bandwidth

A sample output from the script Network_BW.sql is shown below:

MAXSIZE        AVGSIZE     BANDWIDTH_MBPS
-------        -------     --------------
802862592      111223948             1

This calculation will help in the specification of a network topology to connect the primary and standby sites. Usually, a Oracle instance located within the LAN is not an issue as much as a standby site connected over WAN. The use of ATM, T-3 (USA) or E-3 (Non USA) network topology must be considered in order to provide an optimal network bandwidth for Data Guard configuration transferring redo data over WAN. In nutshell, for a smooth working of the log transfer service, a network has to provide high bandwidth and low latency.

Gathering Oracle Net Related Statistics

Collecting performance statistics for Oracle Net can be a very tedious task due to the fact that Oracle does not provide specific data dictionary tables that hold data pertaining to Oracle Net. In most circumstances, it will be necessary to depend on operating system utilities to diagnose network issues. The view, v$system_event, keeps event and wait statistics for Oracle databases since instance startup. 

It can therefore be used to obtain the average wait related to SQL*Net . If there is a high value in the AVERAGE_WAIT column for SQL*Net events, consider using a trace route or sniffer to find the network latency and performance of intermediate network components between the source and destination. The following SQL statement can be used to find the average wait from the v$system_event view:

For more information, check my notes on Oracle Network Tuning.

Gathering SQL*Net Statistics

Select
   Sum(AVERAGE_WAIT)
From
   V$SYSTEM_EVENT

Network Tuning for the Log Transport Service

The Session Data Unit (SDU) parameter of TNS and listener governs the size of packets transported over Oracle Net. The default value of SDU is 2KB, and the default value of archive log buffer is 1MB. Thus, 1MB archived redo logs will be transferred in 512 packets. This will require 512 round trips on the network.

If the latency is high a round trip will significantly impact the overall performance of Oracle Net. In order to reduce the number of packets, the SDU size can be increased in the tnsnames.ora file and listener.ora file for services identifying standby sites. The maximum value for SDU is 32KB. The following example shows a new SDU size for service stdby1:

# Example tnsnames.ora file:

stdby1 =
  (DESCRIPTION =
    (SDU = 32768)
(ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  jrsp01  )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = appsdb )
    )
  )

# Example listener.ora file:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SDU = 32768)
      (SID_NAME = appsdb)
      (ORACLE_HOME = /sw/oracle/product/9.2.0)
    )
    (SID_DESC =
      (SDU = 32768)
      (SID_NAME = meddb)
      (ORACLE_HOME = /sw/oracle/product/9.2.0)
    )
  )