Physical Standby Database monitoring Tips
Oracle Database Tips by Donald BurlesonDecember 9, 2015
Monitor a Data Guard Physical Standby Databases
Periodically, the Data Guard configuration will
have to be monitored to check the health of standby databases. In
this section, the tools for monitoring the physical Oracle instance
and finding the progress of the recovery process will be presented.
Tools for Monitoring Standby Databases
The following tools can be used to monitor a
physical standby database:
Alert log file on the primary and
the standby site.
Dynamic performance view on the
primary and the standby database.
Static view on the primary
Using Alert Log to Monitor a Data Guard Physical Standby Database
The alert log file on the primary database
keeps a wealth of information related to the changes taking place on
the primary site. All of the information available in the alert log
file may not be relevant from the Oracle instance
the DBA can mine the alert log file to find the operations that can
affect the standby database. Some of these operations include:
Changes in the Control file.
Changes in Redo Log files.
Changes in Data files.
Changes in Tablespace status.
In addition, the alert log on the standby site
holds useful information pertaining to Oracle instance
The log_archive_trace parameter controls the level of information
related to standby operations that will be available in the alert
log file and other associated trace files. The usual setting of
log_archive_trace is 127 and will produce messages related to the
log transfer and the log apply service in the alert log file.
Using Dynamic Performance View
The other effective method use to monitor the
is querying the fixed views. These views hold
information about the changes on the primary database as well as on
the standby database. The following fixed views are useful for
monitoring the events affecting the standby database:
Using Static Views to Monitor Data Guard
The static views are dba_* views. These are
specifically useful for finding changes in tablespaces and datafiles
on the primary database. When the Oracle instance
is in recovery
mode, the static views cannot be queried. The following static views
on the primary database can be queried in order to find the changes
in datafiles and tablespaces:
Monitoring Logical Standby
Once a logical standby is setup, it does not
require a great deal of monitoring. However, keep an eye on the
recovery progress to ensure that the data in the logical standby
database is as current as in the primary database. A backlog in the
recovery process will leave the logical Oracle instance
for reporting, and will cause delay during activation if the DBA
opts to failover or switchover to this logical standby site. In this
section, details on the recovery progress and the tools available
for monitoring will be presented.
Tools to Monitor Logical Standby
Like physical standby databases, the following
means can be used to monitor the recovery progress of logical
The alert log file on the logical standby site
contains useful information about the changes in the logical site.
It also contains messages about the transactions that could not be
applied on database. The output in the alert log file is controlled
by the log_archive_trace parameter. If there is a problem in the
database, set this parameter to produce an extensive output. The
disadvantage of the alert log is that the information is not
organized, and the DBA will have to search through a vast number of
messages to find the needed information. Nevertheless, when no other
tool such as database views can be used, the alert log is the only
companion the DBA has.