Multi-Instances Redo Apply em Physical Standby Database Instances em um ambiente 3-Node RAC

Por Y V Ravi Kumar Oracle ACE director, Mariami Kupatadze Oracle Associate, Rodrigo Mufalani Oracle ACE
Publicado em Novembro 2019

Revisado por Juan Pablo Guizado




Introdução:

Até o Oracle 12cR1 (12.1.0.2.0),  o Managed Recovery Process (MRP) e os Redo Apply servers tem sempre que rodar em um único node de uma Physical Standby Database Instances em um ambiente Real Application Clusters (RAC). Esta atividade impacta no node considerando CPU e I/O.

A Oracle introduziu Multi-Instances Redo Apply em Physical Standby Database Instances desde a versão Oracle 12cR2 (12.2.0.1.0).

O Multi-Instance Redo Apply (MIRA) iniciou com o Oracle 12cR2 (12.2.0.1.0), e o Multi-Instance Redo Apply (MIRA) aumenta grandemente a escalabilidade da aplicação de redo para Oracle RAC databases. Ao invés de mesclar todas as threads de redo em um único apply process, multiple apply instances dividem as threads de redo entre as apply instances em physical standby database instances.

A ativação do número de apply instances é controlado pela propriedade do Data Guard Broker, ApplyInstances ou pelo seguinte comando do SQL*Plus

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



Ambiente:

 

  • Primary Database Instances: RCVCAT (3-Node)
  • Physical Standby Database Instances: RCVACT (3-Node)
  • Oracle RDBMS Home: /u01/app/oracle/product/12.2.0.1/dbhome_1
  • GI Home: /u01/app/12.2.0.1/grid



Arquitetura de um Single-Instance Redo Apply:




Multi-Instance Redo Apply executam em todas instances com modo MOUNTED ou OPEN Instances em Physical Standby Database Instances.

Nós Podemos configurar a mesma propriedade usando Data Guard Broker (DGMGRL) também.




Arquitetura de Multiple-Instances Redo Apply:




Cheque o Recovery apply pending e/ou recovery receive buffer free (MIRA):

Considere aumentar _mira_num_local_buffers e _mira_num_receive_buffers
Note: Estes parametros usam espaço da shared pool igual a soma de seus valores (em MB) * o número de apply instances em physical standby database instances.




Cheque Parallel recovery change vector buffer free (MIRA):

Considere aumentar _change_vector_buffers para 2 ou 4




Cheque Parallel recovery read buffer free:

Considere aumentar “_log_read_buffers” para no máximo 256




Cheque o modo da Primary Databases Instances e sua role:

bash-4.2$ . oraenv
ORACLE_SID = [RCVCAT1] ?
The Oracle base remains unchanged with value /u01/app/oracle

bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 8 17:08:08 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select inst_id,name,open_mode,database_role from gv$database;

   INST_ID 	NAME      	OPEN_MODE            		DATABASE_ROLE
---------- 	------------- 	-------------------- 		----------------------
         1 	RCVCAT      	READ WRITE           		PRIMARY
         2 	RCVCAT      	READ WRITE           		PRIMARY
         3 	RCVCAT      	READ WRITE           		PRIMARY
SQL>



Cheque o modo da Physical Standby Databases Instances e sua role:

bash-4.2$ . oraenv
ORACLE_SID = [RCVCAT1] ?
The Oracle base remains unchanged with value /u01/app/oracle
bash-4.2$

bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 8 17:06:22 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select inst_id,name,open_mode,database_role from gv$database;

   INST_ID 	NAME      	OPEN_MODE            				DATABASE_ROLE
---------- 	--------- 	-------------------------------- 	------------------------
         1 	RCVCAT      	READ ONLY WITH APPLY 				PHYSICAL STANDBY
         2 	RCVCAT      	READ ONLY WITH APPLY 				PHYSICAL STANDBY
         3 	RCVCAT      	READ ONLY WITH APPLY 				PHYSICAL STANDBY
SQL>

-bash-4.2$ srvctl status database -d RCVCAT_DR
Instance RCVCAT1 is running on node srv-oradb-01
Instance RCVCAT2 is running on node srv-oradb-02
Instance RCVCAT3 is running on node srv-oradb-03
-bash-4.2$



Cheque a Primary Database Instances e a configuração da Physical Standby Database Instances usando Data Guard Broker (DGMGRL)

-bash-4.2$ dgmgrl / as sysdba
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Aug 8 13:34:39 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "RCVCAT_DR"
Connected as SYSDBA.
DGMGRL>

DGMGRL> show configuration
Configuration - RCVCAT_CONFIG

  Protection Mode: MaxPerformance
  Members:
  RCVCAT    - Primary database
    RCVCAT_DR - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

DGMGRL> show database verbose "RCVCAT_DR";

Database - RCVCAT_DR

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 19.00 KByte/s
  Active Apply Rate:  622.00 KByte/s
  Maximum Apply Rate: 5.58 MByte/s
  Real Time Query:    ON
  Instance(s):
    RCVCAT1 (apply instance)
    RCVCAT2
    RCVCAT3

  Properties:
    DGConnectIdentifier             = 'RCVCAT_DR_DG'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DataGuardSyncLatency            = '0'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    PreferredObserverHosts          = ''
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

  Log file locations(*):
    (*) - Check specific instance for log file locations.

Database Status:
SUCCESS
DGMGRL>



Mude a propriedade “ApplyInstances” no Physical Standby Database através do DGMGRL

DGMGRL> edit database "RCVCAT_DR" set property 'ApplyInstances'='3';
Property "ApplyInstances" updated
DGMGRL>

DGMGRL> show database verbose "RCVCAT_DR";

Database - RCVCAT_DR

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 7.00 KByte/s
  Active Apply Rate:  622.00 KByte/s
  Maximum Apply Rate: 5.58 MByte/s
  Real Time Query:    ON
  Instance(s):
    RCVCAT1 (apply instance)
    RCVCAT2
    RCVCAT3

  Properties:
    DGConnectIdentifier             = 'RCVCAT_DR_DG'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '3'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DataGuardSyncLatency            = '0'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    PreferredObserverHosts          = ''
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

  Log file locations(*):
    (*) - Check specific instance for log file locations.

Database Status:
SUCCESS
DGMGRL> exit



Aplicando Multiple-Redo Apply Instances em um Physical Standby Database pelo SQL*Plus

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
READ ONLY WITH APPLY PHYSICAL STANDBY
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY
READ ONLY            PHYSICAL STANDBY
READ ONLY            PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect using instances 3;
Database altered.

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
READ ONLY WITH APPLY PHYSICAL STANDBY



Cheque o processo MRP e o Recovery Processes nas outras Instances

Quando tentamos habilitar multiple apply instances, mas Podemos ver somente um único 1 MRP rodando pela view gv$managed_standby.

O Managed Recovery Process (MRP0) é o coordenador para todos os outros "pr*" recovery processes em todas as instances. Haverá apenas 1 MRP0 para todo o MIRA recover, o que é normal.

Por favor, cheque se você pode ver árvores de processo pr* em todas as instances, para isso, pode usar o comando (ps -ef|grep pr*)

bash-4.2$ hostname
srv-oradb-01
bash-4.2$

bash-4.2$ ps -ef | grep mrp0_RC*
oracle   25751 27226  0 11:27 pts/3    00:00:00 grep --color=auto mrp0_RC*
bash-4.2$

bash-4.2$ ps -ef|grep pr*_RCA*
oracle    4123     1  0 11:22 ?        00:00:01 ora_pr0p_RCAT1
bash-4.2$

SQL> select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#
from gv$managed_standby
order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

PROCESS   STATUS       CLIENT_P CLIENT_P    THREAD#  SEQUENCE#     BLOCK#
--------- ------------ -------- -------- ---------- ---------- ----------
ARCH      CONNECTED    ARCH     31326             0          0          0
ARCH      CONNECTED    ARCH     11969             0          0          0
ARCH      CONNECTED    ARCH     31314             0          0          0
ARCH      CONNECTED    ARCH     31324             0          0          0
ARCH      CONNECTED    ARCH     31322             0          0          0
ARCH      CONNECTED    ARCH     11971             0          0          0
ARCH      CONNECTED    ARCH     11973             0          0          0
ARCH      CONNECTED    ARCH     11963             0          0          0
ARCH      CLOSING      ARCH     12226             1       3280          1
ARCH      CLOSING      ARCH     12216             2       3058      32768
ARCH      CLOSING      ARCH     12228             2       3059          1
ARCH      CLOSING      ARCH     12224             3       3106          1
RFS       IDLE         Archival 26216             0          0          0
RFS       IDLE         Archival 26340             0          0          0
RFS       IDLE         Archival 27081             0          0          0
RFS       IDLE         LGWR     26234             1       3281        281
RFS       IDLE         LGWR     27251             2       3060        275
RFS       IDLE         LGWR     26390             3       3107        358
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
MRP0      APPLYING_LOG N/A      N/A               1       3281        281

