MIRA - Multi
Instance Redo Apply - 12cR2 onwards/18C
With Oracle 12c Release 2, We can enable Multi Instance Redo
apply ( MIRA) option in the DataGuard which will apply redo logs to multiple
instance at the same time to improve recovery time objective.
Once we enabled, we could see there will be pr* process trees
in both instances and pr** trace files on both nodes (ps -ef|grep pr*)
Please note that there will be 1 MRP0 for the entire MIRA
recovery which is normal. MRP0 is the coordinator process for all other pr*
recovery processes on all instances.
This can be enabled either using sqlplus / dgmgrl
1. How to
enable using sql plus
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
2. How to
enable using DGMGRL
Change value for
ApplyInstances = number of instances (set value to 2 in this case)
DGMGRL> edit database "18CDB" set property ApplyInstances=2;
Property "applyinstances" updated
DGMGRL> show configuration
Alert.log
ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2
2018-05-25T07:22:10.126643+01:00
Attempt to start background Managed Standby Recovery process
(18CDB1)
Starting background process MRP0
2018-05-25T07:22:10.144066+01:00
MRP0 started with pid=47, OS id=343863
2018-05-25T07:22:10.145157+01:00
Background Managed Standby Recovery process started (18CDB1)
2018-05-25T07:22:15.302656+01:00
Started logmerger process on instance id 1
Started logmerger process on instance id 2
Starting Multi Instance Redo Apply (MIRA) on 2 instances
2018-05-25T07:22:15.354896+01:00
Starting Multi Instance Redo Apply (MIRA)
2018-05-25T07:22:15.459146+01:00
.... (PID:399563): Managed Standby Recovery starting Real
Time Apply
2018-05-25T07:22:16.289661+01:00
Started 24 apply slaves on instance
id 1 =========> apply slaves started on instance 1
2018-05-25T07:22:16.818633+01:00
Started 24 apply slaves on instance
id 2 =========> apply slaves started on instance 2
2018-05-25T07:22:17.515862+01:00
ALTER SYSTEM SET remote_listener=' ahbdre1-sc.des.sg.com:1722'
SCOPE=MEMORY SID='18CDB1';
2018-05-25T07:22:17.516825+01:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY
SID='18CDB1';
3.
Background processes => once we enabled we could see the following
background process
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition
Release 18.0.0.0.0 - Production
Version 18.2.0.0.0
[18CDB1] $ ps -ef | grep GW | grep pr
oracle 323507 1
1 07:11 ? 00:00:01
ora_pr00_18CDB1
oracle 323512 1
0 07:11 ? 00:00:00
ora_pr02_18CDB1
oracle 323514 1
0 07:11 ? 00:00:00
ora_pr03_18CDB1
oracle 323516 1
0 07:11 ? 00:00:00
ora_pr04_18CDB1
oracle 323518 1
0 07:11 ? 00:00:00
ora_pr05_18CDB1
oracle 323520 1
0 07:11 ? 00:00:00 ora_pr06_18CDB1
oracle 323522 1
0 07:11 ? 00:00:00
ora_pr07_18CDB1
oracle 323534 1
0 07:11 ? 00:00:00
ora_pr08_18CDB1
oracle 323544 1
0 07:11 ? 00:00:00
ora_pr09_18CDB1
oracle 323546 1
0 07:11 ? 00:00:00 ora_pr0a_18CDB1
oracle 323548 1
0 07:11 ? 00:00:00
ora_pr0b_18CDB1
oracle 323550 1
0 07:11 ? 00:00:00
ora_pr0c_18CDB1
oracle 323552 1
0 07:11 ? 00:00:00
ora_pr0d_18CDB1
oracle 323554 1
0 07:11 ? 00:00:00
ora_pr0e_18CDB1
oracle 323556 1
0 07:11 ? 00:00:00
ora_pr0f_18CDB1
oracle 323558 1
0 07:11 ? 00:00:00
ora_pr0g_18CDB1
oracle 323560 1
0 07:11 ? 00:00:00
ora_pr0h_18CDB1
oracle 323562 1
0 07:11 ? 00:00:00
ora_pr0i_18CDB1
oracle 323564 1
0 07:11 ? 00:00:00
ora_pr0j_18CDB1
oracle 323566 1
0 07:11 ? 00:00:00
ora_pr0k_18CDB1
oracle 323568 1
0 07:11 ? 00:00:00
ora_pr0l_18CDB1
oracle 323570 1
0 07:11 ? 00:00:00
ora_pr0m_18CDB1
oracle 323572 1
0 07:11 ? 00:00:00
ora_pr0n_18CDB1
oracle 323574 1
0 07:11 ? 00:00:00
ora_pr0o_18CDB1
oracle 323576 1
0 07:11 ? 00:00:00
ora_pr0p_18CDB1
oracle 323582 1 0
07:11 ? 00:00:00 ora_pr1e_18CDB1
oracle 323584 1
0 07:11 ? 00:00:00
ora_pr1f_18CDB1
oracle 323586 1
0 07:11 ? 00:00:00
ora_pr1g_18CDB1
oracle 323588 1
0 07:11 ? 00:00:00
ora_pr1i_18CDB1
oracle 323590 1
0 07:11 ? 00:00:00
ora_pr1l_18CDB1
oracle 323592 1
0 07:11 ? 00:00:00
ora_pr1n_18CDB1
4.
Managed standby processes => there will be only one MRP process
1* select inst_id,PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from gv$managed_standby order by
CLIENT_PROCESS,THREAD#,SEQUENCE#
SQL> /
INST_ID
PROCESS STATUS CLIENT_P CLIENT_PID THREAD#
SEQUENCE# BLOCK# ACTIVE_AGENTS
KNOWN_AGENTS
---------- --------- ------------ --------
---------------------------------------- ---------- ---------- ----------
------------- ------------
1 ARCH CONNECTED ARCH
399744
0 0 0 0 0
1 ARCH CONNECTED ARCH
399776
0 0 0 0 0
1 ARCH CONNECTED ARCH
399774 0 0 0 0 0
1 ARCH CONNECTED ARCH
399770
0 0 0 0
0
1 ARCH CONNECTED ARCH
399768
0 0 0 0 0
1 ARCH CONNECTED ARCH
399766
0 0 0 0 0
1 ARCH CONNECTED ARCH
399764
0 0 0 0 0
1 ARCH CONNECTED ARCH
399750 0 0 0 0 0
1 ARCH CONNECTED ARCH
399754
0 0 0 0 0
1 ARCH CONNECTED
ARCH 399756
0 0 0 0 0
1 ARCH CONNECTED ARCH
399758
0 0 0 0 0
INST_ID
PROCESS STATUS CLIENT_P CLIENT_PID THREAD# SEQUENCE#
BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ --------
---------------------------------------- ---------- ---------- ----------
------------- ------------
1 ARCH CONNECTED ARCH
399760
0 0 0 0 0
2 ARCH CLOSING ARCH
117678
1 2242
10240 0 0
2 ARCH CLOSING ARCH
117670
1 2243 1 0 0
2 ARCH CLOSING ARCH
117690
1 2244 1 0 0
2 ARCH CLOSING ARCH
117684
1 2245 1
0 0
2 ARCH CLOSING ARCH
117680
1 2246 10240 0 0
2 ARCH CLOSING ARCH
117674
1 2247 1 0 0
2 ARCH CLOSING ARCH
117686
2 2215 45056 0 0
2 ARCH CLOSING ARCH
117664
2 2218 4096 0 0
2 ARCH CLOSING ARCH
117668
2 2237 1 0 0
2 ARCH CLOSING ARCH
117688 2 2238 1 0 0
INST_ID
PROCESS STATUS CLIENT_P CLIENT_PID THREAD# SEQUENCE#
BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ --------
---------------------------------------- ---------- ---------- ----------
------------- ------------
2 ARCH CLOSING ARCH
117682
2 2239 10240 0 0
2 ARCH CLOSING ARCH
117676
2 2240 1 0 0
2 RFS IDLE Archival 222920 1 0 0 0 0
2 RFS IDLE Archival 67323 2 0 0 0 0
2 RFS IDLE
LGWR 222400
1 2248 136 0 0
2 RFS IDLE LGWR 67057
2 2241 180 0 0
1 DGRD ALLOCATED N/A
N/A
0 0 0 0 0
2 DGRD ALLOCATED N/A
N/A
0 0 0 0 0
2 DGRD ALLOCATED N/A
N/A
0 0 0 0 0
1 DGRD ALLOCATED N/A
N/A
0 0 0 0 0
1 MRP0 APPLYING_LOG N/A N/A
1 2248 135 62 62
33 rows selected.
No comments:
Post a Comment