Playing with Active Data Guard – DML Redirection and Automatic Block Media Recovery

Today we will be playing with two functionalities of Oracle Active Data Guard (ADG):

• DML Redirection
• Automatic Block Media Recovery

DML Redirection is the capability ADG has to redirect DML operations made in the active-standby node to the primary node without impacting its protection of it (by redirecting all DML transactions to the primary database and applying them to the primary to be then streamed and applied to the standby). Automatic Block Media Repair provides automatic and user-transparent recovery of corrupted database blocks from either the primary or the standby database.

Let’s get started. We will start playing with the DML redirect functionality first.

1st – Let’s connect to our primary database and check its status.

SQL> Select name, db_unique_name, database_role from v$database;

2nd – As we can see above, we are connected to the database named “DGHOL” and its role is “PRIMARY”.

3rd – Now we need to create a common user called “C#HOLUSER” identified by “Welc0me2##” with access to all pdbs.

SQL> create user C##HOLUSER identified by "WelC0me2##" container = all;

4th – Let’s grant the user “C##HOLUSER” with the minimum privileges required to perform its duties. For this example, we are granting the “connect, resource, and dba” privilege. Please when doing this in a production environment be sure to only grant the required privileges to your common user.

SQL> grant connect,resource, dba to C##HOLUSER;

5th – Just to be sure, let’s check if the user was created.

SQL> select username from dba_users where username like '%HOL%';

6th – Now it is time to enable the DML Redirection at the primary and standby database, as shown below:

SQL> alter system set adg_redirect_dml=true scope=both;

7th – Now, it is time to test this functionality. We will create a table in the primary database and then do an insert at the standby database (We will use our common user created in step 3 for this example).

SQL> connect C##HOLUSER/WelC0me2##

SQL> create table DMLTable (id number);

SQL> insert into DMLTable(id) values (1);
SQL> commit;

You will notice that the insert was a little slow but that is expected as the insert command is being redirected to the primary database to be executed before it could be confirmed.

8th – Let’s connect to the primary database and check if the data inserted at the standby database is really there.

SQL> select * from DMLTable;

Now, let’s play with the Automatic Block Media Recovery functionality.

1st – At the primary host, let’s download 3 scripts for this exercise.

wget https://oracle-livelabs.github.io/database-maa/19c-adg-fundamentals/media-recovery/scripts/01-abmr.sql
wget https://oracle-livelabs.github.io/database-maa/19c-adg-fundamentals/media-recovery/scripts/02-abmr.sql
wget https://oracle-livelabs.github.io/database-maa/19c-adg-fundamentals/media-recovery/scripts/03-abmr.sql

2nd – Let’s tail the alert log

tail -f  /u01/app/oracle/diag/rdbms/${ORACLE_UNQNAME,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log

3rd – Now we would open a new ssh connection to the primary host and connect to the pdb called “mypdb”.

SQL> alter session set container=mypdb;

4th – We will now run the script “01-abr.sql” we previously downloaded. It will create a new tablespace, create a new table and insert a row. This script will also return a rowID. Take a note of this number as you will need it in step 5, the step that will introduce the corruption to our database.

SQL> @01-abmr.sql

5th – In the same session, execute script “02-abmr.sql“. This script will ask for a number. This is the number from the previous step (12 in our case) and we will use this to corrupt the datafile that the previous script has created.

SQL> @02-abmr.sql

6th – Our demo database is not active, so it will be necessary to flush the caches before we access the table with the corrupt block. We will see that event that this data is corrupted at the disk level, our query will return without any error to the user, and Active Data Guard will repair the corrupt block before returning the query result automatically. To make this step easy, we will use the script “03-abmr.sql” for this.

SQL> @03-abmr.sql

7th – As previously mentioned, even with a block corrupt the SELECT command previously executed did not return any error to the user, It was able to return all data as the block was automatically recovered by using our standby database. If we take a closer look at the ssh connection with a “tail” to our log file, we will be able to see what happened in the background when Oracle detected the corruption when trying to read the datafile.

And this is all for today. Hope you enjoyed this tutorial. If you want to practice these examples and more, feel free to try Oracle Live Labs tutorial “Protect Your Data with Oracle Active Data Guard” for free here: https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=625&clear=RR,180&session=116681949982689

Regards,

Francisco Munoz Alvarez