A large portion of Oracle customers relies on Data Guard as their disaster recovery solution. With Data Guard, customers can achieve near real-time replication of the database, from the primary to the standby. Now, with Oracle Database 19c, even more innovative features have been added to Data Guard.
Industry experts claim that Active Data Guard-DML Redirection is the best new feature introduced in Oracle Database 19c. ADG DML redirection allows data manipulation language (DML) in the form of inserts, updates, and deletes to be executed against the standby database. Incidental DML operations can be run on Active Data Guard standby databases. This allows more applications to benefit from using an Active Data Guard standby database when some writes are required. On execution, the DML operation is passed to the primary database.
DML redirection helps in load balancing between the primary and standby databases. When an incidental DML is issued on an Active Data Guard standby database, the update is passed to the primary database, where it is executed. The resulting redo of the transaction updates the standby database, after which control is returned to the application.
DML operations on standby can be transparently redirected to and run on the primary database. This includes DML statements that are part of PL/SQL blocks. The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby.
DML redirection on the standby database requires the Active Directory license, and the COMPATIBLE database initialization parameter must be set to 19.0.0 or higher. The database must be in open mode with archive logs being applied on the standby database, and flashback must also be enabled as a prerequisite. Below are the steps that you can execute to effectively leverage the ADG DML redirection feature:
On the primary database, set the ADG_REDIRECT_DML parameter.
On the ADG standby database, set the ADG_REDIRECT_DML parameter.
On the primary database, create a database user and log in as the database user (do not do sqlplus / as sysdba).
On the ADG standby database, log in as the same database user created earlier in the primary database.
On the primary database, when you execute the query against the mytab table, you will see only 3 values:
Another notable feature of Data Guard 19c is the automatic flashback of the standby database. When flashback or point-in-time recovery is performed on the primary database in an Oracle Data Guard configuration, the same operation is performed on the standby database. Following a flashback or PITR operation, the primary database is then opened with the RESETLOGS option. The RESETLOGS leads to a new incarnation of the primary or the PDB in the primary. What's new then in Oracle 19c? The MRP process on the standby detects the new incarnation, moving the standby database to the new 'branch' of redo. It will then flash back the standby, or the pluggable database, on the standby to the same point in time as that of the primary or the PDB on the primary.
In earlier releases, we had to obtain the RESETLOGS SCN# on the primary and then manually issue a FLASHBACK DATABASE command on the standby database to enable managed recovery and continue with the redo apply process.
Another new Oracle Database 19c feature is when we create a Restore Point on the primary database, it will automatically create a restore point on the standby database as well. These restore points are called Replicated Restore Points and have the restore point name suffixed with a "_PRIMARY." Let's take a look at this feature in action; on the primary database, we will create a guaranteed restore point.
Note that on the standby database, the restore point has been automatically created, and the name has the suffix _PRIMARY.
On the primary database, we can see that the REPLICATED column has the value of NO for the restore point, while on the standby database, the value is YES.
We now simulate a case where a human error has been made, and a flashback operation will need to be performed on the primary to resolve the human error.
Flashback is performed to the restore point created earlier, and we then open the database with the RESETLOGS option.
The standby database is placed in MOUNT mode, and we will see that the MRP process on the standby database will start and perform the automatic flashback operation on the standby database.
When we see the message "Flashback Media Recovery Complete" in the standby database alert log, we can now open the standby database.
Note: that the Data Guard Broker configuration does not show any error(s), and we did not have to perform any manual steps on the standby database to enable the configuration following the flashback of the primary database.
Oracle introduced two additional parameters for Data Guard in Oracle Database 19c to tune automatic outage resolution:
The DATA_GUARD_MAX_IO_TIME has a range of 10 to 7200, with a default value of 240, which specifies the maximum number of seconds that can elapse before Data Guard considers a process performing read, write, and status I/O operations on the Data Guard configuration in a hung state.
The DATA_GUARD_MAX_LONG_IO_TIME also has a range of 10 to 7200, with a default value of 240, which specifies the maximum number of seconds that can elapse before Data Guard considers a process long I/O open and closes operations on the Data Guard configuration in a hung state.
These two parameters come in handy when Data Guard detects hung processes caused by delayed network outages, network disconnects, and disk I/O issues. These parameters allow for wait time to be customized and tuned for Data Guard before terminating redo apply processes to automate outage resolution caused by network outages and disk I/O issues.
FSFO allows for the broker to automatically failover the database to the standby when the observer process detects a failed condition to the primary database. Until Oracle Database 19c, we could not really test a failover condition without making changes to the configuration or impacting the primary database. Now, we can configure FSFO to be on Observe Only mode and create a test situation to see when a failover, or other interaction, would have occurred during
This feature allows us to tune FSFO for finer granularity and improve our failover validation. This method allows you to see if FSFO would have actually occurred without production impact.
Starting in Oracle Data Guard 19c, we can back up the configuration of the broker to a text file. Now, the export and import of our Data Guard Broker configuration can be to an external text file. The export of DG configuration can be done with the EXPORT CONFIGURATION command:
A snippet of the broker export file looks like this:
Likewise, we can import the Data Guard metadata configuration file with the IMPORT CONFIGURATION command:
The ADG DML Redirection feature is ideal for mostly read standby databases, where customers need some DML activity on the standby database. Now, with innovative features introduced in Oracle Database 19c, customers can offload more workloads from the primary database. As many are embarking on the cloud journey, customers are choosing Data Guard over RAC for high availability strategies.
Other features introduced in Oracle Database 19c, such as automatic flashback of the standby databases when the primary database executes a flashback, allow for easier management for the DBAs. Replicated restore points also simplify the manageability of the primary and standby databases. Other innovative features for Oracle Database 19c worth noting are:
One of the sayings we have at Viscosity is that our customers "have four aces in their pocket." Over the previous 11 days, the talented staff at Viscosity, along with our Oracle ACEs, have addressed more Oracle Database 18c and 19c new features. Continue to join us next year as we continue our Oracle Database 19c hands-on lab workshops.
Happy Holidays!