Verify readiness of Database switchover using DG Validate command ~ Dilli's Oracle/MySQL Linux Blog

Oracle Dataguard ensures high availability, data protection, and disaster recovery for Oracle database. Data Guard provides a comprehensive set of services for DR solution. The services can be used to create, maintain, manage, monitor one or more standby databases, and validate the database for the readiness of role switch. Data Guard can switch any standby database manually or automatically in case the production database becomes unavailable due to a planned or unplanned outage. Data Guard minimizes the downtime associated with the outage.

Oracle Data Guard is included with the Enterprise Edition and Personal Edition of the Oracle database software. We can manage an Oracle Data Guard configuration by using either SQL*Plus, the Oracle Data Guard broker's command-line interface (DGMGRL), or a compatible version of Oracle Enterprise Manager Cloud Control (Cloud Control).

Validate command has a set of options used to check a comprehensive set of checks prior to role change. This command is used to verify the readiness of switchover and to validate the standby database is ready to failover and switchover anytime. Validate command was first introduced in Oracle database 12.1.0.0. New commands like validate database spfile, validate network configuration, validate static connect identifier was introduced in Oracle Database Release 18c, Version 18.1. For more details click here.

Environment:

Docker Image: dillimaharjan/oracle19c

Docker Container: ora19cp, ora19cs

Oracle Database version: 19.3.0.0

Oracle Database Edition: Oracle Database 19c Enterprise Edition

Container database: False

In my case, there are docker images stored in my repo for different versions of the database. I have executed docker image command to list all available images. oracle19c is a docker image with fresh Oracle 19.3.0.0 installed and ready to use.

docker image ls | sort

Start docker container using the docker run command. The following are a brief description of the option being used.

-i : Interactive mode

-t : Allocate a pseudo-TTY

-d : Detach once container is started

--privileged : Give an extended privileges to the container

-v : Bind mount a volume. OS directory will be mounted to the specified mount point in the container.

-p : Publish a container port to the host

--hostname : Host name of the container.

--name : Name of the container.

docker run -i -t -d --privileged -v /Users/dilli/Docker/shared:/shared -p 15219:1521 --hostname ora19c_primary --name ora19cp dillimaharjan/oracle19c

docker run -i -t -d --privileged -v /Users/dilli/Docker/shared:/shared -p 15220:1521 --hostname ora19c_standby --name ora19cs dillimaharjan/oracle19c

List running docker containers with docker ps command.

docker ps

Run bash in a running container to get bash terminal of the container.

docker exec -it ora19cp /bin/bash

docker exec -it ora19cs /bin/bash

Physical standby and the DG have been configured for those docker containers. If you need any help to configure physical standby or DG. Please refer to the links below.

Click here for Step by step Physical standby configuration.

Click here to Configure the Data Guard.

Once DG configuration is completed, the configuration looks like below. The command show configuration will list the primary and standby database along with the FSFO and configuration status.

show configuration.

1. Validate Database

The validate database command is used for a comprehensive set of database checks prior to a role change. Role change from Primary to Standby and from Standby to Primary. This command is used to verify the readiness prior to the switchover.

Syntax:

VALIDATE DATABASE [VERBOSE] <database-name>;

Validating primary database.

The Ready for Switchover is Yes, which means the database has no issue and ready to switchover anytime.

validate database accdb;

Verbose option will reveal more details about the database.

validate database verbose accdb;

Validating standby database.

The Ready for Switchover and the Ready for Failover both have value Yes, which means the database is ready for switchover and failover.

validate database accstdb;

validate database verbose accstdb;

2. Validate database datafile

The validate database datafile validates data files across the primary and standby database. Option ALL can be defined to validate all the datafiles on either of the database or all databases. OUTPUT=<output_file_name> is mandatory and command dumps output to the file. Output should not contain any directory. The output file will be store in the trace directory.

Syntax:

VALIDATE DATABASE [database-name | ALL] DATAFILE [datafile-name | datafile-number | ALL] OUTPUT="<output file name>";

List the available data files in the database.

select file# from v$database;

