12c DG 切换保护模式报ORA-16086: Redo data cannot be written to the standby redo log

最新推荐文章于 2024-12-24 17:56:31 发布

原创最新推荐文章于 2024-12-24 17:56:31 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 12c 同时被 2 个专栏收录

75 篇文章

订阅专栏

故障处理

16 篇文章

订阅专栏

本文详细记录了在Oracle12c数据库中，切换保护模式至最大保护时遇到的问题及解决过程。主库启动报错ORA-03113和ORA-16086，通过调整standby redolog文件大小并重建日志组，最终成功解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2017-07-11 23:4412330原创Oracle 12c 故障处理编辑删除

本文链接：https://www.cndba.cn/leo1990/article/2012

12c 切换保护模式时，切换至最大可用 PROTECTION_LEVEL 为 RESYNCHRONIZATION，

切换至最大保护时主库启动报错。

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY RESYNCHRONIZATION    READ ONLY WITH APPLY


SQL> alter database set standby database to maximize protection;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 28863 Session ID: 1 Serial number: 45919

跟踪trace 查到如下信息，ORA-16086 错误

Error 16086 cre   ating standby archive log file at host 'pdbcndba_p'
*** 2017-06-20 19:41:26.385495 5634 krsh.c
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16086)
*** 2017-06-20 19:41:26.385595 5634 krsh.c
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
ORA-16086: Redo data cannot be written to the standby redo log
*** 2017-06-20 19:41:26.386015 5634 krsh.c
Error 16086 for archive log file 2 to 'pdbcndba_p'
*** 2017-06-20 19:41:26.386053 8506 krsi.c
krsi_dst_fail_caller: LAD:2 err:16086 force:0 blast:1

尝试以下方法，并没有解决：

--备库执行
alter database recover managed standby database cancel;
主库备库删除standby redo log日志组
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL>
在新主库新从库上建立3个standby redo log
alter database add standby logfile group 4 ('/oracle/app/oracle/data/powerdes/redo_dg_01.log') size 50m;
alter database add standby logfile group 5 ('/oracle/app/oracle/data/powerdes/redo_dg_12.log') size 50m;
alter database add standby logfile group 6 ('/oracle/app/oracle/data/powerdes/redo_dg_13.log') size 50m;
然后在新从库执行：执行redo应用：
alter database recover managed standby database disconnect from session;
alter system set standby_file_management='AUTO';

后来发现，redo log 和 standby log 文件大小不一致如下图：

重新创建 standby log

alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo01.log'  size 200M;
alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo02.log'  size 200M;
alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo03.log'  size 200M;
alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo04.log'  size 200M;

select decode(g.THREAD#, null, vs.THREAD#, g.THREAD#) THREAD#,
       decode(g.GROUP#, null, vs.GROUP#, g.GROUP#) GROUP#,
       decode(g.BYTES, null, vs.BYTES / 1024 / 1024, g.BYTES / 1024 / 1024) BYTES,
       decode(g.STATUS, null, vs.STATUS, g.STATUS) STATUS,
       decode(g.ARCHIVED, null, vs.ARCHIVED, g.ARCHIVED) ARCHIVED,
       --decode(g.GROUP#,null,'STANDBY REDO','REDO') type,
       gf.TYPE,
       gf.MEMBER
  from v$logfile gf
  left join v$log g
    on gf.GROUP# = g.GROUP#
  left join v$standby_log vs
    on gf.GROUP# = vs.GROUP#
 order by 1, 2

结果一样：

可以正常切换保护模式,出现上述问题的原因，主要是不细心，其实所有的操作步骤都是对的，在搭建DG 的时候没注意,埋了个坑。

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PROTECTION   PRIMARY	      MAXIMUM PROTECTION   READ WRITE