ITPub博客

首页 > 数据库 > Oracle > 判断standby日志是否同步primary

判断standby日志是否同步primary

原创 Oracle 作者:pentium 时间:2019-01-08 15:39:39 0 删除 编辑

 

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-09-30

  • 博文量
    191
  • 访问量
    286662