暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

ORA-16086: Redo data cannot be written to the standby redo log

原创 肖雪松 2021-11-17
6010

从报错字面上可以得知是standby redo没办法写入,有很多原因,应该首先排除最常见的可能

1,主库,特别是standby alert日志是否有有价值的信息
2,redo 状态是否正常
select group#,bytes/1024/1024,members,status from v$log;
3,从库的参数,特别是log_archived_dest_1是否配置正确(改参数配置为备库的信息)

以上提供了几个排查思路供大家参考,下面引用了一些其他的可能

ORA-16086: Redo Data Cannot Be Written To The Standby Redo Log (Doc ID 2281449.1)

APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS

Disclaimer:
NOTE: In the images and/or the document content below, the user information and environment data used represents
fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or
other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and
not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Primary:
DB Unique Name: PRIMARY - Net Connection Alias: PRIMARY

Standby:
DB Unique Name: STDBY

In primary database

SQL> SELECT RECOVERY_MODE database_mode, type, status FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

DATABASE_MODE TYPE STATUS


IDLE UNKNOWN ERROR

SQL> SELECT status, error FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> ‘DEFERRED’ AND STATUS <> ‘INACTIVE’ and DEST_ID=2;

STATUS ERROR


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

CHANGES
Primary database was restarted

CAUSE
On Standby

