DBA Tips Archive for Oracle


Standby Database Maintenance

by Jeff Hunter, Sr. Database Administrator


  1. Introduction
  2. Checking Destination Status
  3. Checking Archive Redo Log Status


This article provides miscellaneous notes for maintaining Oracle's standby database.

The Standby Database feature of Oracle (Oracle7 and higher) allows the DBA to maintain a duplicate, or standby, copy of a database at a remote site to provide continued primary database availability in the event of failure. The standby database is created with a special copy of the control file from the primary database. The standby database is kept in close synchronization with the primary database by applying the primary database's archived log files to the standby database. It is therefore necessary to operate the primary database in Archivelog Mode to avail the benefit of a standby database.

A standby database is generally used where high availability of data is required. A standby database is one way of quickly providing access to data if the primary database fails and recovery will take longer than the desired time.

Checking Destination Status

Before delving into parameter details, it is important to understand the concept of "state". How Oracle treats a particular destination with respect to current and future archive activities is driven by the "state" of that destination. The "state" in turn is determined by the combination of values of three non-persistent characteristics. These characteristics are:

The characteristics above combine to result in a state shown in the table below where "T" means true and "F" means false. The current state of a destination is contained in v$archive_dest.

Valid Enabled Active State     Remark
----- ------- ------ ------    ----------------------------------------------------------------
  F     N/A    N/A   Inactive  Destination was not defined        - not used for archive
  T      T      T    Valid     Destination defined, enabled, no current errors           
  T      F      T    Deferred  Set or initialized as "defer"      - not used for future archive
  T      F      F    Disabled  Manually disabled following error  - not used for archive
  T      T      F    Error     Error during archive               - not used for future archive


The enabled/disabled characteristic noted above is defined by the log_archive_dest_state_n parameter. The possible values are "enable" or "defer", with the default value being enable. Enable could be interpreted to mean for Oracle to use the destination, and defer to mean for Oracle not to use it. This parameter can be changed dynamically at any time via "alter system" to get Oracle to start using the destination. This is an attractive way of pre-defining an emergency local destination.

For example, if log_archive_dest_state_3=defer was defined in the init<SID>.ora, and the log_archive_dest_3 destination was defined as a local directory, it would not be used for archiving, but would show up in v$archive_dest in deferred state. However, entering "alter system set log_archive_dest_state_3=enable" and starting an archive after failure would cause Oracle to write to this destination and would help to fulfill log_archive_min_dest_succeed requirements only.

The other user controlled characteristic in the state table shown above is valid/invalid, and this is determined by defining a log_archive_dest_n parameter. As with log_archive_dest_state_n, there are five available parameters to use, represented by the 1 through 5 values of the n suffix.

This parameter a space delimited string that is parsed at startup. The string must contain a destination, and that may be followed by optional attributes. The general format is log_archive_dest_n="destination-clause attribute1-clause attribute2-clause".

If a log_archive_dest_n is defined in init<SID>.ora, it must contain a destination option. There are two forms of this option, location= and service=. The service= option denotes a remote standby database host, and should be a Net8 Service Name. The location= option denotes a local destination and should be a valid directory in which Oracle has write permissions. There is no default value for the destination clause. If it is not supplied, an "ORA-16027: parameter log_archive_dest_n is missing a destination option" is received during startup.

For example, log_archive_dest_3="service=FARHOST" would define a remote destination. The Net8 Service Name "FARHOST" would have to be defined in the network files to provide the necessary network support.

In addition to the required destination option, log_archive_dest_n accepts three different optional attributes: reopen=, and optional/mandatory.

Reopen= n sets the number of seconds before Oracle will retry the archive from the beginning to a failed destination. This parameter applies to problems with creating the file, local or remote. The default behavior is to put the destination into "error" status and not retry the archive. If retry is successful, the destination is taken out of error status.

The optional/mandatory option sets archival success to a destination as a condition for reuse of a redo log group. The default is optional, that is, success is not required. Any log_archive_dest_n with an attribute of "mandatory" MUST complete for the redo log to be reused, regardless of any other parameter or any other log_archive_dest_n that completes.

For remote destinations, the optional/mandatory attribute of log_archive_dest_n is the only factor Oracle uses with respect to that destination when determining if a redo log can be used again.