25 rows selected.
SQL> exit

-bash-4.2$ hostname
srv-oradb-02
-bash-4.2$

-bash-4.2$ ps -ef | grep mrp0_RC*
oracle    3146  2801  0 11:28 pts/1    00:00:00 grep --color=auto mrp0_RC*
-bash-4.2$

-bash-4.2$ ps -ef|grep pr*_RCA*
oracle   12427     1  0 11:22 ?        00:00:00 ora_pr0p_RCAT2
-bash-4.2$

-bash-4.2$ . oraenv
ORACLE_SID = [oracle] ? RCVCAT2
The Oracle base has been set to /u01/app/oracle

-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 12 11:28:40 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#
from gv$managed_standby
order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

PROCESS   STATUS       CLIENT_P CLIENT_P    THREAD#  SEQUENCE#     BLOCK#
--------- ------------ -------- -------- ---------- ---------- ----------
ARCH      CONNECTED    ARCH     31326             0          0          0
ARCH      CONNECTED    ARCH     11969             0          0          0
ARCH      CONNECTED    ARCH     31314             0          0          0
ARCH      CONNECTED    ARCH     31324             0          0          0
ARCH      CONNECTED    ARCH     31322             0          0          0
ARCH      CONNECTED    ARCH     11971             0          0          0
ARCH      CONNECTED    ARCH     11973             0          0          0
ARCH      CONNECTED    ARCH     11963             0          0          0
ARCH      CLOSING      ARCH     12226             1       3280          1
ARCH      CLOSING      ARCH     12216             2       3058      32768
ARCH      CLOSING      ARCH     12228             2       3059          1
ARCH      CLOSING      ARCH     12224             3       3106          1
RFS       IDLE         Archival 26216             0          0          0
RFS       IDLE         Archival 26340             0          0          0
RFS       IDLE         Archival 27081             0          0          0
RFS       IDLE         LGWR     26234             1       3281        402
RFS       IDLE         LGWR     27251             2       3060        433
RFS       IDLE         LGWR     26390             3       3107        479
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
MRP0      APPLYING_LOG N/A      N/A               1       3281        402

25 rows selected.
SQL> exit

bash-4.2$ hostname
srv-oradb-03
-bash-4.2$

-bash-4.2$ ps -ef | grep mrp0_RC*
oracle   23497     1  0 11:22 ?        00:00:03 ora_mrp0_RCVCAT3
-bash-4.2$

-bash-4.2$ . oraenv
ORACLE_SID = [oracle] ? RCVCAT3
The Oracle base has been set to /u01/app/oracle

-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 12 11:29:48 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#
from gv$managed_standby
order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

PROCESS   STATUS       CLIENT_P CLIENT_P    THREAD#  SEQUENCE#     BLOCK#
--------- ------------ -------- -------- ---------- ---------- ----------
ARCH      CONNECTED    ARCH     11963             0          0          0
ARCH      CONNECTED    ARCH     31326             0          0          0
ARCH      CONNECTED    ARCH     31324             0          0          0
ARCH      CONNECTED    ARCH     31322             0          0          0
ARCH      CONNECTED    ARCH     31314             0          0          0
ARCH      CONNECTED    ARCH     11973             0          0          0
ARCH      CONNECTED    ARCH     11971             0          0          0
ARCH      CONNECTED    ARCH     11969             0          0          0
ARCH      CLOSING      ARCH     12226             1       3280          1
ARCH      CLOSING      ARCH     12216             2       3058      32768
ARCH      CLOSING      ARCH     12228             2       3059          1
ARCH      CLOSING      ARCH     12224             3       3106          1
RFS       IDLE         Archival 27081             0          0          0
RFS       IDLE         Archival 26216             0          0          0
RFS       IDLE         Archival 26340             0          0          0
RFS       IDLE         LGWR     26234             1       3281        470
RFS       IDLE         LGWR     27251             2       3060        506
RFS       IDLE         LGWR     26390             3       3107        563
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
DGRD      ALLOCATED    N/A      N/A               0          0          0
MRP0      APPLYING_LOG N/A      N/A               1       3281        470