log_archive_dest_1 location=E:<Fast_Recovery_Area directory>\ ARCH NODELAY MANDATORY REOPEN=300 NOMAX_FAILURE VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME =STDBY
log_archive_dest_2 service=PRIMARY LGWR SYNC AFFIRM VALID_FOR=(ONLINE _LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 DEFER
log_archive_min_succeed_dest 2

SOLUTION
As per documentation link:

https://docs.oracle.com/database/122/REFRN/LOG_ARCHIVE_MIN_SUCCEED_DEST.htm#REFRN10091

"If you are using the LOG_ARCHIVE_DEST_n parameters and automatic archiving is enabled, then the value of this parameter cannot exceed the number of enabled, valid destinations specified as MANDATORY plus the number of enabled, valid destinations that are configured with the OPTIONAL and LOCATION attributes.

If you are using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST and automatic archiving is enabled, a value of 1 specifies that the destination specified in LOG_ARCHIVE_DEST must succeed. A value of 2 specifies that the destinations specified in both parameters must succeed.

If the value of this parameter is less than the number of enabled, valid MANDATORY destinations, this parameter is ignored in favor of the MANDATORY destination count. If the value is more than the number of enabled, valid MANDATORY destinations, then some of the enabled, valid destinations configured with the OPTIONAL and LOCATION attributes are treated as MANDATORY.

You can switch dynamically from using the older parameters to the LOG_ARCHIVE_DEST_n parameter using ALTER SYSTEM, as follows:

Set LOG_ARCHIVE_MIN_SUCCEED_DEST to 1.

Set the value of LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST to the null string.

Set the desired number of destinations for the LOG_ARCHIVE_DEST_n parameters.

Reset LOG_ARCHIVE_MIN_SUCCEED_DEST to the desired value."

Parameter log_archive_min_succeed_dest should be set on 1 on Standby.

ORA-16086:Redo无法写入standby redo log 报有 RFS[368]: No standby redo logfiles selected (reason:7) (Doc ID 2469675.1)

适用于:
Oracle Database Cloud Exadata Service - 版本 N/A 和更高版本
Oracle Database Cloud Service - 版本 N/A 和更高版本
Oracle Database - Enterprise Edition - 版本 12.1.0.2 和更高版本
Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本
本文档所含信息适用于所有平台
症状
注意:在下面的图片/文档内容中,使用的用户信息和环境数据表示来自Oracle示例模式、随Oracle数据库产品交付的公共文档或其他培训材料的虚构数据。任何与真实环境、真实的人相似之处纯粹是巧合,而不是以任何方式有意为之。
在本文档中,用以下虚构的环境作为例子来描述这个过程

Database Unique Name: rmsdb_prd
Primary database: tccracdb
Standby database: racprdro
Configuration: dgconf1


错误:ORA-16810:检测到数据库的多个错误或警告

Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

ERROR ORA-16086: Redo data cannot be written to the standby redo log
RFS[368]: No standby redo logfiles selected (reason:7)

Standby Database Alertlog

Archived Log entry 56 added for thread 1 sequence 3810 rlc 907769776 ID 0x7a27572d dest 2:

RFS[1]: No standby redo logfiles available for T-1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
RFS[1]: Opened log for thread 1 sequence 3811 dbid 2049385006 branch 907769776
Wed Jan 03 16:43:37 2018
Archived Log entry 57 added for thread 2 sequence 4381 rlc 907769776 ID 0x7a27572d dest 2:
RFS[2]: No standby redo logfiles available for T-2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
RFS[2]: Opened log for thread 2 sequence 4382 dbid 2049385006 branch 907769776
Wed Jan 03 16:49:54 2018

Primary Database Alert log

Wed Jan 24 08:55:58 2018
Errors in file /dbhome/oracle/diag/rdbms/tccracdb/tccracdb1/trace/tccracdb1_lgwr_25601.trc:
ORA-16086: Redo data cannot be written to the standby redo log
DataGuardBroker log

Error: The actual protection level ‘Resynchronization’ is different from the configured protection mode ‘MaxAvailability’.
Redo transport problem detected: redo transport to database racprdro has the following error:
ORA-16086: Redo data cannot be written to the standby redo log
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration dgconf1 Warning ORA-16607
Primary Database tccracdb Error ORA-16810
Physical Standby Database racprdro Success ORA-00000
DataGuardBroker log

01/24/2018 08:59:49
Redo transport problem detected: redo transport to database racprdro has the following error:
ORA-16086: Redo data cannot be written to the standby redo log
01/24/2018 09:00:49
Redo transport problem detected: redo transport to database racprdro has the following error:
ORA-16086: Redo data cannot be written to the standby redo log

DGMGRL

DGMGRL> show configuration verbose;
Configuration - dgconf1
Protection Mode: MaxAvailability
Members:
tccracdb - Primary database
Error: ORA-16810: multiple errors or warnings detected for the database
racprdro - Physical standby database
Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold
Primary Database

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gvarchivedestgvad,gvarchive_dest gvad, gvinstance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

THREAD# DEST_ID STATUS ERROR FAIL_SEQUENCE
1 1 VALID 0
1 3 ERROR ORA-16086: Redo data cannot be written to the standby redo log 4272
2 1 VALID 0
2 3 ERROR ORA-16086: Redo data cannot be written to the standby redo log 4814

Standby Database

SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#; <<<<<<<<<<<<< Non of the standby redo logs are active at standby database side

THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
1 9 0 1048576000 YES UNASSIGNED
1 10 0 1048576000 YES UNASSIGNED
1 11 0 1048576000 YES UNASSIGNED
1 12 0 1048576000 YES UNASSIGNED
1 13 0 1048576000 YES UNASSIGNED
2 14 0 1048576000 YES UNASSIGNED
2 15 0 1048576000 YES UNASSIGNED
2 16 0 1048576000 YES UNASSIGNED
2 17 0 1048576000 YES UNASSIGNED

SQL> SELECT timestamp, gvi.thread#, message FROM gvdataguardstatusgvds,gvdataguard_status gvds, gvinstance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in (‘Error’,‘Fatal’) ORDER BY timestamp, thread#;

TIMESTAMP THREAD# MESSAGE
24-JAN-2018 05:47:25 1 RFS[368]: No standby redo logfiles selected (reason:7)
24-JAN-2018 05:50:42 1 RFS[370]: No standby redo logfiles selected (reason:7)
24-JAN-2018 05:53:26 1 RFS[372]: No standby redo logfiles selected (reason:7)

更改

原因
Standby数据库 standby redolog的blocksize(512)与主数据库redolog的blocksize(4096)未匹配。因此,Primary数据库无法在Standby数据库standby redo 上写入日志数据。

Primary database

SQL> select group#,thread#,BYTES/1024/1024/1024 ,BLOCKSIZE,MEMBERS, status from v$log;
SQL> /

GROUP# THREAD# BYTES/1024/1024/1024 BLOCKSIZE MEMBERS STATUS


1 1 .9765625 4096 1 ACTIVE <<<< Blocksize 4096

2 1 .9765625 4096 1 INACTIVE
3 1 .9765625 4096 1 CURRENT
4 1 .9765625 4096 1 INACTIVE
5 2 .9765625 4096 1 ACTIVE
6 2 .9765625 4096 1 INACTIVE
7 2 .9765625 4096 1 CURRENT
8 2 .9765625 4096 1 ACTIVE

8 rows selected.

SQL> select GROUP#,THREAD#,BYTES/1024/1024/1024,BLOCKSIZE,STATUS from v$standby_log;

GROUP# THREAD# BYTES/1024/1024/1024 BLOCKSIZE STATUS


9 1 .9765625 512 UNASSIGNED <<<< Blocksize 512

10 1 .9765625 512 UNASSIGNED
11 1 .9765625 512 UNASSIGNED
12 1 .9765625 512 UNASSIGNED
13 1 .9765625 512 UNASSIGNED
14 2 .9765625 512 UNASSIGNED
15 2 .9765625 512 UNASSIGNED
16 2 .9765625 512 UNASSIGNED
17 2 .9765625 512 UNASSIGNED

Standby Database

SQL> select group#,thread#,BYTES/1024/1024/1024,BLOCKSIZE, MEMBERS, status from v$log;

GROUP# THREAD# BYTES/1024/1024/1024 BLOCKSIZE MEMBERS STATUS


1 1 .9765625 4096 2 UNUSED <<<< Blocksize 4096

2 1 .9765625 4096 2 UNUSED

3 1 .9765625 4096 2 UNUSED
4 1 .9765625 4096 2 UNUSED
5 2 .9765625 4096 2 UNUSED
6 2 .9765625 4096 2 UNUSED
7 2 .9765625 4096 2 UNUSED
8 2 .9765625 4096 2 UNUSED

8 rows selected.

SQL> select GROUP#,THREAD#,BYTES/1024/1024/1024,BLOCKSIZE,STATUS from v$standby_log;

GROUP# THREAD# BYTES/1024/1024/1024 BLOCKSIZE STATUS


9 1 .9765625 512 UNASSIGNED <<<< Blocksize 512

10 1 .9765625 512 UNASSIGNED
11 1 .9765625 512 UNASSIGNED
12 1 .9765625 512 UNASSIGNED
13 1 .9765625 512 UNASSIGNED
14 2 .9765625 512 UNASSIGNED
15 2 .9765625 512 UNASSIGNED
16 2 .9765625 512 UNASSIGNED
17 2 .9765625 512 UNASSIGNED
18 2 .9765625 512 UNASSIGNED

解决方案
Standby数据库standby redolog的blocksize(512)与Primary数据库redolog的blocksize(4096)未匹配。由于此Primary数据库无法在Standby数据库standby redo logs中写入数据。

删除并重新创建Primary数据库和Standby数据库上的standby redo log 并使用正确的块大小

ALTER DATABASE DROP STANDBY LOGFILE GROUP <group#>;
ALTER DATABASE ADD STANDBY LOGFILE THREAD <thread#> GROUP <group#> ‘’ size <primary_db_redolog_size> BLOCKSIZE ;

最后修改时间:2021-11-18 16:37:09
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

目录
  • 从报错字面上可以得知是standby redo没办法写入,有很多原因,应该首先排除最常见的可能
    • 以上提供了几个排查思路供大家参考,下面引用了一些其他的可能
    • ORA-16086:Redo无法写入standby redo log 报有 RFS[368]: No standby redo logfiles selected (reason:7) (Doc ID 2469675.1)