判断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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14377/viewspace-2375059/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14377/viewspace-2375059/