Dear Readers,

Trust you are doing well!!!

Today I am going to write about interesting feature being introduced in Oracle version 12.2 & widely used in 19c.

Multi-Instance Redo Apply(MIRA)

Basically Oracle has different ways to apply redo on standby site.

SIRA: Single Instance Redo Apply ==> Wherein MRP runs only on one of the instance on Standby RAC/Single node database.
MIRA: Multi Instance Redo Apply ==> Wherein MRP runs only on more than one instance on Standby RAC database.

Multi-Instance Redo Apply (MIRA) significantly improves scalability/performance of redo apply for Oracle RAC databases. Instead of merging all threads of redo into a single apply process, multiple apply instances divide the threads of redo between the apply instances.

Syntax for stating MIRA recovery:

From SQL*PLUS:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE INSTANCES [ALL|integer];

From DG Broker:

DGMGRL> edit database "" set property ApplyInstances=[integer];

How to check if MIRA is enabled or being used:

Check messages in alert log ==> Starting Multi Instance Redo Apply (MIRA) 

Important things to take into consideration while implementing MIRA:

• Disable SMART PING feature on EXADATA/SuperCluster box before starting MIRA.

Mira cannot handle the redo generated by SMART PING feature which is enabled by default on Exadata, so we need to disable smart ping as prerequisite on Engineered systems:

“_cache_fusion_pipelined_updates”=false 

Note: This needs to be set on both primary and standby databases. Also this NON RAC rolling parameter requires complete DB bounce.

• Convert to SIRA during RAC rolling upgrade/patching.

In RAC database it is recommended  to use single instance redo apply (SIRA) instead of MIRA when online patching for RAC rolling upgrade is in progress.

• Recommended Database version.

Most MIRA optimisations are implemented in Oracle Database 19c and are not available in earlier database releases. In fact, Oracle recommends the database release be no earlier than Oracle Database 19.13 because it includes some important MIRA fixes.

• Important bug fixes to be considered for MIRA.

34198944; MIRA HANG IN ADG
32012137; REDUCE NUMBER OF LWNHIST RECORDS SENT
33821145; MIRA FAILED TO ADVANCE INFLUX SCN
33940540; MIRA RECEIVER NEEDS TO FREE SENT MESSAGES
34239061; MERGE ON DATABASE RU 19.11.0.0.0 OF 34238063 33940540 ==> Merge of all above 4 patches.
34775309; APPLY PROCESS IS VERY SLOW AFTER APPLYING PATCH 33165338

• Not recommended for environments with poor IO performance.

MIRA is not recommended to for environments with poor IO throughput. It is recommended to solve/tune suboptimal IO throughput before converting from SIRA to MIRA.

Important Documents to refer:

• Known Issues and Recommendations related to MIRA from 12.2 Onwards (Exadata and Non Exadata environment) (Doc ID 2678711.1)
• Recommended Bundle Patch for MIRA(Multi Instance Redo Apply)(Doc ID 2710939.1)
• Oracle Database 19c Important Recommended One-off Patches(Doc ID 555.1)

Hope u will find this post very useful!!!

Cheers

Regards,
Adityanath