Starting with 12.2, Oracle introduced a new feature called Multi-Instance Redo Apply (MIRA), in a nutshell, this feature allows the Redo Apply to sync the data from all standby nodes (in parallel) which improves the scalability of Redo Apply on Standby RAC DB. But the bad news is that, this feature is not functioning properly, and it’s highly advisable to avoid using it!

When I tested this feature on a 19.5 RAC two nodes standby DB I figured out that while the Redo Apply and MRP process is running after enabling MIRA, none of the RAC standby instance are applying the Redo data on the database! Let’s have a look into this demonstration:

On Standby node1: Enable MIRA by adding INSTANCES ALL argument to the Recover command]

SYS@drorcl1>  RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT INSTANCES ALL;
Media recovery complete.

[oracle@drhost01]$ ps -ef|grep mrp|grep -v grep
oracle 18492 1 0 14:14 ? 00:00:00 ora_mrp0_drorcl1

Now let’s test the data sync on the Standby DB by creating a new table on the Primary DB:

On Primary:

SYS@orcl1> create table test(a number);
Table created.

SYS@orcl1> alter system archive log current;
System altered.

On Standby:

SYS@drorcl1>  desc test

ERROR:
ORA-04043: object test does not exist

The table didn’t sync to the standby DB although the MRP process is running!

Once I noticed that MIRA is not working I switched back to the legacy single instance Redo Apply mode:

On Standby:

SYS@orcl1> RECOVER MANAGED STANDBY DATABASE CANCEL;
Media recovery complete.

SYS@orcl1> RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT;
Media recovery complete.

SYS@orcl1> desc test

Name Null? Type
———————————————————————————————– ——–
A NUMBER

I opened an SR with Oracle Support, and they came back suggesting upgrading the DB to 19.6, then apply Patch 32071879

Considering the effort of upgrading the DB to 19.6 using RU patch then applying the mentioned patch, I decided to take the fastest route and keep using the legacy Redo Apply option!

Moreover, by having a look to the bugs resolved by that patch, you can easily notice that MIRA is an immature or better say a “catastrophic feature!”

😱

Conclusion:

The new feature of Multi-Instance Redo Apply (MIRA) in 19c is unreliable, it’s advised to keep using the legacy Single instance Redo Apply option.