Monitor MRP in physical standby
The physical standby database in a dataguard may lag from the archivelogs between primary and standby
This causes the primary database to stop shipping logs and causes MRP stuck issues
To monitor the lags between the primary and standby
Standby:
To start MRP in standby
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 1295 1
ARCH CLOSING 1294 1
ARCH CONNECTED 0 0
ARCH CLOSING 1292 1
RFS IDLE 1296 1
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
MRP0 APPLYING_LOG <---- 1296 1
9 rows selected.
The status of the standby should be applying_log and MRP should be active
Standby:
SQL> !ps -ef|grep -i mrp
oracle 6129 1 42 18:54 ? 01:01:13 ora_mrp0_orcldg
Heartbeat should be happening between primary and standby from dataguard_status
Standby:
SQL> select message from v$dataguard_status;
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: Beginning to archive thread 1 sequence 1290 (7385563-7485630)
ARC0: Completed archiving thread 1 sequence 1290 (7385563-7485630)
Error 12541 received logging on to the standby
Check whether the listener is up and running.
MESSAGE
--------------------------------------------------------------------------------
Error 12541 for archive log file 1 to 'orcldg'
Error 12541 received logging on to the standby
Check whether the listener is up and running.
FAL[server, ARC3]: Error 12541 creating remote archivelog file 'orcldg'
ARC3: Beginning to archive thread 1 sequence 1291 (7485630-7486952)
ARC3: Completed archiving thread 1 sequence 1291 (7485630-7486952)
TT00: Standby redo logfile selected for thread 1 sequence 1291 for destination L
OG_ARCHIVE_DEST_2
LNS: Beginning to archive log 1 thread 1 sequence 1291
LNS: Completed archiving log 1 thread 1 sequence 1291
MESSAGE
--------------------------------------------------------------------------------
TT00: Standby redo logfile selected for thread 1 sequence 1292 for destination L
OG_ARCHIVE_DEST_2
LNS: Beginning to archive log 2 thread 1 sequence 1292
ARC0: Standby redo logfile selected for thread 1 sequence 1068 for destination L
OG_ARCHIVE_DEST_2
ARC2: Archive log rejected (T-1.S-1096) at host 'orcldg'
ARC3: Archive log rejected (T-1.S-1097) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1128) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1160) at host 'orcldg'
MESSAGE
--------------------------------------------------------------------------------
ARC2: Archive log rejected (T-1.S-1161) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1190) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1191) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1221) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1222) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1246) at host 'orcldg'
ARC3: Beginning to archive thread 1 sequence 1292 (7486952-7491549)
LNS: Completed archiving log 2 thread 1 sequence 1292
TT00: Standby redo logfile selected for thread 1 sequence 1293 for destination L
OG_ARCHIVE_DEST_2
MESSAGE
--------------------------------------------------------------------------------
LNS: Beginning to archive log 3 thread 1 sequence 1293
ARC3: Completed archiving thread 1 sequence 1292 (7486952-7491549)
ARC0: Beginning to archive thread 1 sequence 1293 (7491549-7495011)
LNS: Completed archiving log 3 thread 1 sequence 1293
TT00: Standby redo logfile selected for thread 1 sequence 1294 for destination L
OG_ARCHIVE_DEST_2
LNS: Beginning to archive log 1 thread 1 sequence 1294
ARC0: Completed archiving thread 1 sequence 1293 (7491549-7495011)
ARC1: Beginning to archive thread 1 sequence 1294 (7495011-7498733)
LNS: Completed archiving log 1 thread 1 sequence 1294
MESSAGE
--------------------------------------------------------------------------------
TT00: Standby redo logfile selected for thread 1 sequence 1295 for destination L
OG_ARCHIVE_DEST_2
LNS: Beginning to archive log 2 thread 1 sequence 1295
ARC1: Completed archiving thread 1 sequence 1294 (7495011-7498733)
If applied archivelog sequence in standby is close to primary then both are in sync
Primary:
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
Check max sequence from primary database
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
1532
Check archivelog list to validate
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1295
Next log sequence to archive 1297
Current log sequence 1297
Standby:
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 1295 1
ARCH CLOSING 1294 1
ARCH CONNECTED 0 0
ARCH CLOSING 1292 1
RFS IDLE 1296 1
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
MRP0 APPLYING_LOG 1296 1
Both are in sync !!!