Saturday, April 30, 2011

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

Symptoms:
Dataguard physical standby redolog all become "active", and no redo can be received from primary database because of standby redolog full.
The setup previously was fine, the change is only a failover. (old primary becomes standby database through "reinstate database" command.)


RFS[214]: No standby redo logfiles of size 102400 blocks available
RFS[214]: No standby redo logfiles selected (reason:7)
Thu Apr 14 08:33:53 2011
Errors in file /oracle/admin/ORCL/udump/orcl_rfs_4658.trc:
ORA-16086: standby database does not contain available standby log files


And the flash recovery is not full:


SQL> select * from v$standby_log; <-- shows all the standby redo not being archived

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
------------- -------------------- ------------ --------------------
4 3335137383 1 4 52428800 252416 NO ACTIVE
107537956 2011-APR-13 14:40:45 107538463 2011-APR-13 14:44:25

5 3335137383 1 3 52428800 17413632 NO ACTIVE
107512877 2011-APR-13 07:04:46 107537956 2011-APR-13 14:40:45

6 3335137383 1 5 52428800 43436032 NO ACTIVE
107538463 2011-APR-13 14:44:25 107569782 2011-APR-13 19:08:06


GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ----------
FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
------------- -------------------- ------------ --------------------
7 3335137383 1 6 52428800 43436032 NO ACTIVE
107569782 2011-APR-13 19:08:06 107604733 2011-APR-13 22:01:38


SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .4 .27 105
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 9.79 8.95 198



Workaround:


Restarted the standby database, and problem resolved.