Data loading:-

create table t(c1 char(2000),c2 char(2000));

insert into t select 'A','B' from dual connect by rownum<1000000;

commit;

the data loading of 8 GB data took 160 sec .

Now redo generation rate by 2 nodes RAC database as calculated by following example is:-

SYS@EBILPD11> select thread#,sequence#,blocks*block_size/1024/1024 MB,(next_time-first_time)*86400 sec, blocks*block_size/1024/1024/((next_time-first_time)*86400) "MB/s" from v$archived_log where ((next_time-first_time)*86400<>0) and first_time between  to_date('2017/11/15 06:00:00','YYYY/MM/DD HH24:MI:SS') and to_date('2017/11/15 07:00:00','YYYY/MM/DD HH24:MI:SS') and dest_id=2 order by first_time;

THREAD#  SEQUENCE#         MB        SEC       MB/s

———- ———- ———- ———- ———-

1       2264 56.8867188          2 28.4433594

1       2265  56.984375          2 28.4921875

1       2266 72.2739258          3 24.0913086

2       2252 .004882813          3 .001627604

1       2516 56.8862305          1 56.8862305

1       2517 56.8833008          1 56.8833008

1       2518 66.6621094          1 66.6621094

2       2253 .008789063          3 .002929688

1       2519 56.8852539          1 56.8852539

1       2520 56.8828125          1 56.8828125

 

Average redo generation per sec:50 Megabits per second

 

Now Bandwidth calculation:-

Required bandwidth = ((Redo rate bytes per sec. / 0.75) * 8) / 1,000,000 = bandwidth in Mbps

So if we calculate our requirement is as following:-

((50000000/ 0.75) * 8) / 1,000,000 = 533 MBPS

We did not find any lag when we run below query in standby as our WAN bandwidth is 2GBPS.

SQL> select name,value,time_computed,datum_time from v$dataguard_stats where name='transport lag';

 

NAME

——————————–

VALUE

—————————————————————-

TIME_COMPUTED                  DATUM_TIME

—————————— ——————————

transport lag

+00 00:00:00

11/15/2017 08:17:47            11/15/2017 08:17:47

 

 

Network transport lag

We diagnosed the network transport lag using below command which needs to be enabled during the testing in both primary as well as DR

alter system set events '16421 trace name context forever, level 1';

This will generate TT0 tracefile under dump destination.

vi EBILPD11_tt00_16385.trc

File transfer time (micro seconds)       1663153

Begin Statistics:-

DESTINATION 2 – OCI REQUEST

Total count  – OCI REQUEST             122

Total time   – OCI REQUEST             38498

Average time – OCI REQUEST             315

LOG_ARCHIVE_DEST_2 – NETWORK SEND

Total count  – NETWORK SEND            122

Total time   – NETWORK SEND            19605

Average time – NETWORK SEND            160

Total data buffers queued              121

Total data buffers completed           121

Total bytes written                    59650048

Average network send size (blocks)     962

Average network send buffers           1.00

Average buffer turnaround time         6289

Throughput (MB/s)                         34.20

DESTINATION 2 – NETWORK NO-STALL REAP

Total count  – NETWORK NO-STALL REAP   18

Total time   – NETWORK NO-STALL REAP   192

Average time – NETWORK NO-STALL REAP   10

Total network layer time               58295

Percentage of time in network          3.51

—–

Total count  – DISK READ               121

Total time   – DISK READ               30848

Average time – DISK READ               254

Total count  – BUFFER RELEASE          121

Total time   – BUFFER RELEASE          94

Average time – BUFFER RELEASE          0

Total disk layer time                  30942

    Percentage of time in disk layer       1.86

—–

Total count  – SLEEP                   68

Total time   – SLEEP                   1402529

Average time – SLEEP                   20625

Total DG layer time                    1573916

Percentage of time in DG layer         94.63

 

End statistics:-

LOG_ARCHIVE_DEST_2 – NETWORK SEND

Total count  – NETWORK SEND            59

Total time   – NETWORK SEND            8109

Average time – NETWORK SEND            137

Total data buffers queued              58

Total data buffers completed           58

Total bytes written                    59648000

Average network send size (blocks)     2008

Average network send buffers           1.00

Average buffer turnaround time         80387

Throughput (MB/s)                        101.80

DESTINATION 2 – NETWORK NO-STALL REAP

Total count  – NETWORK NO-STALL REAP   53

Total time   – NETWORK NO-STALL REAP   665

Average time – NETWORK NO-STALL REAP   12

DESTINATION 2 – NETWORK STALL REAP

Total count  – NETWORK STALL REAP      26

Total time   – NETWORK STALL REAP      403534

Average time – NETWORK STALL REAP      15520

Total network layer time               439520

Percentage of time in network          78.66

—–

Total count  – DISK READ               58

Total time   – DISK READ               72206

Average time – DISK READ               1244

Total count  – BUFFER RELEASE          58

Total time   – BUFFER RELEASE          70

Average time – BUFFER RELEASE          1

Total disk layer time                  72276

Percentage of time in disk layer       12.93

—–

Total DG layer time                    46968

Percentage of time in DG layer         8.41

 

The above output was taking from a test run where no transport lag was seen. We see in end statistics report ,we spent 79% of the time on the network, 12% of the time in the disk layer, and 8% of the time in the DG layer, mainly due to sleeps.This statistics shows we are almost reaching congestion point as network percentage time increasing and DG layer dropping. Remember,We found only 3% time for network in the beginning before starting loading.

 

References

 

How To Calculate The Required Network Bandwidth Transfer Of Redo In Data Guard Environments (Doc ID 736755.1)

Measuring Network Capacity using oratcptest (Doc ID 2064368.1)

//www.oracle.com/technetwork/database/availability/async-2587521.pdf