Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









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.

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

Determining Optimal Network Bandwidth

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

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


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 =
    (SDU = 32768)

      (ADDRESS = (PROTOCOL = TCP)(HOST =  jrsp01  )(PORT = 1521))
      (SID = appsdb )

# Example listener.ora file:

      (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)



The above text is an excerpt from the book: Oracle Data Guard Handbook

Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.