DatabaseVerse: Journey into the World of Databases

There is a lot to tell. Data Guard has been improved a lot in Oracle 23ai version. It’s now so simple that with just a little bit of knowledge, anyone could easily set up a configuration and protect your database!

But is this for real? Well, let’s take a look at the new features introduced!

1. Automatic preparation of the primary
2. New views to monitor Data Guard and getting broker info
   1. V$DG_BROKER_PROPERTY
   2. V$DG_BROKER_ROLE_CHANGE
   3. V$FAST_START_FAILOVER_CONFIG
   4. V$FS_LAG_HISTOGRAM
3. New DGMGRL CLI Commands!
   1. VALIDATE DGConnectIdentifier
   2. EDIT ALL MEMBERS [SET|RESET] PROPERTY | PARAMETER
4. Conclusion

Automatic preparation of the primary

We now have a handy command that was actually introduced in Oracle 21c but has been improved in 23ai, and it will help with the preparation of the primary database. We normally do all this kind of task/operations using sqlplus, but now we can do it all at once using the Data Guard command-line interface (DGMGRL), which is quite nice.

dgmgrl /
help prepare 

PREPARE DATABASE FOR DATA GUARD command configures a database for use as a primary database in a Data Guard broker configuration. Database initialization parameters are set to recommended values, but what are these “recommended values”?

Well, parameters values are set as per the values recommended for the Maximum Availability Architecture (MAA):

• DB_FILES=1024
• LOG_BUFFER=256M
• DB_BLOCK_CHECKSUM=TYPICAL If this value is already set to FULL, the value is left unchanged.
• DB_LOST_WRITE_PROTECT=TYPICAL If this value is already set to FULL, the value is left unchanged.
• DB_FLASHBACK_RETENTION_TARGET=120 If this parameter is already set to a non-default value, it is left unchanged.
• PARALLEL_THREADS_PER_CPU=1
• DG_BROKER_START=TRUE

Also:

• This command enables archivelog mode, enables force logging, enables Flashback Database, and sets the RMAN archive log deletion policy to SHIPPED TO ALL STANDBY.
• If standby redo logs do not exist in the primary database, they are added. If the logs exist and are misconfigured, they are deleted and recreated.

Sounds, pretty nice uh? Now, let’s take a look to the PREPARE DATABASE FOR DATA GUARD command parameters, and we have:

• db_unique_name: The value for the DB_UNIQUE_NAME parameter. If a different value is set, it is replaced with db_unique_name. If not specified, it takes the value of the DBNAME parameter.
• directory_location: The directory name for the DB_RECOVERY_FILE_DEST initialization parameter, which represents the fast recovery area location. The specified directory must be accessible by all instances of a RAC database. This parameter can be omitted if a local archive destination is set. However, if the DB_RECOVERY_FILE_DEST initialization parameter has not been set and no local archive destination has been set, specifying this parameter is mandatory. If directory_location is specified, the log_archive_dest_n parameter is set to USE_DB_RECOVERY_FILE_DEST, regardless of any existing local archive destination.
  • size: A size value for the DB_RECOVERY_FILE_DEST initialization parameter. This parameter is mandatory if the DB_RECOVERY_FILE_DEST is specified.
• broker_config_file_1_location and broker_config_file_2_location: Locations for the DG_BROKER_CONFIG_FILEX initialization parameter. The specified files must be accessible by all RAC database instances. These are optional command parameter.
• restart: The RESTART keyword allows for automatic restart of the database if any static initialization parameters require a change, or if the database requires to be in MOUNTED mode to enable archive log mode for example. If omitted, and any static changes are required, the command will fail.

Prerequisites

You must connect to the primary database as a user with the SYSDBA privilege, example “dgmgrl / as sysdba“, “dgmgrl sys” “dgmgrl sys@tns_entry“

Other things to keep in mind:

• Database versions starting from Oracle Database 12c Release 2 are supported.
• For a single-instance database, if a server parameter file does not exist, it is created using the current in-memory parameter settings and stored in the default location.

Let’s check it out!

PREPARE DATABASE FOR DATA GUARD 
WITH 
DB_UNIQUE_NAME IS DBTEST_mr7_fra
DB_RECOVERY_FILE_DEST IS "+RECO"
DB_RECOVERY_FILE_DEST_SIZE IS "250G"
BROKER_CONFIG_FILE_1 IS "+DATA/DBTEST_MR7_FRA/DGCONFIG/dr1dbtest.dat"
BROKER_CONFIG_FILE_2 IS "+DATA/DBTEST_MR7_FRA/DGCONFIG/dr2dbtest.dat"
;

So it has failed, but this is good, and I will tell you why this is good. In Oracle 21c, if you do this… it will restart your database and that would be kind of catastrophic if you are preparing a production database… right?

In 23ai, Oracle has added an additional keyword “restart” mentioned before. The restart keyword basically specifies that you are good with the restart of the database; if you don’t specify it as I did in the example above, then the broker will tell you, “Hey, I can’t proceed for whatever X reason.”

