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

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 !!!

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: