签到成功

知道了

CNDBA社区CNDBA社区

Oracle Data Guard ORA-16086 Redo data cannot be written to the standby redo log 解决方法

2025-06-19 00:19 27 0 原创 Oracle 19c
作者： dave
本文链接：http://www.cndba.cn/dave/article/131636

1 故障现象

搭建一套RAC 到 单实例的DG，启动同步后报错如下：http://www.cndba.cn/dave/article/131636

http://www.cndba.cn/dave/article/131636
http://www.cndba.cn/dave/article/131636

SQL> select dest_name,status,process,error,transmit_mode from v$archive_dest where target='STANDBY';

DEST_NAME         STATUS    PROCESS
-------------------- --------- ----------
ERROR                                  TRANSMIT_MOD
----------------------------------------------------------------- ------------
LOG_ARCHIVE_DEST_2   VALID     LGWR
                                  PARALLELSYNC

LOG_ARCHIVE_DEST_3   ERROR     LGWR
ORA-16086: Redo data cannot be written to the standby redo log      PARALLELSYNC

2 解决方法

查看redo 相关信息：http://www.cndba.cn/dave/article/131636

SQL>  select group#,bytes/1024/1024,members,status from v$log;

    GROUP# BYTES/1024/1024    MEMBERS STATUS
---------- --------------- ---------- ----------------
    15           500        2 CLEARING
    14           500        2 CLEARING
    13           500        2 CURRENT
    11           500        2 CLEARING
    21           500        2 CLEARING
    20           500        2 CLEARING
    19           500        2 CLEARING
    18           500        2 CLEARING
    17           500        2 CLEARING
    16           500        2 CURRENT

10 rows selected.

SQL> select group#,type, member from v$logfile order by 2;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
    21 ONLINE  /u01/archivelog/onlinelog/group_21.341.1110205541
    21 ONLINE  /u01/archivelog/onlinelog/group_21.2150.1110205557
    20 ONLINE  /u01/archivelog/onlinelog/group_20.2151.1110205527
    20 ONLINE  /u01/archivelog/onlinelog/group_20.340.1110205511
    19 ONLINE  /u01/archivelog/onlinelog/group_19.2152.1110205497
    19 ONLINE  /u01/archivelog/onlinelog/group_19.339.1110205481
    18 ONLINE  /u01/archivelog/onlinelog/group_18.2153.1110205467
    18 ONLINE  /u01/archivelog/onlinelog/group_18.338.1110205451
    17 ONLINE  /u01/archivelog/onlinelog/group_17.2154.1110205437
    17 ONLINE  /u01/archivelog/onlinelog/group_17.337.1110205421
    16 ONLINE  /u01/archivelog/onlinelog/group_16.2155.1110205407
    16 ONLINE  /u01/archivelog/onlinelog/group_16.336.1110205393
    15 ONLINE  /u01/archivelog/onlinelog/group_15.2156.1110205377
    15 ONLINE  /u01/archivelog/onlinelog/group_15.335.1110205363
    14 ONLINE  /u01/archivelog/onlinelog/group_14.2157.1110205349
    14 ONLINE  /u01/archivelog/onlinelog/group_14.334.1110205333
    13 ONLINE  /u01/archivelog/onlinelog/group_13.2158.1110205319
    13 ONLINE  /u01/archivelog/onlinelog/group_13.333.1110205303
    11 ONLINE  /u01/archivelog/onlinelog/group_11.2159.1110205289
    11 ONLINE  /u01/archivelog/onlinelog/group_11.309.1110205273
    31 STANDBY /u01/archivelog/onlinelog/group_31.354.1110206095
    33 STANDBY /u01/archivelog/onlinelog/group_33.2137.1110206001
    32 STANDBY /u01/archivelog/onlinelog/group_32.348.1110206021
    32 STANDBY /u01/archivelog/onlinelog/group_32.2131.1110206043
    33 STANDBY /u01/archivelog/onlinelog/group_33.343.1110205983
    30 STANDBY /u01/archivelog/onlinelog/group_30.2127.1110206095
    30 STANDBY /u01/archivelog/onlinelog/group_30.351.1110206075
    29 STANDBY /u01/archivelog/onlinelog/group_29.2134.1110206041
    29 STANDBY /u01/archivelog/onlinelog/group_29.347.1110206021
    28 STANDBY /u01/archivelog/onlinelog/group_28.2136.1110206001
    28 STANDBY /u01/archivelog/onlinelog/group_28.344.1110205983
    27 STANDBY /u01/archivelog/onlinelog/group_27.2125.1110206095
    27 STANDBY /u01/archivelog/onlinelog/group_27.353.1110206077
    26 STANDBY /u01/archivelog/onlinelog/group_26.2126.1110206095
    26 STANDBY /u01/archivelog/onlinelog/group_26.352.1110206077
    25 STANDBY /u01/archivelog/onlinelog/group_25.2128.1110206081
    25 STANDBY /u01/archivelog/onlinelog/group_25.350.1110206065
    24 STANDBY /u01/archivelog/onlinelog/group_24.2133.1110206043
    24 STANDBY /u01/archivelog/onlinelog/group_24.349.1110206021
    23 STANDBY /u01/archivelog/onlinelog/group_23.2132.1110206043
    23 STANDBY /u01/archivelog/onlinelog/group_23.346.1110206019
    22 STANDBY /u01/archivelog/onlinelog/group_22.2135.1110206037
    22 STANDBY /u01/archivelog/onlinelog/group_22.345.1110206015
    31 STANDBY /u01/archivelog/onlinelog/group_31.2124.1110206109

