References

Network bandwidth Implications of Oracle Dataguard

Dataguard Redo Transport & network configuration

Determine the redo generation rate

– You can query dba_hist_sysstat to find out your redo generation rate in bytes.
– You can choose to use either your average redo generation per hour or peak redo generation per hour (I would recommend that you size for the peak redo generation).
– Let us say that you determined that the redo generation rate, in Bytes, PER DAY, happened to be RedoBytes.
– You have to add a 30% overhead for tcp. So RedoBytesPlusOverhead=RedoBytes*1.3

Convert redo generation rate to Mbps (Megabitspersecond)

– (RedoBytesPlusOverhead*8)/((24*60*60)*(1024*1024))
– This is the theoretical minimum bandwidth that you are going to require.

Other important considerations

– Network latency is a huge factor in the amount of redo you will be able to transport from your primary site to the standby site. This value is unique to your network, so if you have a high latency network you might not be able to sustain the required rate of redo shipping.
– Usually the wide area network between the primary site and standby site is used by more than just dataguard (eg: e-mail etc). So those bandwidth requirements have to be factored in.
– The above two points is why customers should not rely too much on theoretical calculations and to actually deploy dataguard and test the actual redo generation and redo transport performance statistics.
– If you do not deploy a network that can ship redo at a rate of 45 mbps, all that means is that, at times your redo shipping will fall behind (ie your standby site will be behind the primary site) but dataguard still works. In a lot of cases this is acceptable (Based on the customers recovery point objective and recovery time objective).
– There are network tuning best practices outlined in “Dataguard Redo Transport & network configuration” , that you are optimizing the redo transport mechanism and the network. These have to be followed to achieve the best possible network performance.
– There are other techniques like network compression (Hardware compression using wan compression devices, or actual software compression in dataguard 11g) which enable you to reduce the network bandwidth requirements.

Scripts

– You can run the following script to extract redo generation information from dba_hist_sysstat.

set pages 0
set head off
set lines 132
set colsep ~
col curval format 9999999999999999999999
col prevval format 9999999999999999999999
Select
sn.snap_id
,cur_stat.snap_id
,prev_stat.snap_id
,to_char(sn.begin_interval_time,’DD-MON-YY HH24′)
,to_char(sn.end_interval_time,’DD-MON-YY HH24′)
,cur_stat.value curval
,prev_stat.value prevval
,(cur_stat.value-prev_stat.value) RedoGen
from dba_hist_snapshot sn,
(select snap_id,value from dba_hist_sysstat
where stat_name = ‘redo size’) cur_stat
,(select snap_id,value from dba_hist_sysstat where
stat_name = ‘redo size’) prev_stat
Where sn.snap_id = cur_stat.snap_id
and cur_stat.snap_id = prev_stat.snap_id + 1 order by 1;

– Spool the contents into a file RedoInfo.dat

– Create a table in the oracle database named RedoInfo

create table redoinfo (
inid    number,
bdate    date,
edate    date,
totredo    number
);

– Use sql*loader to load the contents of the spool file into redoinfo (At this point some would ask, “why dont i just do a create table as in the same database”, my assumption is that you probably dont want to be creating these temp tables in a production env.).

load data
infile ‘RedoInfo.dat’
append into table RedoInfo
fields terminated by “~” optionally enclosed by ‘”‘
(
field1 filler,
field2 filler,
inid,
bdate  Date “DD-MON-YY HH24”,
edate  Date “DD-MON-YY HH24”,
field3 filler,
field4 filler,
totredo
)

– Then you can run all kinds of queries on this to learn the different charachteristics of your redo generation
– The query below gives you the total redo generation per day and  the Mbps

select to_char(edate,’dd-mon-yy’) Day,sum(totredo)/(1024*1024) TotRedoMb
,(sum(totredo)*1.3)/(1024*1024) RedoPlusOvrHd,((sum(totredo)*1.25)*8)/(1024*1024) Mbits
,round(((sum(totredo)*1.25)*8)/(24*60*60*1024*1024)) Mbps FROM RedoInfo
group by to_char(edate,’dd-mon-yy’)
order by 1;