Skip to Main Content
  • Questions
  • Effects of frequent redo log switches in active data guard set up


Question and Answer

Connor McDonald

Thanks for the question, Tyler.

Asked: September 06, 2023 - 1:18 am UTC

Last updated: October 02, 2023 - 11:28 pm UTC

Version: 19.16

Viewed 1000+ times

You Asked


Just curious what (if any) deleterious effects we would see in the standby databases in an active data guard set up from switching redo logs every 2-3 minutes on the primary?

I’m also curious what would be the best diagnostic methods to troubleshoot issues on read only standby databases.

Thanks !

and Connor said...

The days of DataGuard being only updated after a full log is transmitted are long gone. Nowadays (with standby redo) your standby database (Active DG or not) is updated in near real time, so I don't really see a large benefit of high frequency log switches.

In terms of monitoring (in particular performance) on an Active DG database, we have a facility where you can collect AWR information *from* the Active DG but stored *elsewhere* in a read/write database.

Rather than replicate the process here, DBI did a nice write up


  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


Negative effects?

Tyler Forsyth, September 19, 2023 - 2:23 pm UTC

Thanks Connor!

Very much appreciate the response, but one part that was missed out was the "what bad things might we see on the standby" with respect to frequent log switches. As you said the redo apply doesn't need to wait for a log switch so I guess perhaps the question becomes more "what bad things might we see on the read only standby(s) as a consequence of frequent checkpoint operations on the primary".

Connor McDonald
October 02, 2023 - 11:28 pm UTC

A redo switch still invokes some checkpoint style activity, which means a busier DBWR process flushing out dirty if you start to see "log file (checkpoint incomplete)" style waits/messages then it probably means you're over stressing your DBWR on the primary.

I'm not a fan of over stressing my primary database for whatever reason :-)

You can take a lead from our Autonomous databases

SQL> select bytes/1024/1024/1024 from v$log;


we use large log files rather than small ones.

More to Explore


Need more information on Administration? Check out the Administrators guide for the Oracle Database