Oracle released a really cool feature called Active Data Guard DML Redirection. It is described on the following links:

I was eager to test this new feature to understand if it can enable Blitz Report extract data from the standby databases.

So I created a physical standby database for my development EBS 12.2.12 on top of Oracle Database 19c using this article. Everything was ready and I executed the following statement before running an insert in the standby:

sqlplus apps/****@STANDBY_PDB
alter session enable adg_redirect_dml;
insert into test_tab values ('test_adg');

But the insert did not work and I saw the following errors:

ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed
ORA-16397: primary database could not be reached

I spent a lot of time investigating the root cause of the errors. It was interesting that everything worked on the CDB level, but not on the EBS PDB level. The DBA SGA Active Session History Blitz report helped me to identify the SQL statements that the database executed in background. Turned out it looked for the ADGREDIRECT database link in the standby PDB to connect to the primary PDB. So here are the steps to create the link. I executed them as APPS E-Business Suite user in the primary PDB as all the changes are synced with the standby database.

Make sure the global name in primary does not contain the domain before creating the ADGREDIRECT db link as the domain will get appended to the db link name and this will prevent Oracle from finding it. It happens even if the instance parameter global_names is set to false.

sqlplpus sys/***@PRIMARY_PDB as sysdba
select * from global_name;
update global_name set global_name ='DBNAME';
commit;
shutdown immediate
startup

Restart the Database and connect to the primary PDB. Then create the database link to the primary. It will get created in standby automatically and then will be used for the Active Data Guard DML Redirection.

sqlplus apps/****@PRIMARY_PDB
 
create database link ADGREDIRECT connect to apps identified by apps using '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=apps.example.com)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=ebs_EBSDB) (INSTANCE_NAME=EBSCDB)))';
 
exit;
sqlplus apps/****@STANDBY_PDB
select * from dual@ADGREDIRECT;

Now GLOBAL_NAME needs to be set back.

sqlplpus sys/***@PRIMARY_PDB as sysdba
select * from global_name;
update global_name set global_name ='DBNAME.DOMAINNAME.COM';
commit;
shutdown immediate
startup

After that I tried to run the test insert in the standby again and it worked! I was very happy. This feature allowed to introduce the new profile option ‘Blitz Report Target Database’ and Target Database field allows moving Blitz reporting load to a standby server.

I will appreciate your questions and comments.

Author: