The Advantage of having Standby Redo Logs (SLR) is that every records written into the Online RedoLogs of the Primary Database is transferred to the Standby database and written into the Standby Redo Logs at the same time.
therefore, you it minimizes the probability of Data Loss on the Standby Database.

Without Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed,
this Archived Redo Log is applied to the Standby Database by the MRP process . An incomplete ArchiveLog file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.

If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log ,when there record entered in online redolog and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log, while the MRP process applies the information to the Standby Database. In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.

NOTE:

  • Size of standby redolog should be same as that of primary online redolog.
  • The standby redo log must have at least one more redo log group than the redo log at the redo source database.
  • Standby redolog is mandatory for realtime apply
  • In 12c DEFAULT MRP will go to REAL TIME APPLY mode.
  • Oracle recommends to keep standby redologs on both primary and standby database.(Though SLR is not mandatory for primary, it will make the switchover quick without any additional DBA activity.

 

Steps to add standby redolog:

1. If you are setting up standby database newly, i.e standby database is not created yet, then add the standby redologs on primary database, So that after duplicating standby database from primary, similar standby logs will be created in standby.

Just add the SLR on primary:

alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo1.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo2.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo3.log' size 2G;

 

2. If the standby database is already created without standby redolog. Then add the standby redolog on both primary and standby db as below.

Primary:

alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo1.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo2.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo3.log' size 2G;

 

Standby database:

cancel recovery:

alter database recover managed standby database cancel;

Make standby_file_management Manual:

alter system set  standby_file_management='MANUAL' scope=both;

add standby redolog:

alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo1.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo2.log' size 2G;
alter database add standby logfile '+PROD/stdby/onlinelog/standbyredo3.log' size 2G;

Revert standby_file_management to AUTO:

alter system set  standby_file_management='AUTO' scope=both;

Start MRP with real time:

alter database recover managed standby database using current logfile disconnect from session;