ORA-16086: standby database does not contain available standby log files

早上来检查告警邮件,一套10.2.0.5的dg主库一直在报ora-16086错误,根据错误提示大概是备库没有可用的standby log files。在11g以后,这个错误名称换成了ORA-16086 Redo data cannot be written to the standby redo log

1
2
3
4
5
16086, 0000, "standby database does not contain available standby log files"
// *Cause: The primary database is in "no data loss" mode, but the standby
// database does not contain any "standby log files".
// *Action: Add one or more standby log files to the standby database.
// This can be done while the standby database is mounted.

当前主备同步依旧正常,切换日志也能顺利传到备库,备库也能正常应用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
主:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 384007
Next log sequence to archive 384012
Current log sequence 384012

备:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 383998
Next log sequence to archive 0
Current log sequence 384012

主备库均为最大可用模式

1
2
3
4
5
SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION

而主备库的standby log file均存在,且大小一致,状态正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/test/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/test/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/test/redo03.log NO
4 ONLINE /u01/app/oracle/oradata/test/redo04.log NO
5 ONLINE /u01/app/oracle/oradata/test/redo05.log NO
6 ONLINE /u01/app/oracle/oradata/test/redo06.log NO
7 STANDBY /u01/app/oracle/oradata/test/redo07.log NO
8 STANDBY /u01/app/oracle/oradata/test/redo08.log NO
9 STANDBY /u01/app/oracle/oradata/test/redo09.log NO
10 STANDBY /u01/app/oracle/oradata/test/redo10.log NO
11 STANDBY /u01/app/oracle/oradata/test/redo11.log NO
12 STANDBY /u01/app/oracle/oradata/test/redo12.log NO
13 STANDBY /u01/app/oracle/oradata/test/redo13.log NO

MOS上有说可能是因为fra目录满导致的bug,但检查数据库并没有启用fra

1
2
3
4
5
6
7
SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0

尝试重建standby log file,主备库均删除重建

1
2
3
4
5
6
7
8
9
10
11
12
13
alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;
alter database drop standby logfile group 10;
alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
alter database drop standby logfile group 13;

select 'alter database add standby logfile group '||group#||' '''||member||''' size 1g reuse;' from v$logfile where type='STANDBY';

recover managed standby database cancel;

recover managed standby database using current logfile disconnect from session;

观察了一小时,并未再出现告警。


ORA-16086: standby database does not contain available standby log files
https://www.xbdba.com/2019/02/14/ora-16086-standby-database-does-not-contain-available-standby-log-files/
作者
xbdba
发布于
2019年2月14日
许可协议