Why extra standby redo log group?

From: Yong Huang <yong321_at_yahoo.com>
Date: Fri, 3 Apr 2009 18:53:48 -0700 (PDT)
Message-ID: <528970.93850.qm_at_web80602.mail.mud.yahoo.com>

Oracle recommends one more group for standby redo logs (SRL) on the physical standby than the primary online logfile groups:


"Step 2 Determine the appropriate number of standby redo log file groups. Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database.... (maximum number of logfiles for each thread + 1) * maximum number of threads Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database."

I think it says that if you have groups #1 and #2 on primary and #1, #2 on standby, and if LGWR on primary just finished #1, switched to #2, and now it needs to switch to #1 again because #2 just became full, the standby must catch up, otherwise the primary LGWR cannot reuse #1 because the standby is still archiving the standby's #1. Now, if you have the extra #3 on standby, the standby in this case can start to use #3 while its #1 is being archived. That way, the primary can reuse the primary's #1 without delay.

I did a test on with two groups on primary and three SRL groups on standby. I keep doing "alter system switch logfile" on primary, which changes current log between #1 and #2. However, I see only two of the three SRL groups on standby, #1 and #2, being used, just like on primary; #3 is never used. It makes no difference whether log_archive_max_processes is set to 2 or 10. This seems to contradict what documentation says. However, I barely have any DML or DDL so log switching is not triggered because the file is full, but triggered manually by my command. Does that matter?

Again, my question is, What's exactly the extra SRL group used for on standby?

Yong Huang       

Received on Fri Apr 03 2009 - 20:53:48 CDT

Original text of this message