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
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;
b- You can disable DB_BLOCK_CHECKING parameter for fast redo apply.
SQL> alter system set DB_BLOCK_CHECKING=FALSE;
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;
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;
SQL>alter system set db_writer_processes=2 scope=spfile;
!!!!As an advice for health testing try each change one by one for seeing the effect of each parameter change operation.
Metalink Note ID: 241925.1 Troubleshooting 9i Data Guard Network Issues