But for local destinations, Oracle also considers the log_archive_min_succeed initialization parameter. This parameter, which defaults to 1, has been reinterpreted to mean the number of LOCAL destinations that must succeed to allow reuse of a redo log group. If there is a logical discrepancy between the number of local destinations defined as mandatory and the number of local successes required by log_archive_min_succeed, the strictest condition applies. In other words, a log_archive_min_succeed parameter may cause a local destination defined as "optional" to become "mandatory", but it may not cause a local "mandatory" destination to become "optional". Log_archive_min_succeed may not be set higher than the number of local defined destinations, and it may not be less than 1. In archivelog mode, Oracle requires that one local archive succeed no matter what.

Example 1
A typical implementation might be to define several local destinations to provide local archive log duplexing, and one remote destination to supply an archive for a standby database.
log_archive_dest_1="location=/u02/archdata reopen=300"   
log_archive_dest_2="location=/u05/archdata reopen=300"
log_archive_dest_3="service=STBYHOST reopen=300"
Here there are two local destinations defined and one remote. All destinations are enabled by default as there are no log_archive_dest_state_n entries. All destinations are defined as "optional" by default. However, one or the other of the two local destinations must complete to reuse the redo log group because log_archive_min_succeed is 1, and in any case, Oracle requires that at least one local destination succeed in archivelog mode. If log_archive_min_succeed had been set to 2, then both of the local "optional" destinations would have to successfully archive to allow redo log reuse and would therefore become "mandatory". Log_archive_min_dest_succeed could not have been set to 0, and it could not have been set to 3 since there are not that many local destinations available.

The local destinations should preferably point to directories located on separate disks off of separate controllers in separate cabinets. The point would be to avoid a single point of failure. Since both destinations are defined as "optional" and only one must succeed, the possibility of having the database "hang" do to archive failure is remote.

Log_archive_3 is defined as a remote destination because of the service being used in the destination option instead of location. STBYHOST is a Net8 Service Name(a.k.a. alias in tnsnames.ora), that points to the host where the standby database resides. This destination is "optional", and, since log_archive_min_succeed does not affect it will have no affect on redo log file reuse. In view of the variables involved in sending files to a remote, such as status of the network and the remote host, it would seem prudent to make remote destinations "optional" to prevent the database from hanging after running out of redo log files.

Example 2
Not too practical, but good for discussion.
log_archive_dest_1="location=/u02/archdata mandatory"
log_archive_dest_2="location=/u05/archdata mandatory"
log_archive_dest_5="service=STBYHOST mandatory"
log_archive_min_succeed only requires that two local destinations succeed, so if destination 2 and destination 4 succeeded, its' requirements would be fulfilled. But destinations 1 and 5 would still have to succeed because they are defined as mandatory.

Destination 3 is deferred and will not be used for archiving until it is dynamically enabled by "alter system set" or "alter session set". If enabled and an archive started, it could be used to fulfill log_archive_min_dest_succeed requirements, but destinations defined as "mandatory"(1,2 and 5) would still have to succeed!

Checking Archive Redo Log Status

The usual displays about archivelog mode like archive log list, v$log, v$archived_log, and v$log_history apply. The most important display to show complete information about archive destinations, and to help analyze errors is v$archive_dest. See the Oracle 8 reference for a complete description. Some of these are deserve further discussion here.

The Status and what combinations of characteristics create a status were discussed in an earlier session. If there has been a problem with an archive destination, this column should be in "error".

The Fail_date, Fail_sequence, and Error fields should be populated if an archive has erred. Fail_sequence gives the sequence number that failed, and Error provides an actual ORA message indicating the cause of error.

The Binding column is important because it tells what destinations have been explicitly defined as mandatory. As stated previously mandatory destinations must complete. More specifically, if log_archive_dest_3 is defined as mandatory, log_archive_dest_3 must complete. Subsequently adding another destination, say log_archive_dest_5, will help satisfy the log_archive_min_dest_succeed requirement, but not the requirement for log_archive_dest_3 to complete.

Name_space will tell whether this is a session level or system level display. The log_archive_dest_state_n and log_archive_dest_n parameters can be dynamically redefined by "alter system set" and "alter session set" commands. The alter session command only modifies the parameters for that session, and would only be used by foreground processing initiated from that session only. The real time parameters remain intact and ARCH would use these. The alter system command would last for the life of the instance and be used by foreground and background processing in that session and all others. It is more straightforward to use the "alter system" commands.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 21-Jun-2007 16:32:12 EDT
Page Count: 30162