When working with Oracle Database Data Guard, standby redo log (SRL) configuration is usually straightforward. But every DBA eventually runs into that one logfile that refuses to behave.
A common and frustrating scenario is when a standby redo logfile is marked as ACTIVE, yet the physical file does not exist on disk. This prevents you from dropping or recreating the logfile and can block a clean Data Guard setup.
In this blog, we’ll walk through:
- Why this issue happens
- How to identify the problematic standby redo log
- Why dropping it fails
- The correct and safe solution: clearing the logfile first
- Best practices to avoid this issue in the future
This guide is written in a practical, human-friendly way and optimized for DBAs who want a clean fix—not guesswork.
Understanding Standby Redo Logfiles
Standby redo logs are critical in a physical standby database. They receive redo data directly from the primary database, enabling real-time apply and reducing data loss.
Unlike archived redo logs, SRLs are written to continuously. Because of this, Oracle keeps tight control over their state. If Oracle thinks an SRL is in use—even incorrectly—it will refuse destructive operations like DROP.
The Problem: A Standby Redo Log Stuck in ACTIVE State
During the setup of a physical standby database, you query the standby redo logs:
SELECT thread#, group#, sequence#, bytes, archived, status
FROM v$standby_log
ORDER BY thread#, group#;
You notice something unusual:
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
Everything looks normal—except group 11, which is marked ACTIVE.
Digging Deeper: The File Path Doesn’t Exist
Next, you check where Oracle thinks this logfile lives:
SELECT group#, type, member
FROM v$logfile
WHERE group# = 11;
Result:
GROUP# TYPE MEMBER
------ -------- ---------------------------------------------------
11 STANDBY /backup/fast_recovery_area/PRODDB01/onlinelog/stb_redo01.log
Here’s the problem:
- The directory does not exist on the standby server
- The path was likely copied from the primary database
- Oracle metadata still believes the logfile is present and active
This mismatch between control file metadata and physical storage is what causes the issue.
Why Dropping the Standby Redo Log Fails
Naturally, you try to drop the logfile:
ALTER DATABASE DROP STANDBY LOGFILE GROUP 11;
Oracle responds with:
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
This happens because:
- Oracle believes the logfile is ACTIVE
- An ACTIVE log cannot be dropped
- Oracle attempts to open the file
- The file doesn’t exist → operation fails
At this point, many DBAs get stuck.
The Correct Solution: Clear the Standby Redo Logfile
The key insight is this:
You must clear the logfile before dropping it.
Clearing a logfile tells Oracle:
- Forget the current redo contents
- Reset the logfile state
- Mark it safe for reuse or removal
Step 1: Clear the Standby Redo Log
ALTER DATABASE CLEAR LOGFILE GROUP 11;
Output:
Database altered.
This operation:
- Does not require the physical file to exist
- Resets the logfile metadata
- Removes the ACTIVE state
Step 2: Drop the Standby Redo Logfile
Now that the logfile is cleared, dropping it works:
ALTER DATABASE DROP STANDBY LOGFILE GROUP 11;
Output:
Database altered.
At this stage, the problematic standby redo log is fully removed from the control file.
Step 3: Recreate the Standby Redo Logfile Correctly
Finally, recreate the standby redo log using the correct path for the standby server:
ALTER DATABASE ADD STANDBY LOGFILE
GROUP 11 ('/data1/oradata/PRODDB01/stb_redo01.log')
SIZE 2000M;
Your standby redo log setup is now clean and consistent.
Why Clearing Works (And Why It’s Safe)
Clearing a logfile:
- Does not affect primary database redo
- Is safe on a standby database
- Is specifically designed for recovery and corruption scenarios
Oracle allows this operation precisely for situations where redo metadata and physical files are out of sync.
Best Practices to Avoid This Issue
To prevent this problem in future Data Guard setups:
1. Use Correct File Name Conversion
Always configure:
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
especially when directory structures differ between primary and standby.
2. Create Standby Redo Logs Manually
Avoid copying control files with invalid paths. Create SRLs explicitly on the standby.
3. Verify After Setup
Immediately run:
SELECT group#, status FROM v$standby_log;
Ensure no logs are stuck in ACTIVE state unexpectedly.
4. Monitor Data Guard Health
Use v$dataguard_stats and redo apply monitoring to catch issues early.
Final Thoughts
A standby redo logfile marked as ACTIVE—even when it doesn’t exist physically—can completely block a clean Oracle Data Guard configuration. The fix, however, is simple once you understand Oracle’s internal logic:
- Clear the logfile
- Drop it
- Recreate it correctly
This is one of those DBA problems that feels painful at first—but becomes a powerful troubleshooting skill once you’ve solved it once.
If you work regularly with Oracle Data Guard, keep this technique in your toolbox. It will save you hours the next time redo metadata and storage fall out of sync.
