— Query to check MRP Process :
SELECT PROCESS, STATUS, SEQUENCE# FROM gv$MANAGED_STANDBY;— Query to Find difference between Primary and Standby site :
Query 1:
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM gv$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM gv$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM gv$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM gv$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;Query 2:
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#;— Find latest sequence applied on Standby Site :
SELECT max(sequence#) AS "STANDBY", applied FROM v$archived_log GROUP BY applied;Disclaimer:
This is an independent personal blog and is not affiliated with, sponsored by, or endorsed by Oracle Corporation. The views, explanations, and experiences expressed here are the author’s own. Commands, scripts, and procedures may be based on Oracle’s official documentation and are provided for educational and reference purposes only. Always test and validate them before use in a Live/Production environment. Sample output shown in this article was generated during the author’s own testing and may vary depending on the environment.
Please read full Disclaimer.