Skip to Main Content
  • Questions

  • Effects of frequent redo log switches in active data guard set up

Breadcrumb

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 100+ times

You Asked

Howdy,

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

https://www.dbi-services.com/blog/oracle-18c-active-data-guard-and-awr-reports/

Rating

  (1 rating)

Comments

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".

Cheers,
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 buffers...so 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;

BYTES/1024/1024/1024
--------------------
               31.25
               31.25
               31.25
               31.25
               31.25
               31.25
               31.25
               31.25
               31.25


we use large log files rather than small ones.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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