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.
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;