Oracle Data Guard 23ai provides a number of new VALIDATE options to check the preparedness or validity of Primary and Standby databases part of a Data Guard configuration before we perform a role change operation like a Data Guard Switchover.
- VALIDATE DATABASE STRICT ALL
- VALIDATE DGCONNECTIDENTIFIER
The VALIDATE DATABASE command performs a comprehensive set of database checks prior to a performing a Data Guard role change.
The VALIDATE DATABASE command gives the option of running the basic checks that indicate whether the specified standby database is ready for switchover or failover.
A Data Guard 23ai new feature is the ability to run a much more stricter level of checks using the various options available for the STRICT clause – like STRICT ALL.
The VALIDATE DATABASE clause is shown below.
VALIDATE DATABASE [VERBOSE] <db_unique_name>
[STRICT { ALL | APPLY_PROPERTY | FLASHBACK | FORCE_LOGGING | LOG_FILES_CLEARED |
LOG_FILE_CONFIGURATION | TEMP_FILES | TRANSPORT_PROPERTY }];The STRICT ALL option provides the use all of the strict options shown below in a single command for the validation of the primary and standby databases part of a Data Guard Configuration.
In the example below, we execute the VALIDATE DATABASE without the new STRICT ALL option.
It shows that the standby database is ready for switchover.
DGMGRL> validate database orcl_sb;
Database Role: Physical standby database
Primary Database: orcl
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
orcl : NO
orcl_sb: NO
The static connect identifier allows for a connection to database "orcl".
Parameter Settings:
Parameter orcl Value orcl_sb Value
DB_BLOCK_CHECKING FALSE FALSE
DB_BLOCK_CHECKSUM TYPICAL TYPICAL
DB_LOST_WRITE_PROTECT TYPICAL TYPICAL
But if we use the VALIDATE DATABASE with the STRICT ALL option we can see that the validation now shows that the database is not in a ready state for a Switchover!
DGMGRL> validate database orcl_sb strict all;
Database Role: Physical standby database
Primary Database: orcl
Ready for Switchover: No
One or more pluggable databases have save state enabled.
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
orcl : NO
orcl_sb: NO
The static connect identifier allows for a connection to database "orcl".
Parameter Settings:
Parameter orcl Value orcl_sb Value
DB_BLOCK_CHECKING FALSE FALSE
DB_BLOCK_CHECKSUM TYPICAL TYPICAL
DB_LOST_WRITE_PROTECT TYPICAL TYPICAL
We connect to the Container Database (CDB) and issue the ALTER PLUGGABLE DATABASE ALL DISCARD STATE command.
[oracle@perth bin]$ sqlplus sys as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Thu Nov 7 04:39:48 2024
Version 23.6.0.24.10
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Limited Availability
Version 23.6.0.24.10
SQL> alter pluggable database all discard state;
Pluggable database altered.
SQL> quit
Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Limited Availability
Version 23.6.0.24.10
Now the earlier execution VALIDATE DATABASE command with the STRICT ALL option which was showing that the database is not ready for a switchover – now it shows that all the validation checks have now been performed and the primary and standby databases are ready for a role change.
[oracle@perth bin]$ dgmgrl
DGMGRL for Linux: Release 23.0.0.0.0 - Limited Availability on Thu Nov 7 04:40:15 2024
Version 23.6.0.24.10
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/DreamLiner787##
Connected to "orcl"
Connected as SYSDBA.
DGMGRL> validate database orcl strict all;
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
orcl: NO
The static connect identifier allows for a connection to database "orcl".
VALIDATE DGConnectIdentifier is used to check network resolution, connectivity, password, and service names of the databases part of the Data Guard configuration.
DGMGRL> VALIDATE DGCONNECTIDENTIFIER orcl_sb
Warning: Current database session was authenticated using operating system credentials.
At instance 'orcl' of member 'orcl_sb'
'orcl_sb' translates to:
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sydney)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl_sb)))
Environment Variables:
TNS_ADMIN:
ORACLE_HOME: /u01/app/oracle/product/23.0.0/dbhome_1
ORACLE_BASE: /u01/app/oracle
GRID_HOME:
Initialization Parameters:
LOCAL_LISTENER:
Connection Test Result:
Connected to instance 'orcl' at member 'orcl_sb'
DGMGRL> VALIDATE DGCONNECTIDENTIFIER orcl
Warning: Current database session was authenticated using operating system credentials.
At instance 'orcl' of member 'orcl'
'orcl' translates to:
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = perth)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
Environment Variables:
TNS_ADMIN:
ORACLE_HOME: /u01/app/oracle/product/23.0.0/dbhome_1
ORACLE_BASE: /u01/app/oracle
GRID_HOME:
Initialization Parameters:
LOCAL_LISTENER:
Connection Test Result:
Connected to instance 'orcl' at member 'orcl'
