Sizing of Standby redo log in Oracle 10g Dataguard

In Oracle 10g Dataguard replication, Standby redo log helps you to have committed records propagated from primary to physical standby database without having to wait for log shipping. But you need to make sure that standby redo logs are of same size as of online redo logs. For performance reasons, don’t multiplex standby redo log and make one extra standby redo log group.


If standby redo log is of different size in physical standby, you won’t see any error in alert log. But new records won’t get appear in standby and quering v$managed_standby view will show as WAIT_FOR_LOG for MRP0 process in standby instance.

SQL> select process, status, sequence#,block#,blocks,delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 2255 0 0 0
RFS IDLE 2255 133468 1 0
RFS IDLE 0 0 0 0

In my case initially I created 100 MB standby redo logs and my online redo logs were 200 MB in size. Because of this difference, real-time propagation was not working. So I dropped initial 100 MB standby redo logs and created 200 MB ones in primary instance.
SQL> alter database drop logfile group 5;
SQL> alter database drop logfile group 6;
SQL> alter database drop logfile group 7;
SQL> alter database drop logfile group 8;
SQL> alter database add standby logfile group 5 ('/redo1/ptnt/ptnt_srl_01a.log') size 200M reuse ;
SQL> alter database add standby logfile group 6 ('/redo2/ptnt/ptnt_srl_02a.log') size 200M reuse;
SQL> alter database add standby logfile group 7 ('/redo1/ptnt/ptnt_srl_03a.log') size 200M reuse;
SQL> alter database add standby logfile group 8 ('/redo2/ptnt/ptnt_srl_04a.log') size 200M reuse;
SQL> alter database add standby logfile group 9 ('/redo2/ptnt/ptnt_srl_05a.log') size 200M;

Do the same thing on ptnt_stndby - standby instanceSQL> select process, status, sequence#,block#,blocks,delay_mins from v$managed_standby;

Don't forget to cancel out current managed recovery in standby instance before adding standby redo logs. If not you will get following error.

SQL> alter database add standby logfile group 5 ('/redo1/ptnt_stndby/ptnt_srl_01a.log') size 200M reuse;
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database add standby logfile group 5 ('/redo1/ptnt_stndby/ptnt_srl_01a.log') size 200M reuse;

Repeat the steps for other standby redo log files too.

Put standby database instance in recovery mode

SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

Modify primary and standby instances with following
On primarySQL> alter system set log_archive_dest_2='SERVICE=ptnt_standby.unix.gsm1900.org LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ptnt_standby' scope=both;
System altered.


On secondary
SQL> alter system set log_archive_dest_2='SERVICE=ptnt.unix.gsm1900.org LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ptnt' scope=both;
System altered.

After recreating standby redo logs with same size, things started propagating to physical standby in near real-time.



PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 2270 397313 1636 0
MRP0 APPLYING_LOG 2271 61303 409600 0
RFS IDLE 2271 61306 5 0
RFS IDLE 0 0 0 0