签到成功

知道了

CNDBA社区CNDBA社区

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

2020-12-20 11:51 743 0 原创 Oracle 11g
作者: Dave

1现象

新搭建的11g Data Guard, 主库报如下错误:

https://www.cndba.cn/dave/article/4331
https://www.cndba.cn/dave/article/4331
https://www.cndba.cn/dave/article/4331

[dave@www.cndba.cn1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 20 11:24:12 2020

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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
ERROR     LGWR
ORA-16086: Redo data cannot be written to the standby redo log    PARALLELSYNC


SQL>

[root@cd1 ~]# orz chkredo
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| GROUP | THREAD | SEQUENCE | STATUS   | ARCHIVED | TYPE    | SIZE(M) | MEMBER                                                 |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 6     | 1      | 6        | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo06.log     |
| 7     | 1      | 7        | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo07.log     |
| 8     | 1      | 8        | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo08.log     |
| 9     | 1      | 9        | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo09.log     |
| 10    | 1      | 10       | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo10.log     |
| 1     | 1      | 11       | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo01.log     |
| 2     | 1      | 12       | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo02.log     |
| 3     | 1      | 13       | INACTIVE | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo03.log     |
| 4     | 1      | 14       | ACTIVE   | YES      | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo04.log     |
| 5     | 1      | 15       | CURRENT  | NO       | ONLINE  | 2048    | /u01/app/oracle/oradata/CD_PD/onlinelog/redo05.log     |
| 20    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log |
| 13    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log |
| 16    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log |
| 14    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log |
| 18    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log |
| 17    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log |
| 19    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log |
| 41    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log |
| 12    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log |
| 11    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log |
| 15    |        |          |          |          | STANDBY |         | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[root@cd1 ~]#

备库查询standby redo log 是有记录,但物理上不存在:https://www.cndba.cn/dave/article/4331

[dave@www.cndba.cn2 archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 20 11:22:48 2020

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

    GROUP# TYPE    MEMBER
---------- ------- ------------------------------------------------------------
         1 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo01.log
         2 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo02.log
         3 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo03.log
         4 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo04.log
         5 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo05.log
         6 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo06.log
         7 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo07.log
         8 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo08.log
         9 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo09.log
        10 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo10.log
        11 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log
        12 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log
        13 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log
        14 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log
        15 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log
        16 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log
        17 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log
        18 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log
        19 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log
        20 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log
        41 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log

21 rows selected.

SQL>

SQL>
SQL>


[dave@www.cndba.cn2 onlinelog]$ ls /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log
ls: cannot access /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log: No such file or directory

2解决方法

此时直接drop 掉原来的redo 文件,并重建即可。https://www.cndba.cn/dave/article/4331

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

alter database drop logfile group  11;
alter database drop logfile group  12;
alter database drop logfile group  13;
alter database drop logfile group  14;
alter database drop logfile group  15;
alter database drop logfile group  16;
alter database drop logfile group  17;
alter database drop logfile group  18;
alter database drop logfile group  19;
alter database drop logfile group  20;
alter database drop logfile group  41;


 alter database add standby logfile group  11 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log') size 2048M;
 alter database add standby logfile group  12 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log') size 2048M;
 alter database add standby logfile group  13 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log') size 2048M;
 alter database add standby logfile group  14 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log') size 2048M;
 alter database add standby logfile group  15 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log') size 2048M;
 alter database add standby logfile group  16 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log') size 2048M;
 alter database add standby logfile group  17 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log') size 2048M;
 alter database add standby logfile group  18 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log') size 2048M;
 alter database add standby logfile group  19 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log') size 2048M;
 alter database add standby logfile group  20 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log') size 2048M;
 alter database add standby logfile group  41 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log') size 2048M;

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

    GROUP# TYPE    MEMBER
---------- ------- ------------------------------------------------------------
         1 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo01.log
         2 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo02.log
         3 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo03.log
         4 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo04.log
         5 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo05.log
         6 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo06.log
         7 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo07.log
         8 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo08.log
         9 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo09.log
        10 ONLINE  /u01/app/oracle/oradata/CD_PD/onlinelog/redo10.log
        11 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log
        12 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log
        13 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log
        14 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log
        15 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log
        16 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log
        17 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log
        18 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log
        19 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log
        20 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log
        41 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log

21 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

主库查询状态没变:https://www.cndba.cn/dave/article/4331

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
ERROR     LGWR
ORA-16086: Redo data cannot be written to the standby redo log    PARALLELSYNC


SQL>

重启下备库后再查询,恢复正常:https://www.cndba.cn/dave/article/4331https://www.cndba.cn/dave/article/4331https://www.cndba.cn/dave/article/4331

--备库:
[dave@www.cndba.cn2 archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 20 11:22:48 2020

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2237776 bytes
Variable Size            1895828144 bytes
Database Buffers         3388997632 bytes
Redo Buffers               57667584 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL>


--主库:
SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL>


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


SQL>

实际上这个环境是RMAN异机迁移后重新搭建的DG, 源环境也是一个DG,但是RMAN 迁移过程中,没有将redo 和standby redo 文件还原出来,虽然这些文件在控制文件中有记录,但实际上没有,从而导致了一系列问题。

https://www.cndba.cn/dave/article/4331

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

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

Dave

关注

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

  • 1902
    原创
  • 2
    翻译
  • 456
    转载
  • 131
    评论
  • 访问:3642151次
  • 积分:3137
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群