25 rows selected.
SQL>



Cheque o Managed Recovery Process (MRP) em Physical Standby Database Instance-1:

bash-4.2$ . oraenv
ORACLE_SID = [RCVCAT1] ?
The Oracle base remains unchanged with value /u01/app/oracle
bash-4.2$

bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 12 14:24:12 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select process, thread#, sequence#, status from v$managed_standby where process='MRP0';

no rows selected

SQL> exit



Checando o Managed Recovery Process (MRP) em Physical Standby Database Instance-2

-bash-4.2$ . oraenv
ORACLE_SID = [oracle] ? RCVCAT2
The Oracle base has been set to /u01/app/oracle
-bash-4.2$

-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 12 14:24:53 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select process, thread#, sequence#, status from v$managed_standby where process='MRP0';

no rows selected

SQL> exit



Checando o Managed Recovery Process (MRP) em Physical Standby Database Instance-3

-bash-4.2$ hostname
srv-oradb-03
-bash-4.2$ . oraenv
ORACLE_SID = [oracle] ? RCVCAT3
The Oracle base has been set to /u01/app/oracle

-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 12 14:25:27 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select process, thread#, sequence#, status from v$managed_standby where process='MRP0';

PROCESS      THREAD#  SEQUENCE# STATUS
--------- ---------- ---------- ------------
MRP0               1       3281 APPLYING_LOG

SQL>



Checando Data Guard Apply Lag

SQL> SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED
from V$DATAGUARD_STATS where name like 'apply lag'; 

NAME         LAG_TIME             DATUM_TIME           TIME_COMPUTED
------------ -------------------- -------------------- -------------------
apply lag    +00 00:00:00         08/08/2019 15:57:12  08/08/2019 15:57:13

1 row selected.
SQL>



Physical Standby Database Instance-1: Alert log file

Primary database is in MAXIMUM PERFORMANCE mode
Identified standby log 102 for network mid-log reconnect
RFS[36]: Assigned to RFS process (PID:7549)
RFS[36]: Selected log 102 for T-1.S-3259 dbid 734168328 branch 962018698
2019-08-08 13:58:54.408000 -04:00
alter database recover managed standby database cancel
MRP0: The following warnings/errors are found:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/RCVCAT_dr/RCVCAT1/trace/RCVCAT1_pr00_3571.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2019-08-08 13:58:55.556000 -04:00
Recovered data files to a consistent state at change 495096385
Reconfiguration started (old inc 104, new inc 106)
List of instances (total 3) :
 1 2 3
My inst 1
 Global Resource Directory frozen
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete (total time 0.1 secs)
Errors in file /u01/app/oracle/diag/rdbms/RCVCAT_dr/RCVCAT1/trace/RCVCAT1_mrp0_3158.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (RCVCAT1)
2019-08-08 13:58:57.414000 -04:00
Managed Standby Recovery Canceled (RCVCAT1)
Completed: alter database recover managed standby database cancel
2019-08-08 13:59:37.797000 -04:00
alter database recover managed standby database disconnect using instances 3
Attempt to start background Managed Standby Recovery process (RCVCAT1)
Starting background process MRP0
MRP0 started with pid=133, OS id=17036
MRP0: Background Managed Standby Recovery process started (RCVCAT1)
2019-08-08 13:59:42.963000 -04:00
Started logmerger process on instance id 1 Started logmerger process on instance id 2 Started logmerger process on instance id 3 Starting Multi Instance Redo Apply (MIRA) on 3 instances
WARNING! File header update interval to record checkpointsreset to default 5 minutes
Starting Multi Instance Redo Apply (MIRA)
Managed Standby Recovery starting Real Time Apply
Reconfiguration started (old inc 106, new inc 108)
List of instances (total 3) :
 1 2 3
My inst 1
 Global Resource Directory frozen
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete (total time 0.1 secs)
2019-08-08 13:59:45.100000 -04:00
Started 16 apply slaves on instance id 1 2019-08-08 13:59:46.707000 -04:00 Started 16 apply slaves on instance id 2 2019-08-08 13:59:48.316000 -04:00 Started 16 apply slaves on instance id 3 2019-08-08 13:59:50.832000 -04:00
Completed: alter database recover managed standby database disconnect using instances 3
Recovery of Online Redo Log: Thread 1 Group 102 Seq 3259 Reading mem 0
  Mem# 0: +RMAN_FRA/RCVCAT_DR/ONLINELOG/group_102.367.995365333



