2

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.

MDMarra
  • 100,183
  • 32
  • 195
  • 326
DCookie
  • 2,098
  • 17
  • 18

4 Answers4

2

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]

  • Apparently, you cannot run a standby database without archivelog mode being on. See this asktom.com artice, halfway down his first response: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1354203041395 . I did check the Oracle support site, with no success. – DCookie Dec 11 '09 at 17:00
  • I think you are misunderatanding the following sentence in Tom's article. "That aside, a standby database requires archivelog mode. " That is because the sentence is a little vague. I will rewrite it for you: "A standby database requires the production database to be in archivelog mode". There is no point in the standby database being in archivelog mode. – Ciaran De Buitlear Mar 16 '10 at 15:57
  • Thanks for the clarification. Clearly, the primary must be in archivelog mode. What about the case when a failover is needed? At that point, the standby now requires archivelog to be on, because it becomes the primary. Does the failover handle switching to archivelog mode? – DCookie Mar 16 '10 at 18:15
  • Yes the failover causes the standby to become the primary and turns on archive logging. – Ciaran De Buitlear Jun 01 '10 at 12:33
2

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.

David Mann
  • 251
  • 1
  • 5
2

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!

DCookie
  • 2,098
  • 17
  • 18
0

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.

namd0gma1
  • 31
  • 1
  • Actually, I gave it all the memory I could. Like I said, the problem is kind of moot now as I've recreated the standby, we don't do the large delete operations anymore, and I haven't seen the issue since. – DCookie Dec 28 '10 at 06:10