If you specify the directory in the output file it will return error: DGM-17138: OUTPUT should not contain a directory. The output file will be saved in the trace file location.

Validate datafile # 1 in database accdb and save all output to accdb_dbfile_1.txt file in the trace location.

Additionally, validate all datafiles in accdb database, validate datafile # 1 in accstdb, and validate all datafiles in all databases.

validate database accdb datafile 1 output='accdb_dbfile_1.txt'

validate database accdb datafile all output='accdb_all_dbfiles.txt'
validate database accstdb datafile 1 output='accstdb_dbfile_1.txt';
validate database all datafile all output='all_datafiles.txt'

Sample content of the output file.

Client is connected to database: accdb. Role: primary database.

Remote database accstdb.remote db role: physical standby

Slave Id 0

Summary:

*******************************************************************************

TOTAL: total no. of blocks found

+--------+------------+-------+---------+---------+

| | | | |

| DIFFV: LOST_WRITE | CORR: corrupted blocks

SAMEV diff ver | SKIPPED:

| block pairs +--+--+ direct load, empty blocks,

+--+--+--+ | | RMAN optimized blocks,

| | | same ver & | LWLOC: lost writes at local db

| | | same checksum & LWRMT: lost writes at remote db

| | | same contents

| | |

| | SAMEV_NO_CHKSUM: same ver & same contents but diff checksum

| | (checksum can be diff but identical contents)

| |

| DIFFPAIR: same ver but differrent contents (data inconsistency)

ENCERR: undecided block pairs due to encryption related issue

(e.g. when Wallet is not open)

ID TOTAL CORR SKIPPED DIFFV SAMEV SAMEV&C ENCERR LWLOC LWRMT DIFFPAIR

00 0039190 0000 0039189 0000001 0000000 0000000 0000000 000000 000000 0000000

02 0042599 0000 0000000 0000005 0042594 0042566 0000000 000000 000000 0000000

06 0126447 0000 0027918 0000286 0098243 0098224 0000000 000000 000000 0000000

14 0000001 0000 0000000 0000000 0000001 0000001 0000000 000000 000000 0000000

3. Validate database spfile

The validate spfile command validates the difference in spfile between the primary and the standby database. Executing this command prior to switchover lists out all the parameter differences. Setting parameters of primary and standby exactly the same may prevent performance issues after switchover. If all the parameter values are the same then "No parameter difference found." message will be displayed.

Syntax:

VALIDATE DATABASE [verbose] {standby database-name} spfile;

Note: You should log in as user sys to execute following command

If you log in with os authentication in dgmgrl then the following error will be displayed

Command requires a connection that uses database or external credentials.

Note: Validate spfile command cannot be executed for the Primary database. The following error will be encountered if you execute validate spfile for the primary database.

This command cannot be used for the primary database.

Validate database <standby database> spfile;

Verbose option will display matched and unmatched parameter values.

4. Validate FAR_SYNC

The validate far_sync validates far sync instance. It performs a set of checks for a far sync instance.

Syntax:

VALIDATE FAR_SYNC VERBOSE] far_sync_instance_name [WHEN PRIMARY IS <primary db_name>]

5. Validate Network configuration.

The validate network configuration command checks the network configuration between members. It performs network connectivity checks and reports for any issues.

Syntax:

VALIDATE NETWORK CONFIGURATION FOR <ALL | MEMBER NAME>;

In my case the hostname of standby is not resolvable from Primary and hostname of Primary is not resolvable from Standby because these settings are missing on my /etc/hosts file. So I have encountered the following error.

Once my error is resolved the validate command passed.

validate network configuration for all;
validate network configuration for accdb;
validate network configuration for accstdb;

6. Validate static connect identifier.

The validate static connect identifier validates the static connect identifier of a database. Static connect identifiers are required to start the database during the switchover.

Syntax:

VALIDATE STATIC CONNECT IDENTIFIER FOR <ALL | DATABASE NAME>;

In my case I have not set <server_name>_DGMGRL static server name in the listener configuration that is why I am getting error ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.