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 database.

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 perspective, but 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 operations. 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 to Monitor

The other effective method use to monitor the Oracle instance 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:

• v$log
   

• v$logfile
   

• v$thread
   

• v$recover_file
   

• v$datafile
   

• v$database

Using Static Views to Monitor Data Guard Standby

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:

• dba_tablespaces
   

• dba_data_files

Monitoring Logical Standby Databases

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 unusable 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 Sites

Like physical standby databases, the following means can be used to monitor the recovery progress of logical standby databases:

• Alert Log file
   

• Dynamic Performance View
   

• Static view (dba_*) Views

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.