Oracle standby and applying redologs methods
Mira vs Sira
Basically Oracle has different ways to apply redo on standby site.
SIRA: Single Instance Redo Apply
Where MRP runs only on one of the instance on Standby RAC/Single node database.
MIRA: Multi Instance Redo Apply
Where MRP runs only on more than one instance on Standby RAC database.
Recommendations related to MIRA from 12.2 Onwards (Exadata and Non Exadata envirnoment)
Starting with Oracle Database 12.2, Multi-Instance Redo Apply (MIRA) greatly improves scalability 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. For example, when two apply nodes are used to apply four
threads of redo from the primary, each apply instance will apply two threads.
Important :- 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.
How to Enable MIRA
From sqlplus
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE INSTANCES [ALL|integer];
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL -> to start from all the instances
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2 -> to start from any two instances
From Broker :- Data Guard Broker database set property ApplyInstances
Change value for ApplyInstances = number of instances (set value to 2 in this case)
DGMGRL> edit database "ORCL" set property ApplyInstances=2;
Property "applyinstances" updated
DGMGRL> show configuration
How to Check if MIRA is running
You can grep the Alert log for Word MIRA
Starting Multi Instance Redo Apply (MIRA) ----------------> If MIRA is used.
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.
Starting from 12c Release 1 (12.1), USING CURRENT LOGFILE is deprecated and no longer required to start real-time apply.
When the real-time apply feature is enabled, redo data can be directly applied to the standby database without waiting for the current standby redo log file to be archived.
To enable the real-time apply feature on the physical standby database, issue the following SQL statement (Active Data guard option is licensed)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Starting from 12c Release 1 (12.1), USING CURRENT LOGFILE is deprecated and no longer required to start real-time apply.
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.
How does it work:
Well, each applied instance on the standby site will divide the threads of redo received from primary among the apply instances to improve the scalability of redo apply.
For example, four redo threads from the primary are divided by two threads for each apply instance on standby.
To cancel the MRP,
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
To check the status of the MRP,
SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS=’MRP0′;
As a result of the managed recovery process (MRP), the standby database will have its archived redo logs maintained in real-time or near real-time.
ENABLE_IMC_WITH_MIRA
ENABLE_IMC_WITH_MIRA enables or disables the In-Memory Column Store and Oracle Data Guard Multi-Instance Redo Apply, at the same time, on an Active Data Guard standby database.
Property | Description |
Parameter type | Boolean |
Default value | false |
Modifiable | ALTER SYSTEM |
Modifiable in a PDB | No |
Range of values | true | false |
Basic | No |
Oracle RAC | Different instances can use different values. |
The value of this parameter is relevant only on the instance where MRP0 is spawned at the start of Redo Apply on a standby database.
Best Regards,
Alireza Kamrani
No comments:
Post a Comment