One single 11.2.0.4 instance Oracle database alert log shows following information;
RFS[1]: No standby redo logfiles available for thread 1
Data Guard shows ORA-16857 error:
DGMGRL> show database "TESTSTY"; Database - TESTSTY Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 10 minutes 32 seconds (computed 48 seconds ago) Apply Lag: 10 minutes 32 seconds (computed 48 seconds ago) Apply Rate: 39.97 MByte/s Real Time Query: OFF Instance(s): TESTSTY Database Warning(s): ORA-16857: standby disconnected from redo source for longer than specified threshold Database Status: WARNING
Checked both primary and standby database, the standby online redo logs have been created. It is strange to see some of the thread id is different between primary and secondary database.
— on standby :
SQL> select GROUP#,THREAD#,BYTES/1024/1024 ,ARCHIVED,STATUS from v$standby_log; 2 GROUP# THREAD# BYTES/1024/1024 ARC STATUS ---------- ---------- --------------- --- ---------- 4 1 50 NO UNASSIGNED 5 0 50 YES UNASSIGNED 6 0 50 YES UNASSIGNED 7 0 50 YES UNASSIGNED
— On Primary
SQL> select GROUP#,THREAD#,BYTES/1024/1024 ,ARCHIVED,STATUS from v$standby_log; GROUP# THREAD# BYTES/1024/1024 ARC STATUS ---------- ---------- --------------- --- ---------- 4 0 50 YES UNASSIGNED 5 0 50 YES UNASSIGNED 6 0 50 YES UNASSIGNED 7 0 50 YES UNASSIGNED
Drop all standby online redo logs on both primary and standby databases, and recreate then again by specify “thread 1” explicitly.
-- for standby db which is under recovery, recovery needs to be stopped first
SQL>alter database recover managed standby database cancel;
SQL>alter database add standby logfile thread 1 group 4 size 50m;
It will create standby online redo logs for one under +FRA, and another one under from “db_create_file_dest” parameter, if “db_create_online_log_dest_x” are not defined.
Finally restart the recovery process, everything is fine.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE; Database altered.