Oracle Standby redo Logs

Aug 15, 2007 / By Paul Moen

Tags: ,

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.

11 Responses to “Oracle Standby redo Logs”

  • jason arneil says:

    I like the technique, even adding the an extra SRL over the number of redo logs. However I would dispute what you say about RAC:

    “This is not the same level of redundancy or availability of Oracle RAC, but getting close.” But if your building blows up or floods, unless you have a stretched RAC cluster your dataguard solution provides you much more protection way above the availability of RAC.

    jason.

  • paulm says:

    Indeed or you combine RAC with Dataguard and run a standby or two off the RAC cluster.

    So you have the availability of RAC and the redundancy and Disaster Recovery (DR) of Dataguard.

  • Andrey Goryunov says:

    Hi Paul,

    you are right – RMAN’s duplicate command does not do everything for you regarding standby redo logs (I am about the case when they are already created on primary database). But after duplication of the target database proper entries (taking into consideration log_file_name_convert parameter on standby side) for standby logs will be created, but without files under that. The files can be simply recreated through ALTER DATABASE CLEAR LOGFILE GROUP (n – standby log file group) command. And after that you can continue with Data Guard (or Standby) configuration.

    Regards,
    Andrey Goryunov

  • SATISH NAWLE says:

    Directory structure is the same on both primary and standby machines.
    Backup is going locally to disk at location source_backup_directory
    TNSNAMES entry STANDBY points to standby db on both machines.
    TNSNAMES entry PROD points to prod db on both machines.
    RMAN catalog exists, and is resolved via TNSNAMES entry rcatdb.
    RMAN default channel locations.
    On Source/Primary db:

    su – oracle
    rman target / catalog rcat_owner@rcatdb
    RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
    RMAN> BACKUP CHECK LOGICAL FULL DATABASE PLUS ARCHIVELOG;
    RMAN> exit;

    scp source_backup_directory oracle@standby:standby_backup_directoryOn Destination or Standby db:

    sqlplus “/ as sysdba”
    startup nomount
    exit;On Source/Primary db:

    rman target / catalog rcat_owner@rcatdb auxiliary sys@STANDBY

    RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
    RMAN> exit;On Source/primary db:

    show parameter log_archive_dest;
    alter system set log_archive_dest_x = ‘SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD’;
    alter system set log_archive_dest_state_x = ‘ENABLE’;On Destination or Auxiliary STANDBY:

    alter system set FAL_SERVER = ‘PROD’;
    alter system set FAL_CLIENT = ‘STANDBY’;

    shutdown immediate;
    startup nomount;
    alter database mount standby database;
    alter database recover managed standby database disconnect from session;To check progress:

    On Primary:

    column destination format a30

    select dest_id,destination,status,database_mode,recovery_mode,error from V$ARCHIVE_DEST_STATUS
    where status != ‘INACTIVE’;To check progress on Primary or Standby:

    select * from v$managed_standby;

    SATISH NAWLE
    SQL STAR
    9224639278

  • Vinod says:

    We have two node RAC. We want to configure DG. The standby does not use RAC. Can anybody provide the steps.

  • PaulM says:

    I am assuming you meant you want a 3rd instance (Physical|Logical Standby) which gets its archivelogs from both RAC nodes.

    I had discussed this Andrey some time ago and we know the way, just didn’t get around to blogging about it.

    Always remember google is your searching-for-answers friend.

    Here is a paper from Oracle which spells out in detail how to implement a Physical standby from a RAC cluster.

    http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10g_RACPrimaryRACPhysicalStandby.pdf

  • Oli says:

    I love these blogs!

    In the 10gR2 docs it states that you may decide to add a standby group on the primary if there are delays in the RFS trace files etc…

    Specifically it says “Whenever you add an online redo log file group to the primary database, you must add a corresponding standby redo log file group to the standby database”

    However, I only get errors if I try to add/drop standby logs on the standby:
    “ORA-01156: recovery in progress may need access to files”

    Any thoughts?

  • Stellios says:

    I recently found out when setting up logical standby with real-time apply the logical standby database MUST be in ARCHIVELOG mode (relates to having the standby redo logs). I couldn’t find this requirement in any of the 10.2 documentation – I raised this with Oracle and they confirmed it is a documentation bug and fixed in 11g (not sure if they’re planning on updating the 10g documentation on tahiti.oracle.com).

    This is the reference in the 11g documentation:

    ““Redo received from another Oracle database via redo transport is written to the current standby redo log group by a RFS background process. When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, and the previously used standby redo log group is archived by an ARCn background process.””

    The only reference to ARCHIVELOG mode howeve is the ARCn process.

    The bug reference number is 8513469.

  • john says:

    Hi
    I need to setup a three instance rac database.The datafiles and flash recovery area will be stored in ASM diskgroups called +data and +fra.
    Which is the best location options for archivelogs so that they can be accessed during recovery without dba intervention ?

    a-)
    cluster file system with each instance writing to a shared location
    or
    b-)
    cluster file system with each instance writing to a seperate location as long as all the locations are in directories under the same mount point

  • REDO Log and Checkpoint « Parul.info says:

    [...] v$logfile can be used to find the path of the redo log or their type (online/standby(used for standby database))For more information on standby redo logs :- http://www.pythian.com/news/581/oracle-standby-redo-logs/ [...]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>