So, make sure you are good to go with the restart of the database before adding the “restart” keyword; let’s execute the command again, adding the keyword:

PREPARE DATABASE FOR DATA GUARD 
WITH 
DB_UNIQUE_NAME IS DBTEST_mr7_fra
DB_RECOVERY_FILE_DEST IS "+RECO"
DB_RECOVERY_FILE_DEST_SIZE IS "250G"
BROKER_CONFIG_FILE_1 IS "+DATA/DBTEST_MR7_FRA/DGCONFIG/dr1dbtest.dat"
BROKER_CONFIG_FILE_2 IS "+DATA/DBTEST_MR7_FRA/DGCONFIG/dr2dbtest.dat"
RESTART
;

As you can see above, I have added the restart keyword and the dgbroker has restarted my database, set all the necessary parameters, and opened it; we can also see the details in the alert log:

Restart of the database instance:

Setting parameters:

Continuation of setting parameters and also we can see that standby redo logs has been created as well:

This is wonderful! before 21c you had to do this manually and the other good thing is that in 23ai you have a better control now with the restart keyword

New views to monitor Data Guard and getting broker info

23ai has introduced four new fixed views.

V$DG_BROKER_PROPERTY

This is a new view introduced in Oracle Database 23ai, contains the properties of the configuration and all the members

set linesize 250
set pagesize 500
col value for a60
SELECT member, dataguard_role, property, property_type, value, scope, valid_role FROM v$dg_broker_property;

V$DG_BROKER_ROLE_CHANGE

V$DG_BROKER_ROLE_CHANGE displays information about the past role changes across a Data Guard broker configuration. The role change history is maintained in the configuration metadata. It keeps up to 10 records of the latest role changes. This view is also replacing V$FS_FAILOVER_STATS as it adds the reason for the fast-start failover so Oracle has deprecated it in Oracle 23ai.

set linesize 250
col begin_time for a35
col end_time for a35
select begin_time, end_time, event, standby_type, old_primary, new_primary FROM v$dg_broker_role_change ORDER BY begin_time;

V$FAST_START_FAILOVER_CONFIG

Query V$FAST_START_FAILOVER_CONFIG view on the primary database to display statistics about the fast-start failover configuration

set linesize 250
col protection_mode for a30
col lag_type for a30
SELECT fast_start_failover_mode, status, threshold, protection_mode,  lag_limit, lag_type FROM v$fast_start_failover_config;

V$FS_LAG_HISTOGRAM

V$FS_LAG_HISTOGRAM displays statistics that are based on the frequency of range of Fast-StartFailover lag time. Lag time is assessed by the primary database every minute only, and no rows are shown if Fast-Start-Failover is disabled

 select * FROM V$FS_LAG_HISTOGRAM;

New DGMGRL CLI Commands!

New commands has been introduced in Oracle 23ai to make our life easier! Let’s review them:

VALIDATE DGConnectIdentifier

VALIDATE DGConnectIdentifier help us to check network resolution, connectivity, password, and service name from the database

 VALIDATE DGCONNECTIDENTIFIER XX;

SHOW ALL MEMBERS PROPERTY_NAME | PARAMETER_NAME

The SHOW ALL MEMBERS (Property)|(Parameter) command displays the value of the specified property or parameter for all members in the configuration.

--PROPERTY
SHOW ALL MEMBERS NETTIMEOUT
--PARAMETER
SHOW ALL MEMBERS PARAMETER log_archive_trace

EDIT ALL MEMBERS [SET|RESET] PROPERTY | PARAMETER

The EDIT ALL MEMBERS SET command changes the value of the specified parameter/property for all members in the broker configuration. Optional ALTER SYSTEM SET command options can be included. These options must be specified in quotation marks.

--PARAMETER
EDIT ALL MEMBERS SET PARAMETER <parameter_name>=<value> ["optional ALTER SYSTEM SET clauses"];
--EXAMPLE:
EDIT ALL MEMBERS SET PARAMETER log_archive_trace=255;
--PROPERTY
EDIT ALL MEMBERS SET PROPERTY <property_name>=value;
--EXAMPLE:
EDIT ALL MEMBERS SET PROPERTY 'NetTimeout'=45;

Conclusion

In summary, the enhancements introduced in Oracle Data Guard 23ai significantly streamline the process of configuring and managing high availability for databases. With features such as the PREPARE DATABASE FOR DATA GUARD command, new views for monitoring, and new DGMGRL commands, the complexity of setting up Data Guard has been reduced to a much simpler process. These improvements not only enhance efficiency but also lend more control to database administrators with the new restart keyword, allowing for safer operations without the risk of unintended disruptions, such as unwanted database restarts. As the landscape of database management continues to evolve, Oracle 23ai exemplifies a commitment to user-friendly interfaces, ultimately empowering users to protect their databases with confidence.

There is more I want to talk about… so make sure to stay tuned for the second part!