Simple blog to enable MRP0 process using DGMGRL prompt
- Dataguard- EDIT DATABASE zabbixdr SET STATE=APPLY-ON;
DGMGRL> show configuration
Configuration - zabbix
Protection Mode: MaxPerformance
Members:
zabbix - Primary database
zabbixdr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 30 seconds ago)
DGMGRL> EDIT DATABASE zabbixdr SET STATE=APPLY-ON;
Succeeded.
DGMGRL> show configuration
Configuration - zabbix
Protection Mode: MaxPerformance
Members:
zabbix - Primary database
zabbixdr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 9 seconds ago)
DGMGRL> show database zabbixdr
Database - zabbixdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 5.00 KByte/s
Real Time Query: OFF
Instance(s):
ZABBIXDR
Database Status:
SUCCESS
- Command to check the DR database status
set lines 200
col MESSAGE for a60
SELECT FACILITY, ERROR_CODE, TIMESTAMP, MESSAGE FROM V$DATAGUARD_STATUS WHERE TRUNC(TIMESTAMP)= TRUNC(SYSTIMESTAMP) ORDER BY TIMESTAMP;
FACILITY ERROR_CODE TIMESTAMP MESSAGE
------------------------ ---------- --------- ------------------------------------------------------------
Log Transport Services 0 19-JUL-21 ARC0: Archival started
Log Transport Services 0 19-JUL-21 ARC1: Archival started
Log Transport Services 0 19-JUL-21 ARC2: Archival started
Log Transport Services 0 19-JUL-21 ARC1: Becoming the 'no FAL' ARCH
Log Transport Services 12541 19-JUL-21 FAL[client, ARC0]: Error 12541 connecting to zabbix for fetc
hing gap sequence
Log Transport Services 0 19-JUL-21 ARC2: Becoming the active heartbeat ARCH
Log Transport Services 0 19-JUL-21 ARC3: Archival started
Log Transport Services 12541 19-JUL-21 Error 12541 received logging on to the standby
Log Transport Services 12541 19-JUL-21 Check whether the listener is up and running.
FACILITY ERROR_CODE TIMESTAMP MESSAGE
------------------------ ---------- --------- ------------------------------------------------------------
Log Transport Services 0 19-JUL-21 ARC2: Becoming the heartbeat ARCH
Log Apply Services 0 19-JUL-21 Attempt to start background Managed Standby Recovery process
Log Apply Services 0 19-JUL-21 MRP0: Background Managed Standby Recovery process started
Log Apply Services 0 19-JUL-21 Managed Standby Recovery starting Real Time Apply
Remote File Server 0 19-JUL-21 RFS[1]: Assigned to RFS process (PID:5588)
Remote File Server 0 19-JUL-21 Primary database is in MAXIMUM PERFORMANCE mode
Remote File Server 0 19-JUL-21 RFS[3]: Assigned to RFS process (PID:5594)
Remote File Server 0 19-JUL-21 RFS[2]: Assigned to RFS process (PID:5592)
Log Transport Services 0 19-JUL-21 ARC3: Beginning to archive thread 1 sequence 53 (2332140-243
2852)
FACILITY ERROR_CODE TIMESTAMP MESSAGE
------------------------ ---------- --------- ------------------------------------------------------------
Log Apply Services 0 19-JUL-21 Media Recovery Waiting for thread 1 sequence 55 (in transit)
Log Apply Services 0 19-JUL-21 Media Recovery Log /u01/app/oracle/fast_recovery_area/ZABBIX
DR/archivelog/2021_07_19/o1_mf_1_54_jhbqjq1l_.arc
Log Transport Services 0 19-JUL-21 ARC3: Completed archiving thread 1 sequence 53 (0-0)
Log Transport Services 0 19-JUL-21 ARC3: Beginning to archive thread 1 sequence 55 (2432983-243
7745)
Log Apply Services 0 19-JUL-21 Media Recovery Waiting for thread 1 sequence 56 (in transit)
Log Transport Services 0 19-JUL-21 ARC3: Completed archiving thread 1 sequence 55 (0-0)
24 rows selected.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
55 1
SQL> SELECT NAME, VALUE, UNIT, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME IN ('transport lag','apply lag','apply finish time');
NAME VALUE UNIT TIME_COMPUTED
-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 07/19/2021 17:25:44
apply lag +00 00:00:00 day(2) to second(0) interval 07/19/2021 17:25:44
apply finish time day(2) to second(3) interval 07/19/2021 17:25:44
SQL>
- Some queries to check the primary and physical standby sync status.
SELECT PID, PROCESS, STATUS, CLIENT_PROCESS, CLIENT_PID, THREAD#, SEQUENCE# SEQ#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ORDER BY PROCESS;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log
where first_time between (sysdate-1) and (sysdate+1)
group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log
group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log where applied='YES'
group by thread# order by 1;
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ASC;
select process, thread#, sequence#, status from v$managed_standby where process='MRP0';