Exibir Contas
Fazer Login
Voltar
Conta da Oracle
Nenhum resultado encontrado
Sua pesquisa não corresponde a nenhum resultado.
Sugerimos que você tente o seguinte para ajudar a encontrar o que procura:
- Verifique a ortografia da sua pesquisa por palavra-chave.
- Use sinônimos para a palavra-chave digitada; por exemplo, tente “aplicativo” em vez de “software.”
- Tente uma das pesquisas populares mostradas abaixo.
- Inicie uma nova pesquisa.
Perguntas Frequentes
Multi-Instances Redo Apply em Physical Standby Database Instances em um ambiente 3-Node RAC
Por Y V Ravi Kumar 
, Mariami Kupatadz , Rodrigo Mufalani
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
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.