Physical Standby Database Instance-2: Alert log file

Managed Standby Recovery not using Real Time Apply
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Reconfiguration started (old inc 104, new inc 106)
List of instances (total 3) :
 1 2 3
My inst 2
 Global Resource Directory frozen
* SM/ADG-SM mode terminates successfully - (publisher 1) set domain valid
 Communication channels reestablished
* domain 0 valid = 1 (flags x820, pdb flags x820) according to instance 1
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete (total time 0.1 secs)
2019-08-08 13:59:43.207000 -04:00
Managed Standby Recovery starting Real Time Apply
Reconfiguration started (old inc 106, new inc 108)
List of instances (total 3) :
 1 2 3
My inst 2
 Global Resource Directory frozen
 Communication channels reestablished
* domain 0 valid = 1 (flags x820, pdb flags x820) according to instance 1
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete (total time 0.1 secs)
2019-08-08 13:59:48.454000 -04:00
Starting Multi Instance Redo Apply (MIRA) 2019-08-08 13:59:50.831000 -04:00
Media Recovery of Online Log [Thread=2, Seq=3030]
Recovery of Online Redo Log: Thread 2 Group 111 Seq 3030 Reading mem 0
  Mem# 0: +RMAN_FRA/RCVCAT_DR/ONLINELOG/group_111.372.995365333



Physical Standby Database Instance-3: Alert log file

List of instances (total 3) :
 1 2 3
My inst 3
 Global Resource Directory frozen
 Communication channels reestablished
* domain 0 valid = 1 (flags x820, pdb flags x820) according to instance 1
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete (total time 0.1 secs)
2019-08-08 13:35:48.363000 -04:00
Starting Multi Instance Redo Apply (MIRA) 2019-08-08 13:58:55.351000 -04:00
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/RCVCAT_dr/RCVCAT3/trace/RCVCAT3_pr00_1419.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Reconfiguration started (old inc 104, new inc 106)
List of instances (total 3) :
 1 2 3
My inst 3
 Global Resource Directory frozen
* SM/ADG-SM mode terminates successfully - (publisher 1) set domain valid
 Communication channels reestablished
* domain 0 valid = 1 (flags x820, pdb flags x820) according to instance 1
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete (total time 0.1 secs)
2019-08-08 13:59:43.207000 -04:00
Managed Standby Recovery starting Real Time Apply
Reconfiguration started (old inc 106, new inc 108)
List of instances (total 3) :
 1 2 3



Checando o “Validate Database” para o Primary e o Standby Instances

DGMGRL> validate database verbose "RCVCAT";

  Database Role:    Primary database
  Ready for Switchover:  Yes

  Flashback Database Status:
    RCVCAT:  Off

  Capacity Information:
    Database  Instances        Threads
    RCVCAT      3                3

  Managed by Clusterware:
    RCVCAT:  YES

  Temporary Tablespace File Information:
    RCVCAT TEMP Files:  1

  Data file Online Move in Progress:
    RCVCAT:  No

  Transport-Related Information:
    Transport On:  Yes

  Log Files Cleared:
    RCVCAT Standby Redo Log Files:  Cleared

  Automatic Diagnostic Repository Errors:
    Error                       RCVCAT
    No logging operation        NO
    Control file corruptions    NO
    System data file missing    NO
    System data file corrupted  NO
    System data file offline    NO
    User data file missing      NO
    User data file corrupted    NO
    User data file offline      NO
    Block Corruptions found     NO
DGMGRL>

