ORACLE dataguard 主备库延迟 using current logfile

来源：互联网发布：不锈钢开孔器淘宝网编辑：程序博客网时间：2024/05/18 02:07

一. 问题描述：

BI分析部门反映夜间拉取的ORACLE备库数据和业务部门导入主库的数据不一致，之间相差近2个小时。

二. 问题分析：

第一反映备库归档有GAP，主备库延迟，经过查询发现主备库归档并无延迟

SQL> select process, status, sequence# , ACTIVE_AGENTS from v$managed_standby;PROCESS   STATUS        SEQUENCE# ACTIVE_AGENTS--------- ------------ ---------- -------------ARCH      CLOSING            1468             0ARCH      CLOSING            1469             0ARCH      CONNECTED             0             0ARCH      CLOSING            1470             0RFS       IDLE                  0             0RFS       IDLE               1471             0RFS       IDLE                  0             0MRP0      WAIT_FOR_LOG       1471            41RFS       IDLE                  0             09 rows selected.

第二种可能性夜间网络延迟等故障导致主备库数据不一致，经过检查alert等并未发现任何异常

手工在主库做测试新建test表，插入数据.....发现确实未同步到备库，且一切正常无任何报错等。

主库测试：SQL> create table tevis (id number(10),  2  name varchar2(10));Table created.SQL> insert into tevis values(1, 'anhui');1 row created.SQL> commit;Commit complete.SQL> 备库查询：SQL> select * from tevis;select * from evisevis           *ERROR at line 1:ORA-00942: table or view does not existSQL>

查询备库v$dataguard_stats视图：

SQL> select * from v$dataguard_stats ;NAME                             VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME-------------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------transport lag                    +00 00:00:00         day(2) to second(0) interval   09/04/2017 16:03:23            09/04/2017 16:03:22apply lag                        +00 04:02:49         day(2) to second(0) interval   09/04/2017 16:03:23            09/04/2017 16:03:22apply finish time                +00 00:00:00.290     day(2) to second(3) interval   09/04/2017 16:03:23estimated startup time           9                    second                         09/04/2017 16:03:23SQL>

发现apply log竟已经达到了4h只多。

手工 alter system switch logfile;

发现备库测试数据tevis表数据已经同步，而且v$dataguard_stats中apply log 为零。

因此：导致主备库延迟的原因为 not apply current logfile

三. 问题处理：

SQL> alter  database recover managed  standby  database  cancel;Database altered.SQL> alter database recover managed standby database using current logfile disconnect;Database altered.SQL>

再去查询视图v$dataguard_stats

SQL> select * from v$dataguard_stats;NAME                             VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME-------------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------transport lag                    +00 00:00:00         day(2) to second(0) interval   09/04/2017 16:10:54            09/04/2017 16:10:52apply lag                        +00 00:00:00         day(2) to second(0) interval   09/04/2017 16:10:54            09/04/2017 16:10:52apply finish time                                     day(2) to second(3) interval   09/04/2017 16:10:54estimated startup time           9                    second                         09/04/2017 16:10:54SQL>

阅读全文

0 0