Multi-Instance Redo Apply (MIRA) in Oracle RAC

By -

 In Oracle 11g, when Data Guard was implemented in a cluster, only the node where the command ALTER DATABASE MANAGE RECOVER was executed (for the first time!) was responsible for applying the redo logs. The remaining nodes could be used for reporting, backup, receiving redo (or archive), and other purposes.

In Oracle 12cR2, an improvement was introduced that allows all nodes to participate in recovery. This feature, called Multi Instance Redo Apply (MIRA), makes it possible to apply redo logs using multiple instances.

To enable this feature, you can add the clause [INSTANCES [ ALL | integer ]] to the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE INSTANCES [ ALL | integer]

For example, with the following command in a test environment containing only two nodes, both nodes will share the redo apply task:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE INSTANCES ALL disconnect;

By checking the alert log on the node where the command was executed, you will see the following messages:

Started logmerger process on instance id 2

Started logmerger process on instance id 1

Starting Multi Instance Redo Apply (MIRA) on 2 instances

2018-09-02T15:30:27.514112+04:30

Starting Multi Instance Redo Apply (MIRA)

2018-09-02T15:30:27.562984+04:30

…. (PID:15215): Managed Standby Recovery starting Real Time Apply

2018-09-02T15:30:27.861206+04:30

Started 16 apply slaves on instance id 2

2018-09-02T15:30:28.117260+04:30

Started 16 apply slaves on instance id 1

2018-09-02T15:30:28.643222+04:30

Also, by executing this command on the second instance, only that node will run the MRP process:

–node 1:

[root@db~]#  ps -eaf|grep mrp

–node 2:

[root@db~]#  ps -eaf|grep mrp

oracle   17458     1  0 14:40 ?        00:00:00 ora_mrp0_stb2

In this case, the MRP process acts as a coordinator between processes that start with the prefix parallel recovery slave process (pr). Based on the alert log messages, 16 pr processes are started on each node:

Started 16 apply slaves on instance id 2

2018-09-02T15:30:28.117260+04:30

Started 16 apply slaves on instance id 1

[root@db~]# ps -eaf|grep pr

oracle   16487     1  0 15:30 ?        00:00:00 ora_pr02_stb2

If recovery is canceled on any of the nodes, media recovery will be completely terminated:

–node 2:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE INSTANCES ALL disconnect;

Database altered.

–node 1:

SQL> alter database recover managed standby database cancel;

Database altered.

–node 2:

ORA-16037: user requested cancel of managed recovery operation

2018-09-02T14:48:52.719683+04:30

Background Media Recovery process shutdown (stb2)

To use the MIRA feature, all instances must be in the same state (open or mount). Otherwise, you will encounter the following error:

Starting Multi Instance Redo Apply (MIRA)

2018-09-02T14:39:31.422046+04:30

…. (PID:15215): Managed Standby Recovery starting Real Time Apply

2018-09-02T14:39:31.426539+04:30

Multi Instance Redo Apply terminaed with error 10459

2018-09-02T14:39:31.432166+04:30

Errors in file /oracle/diag/rdbms/stb/stb2/trace/stb2_pr00_16780.trc:

ORA-10459: cannot start media recovery on standby database; conflicting state detected

MIRA: Mark controlfile recovery error occurred
Vahid Yousefzadeh

Written by Vahid Yousefzadeh

I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com



Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Boolean Data Type in Oracle ِDatabase 23ai

By -
Image
  Oracle introduced the Boolean data type in SQL with version 23ai (although this data type has existed in PL/SQL previously). With this feature, you can define table columns as Boolean during table creation. SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN ); Table created You can also use the keyword  BOOL  instead of  BOOLEAN : SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOL); Table created You can set default values for Boolean columns: SQL > CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE ); Table created To populate the  is_accept  column, you can use the three keywords:  TRUE ,  FALSE , and  NULL : SQL > insert into table1 values ( 1 , true ); 1 row inserted SQL > insert into table1 values ( 2 , null ); 1 row inserted SQL > insert into table1 values ( 3 , false ); 1 row inserted SQL > select * from table1; ID IS_ACCEPT ---------- ---------- ...
Read more