DGMGRL> validate database verbose "RCVCAT_DR";

  Database Role:     Physical standby database
  Primary Database:  RCVCAT

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    RCVCAT   :  Off
    RCVCAT_DR:  Off

  Capacity Information:
    Database  Instances        Threads
    RCVCAT      3                3
    RCVCAT_DR   3                3

  Managed by Clusterware:
    RCVCAT   :  YES
    RCVCAT_DR:  YES

  Temporary Tablespace File Information:
    RCVCAT TEMP Files:     1
    RCVCAT_DR TEMP Files:  1

  Data file Online Move in Progress:
    RCVCAT:     No
    RCVCAT_DR:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 1 second ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:      Yes
    Gap Status:        No Gap
    Transport Lag:     0 seconds (computed 1 second ago)
    Transport Status:  Success

  Log Files Cleared:
    RCVCAT Standby Redo Log Files:     Cleared
    RCVCAT_DR Online Redo Log Files:   Cleared
    RCVCAT_DR Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (RCVCAT)                  (RCVCAT_DR)
    1         2                       3                    Sufficient SRLs
    2         2                       3                    Sufficient SRLs
    3         2                       3                    Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (RCVCAT_DR)               (RCVCAT)
    1         2                       3                    Sufficient SRLs
    2         2                       3                    Sufficient SRLs
    3         2                       3                    Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (RCVCAT)                    (RCVCAT_DR)
    1          200 MBytes                200 MBytes
    2          200 MBytes                200 MBytes
    3          200 MBytes                200 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (RCVCAT_DR)                 (RCVCAT)
    1          200 MBytes                200 MBytes
    2          200 MBytes                200 MBytes
    3          200 MBytes                200 MBytes

  Apply-Related Property Settings:
    Property                        RCVCAT Value         RCVCAT_DR Value
    DelayMins                       0                     0
    ApplyParallel                   AUTO                  AUTO
    ApplyInstances                  0                     3

  Transport-Related Property Settings:
    Property                        RCVCAT Value         RCVCAT_DR Value
    LogXptMode                      ASYNC                 ASYNC
    Dependency                                          DelayMins                       0                     0     Binding                         optional              optional     MaxFailure                      0                     0     MaxConnections                  1                     1     ReopenSecs                      300                   300     NetTimeout                      30                    30     RedoCompression                 DISABLE               DISABLE     LogShipping                     ON                    ON    Automatic Diagnostic Repository Errors:     Error                       RCVCAT  RCVCAT_DR     No logging operation        NO       NO     Control file corruptions    NO       NO     SRL Group Unavailable       NO       NO     System data file missing    NO       NO     System data file corrupted  NO       NO     System data file offline    NO       NO     User data file missing      NO       NO     User data file corrupted    NO       NO     User data file offline      NO       NO     Block Corruptions found     NO       NO DGMGRL>  



Determinando como o transport ou apply lag é feito no standby database consultando a v$dataguard_stats usando uma query similar:

SQL> select * from v$standby_event_histogram where name like '%lag'  and count >0;

NAME        TIME UNIT         COUNT LAST_TIME_UPDATED         CON_ID
----------- ---- ------- ---------- ------------------------- ------
apply lag      0 seconds     177157 08/13/2019 10:18:57            0
apply lag      1 seconds       4030 08/13/2019 10:16:41            0
apply lag      2 seconds        335 08/13/2019 10:05:10            0
apply lag      3 seconds         26 08/13/2019 00:05:44            0
apply lag      4 seconds          3 08/12/2019 23:10:08            0
apply lag      5 seconds          5 08/12/2019 23:20:22            0
apply lag      6 seconds          5 08/12/2019 23:10:10            0
apply lag      7 seconds          2 08/12/2019 11:21:40            0
apply lag      8 seconds          3 08/12/2019 18:00:10            0
apply lag      9 seconds          3 08/12/2019 18:00:11            0
apply lag     10 seconds          3 08/12/2019 18:00:12            0
apply lag     11 seconds          1 08/12/2019 11:21:44            0
apply lag     12 seconds          1 08/12/2019 11:21:45            0
apply lag     13 seconds          1 08/12/2019 11:21:46            0
apply lag     14 seconds          1 08/12/2019 11:21:47            0
apply lag     15 seconds          1 08/12/2019 11:21:48            0
apply lag     16 seconds          1 08/12/2019 11:21:49            0
apply lag     17 seconds          1 08/12/2019 11:21:50            0
apply lag     18 seconds          1 08/12/2019 11:21:51            0
apply lag     19 seconds          1 08/12/2019 11:21:52            0
apply lag     20 seconds          1 08/12/2019 11:21:53            0
apply lag     21 seconds          1 08/12/2019 11:21:54            0
apply lag     22 seconds          1 08/12/2019 11:21:55            0
apply lag     23 seconds          1 08/12/2019 11:21:56            0
apply lag     24 seconds          1 08/12/2019 11:21:57            0
apply lag     25 seconds          1 08/12/2019 11:21:58            0
apply lag     26 seconds          1 08/12/2019 11:21:59            0
apply lag     27 seconds          1 08/12/2019 11:22:00            0
apply lag     28 seconds          1 08/12/2019 11:22:01            0
apply lag     29 seconds          1 08/12/2019 11:22:02            0
apply lag     30 seconds          1 08/12/2019 11:22:03            0
apply lag     31 seconds          1 08/12/2019 11:22:04            0
apply lag     32 seconds          1 08/12/2019 11:22:05            0
apply lag     33 seconds          1 08/12/2019 11:22:06            0
apply lag     34 seconds          1 08/12/2019 11:22:07            0
apply lag     35 seconds          1 08/12/2019 11:22:08            0
apply lag     36 seconds          1 08/12/2019 11:22:09            0
apply lag     37 seconds          1 08/12/2019 11:22:10            0
apply lag     38 seconds          1 08/12/2019 11:22:11            0
apply lag     39 seconds          1 08/12/2019 11:22:12            0
apply lag     40 seconds          1 08/12/2019 11:22:13            0
apply lag     41 seconds          1 08/12/2019 11:22:14            0
apply lag     42 seconds          1 08/12/2019 11:22:15            0
apply lag     43 seconds          1 08/12/2019 11:22:16            0
apply lag     44 seconds          1 08/12/2019 11:22:17            0
apply lag     45 seconds          1 08/12/2019 11:22:18            0
apply lag     46 seconds          1 08/12/2019 11:22:19            0
apply lag     59 seconds          1 08/11/2019 07:41:48            0

