"Thread 1 cannot allocate new log": redo management on RAC One Node

The alert log message

You kick off a large impdp on a RAC One Node database, everything goes fine for half an hour, and then the database starts choking. The alert log shows two lines, not one:

Thread 1 cannot allocate new log, sequence 4521
Checkpoint not complete

or, in a different run:

Thread 1 cannot allocate new log, sequence 4521
All online logs needed archiving

The database hasn't crashed, but every transaction is visibly slower while it waits for a redo log group to free up. The first line is the same either way — the second line is what actually tells you the root cause, and it points to two genuinely different problems.

Context

RAC One Node with two threads is a common setup — thread 1 active (OPEN), thread 2 inactive (CLOSED), reserved for a failover or instance relocation scenario. The problem hits when redo log generation on thread 1 outpaces how fast Oracle can free up a group for reuse, typically right when you're putting the most load on it — a large DataPump import, a bulk load, or a mass update.

The two root causes behind the same first line

"Checkpoint not complete" — DBWR hasn't finished writing the dirty buffers protected by the log group Oracle wants to reuse. The database has to wait for the checkpoint to catch up before that group is safe to overwrite. This is a checkpointing/DBWR-speed problem, not an archiving problem — logs that are too small, or DBWR that's too slow relative to the write rate, are the usual culprits.

"All online logs needed archiving" — every group is full and none has been archived yet; ARCH (or the archive destination) isn't keeping up with the rate logs are filling. This is the archiving-speed problem.

Both produce the identical first line and the identical symptom (transactions stall), but they call for different tuning. Per Oracle's own note on this exact pair of messages (MOS KB136167):

• Enlarging/adding redo log groups helps both cases — it buys more time before a group needs to be reused, whether the bottleneck is checkpointing or archiving.
• Checkpoint not complete specifically calls for checkpoint tuning and faster redo I/O (async I/O, multiple DBWR processes/secondaries) — the group is fine, DBWR just isn't keeping up.
• All online logs needed archiving specifically calls for looking at ARCH/archive destination speed — if that's the real bottleneck, adding groups only delays the problem rather than fixing it.

Oracle's own rule of thumb: redo log switches should happen at most once every 20 minutes under normal load; more frequent switching under heavy load (like a large import) is the signal to look at group size and count before anything else.

Diagnosis

The first step is checking the current state of redo logs and threads:

SELECT group#, thread#, members, ROUND(bytes/1024/1024) AS mb, status, archived
FROM v$log
ORDER BY thread#, group#;

SELECT group#, member, type FROM v$logfile ORDER BY group#;

SELECT thread#, status, enabled FROM v$thread;

What you're looking for: how many groups exist per thread, how large they are, and whether groups are stuck on ACTIVE (checkpoint still pending) instead of moving to INACTIVE, or stuck waiting to archive.

The fix — adding redo log groups to thread 1

If the problem is simply not enough room for redo log rotation, the fix is to add groups:

ALTER DATABASE ADD LOGFILE THREAD 1 ('+DATA', '+RECO') SIZE 2046M;
-- repeat for enough groups, e.g. 4x at 2046MB each
ALTER SYSTEM SWITCH LOGFILE;

The detail that trips people up most: if the original groups have 2 members (multiplexing, MEMBERS=2), the new groups must have the same number of members across both disk groups:

-- CORRECT:
ALTER DATABASE ADD LOGFILE THREAD 1 ('+DATA', '+RECO') SIZE 2046M;

-- WRONG -- gives only one member, you lose multiplexing protection:
ALTER DATABASE ADD LOGFILE THREAD 1 ('+DATA') SIZE 2046M;

If you get this wrong, you won't get an error right away — you'll only get one once you actually need the redundancy, which is the worst possible moment to notice.

Dealing with the inactive thread (thread 2)

If you want to clean up old logs on the inactive thread, you'll hit a specific problem: a log that is currently CURRENT for that thread can't be dropped without either switching it away first or disabling the thread.

Correct order of operations:

-- 1. Add at least 2 new groups for thread 2
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9  ('+DATA', '+RECO') SIZE 2046M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+DATA', '+RECO') SIZE 2046M;

-- 2. Disable the thread (now that new groups exist)
ALTER DATABASE DISABLE THREAD 2;

-- 3. Now you can drop the old groups
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;

-- 4. Re-enable the thread for failover readiness
ALTER DATABASE ENABLE PUBLIC THREAD 2;

Errors you'll encounter and what they mean

ORA-01623: log N is current log for instance UNNAMED_INSTANCE_2 (thread 2) — cannot drop
The log is still the current one for that thread. Either force a log switch so a different group becomes current, or disable the thread first — both are valid ways to clear this.

ORA-01567: dropping log group N would leave less than 2 logs for instance
Oracle won't let an enabled thread end up with fewer than 2 groups. Add the new groups before dropping the old ones — the order from the previous step isn't optional, it's the only way to avoid this.

The takeaway