Oracle physical standby real time apply & 10.2.0.3
(2 votes, average: 4.5 out of 5)
Loading ...We have been happily running an oracle dataguard physical standby for many months with the 10.2.0.2 patchset. We were using real time apply with the physical standby which means the redo generated on the primary are applied to the standby as soon as they are received. After upgrading to 10.2.0.3 it seems we are having real difficulty in getting the standby to run with real time apply:
MRP0: Background Media Recovery terminated with error 355 ORA-00355: change numbers out of order ORA-00353: log corruption near block 2 change 863653355 time 02/21/2007 11:58:53 ORA-00312: online log 30 thread 1: '+DATA2/nom/standby30.log' Managed Standby Recovery not using Real Time Apply
Again, you start to worry about hardware issues, but as far as I can tell, there seems to be no hardware problems. I have managed to repeat this issue on 2 completely distinct installations so hardware seems unlikely. We are also seeing quite random behaviour in that sometimes the standby can be made to go into real time apply mode but nothing gets applied, even though redo is being shipped to it.
After Oracle had failed to come up with anything for a few days, I noticed that real time apply was using parallel log recovery, so I tried turning parallelism off, by setting the following parameter:
alter system set parallel_max_servers=0 scope=both;
Upon starting real time apply again I saw the following in the alert log:
parallel recovery setup failed: using serial mode
But it did start in real time apply. Though it seems if after attempting real time apply in parallel a standby redo has been corrupted previously i need to do a few log switches on the primary before it will go into real time apply again in serial mode.
Oracle have said it is similiar to BUG: 3806172 but this applied to 10.1 and logical standby not physical. It is possible to get managed recovery to start without using real time apply but then you have to start switching your log files in a more predictable fashion using archive_lag_target.
We are still awaiting a fix for this issue.
March 30th, 2007 at 3:43 pm
Did you get this issue resolved? We are planning on implementing 10.2.0.3 with DG and reading this is not too encouraging.
Thanks.
March 30th, 2007 at 3:50 pm
This is not as yet resolved, oracle developers are at 30/03/07 still investigating the bug, it has bug number: 5935278.
I think 10.2.0.3 is not the most stable of Oracle patchsets, 10.2.0.2 seemed more stable. There are as of this date 133 patches available to apply on top of 10.2.0.3 - and it’s only been released 3 months.
That being said do you really need real time apply? We are currently running using managed recovery mode and this seems fine, though we have had problems opening the standby read-write and flashing back but thats is another story…
April 3rd, 2007 at 6:54 pm
If you use managed recovery how much behind is the standby db? IF you are on 10.2.0.2 does real time apply work fine? Thanks.
April 3rd, 2007 at 8:19 pm
Managed Recovery means redo arriving at the standby is only applied when a log switch occurs on
the primary. You can use archive_lag_target to ensure the primary switches at regular intervals (and hence determine how far behind the standby is). Note of course this delay is about applying the changes - the changes themselves can be sent to the standby with no delay.
I have found 10.2.0.2 to be excellent for real time apply consistently seeing around a 12 second delay on the standby - not bad considering there is a 17ms round trip time
April 5th, 2007 at 3:37 pm
So in managed recovery redo is So if I am getting this right in Managed Recovery the log is applied only after a switch but as soon as as data is written to an online redo log on primary it is sent over to the standby as well?
We have been wondering betweeon 10.2.0.2 and 3 and I am glad I ran across your blog. We have not decided between real time apply and managed recovery but want to stay with a version that is stable and has least amount of problems. From your experience and advise it seems that 10.2.0.2 might be it. Thanks.
April 5th, 2007 at 3:55 pm
Yep, you have it exactly right wrt managed recovery.
I think it is a tough call, in my experience of running 10.2.0.2 for 9 months it was more stable than 10.2.0.3.
However, 10.2.0.3 is more secure, you don’t need to apply the latest (jan 2007) cpu to it.
also we are running RAC and there were changes to clusterware behaviour that were useful in 10.2.0.3.
as of (today) 05/04/2007 there are 157 patches available for 10.2.0.3 can’t be too long for 10.2.0.4
April 5th, 2007 at 3:59 pm
oh, i should also point out that for RAC we needed 10.2.0.3 to enable us to open our standby read-write for testing and then flash it back to become a standby again
April 5th, 2007 at 10:20 pm
Yeah your right 10.2.0.4 might be around the corner. We are not using RAC. We would want to open the standby db for read only access for reporting. Is that possible with using a physical standby using real time apply?
April 10th, 2007 at 7:24 am
hello again,
yes read-only is dead easy all you do is stop the apply process (real time or managed recovery) and do an alter database open read only.
April 14th, 2007 at 5:03 am
Do you use the DG Broker for this? Do you have any reasons for why or why not? Thanks.
August 15th, 2007 at 4:42 am
Hello
We are using Oracle 10G Data Guard Real Time Apply in Oracle 10.2.0.2 and it works very fine.
For bug with Hash Group By giving wrong results, we have to upgrade to Oracle 10.2.0.3.
Would you kindly let us know whether Oracle has published any fix with the Real Time Apply problem in Oracle 10.2.0.3.
We are not using ASM or RAC.
Thanks & Regards/TJ
August 15th, 2007 at 10:10 am
Hello TJ,
Well if you can hang on for 10.2.0.4 it can’t be long before this is available, and then you get the latest CPU’s for free.
I’d watch the upgrade process wrt your standby it is quite an involved procedure.
I’d be interested to hear if you can run real time apply after the upgrade - though a rebuild of the standby probably fixes it.
Yeah, we got a patch for our problem: 5746174. This is included in 10.2.0.4, note I’m not convinced this patch is neccessary to run real time apply
jason