48 rows selected.
SQL>



Para obter o histograma que mostra o histórico dos valores do apply lag desde que as standby instances foram iniciadas, para isso consulte a view V$STANDBY_EVENT_HISTOGRAM

SQL> select NAME,TIME,UNIT,COUNT,LAST_TIME_UPDATED from V$STANDBY_EVENT_HISTOGRAM
where name like '%lag' and count > 0
order by LAST_TIME_UPDATED;  2    3

NAME        TIME UNIT              COUNT LAST_TIME_UPDATED
----------- ---- ------------ ---------- ------------------------------
apply lag     59 seconds               1 08/11/2019 07:41:48
apply lag      7 seconds               2 08/12/2019 11:21:40
apply lag     11 seconds               1 08/12/2019 11:21:44
apply lag     12 seconds               1 08/12/2019 11:21:45
apply lag     13 seconds               1 08/12/2019 11:21:46
apply lag     14 seconds               1 08/12/2019 11:21:47
apply lag     15 seconds               1 08/12/2019 11:21:48
apply lag     16 seconds               1 08/12/2019 11:21:49
apply lag     17 seconds               1 08/12/2019 11:21:50
apply lag     18 seconds               1 08/12/2019 11:21:51
apply lag     19 seconds               1 08/12/2019 11:21:52
apply lag     20 seconds               1 08/12/2019 11:21:53
apply lag     21 seconds               1 08/12/2019 11:21:54
apply lag     22 seconds               1 08/12/2019 11:21:55
apply lag     23 seconds               1 08/12/2019 11:21:56
apply lag     24 seconds               1 08/12/2019 11:21:57
apply lag     25 seconds               1 08/12/2019 11:21:58
apply lag     26 seconds               1 08/12/2019 11:21:59
apply lag     27 seconds               1 08/12/2019 11:22:00
apply lag     28 seconds               1 08/12/2019 11:22:01
apply lag     29 seconds               1 08/12/2019 11:22:02
apply lag     30 seconds               1 08/12/2019 11:22:03
apply lag     31 seconds               1 08/12/2019 11:22:04
apply lag     32 seconds               1 08/12/2019 11:22:05
apply lag     33 seconds               1 08/12/2019 11:22:06
apply lag     34 seconds               1 08/12/2019 11:22:07
apply lag     35 seconds               1 08/12/2019 11:22:08
apply lag     36 seconds               1 08/12/2019 11:22:09
apply lag     37 seconds               1 08/12/2019 11:22:10
apply lag     38 seconds               1 08/12/2019 11:22:11
apply lag     39 seconds               1 08/12/2019 11:22:12
apply lag     40 seconds               1 08/12/2019 11:22:13
apply lag     41 seconds               1 08/12/2019 11:22:14
apply lag     42 seconds               1 08/12/2019 11:22:15
apply lag     43 seconds               1 08/12/2019 11:22:16
apply lag     44 seconds               1 08/12/2019 11:22:17
apply lag     45 seconds               1 08/12/2019 11:22:18
apply lag     46 seconds               1 08/12/2019 11:22:19
apply lag      8 seconds               3 08/12/2019 18:00:10
apply lag      9 seconds               3 08/12/2019 18:00:11
apply lag     10 seconds               3 08/12/2019 18:00:12
apply lag      4 seconds               3 08/12/2019 23:10:08
apply lag      6 seconds               5 08/12/2019 23:10:10
apply lag      5 seconds               5 08/12/2019 23:20:22
apply lag      3 seconds              26 08/13/2019 00:05:44
apply lag      2 seconds             335 08/13/2019 10:05:10
apply lag      1 seconds            4026 08/13/2019 10:12:59
apply lag      0 seconds          176821 08/13/2019 10:13:16