因为是从RAC 的备份直接恢复到单实例的，所以相关的redo log 文件并不存在：http://www.cndba.cn/dave/article/131636

http://www.cndba.cn/dave/article/131636
http://www.cndba.cn/dave/article/131636 

[oracle@cndba.cn archivelog]$ ll /u01/archivelog/onlinelog/group_11.2159.1110205289
ls: cannot access /u01/archivelog/onlinelog/group_11.2159.1110205289: No such file or directory

删除 standby redo 并重建恢复：http://www.cndba.cn/dave/article/131636http://www.cndba.cn/dave/article/131636

取消MRP:http://www.cndba.cn/dave/article/131636

SQL> alter database recover managed standby database cancel;


alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
alter database drop logfile group 25;
alter database drop logfile group 26;
alter database drop logfile group 27;
alter database drop logfile group 28;
alter database drop logfile group 29;
alter database drop logfile group 30;
alter database drop logfile group 31;
alter database drop logfile group 32;
alter database drop logfile group 33;



SQL> select group#,type, member from v$logfile order by 1;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
    11 ONLINE  /u01/archivelog/onlinelog/group_11.2159.1110205289
    11 ONLINE  /u01/archivelog/onlinelog/group_11.309.1110205273
    13 ONLINE  /u01/archivelog/onlinelog/group_13.2158.1110205319
    13 ONLINE  /u01/archivelog/onlinelog/group_13.333.1110205303
    14 ONLINE  /u01/archivelog/onlinelog/group_14.334.1110205333
    14 ONLINE  /u01/archivelog/onlinelog/group_14.2157.1110205349
    15 ONLINE  /u01/archivelog/onlinelog/group_15.335.1110205363
    15 ONLINE  /u01/archivelog/onlinelog/group_15.2156.1110205377
    16 ONLINE  /u01/archivelog/onlinelog/group_16.336.1110205393
    16 ONLINE  /u01/archivelog/onlinelog/group_16.2155.1110205407
    17 ONLINE  /u01/archivelog/onlinelog/group_17.2154.1110205437
    17 ONLINE  /u01/archivelog/onlinelog/group_17.337.1110205421
    18 ONLINE  /u01/archivelog/onlinelog/group_18.338.1110205451
    18 ONLINE  /u01/archivelog/onlinelog/group_18.2153.1110205467
    19 ONLINE  /u01/archivelog/onlinelog/group_19.2152.1110205497
    19 ONLINE  /u01/archivelog/onlinelog/group_19.339.1110205481
    20 ONLINE  /u01/archivelog/onlinelog/group_20.340.1110205511
    20 ONLINE  /u01/archivelog/onlinelog/group_20.2151.1110205527
    21 ONLINE  /u01/archivelog/onlinelog/group_21.341.1110205541
    21 ONLINE  /u01/archivelog/onlinelog/group_21.2150.1110205557

20 rows selected.


alter database add standby logfile group 22 ('/u01/app/oracle/oradata/hisdb/std_redo_22.LOG') size 500m;
alter database add standby logfile group 23 ('/u01/app/oracle/oradata/hisdb/std_redo_23.LOG') size 500m;
alter database add standby logfile group 24 ('/u01/app/oracle/oradata/hisdb/std_redo_24.LOG') size 500m;
alter database add standby logfile group 25 ('/u01/app/oracle/oradata/hisdb/std_redo_25.LOG') size 500m;
alter database add standby logfile group 26 ('/u01/app/oracle/oradata/hisdb/std_redo_26.LOG') size 500m;
alter database add standby logfile group 27 ('/u01/app/oracle/oradata/hisdb/std_redo_27.LOG') size 500m;
alter database add standby logfile group 28 ('/u01/app/oracle/oradata/hisdb/std_redo_28.LOG') size 500m;
alter database add standby logfile group 29 ('/u01/app/oracle/oradata/hisdb/std_redo_29.LOG') size 500m;
alter database add standby logfile group 30 ('/u01/app/oracle/oradata/hisdb/std_redo_30.LOG') size 500m;
alter database add standby logfile group 31 ('/u01/app/oracle/oradata/hisdb/std_redo_31.LOG') size 500m;
alter database add standby logfile group 32 ('/u01/app/oracle/oradata/hisdb/std_redo_32.LOG') size 500m;
alter database add standby logfile group 33 ('/u01/app/oracle/oradata/hisdb/std_redo_33.LOG') size 500m;

再次查看同步恢复正常：

SQL> select dest_name,status,process,error,transmit_mode from v$archive_dest where target='STANDBY';

DEST_NAME         STATUS    PROCESS
-------------------- --------- ----------
ERROR                                  TRANSMIT_MOD
----------------------------------------------------------------- ------------
LOG_ARCHIVE_DEST_2   VALID     LGWR
                                  PARALLELSYNC

LOG_ARCHIVE_DEST_3   ERROR     LGWR
ORA-16086: Redo data cannot be written to the standby redo log      PARALLELSYNC
--这里还是异常，等几分钟，在重新查询就正常了：

SQL> select dest_name,status,process,error,transmit_mode from v$archive_dest where target='STANDBY';

DEST_NAME         STATUS    PROCESS
-------------------- --------- ----------
ERROR                                  TRANSMIT_MOD
----------------------------------------------------------------- ------------
LOG_ARCHIVE_DEST_2   VALID     LGWR
                                  PARALLELSYNC

LOG_ARCHIVE_DEST_3   VALID     LGWR
                                  PARALLELSYNC

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2266
    原创
  • 3
    翻译
  • 579
    转载
  • 192
    评论
  • 访问：8106734次
  • 积分：4362
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