Monitoring The Environment

In today’s article, I will tell you about Data Guard Broker – Monitoring The Environment.

After installing the Data Guard Environment and enabling the Broker Configuration, we need to check the operability of the system. Below are the DGMGRL commands to make these checks.

[Commands run from DGMGRL are all run from Primary-1 by connecting to DGMGRL]

1. Our first check query is to query the entire configuration.

From here;

Protection Mode,

Which DBs are included in the Broker Environment,

The status of Fast-Start-Failover,

We can see the general status of the configuration.

2. General checks can be made at the database level.

From here;

We learn the status of Redo Transport services.

We learn the status of the database.

From here;

The role of databases,

Whether or not Redo-Apply is active,

Redo and Transport Lag information,

Whether Real-Time-Query (Active Data Guard) is active for the Physical Standby Database,

On which instances the Apply Services are running,

We learn information about the status of databases.

3. If Database or Configuration status is ERROR, we create an error condition to see the parameters to be checked.

While the Broker Configuration is ENABLE, we are changing the parameters from SQLPLUS.

We are querying the Broker Configuration.

4. The error may not always be obvious as above. Below are the parameters we will look at in such cases.

We’re looking at the broker’s logs.

Note: It appears from the Error that the LOG_ARCHIVE_DEST_STATE_2 parameter is DEFERred.

We can see the error with the “StatusReport” property without looking at the log.

We also question other properties related to transport services.

LogXptStatus: Shows Transport Errors caught by all instances in the Primary Database.

InconsistentProperties: Lists if there is an inconsistent situation in the broker configuration and database settings.

InconsistentLogXptProps: Lists the inconsistencies between the Broker configuration and Redo Transport settings of parameters related to Redo Transport in Standby Databases.

5. Suppose there are many log switch operations until the error is noticed. In this case, we can see which archive files are not processed as follows.

We are doing the Log Switch operation for testing.

We check the Sequence numbers on the Primary and Standby sides.

It is seen that some archives do not go to the Physical Standby side. These archives are seen by querying the “SendQEntries” property.

In the STATUS column here,

CURRENT: Shows the Redo Log where currently writing is done.

ARCHIVED: Indicates that online Redo Log’s writing process is completed, local archive is created but archive is not sent to standby side.

NOT_ARCHIVED: Indicates that the online Redo Log’s writing process has been completed, but the local archive has not yet been retrieved.

We ENABLE the DEFERRED LOG_ARCHIVE_DEST_STATE_2 parameter and query the status of the configuration.

6. We first set up a test environment to learn about the property, where we can see which archives that come to the standby side are not APPLY. Then the relevant property is queried.

On the logic side, we question the status of Redo -Apply.

For double control, a table is created on the Primary side and it is seen whether it goes to the Standby sides.

On the logic side, we stop the Redo-Apply service.

[Logical-1]—>ALERT LOG

We are performing the Log Switch process so that archive logs can be created.

We are querying the Redo Apply status of the Logical Database.

We learn which archives are not applied.

The SQLPLUS command of the above DGMGRL query is below.

7. We learn the Transaction that causes Log Apply services to stop in Logical Standby Database as follows.

8. In the Logical Standby Database, the processes used by Log Apply services and the MAX_SGA information are learned as follows.

9. We see TopWaitEvents as follows.

This information is fetched from V$SYSTEM_EVENT View.

10. With the example below, we can see the situations where the broker configuration file and the SPFILE parameter file are inconsistent with each other.

We are querying the Broker Configuration.

We question what is causing the error.

We compare the values of the LogArchiveTrace parameter with the Broker configuration file in the Database.

We also check if this is the case by checking the values of the parameters.

We reset the parameter value in the Database.

We check if the configuration is healthy.