During set up of a physical standby database, one of the standby redo logfiles was marked as "ACTIVE" when querying the v$standby_log:
THREAD# GROUP# SEQUENCE# BYTES ARCHIVED STATUS
---------- ---------- ---------- ---------- --------- ------------------------------
0 12 0 2097152000 YES UNASSIGNED
0 13 0 2097152000 YES UNASSIGNED
0 14 0 2097152000 YES UNASSIGNED
0 15 0 2097152000 YES UNASSIGNED
0 16 0 2097152000 YES UNASSIGNED
0 17 0 2097152000 YES UNASSIGNED
0 18 0 2097152000 YES UNASSIGNED
1 11 392344 2097152000 YES ACTIVE
The file is not even existing on my system, but the path was somehow copied from the primary database, which has a different file structure.
The query:
SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
would return
GROUP# TYPE MEMBER
---------- --------------------- ------------------------------------------------------------
11 STANDBY /backup/fast_recovery_area/PRODDB01/onlinelog/stb_redo01.log
The path /backup/fast_recovery_area/PRODDB01/onlinelog doesn't even exist on my server.
This makes it impossible to drop and recreate it:
SQL> alter database drop standby logfile group 11;
alter database drop standby logfile group 11
*
ERROR at line 1:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1:
'/fra/stdb/onlinelog/stb_redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Solution: Clear the logfile:
SQL> alter database clear logfile group 11;
Database altered.
Then drop and recreate the standby redo logfile:
SQL> alter database drop standby logfile group 11;
Database altered.
SQL> alter database add standby logfile group 11 ('/data1/oradata/PRODDB01/stb_redo01.log') size 2000M;