I don't remember hearing about this before with Oracle 11.
There's an Oracle note which may help:
Alert.log shows No Standby Redo Logfiles Of Size 153600 Blocks Available (Doc ID 405836.1)
Thanks, I did see that, but my problem is different.
It's not that the SRLs are all active, not releasing, and there are no more.
I have only one ACTIVE.
The one thing that my problem has in common with this Metalink note though, is that the logs catch up and apply. I just don't understand why it can't find an SRL.
I would consider bumping this to 8
"log_archive_max_processes set to 4"
For some reason Oracle does not like your standby redo logs.
After looking at your query they certainly appear to 10GB, but Oracle still does not like them.
I would strongly consider dropping the standby redo logs and recreating them. I would also consider creating more of them. Given the large size of them I assume you have a large load and throughput and the error message may point to not enough SRL to handle this.
Message was edited by: mseberg
Here's what I think is happening, which has spurred another question.
Our primary site is a 3-node RAC cluster and our standby is a 3-node RAC cluster.
On the primary our redo logs are split out into groups for each thread, where the thread relates to the instance, or node.
On the standby our redo logs were created as a duplicate of the database, and 3 threads of logfile groups were created, even though there are only 2 instances.
I can't see that this is hurting anything, and I can't drop the 3rd thread groups.
Now I'm not sure if I need 3 threads to hook back to the primary, I'm unclear on how this works.
My problem with the standbys I think are also related to threads. The standbys were created with THREAD 1 in the statement, and we didn't catch that. I'm changing them now created with no THREAD clause, and have found documentation that when using RAC SRLs should be created without it and the thread# gets assigned as it needs to. I think by defining thread 1, that's all it was ever using, and had to keep waiting. As usual, Oracle is smarter than me when it comes to making database decisions. As I move further up the versions of Oracle I'm finding that sometimes the less we tell Oracle what to do, the better decisions it makes.
So my question now is, how are threads used for online redo logs in a standby environment, especially as related to the primary.
After I get the SRLs squared away and let it cook for a little while I'll know if removing the thread clause makes a difference.
If I had your issue I would open a ticket with Oracle support ASAP. I ran the v$standby on several of my systems, but none appear as busy as yours.
Best of luck resolving it and please consider post how you solve it here.
So far, so good. I believe my issue was that the original SRLs were created with the THREAD 1 clause in the add standby logfile statement. We are using LGWR Redo Transport, so when the SRLs were written to from primary's LGWR, one standby had to accommodate 3 primaries. There was a 'log jam' at the standby SRL while it wrote the the online redo log and then to the archive log.
I created the standby redo logs without a thread clause, now I can see simultaneously 3 threads being consumed.
The message has not returned to the alert log.
I still have 3 threads on the standby to accommodate 3 threads on the primary, even though I really only have 2 nodes at standby. It doesn't seem to be hurting anything, but now I'm trying to get this straight in my head how that part is working.
Wow! That's a step in the right direction. Thanks for sharing this information.
So a thread for each primary thread. I kind of see how that would help it happy.