Oracle 10.2.0.4 database with a logical standby on Win2K3. Recently a rather large delete operation was carried out on the production instance. I'm experiencing difficulty with the logical standby, in that it gets a couple of hundred (58M size) archive logs into the operation and the apply process fails with an out-of-memory error. Unfortunately, every time it fails it has to restart the apply from the beginning of the transaction. This is taking a couple of days each time. Anyway, in trying to resolve this problem, I've noticed that each archive log from the production system generates 5 or 6 log switches on the standby. I don't understand why this should be. Anyone have any ideas?
A related question that I've not found the answer for: does anyone know if the logical standby must be running in archivelog mode? I really don't have a need to keep the logs.
I wouldn't run a standby database in archivelog mode. I would turn this off first and then retry see what happens. As for the out-of-memory error: I assume you are getting "ORA-04030" errors. If not then please post the error number here. If so then have a look at the following on the oracle "My support" site. MS-Windows: Quick steps to solve ORA-04030 errors on 10G [ID 762031.1]
Are your memory parameter settings on the Primary and Standby the same? Obviously your Primary can successfully complete the large transaction, I'm wondering if there are any parameter differences that might cause this issue on the Standby side.
I never got an explanation from Oracle on this. Ultimately, I couldn't wait any longer and simply re-created the standby, bypassing the problem. I haven't seen the issue since, but then, I haven't encountered a similar situation, either.
As I pointed out in my comment to @Claran, there is an asktom.com question wherein he states as part of his answer that a standby must run in archivelog mode. So there you have it.
Thanks to David and Claran for their answers, +1 to each for their efforts.
Update (12/24/2009):
It seems there is a patch for a memory leak in the SQL Apply process. It's in the CPUJul2009 patch. I'll need a window to upgrade, so it will be a while. The problem recurred again this past week. It seems to be related to the fact that the log miner process keeps the entire transaction in memory until it sees the commit. To me, this is a recipe for failure!
Archive log mode is mandatory for standby databases - trust me on this one (I run several of these logical standbys). You need to increase the amount of SGA available for logical redo apply, or if the table is unimportant, you can exclude it from the standby. Easiest way to do either is to use Enterprise Manager.