48 rows selected.
SQL>




Conclusão: Multi-Instances Redo Apply em um Physical Standby Database Instances em um ambiente RAC irão compartilhar os seguintes benefícios:

  • Paralelismo, multi-instance recovery: physical standby database instances irão manter
    • Physical Standby recovery - utiliza CPU e I/O em todos os nodes de um RAC standby database
  • Multi-Instance Apply roda em todas instances em modo MOUNTED ou OPEN em physical standby database instances.
  • Exposto na configuração do Data Guard Broker (DGMGRL) com a propriedade ‘ApplyInstances’ no standby database.



Y V RaviKumar é um Oracle ACE e Oracle Certified Master (OCM) com 18 anos de experiência em instituições financeiras, serviços financeiros e seguros (BFSI) e atuou em diversos papeis como Senior Database Architect e Production DBA. Ele também é OCP em Oracle 8i, 9i, 10g, 11g & 12c e Certificado em Golden Gate, RAC, Performance Tuning& Oracle Exadata. Ele continua motivando muitos DBAs e ajudando a Oracle Community publicando suas dicas /ideias/sugestões/soluções em seu blog. Ele escreveu 40+ artigos OTN sobre Oracle Exadata, Oracle RAC e Oracle GoldenGate para a OTN em Espanhol, OTN em Português e OTN em inglês e 19 artigos para a TOAD World, 2 Artigos para o UKOUG, 3 Artigos para OTech Magazine e 2 Artigos para a Redgate. Ele é membro do AllIndia Oracle UserGroup (AIOUG) e frequente Oracle speaker in @NYOUG, @OTN, AIOUG, Sangam e IOUG. Ele desenha, projeta e implementa Core Banking System (CBS) Databases para o Central Banks em dois países – India e Mahe, Seychelles. Ele é Co-Founder do OraWorld (www.oraworld.com). Leia mais sobre o seu perfil na LaserSoft.

Mariami Kupatadze é uma Oracle Certified Master desde 2016. Ela é a primeira OCM em seu país, Georgia. Mari também é membro do ACE program. Ela é Senior Solutions Architect na FlashGrid Inc. Ela tem mais de 10 anos de experiência em Database Administration e ela vem trabalhando totalmente para 20 empresas durante sua carreira. Ela é altamente especializada em Exadata, Oracle 10g, 11g, 12c, 18c e19c Databases, RAC, Data Guard, Golden Gate, Database Optimization e Tuning, EM, Cloud Control, Backup e Recovery, Database Migrations, Oracle Cloud IaaS, Oracle Database Security, Oracle Audit Vault and Database Firewall, Linux Administration. Ela publicou muitos artigos na OraWorld e-magazine. Mari é palestrante de eventos como APAC Oracle Users Groups Community (APACOUC) e Luxembourg Oracle Users Group (LUXOUG). Ela é blogger desde 2010, publicou 279 posts sobre sua experiência com Oracle e Linux. Seu blog, dba010.com, entrou na lista dos Top 60 Oracle blogs.

Rodrigo Mufalani é um Oracle ACE member e Oracle Certified Master (OCM) com 15 anos de experiência, começou com o Oracle 8i, mas teve a oportunidade de dar suporte a Oracle 7.3.4 em diante. É especialista em banco de dados Oracle com foco principal em Engineered Systems, Performance & Tuning e RAC. Ele é fundador e presidente e também palestrante do Luxembourg Oracle User Group. É palestrante em eventos de Oracle como: OTN LAD TOUR e OTN EMEA TOUR e outros. Atualmente trabalha como Principal DBA na eProseed Europe. Foi o terceiro Oracle ACE a ser nomeado no Brasil. Twitter @mufalani / blog Mufalani.worpress.com

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.