判斷standby日誌是否同步primary

 

Verify standby STB_DB is in synch with Primary PROD_DB

Confirm physical standby database STB_DB is in sync with the primary database PROD_DB
1. Connect to primary database PROD_DB  and note the current log sequence.
  SQL> select max(SEQUENCE#) from v$log;

2. Connect to standby database STB_DB server and confirm that:
   - The RFS process is currently receiving the same sequence#
   - The MRP0 (managed recovery) process is also processing the same sequence#
  SQL> set lines 200 pages 40
  SQL> s elect process, status, client_process, sequence#, block# from v$managed_standby where process = 'MRP0' or client_process = 'LGWR';

以上驗證方式只表示收到了 log, 但不表示已經 apply 了 .

 

下面的 SQL statement 驗證是否已經 applied, 以及是否同步 .

啟動 RMP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT ；

select   process,status,thread#,sequence#,blocks from v$managed_standby where process   like '%MRP%';     用以下查詢 check 是否還有 gap. SQL>   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# ; 現在沒有 gap 了 .

 

上面的查詢可以分解成以下幾個 :

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# ;

從 V$LOG_HISTORY 裡查詢到的最大的 sequence 怎麼就是 applied 的最大 sequence? 是不是因為它顯示的是控制檔案的 log history? 如果 applied 了 , 才可能更新了控制檔案 ?

v$log_history displays log history information from the control file.

v$archived_log displays archived log information from the control file, including archive log names.

 

或者用下面的查詢 ?

Primary:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
70

Standby:

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
70