Tuning Physical Standby part I

In my opinion tuning dataguard can be analyzed under two topics

1- Tuning network
a-Is the network bandwidth sufficient for the redo rate?

If your redo size and redo generation rate is high for bandwith between primary and standby server you have to check your network if you cant decrease the size of redo and redo generation rate.

For calculating the bandwidth need you can do the following calculation.

Required bandwidth = ((Redo rate in bytes ps / .7) * 8 ) / 1000000 = bandwidth in Mbps

for example if your redo rate per second is 500 bytes then you need
= ((500 * 1024) / .7) * 8 ) / 1000000=5.8 MBps

If you bandwith below 5.8 MBps then

you can get your redo rate ps from a statspack or awr report taken for a peek time period.

b- If the network is sufficent then check that there are no network errors or collisions on the primary and the standby

Use the ‘netstat’ command on the primary and the standby to validate that no network errors or collisions are occurring. You must get zero from the output of netstat commands collision column

C:\Documents and Settings\cgundogar>netstat -e
Interface Statistics

Received Sent

Bytes 3953227193 1644532922
Unicast packets 23348932 15441759
Non-unicast packets 157696847 1061
Discards 0 0
Errors 0 0
Unknown protocols 1396632
use netstat -i for unix or solaris OS

2- Tuning log apply process

a- If you have multiple CPU on your machine then parallelize the log apply process

SQL>alter database recover managed standby database parallel N disconnect;

max(N)=number_of_CPU*2

b- You can disable DB_BLOCK_CHECKING parameter for fast redo apply.
SQL> alter system set DB_BLOCK_CHECKING=FALSE;

System altered.

c- You can increase PARALLEL_EXECUTION_MESSAGE_SIZE from 2148 to 4096. (parameter can not be set dynamically )
SQL> alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=4096 scope=spfile;

System altered.

d- If your system supports ASYNC I/O then enable it with setting the parameter DISK_ASYNCH_IO to TRUE. If ASYNC I/O is not supported then increase your db_writer processes by increasing .db_writer_processes parameter to higher value.

SQL> alter system set DISK_ASYNC_IO=true scope=spfile;

System altered.

SQL>alter system set db_writer_processes=2 scope=spfile;

System altered.

!!!!As an advice for health testing try each change one by one for seeing the effect of each parameter change operation.

Referances Used:

Metalink Note ID: 241925.1 Troubleshooting 9i Data Guard Network Issues

Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)