启动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# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
4 STANDBY /u01/app/oracle/oradata/orcl/redo04.log
5 STANDBY /u01/app/oracle/oradata/orcl/redo05.log
6 STANDBY /u01/app/oracle/oradata/orcl/redo06.log
7 STANDBY /u01/app/oracle/oradata/orcl/redo07.log
7 rows selected.
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION
在Oracle 官网上搜了一下， 发现有一个bug 4395779会导致这个问题。 bug 的描述如下：
| Bug 4395779 - ORA-16086 error when recovery area not used for redo logs [ID 4395779.8] | ||
|
| ||
|
| Modified 24-SEP-2008 Type PATCH Status PUBLISHED |
|
Bug 4395779 ORA-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
Click here for details of each of the sections below.
Affects:
| Product (Component) | Oracle Server (Rdbms) |
| Range of versions believed to be affected | Versions < 11 |
| Versions confirmed as being affected | |
| Platforms affected | Generic (all / most platforms affected) |
Fixed:
| This issue is fixed in | ||||
Symptoms: | Related To: | |||
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.1 Information on the sections in this article
但是查看了一下FRA，没有问题：
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
db_recovery_file_dest_size big 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://www.cndba.cn/Dave/article/1282
官网上还搜到一条信息，说是备库的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# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
4 STANDBY /u01/app/oracle/oradata/orcl/redo04.log
5 STANDBY /u01/app/oracle/oradata/orcl/redo05.log
6 STANDBY /u01/app/oracle/oradata/orcl/redo06.log
7 STANDBY /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的原因导致的。 如果遇到同样的问题，不妨先检查一下参数问题。
------------------------------------------------------------------------------
QQ:492913789
Email:ahdba@qq.com
Blog: http://www.cndba.cn/dave
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
