Oracle Data Guard ORA-16086: standby database does not contain available standby 处理方法

启动Data Guard后，查看同步情况：

SQL>select error from v$archive_dest;

ERROR

-----------------------------------------------------------------

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

ERROR

-----------------------------------------------------------------

10 rows selected.

SQL>

报了个错，因为我设置的是最大可用性模式，这种模式必须配置standby logfile。这个环境下的standby log我之前设置过了：

SQL> select * from v$logfile;

rows will be truncated

GROUP# STATUSTYPEMEMBER

---------- ------- ------- -----------------------------------------------------

3ONLINE/u01/app/oracle/oradata/orcl/redo03.log

2ONLINE/u01/app/oracle/oradata/orcl/redo02.log

1ONLINE/u01/app/oracle/oradata/orcl/redo01.log

4STANDBY /u01/app/oracle/oradata/orcl/redo04.log

5STANDBY /u01/app/oracle/oradata/orcl/redo05.log

6STANDBY /u01/app/oracle/oradata/orcl/redo06.log

7STANDBY /u01/app/oracle/oradata/orcl/redo07.log

7 rows selected.

SQL>select protection_mode,protection_level from v$database;

PROTECTION_MODEPROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY RESYNCHRONIZATION

在Oracle官网上搜了一下，发现有一个bug 4395779会导致这个问题。bug的描述如下：

Bug 4395779 - ORA-16086 error when recovery area not used for redo logs [ID 4395779.8]
	Modified24-SEP-2008TypePATCHStatusPUBLISHED

Bug 4395779ORA-16086 error when recovery area not used for redo logs

This note gives a brief overview of bug 4395779.
The content was last updated on: 03-APR-2008
Clickherefor details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versionsbelievedto be affected	Versions < 11
Versionsconfirmedas being affected	10.1.0.5 10.2.0.2
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in		10.2.0.3 (Server Patch Set) 11.1.0.6 (Base Release)
Symptoms:		Related To:
Error May Occur ORA-16086		Physical Standby Database / Dataguard

Description

A physical standby may report ORA-16086 when the recovery area is full,

even if archived or standby redo logs are not being placed in the

recovery area. This fix also checks for other non recovery area

destinations.

Please note:The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

References

Bug:4395779(This link will only work for PUBLISHED bugs)
Note:245840.1Information on the sections in this article

---

---

但是查看了一下FRA，没有问题：

SQL> show parameter db_recovery

NAMETYPEVALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_deststring/u01/app/oracle/flash_recovery

db_recovery_file_dest_sizebig integer 2G

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100

-----------------------------

.0819

空间还有很多，所以应该不是这个bug导致的。有关FRA的问题参考：

Flash Recovery Area空间不足导致数据库不能打开或hang住

http://blog.youkuaiyun.com/xujinyang/article/details/6830475

官网上还搜到一条信息，说是备库的standby log和主库的online大小不一致，检查了一下这2个大小，也没有问题：

主库：

SQL> SELECT BYTES FROM V$LOG;

BYTES

----------

52428800

52428800

52428800

备库：

SQL> select bytes from v$standby_log;

BYTES

----------

52428800

52428800

52428800

52428800

所以决定重建一下standby log files。在主备库都重建。之前这个DG上做了Broker的测试，可能是这个原因导致的。

主库简单一点，直接drop，再添加就可以了。备库需要先取消recover进程，才能操作，最后启用recover。这里只列举备库的操作：

取消recover：

SQL> alter database recover managed standby database cancel;

Database altered.

删除standby log：

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

删除物理文件：

[oracle@dg2 orcl]$ rm redo04.log

[oracle@dg2 orcl]$ rm redo05.log

[oracle@dg2 orcl]$ rm redo06.log

[oracle@dg2 orcl]$ rm redo07.log

添加standby log：

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;

Database altered.

SQL> select * from v$logfile;

rows will be truncated

GROUP# STATUSTYPEMEMBER

---------- ------- ------- -----------------------------------------------------

3ONLINE/u01/app/oracle/oradata/orcl/redo03.log

2ONLINE/u01/app/oracle/oradata/orcl/redo02.log

1ONLINE/u01/app/oracle/oradata/orcl/redo01.log

4STANDBY /u01/app/oracle/oradata/orcl/redo04.log

5STANDBY /u01/app/oracle/oradata/orcl/redo05.log

6STANDBY /u01/app/oracle/oradata/orcl/redo06.log

7STANDBY /u01/app/oracle/oradata/orcl/redo07.log

7 rows selected.

启动recover：

SQL> alter database recover managed standby database disconnect from session;

Database altered.

到主库再次查看一下，搞定：

SQL> select error from v$archive_dest;

ERROR

-----------------------------------------------------------------

10 rows selected.

用SQL查看了一下同步正常：

SQL> select sequence#,applied from v$archived_log;

但是把OS重启之后，又出现了同样的问题。这次不能在重建standby log了。因为在这个DG上配置了Broker。后来把Broker删除了。所以打算先看一下pfile文件。

先用spfile创建了pfile，然后查看了一下。发现pfile里有很多重复的参数，并且格式不一样，如：

*.standby_archive_dest='/u01/archive'

orcl.standby_archive_dest=''

加星号是正确的参数，以实例开头的参数是错误的。把这些以实例orcl开头的参数全部删除。在用pfile重启启动。没有错误了。看来还是Broker的原因导致的。如果遇到同样的问题，不妨先检查一下参数问题。

------------------------------------------------------------------------------