Tales From A Lazy Fat DBA

What are Standby Redo Log Files and why they are important ?

Recently asked by one of my good Fried Sowmya about some internals of Standby Redo Log Files and soon i found myself in bit of soreness. So, here i decided to pt some light over this very basic thing if one has to start with Oracle Data Guard.

Standby Redo Logs: They are very similar to Online Redo but with the only difference that S’by Redo Logs are used to contain redo data from Primary DB. S’by Redo Logs are only used if you are using Log Writer as the log transport medium to Standby Site.

When we use Standby Redo Log Files:
*  When using Real-Time Apply service.
*  If using Maximum Protection or Maximum Availability Models.

Points To Remember:
* You should create standby redo logs on both the standby and the primary database which is helpful in case of switch-overs (But is not mandatory for Primary Database)
* The standby redo logs should be at least as big as the largest online redo log .
* You can create Standby Logs after creating Standby Environment.

* In case of disaster and complete loss of the primary database, the entire redo log worth of data will not be lost because the standby redo log file on standby site will contain some or all of the transactions contained in the lost redo log file. This has been one of the most useful enhancements in Data Guard technology, as it minimizes the loss of data while operating in MAXIMUM PERFORMANCE mode.

Example:
Creating Standby Redo log Files on
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo01.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo02.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo03.log’) SIZE 50M;

SQL> select group#, member, type from v$logfile;

GROUP#                          MEMBER                                                                            TYPE
———-    ———————————————————————————————————————————–—
4       /u01/app/oracle/oradata/DB11G/standby_redo01.log                      STANDBY
3       /u01/app/oracle/oradata/DB11G/standby_redo02.log                      STANDBY
5       /u01/app/oracle/oradata/DB11G/standby_redo03.log                      STANDBY

Here i’ve added a standby logfile on Std DB end:

ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo01.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo02.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo03.log’) SIZE 50M;

Pic Credits: Google/Oracle.com