从报错字面上可以得知是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.
SYMPTOMSDisclaimer:
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: PRIMARYStandby:
DB Unique Name: STDBYIn 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 restartedCAUSE
On Standbylog_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 2SOLUTION
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 2018Primary 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 logError: 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 log01/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 logDGMGRL
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 DatabaseSQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM 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 4814Standby 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 UNASSIGNEDSQL> SELECT timestamp, gvi.thread#, message FROM 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 ACTIVE8 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 UNASSIGNEDStandby 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 UNUSED8 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 ;