Data Guard standby redo log management Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard- Log Management Services

A Sample Log Management Policy

These attempts will be made at an interval of 90 seconds. If the archival is not successful, the log transfer service will try to archive the log files on volume /oracle2. The Oracle instance on the local host is dependent on the success of archival in archive destination #1 as specified by the DEPENDENCY attribute.

Archival to the Oracle instance on the remote host is optional; however, the log transfer service will make five attempts to archive the redo logs to this destination.

Depending on the data protection mode and the performance requirement of the environment, the DBA can choose from the list of attributes available for the log_archive_dest_n parameter and build a log management policy that suits the environment.

Standby Redo Logs

Standby redo logs are equivalent to online redo logs on a physical standby database. When standby redo logs are created on the standby site, the log transfer service updates the standby redo logs, which is then archived in the form of archive redo logs by the archiver process running on the standby site. Hence, the ARCH or ARCn process must be started on the standby database if the standby redo logs are in use.

The way that the recovery process works is much the same.  The Managed Recovery Process reads the archived redo log and applies the changes on to standby database. For maximum protection and maximum availability data protection mode, the standby redo logs must be created and the log writer configured on the primary database to participate in the log transfer.

Standby Redo Logs

In order to avoid any wait in reuse of standby redo logs, it is recommended that one more standby redo log group be retained than the number of online redo log groups configured on the primary database. The maximum number of standby redo log groups and members per group can not exceed the maxlogfiles and maxlogmembers parameters specified during the creation of the primary database.

Additionally, the size of standby redo logs should be same as that of online redo logs of the primary database; otherwise, RFS on the standby site will not use the standby redo logs.

Standby redo logs are not created automatically during the Oracle instance creation, so they should be created when the data protection mode is changed to maximum protection or maximum availability. Executing the following SQL statement on standby database can create these logs:

ALTER DATABASE ADD STANDBY LOGFILE GROUP <n>
'filespec' SIZE 'size' K|M;

filespec is the full filename of log member, size is the size of log file, and <n> is the log group number.

A redo log member can be added to the existing standby redo log group using the following statement:

ALTER DATABASE ADD STANDBY LOGFILE MEMBER 'filespec' TO GROUP <n>;

After adding the standby log file, the script, stdby_log.sql, from code depot can be used to verify it:

tandby Redo Logs

 
 
Set linesize 90
Column Group# Format 99
Column Filename format a50 Trunc
Column Logtype format a7
Column Status format a7
Column SizeinBytes format 999999999
Select
   s.GROUP# Group#,
   l.MEMBER Filename,
   l.TYPE Logtype,
   s.STATUS Status,
   s.BYTES SizeinBytes
from
   V$STANDBY_LOG s,
   V$LOGFILE l
SEE CODE DEPOT FOR FULL SCRIPT

The following is a sample output from stdby_log.sql script.:

GROUP# FILENAME                        LOGTYPE STATUS     SIZEINBYTES
------ ------------------------------- ------- ---------- -----------
     4 /oracle/stdbydb/redo/redo04.log STANDBY UNASSIGNED     1048576
     5 /oracle/stdbydb/redo/redo05.log STANDBY UNASSIGNED     1048576

In the next section, information other attributes of log transfer service that are useful for the configuration of various data protection modes will be presented.

Description of remote_archive_enable Parameter

The remote_archive_enable initialization parameter controls the transfer and acceptance of redo data in Standby Database configuration. It can take four different values: TRUE; FALSE; SEND; and RECEIVE.