Data Guard tips and tricks

If you don’t know about Data Guard, it is something that everyone should know about. A few tips and tricks from the session

  1. network bandwidth is important. There are a bunch of tuning metrics that can be done at the TCP layer level
  2. low latency, low problems. high latency, problems start to creep in.
  3. async redo transport – no async buffer to fill up. LNS can fall behind and will go into archive log if it is too slow.
  4. gap resolution – increase the number of arch processes from 2 to 4. Also use MAX_CONNECTIONS to 2. this reduces the overall time needed to resolve a gap by transferring an archive log using multiple processes.
  5. if the gap between primary and physical gets very large, use incremental backup to speed up gap resolution. Stop redo apply process, determine the current SCN of physical, take incremental backup of primary, catalog and recovery the incremental to physical, restart redo.
  6. automate archive log management. move backups to a physical standby. use RMAN to setup the archive log retention. requires flash recovery area on all databases
  7. this is slightly different for SQL apply. For 10.1, it required a manual purge applied to archive logs. For 10R2 they are automatic. This can be disabled on 10R2 but why?

switchover

  • multiple standby configurations is not that difficult and works well. It is recommended to define standby machines on the standby just incase it becomes the primary.This does require more redo being shipped between systems.
  • switchover stops all xactions and the redo log is drained. Once the log is cleared, the switchover will happen. You can make a physical standby in a logical switchover reinitiate itself as a physical standby but this is a manual process.
  • always try to failover to your physical standby. If you must switchover to a logical, use flashback to recovery primary and process redo logs to bring it back in sync.
  • switchover should not take more than a few minutes. you can speed this up by using real time apply.
  • in 10r2, DB_ROLE_CHANGE can fire a trigger when roles change

If I want to run a report on a standby I can suspend redo apply, flash back the database to a point in time, and run the report. This will allow you to write data to the database then flush the changed when you flash it back to a restore point and turn on the redo apply.

Like I said, there is a good reason to understand Data Guard. It is a very powerful tool that allows you to offload your production server and have a live standby backup of your system.