Oracle Standby redo Logs

So you have created your standby database using the RMAN DUPLICATE command, you have set the minimum log switch individual using ARCHIVE_LAG_TARGET, and you have sorted out those nasty “datafile missing” errors using automatic file management.

Management is now happy with the Mean Time To Recovery (MTTR)… but not really. They review the documentation generated from your database failover test and see that the time to switchover is slow and that there is still a possibility of losing some data.

Enter standby redo logs. These are required to enable real time apply of redo data onto the standby. This is not the same level of redundancy or availability of Oracle RAC, but getting close.

Essentially, the standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby. So the loss of data in the event of a failover is minimized.

Rather than regurgitate the documentation, I have attached the links to the specific parts of the documentation. Since I’m focused on providing ways for you to “get the job done”, I have also included some SQL that uses dynamic SQL to generate a series of ALTER DATABASE ADD STANDBY LOGFILE commands to run on both the primary and standby databases.

While it would be nice if the RMAN DUPLICATE command added the standby redo logs, it does not.

Have Fun!

Paul.

New Features in 10G Data Guard

Asynchronous Redo Transport Services

Broad Description

Sizing guidelines

Configuring standby redo logs

Configuring real-time apply (using standby redo logs)

Now the best bit. You just want to create the standby redo logs, right? Then use the dynamic SQL and you’re done.

Oracle REGEX functions:

REGEXP_REPLACE

REGEXP_SUBSTR

The SQL code:

select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
/

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1          1      64184   52428800          2 YES ACTIVE               647791233 20070807 22:50
2          1      64185   52428800          2 NO  CURRENT              647803055 20070807 23:00
3          1      64183   52428800          2 YES INACTIVE             647779820 20070807 22:40

SQL> col member for a40
SQL> select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- -------------------------------------------
3         ONLINE  /oracle/redo/prod1/redo03a.rdo        NO
3         ONLINE  /oracle/redo/prod1/redo03b.rdo        NO
2         ONLINE  /oracle/redo/prod1/redo02a.rdo        NO
2         ONLINE  /oracle/redo/prod1/redo02b.rdo        NO
1         ONLINE  /oracle/redo/prod1/redo01a.rdo        NO
1         ONLINE  /oracle/redo/prod1/redo01b.rdo        NO

"Create Standby redo"
------------------------------------------------------------------------------------------------
alter database add standby logfile '/oracle/redo/prod1/stdby_redo03a.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo03b.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo02a.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo02b.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo01a.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo01b.rdo' size 52428800;

6 rows selected.

Dynamic SQL to which generates extra standby redo log following the naming convention, stdby_redo0Group#.rdo

select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
union all
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
and rownum <=2
/

Create Standby redo
------------------------------------------------------------------------------------------------
alter database add standby logfile '/oracle/redo/prod1/stdby_redo03a.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo03b.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo02a.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo02b.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo01a.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo01b.rdo' size 52428800;
alter database add standby logfile '/oracle/redo/prod1/stdby_redo04.rdo' size 52428800;

7 rows selected.