I could have sworn that I had blogged about it. Aleksey had a comment on the blog and I could not find the answer. But I knew that I had discussed this topic with several people in email already. Maybe it has fallen off my long list of future blog posts. No worries, here it is: DBMS_OPTIM_BUNDLE and the standby database?
DBMS_OPTIM_BUNDLE – short recap
I have a longer list of DBMS_OPTIM_BUNDLE blog posts, and you can easily navigate to them here. In very short, you will use the package to enable potentially behavior changing optimizer fixes which are disabled by default in Release Updates to prevent any accidental change.
DBMS_OPTIM_BUNDLE and standby databases
Well, this was Aleksey’s question:
How to enable optim_fixes in standby database?
And to be frank, this is a very legit question. At first, using DBMS_OPTIM_BUNDLE on the primary doesn’t propagate to any of your standby databases, regardless whether it is a physical or a logical standby. This means, neither the parameter change nor the execution of the DBMS_OPTIM_BUNDLE package call gets into your standby.
Then the question is: How do I tell my standby database(s) about the changes on the primary? If you don’t do this, you may end up with a totally different behavior of your database in case you have set DBMS_OPTIM_BUNDLE‘s _fix_control settings on the primary after a switchover or a failover?
This could become really dangerous for an application which is used to benefit from all those fixes, and apparently they are gone. And even better, when you setup Application Continuity, and your application users luckily didn’t even recognize this switch – but you have different settings now? With 19.27.0, this would apply to far over 200 fixes.
Luckily, MOS Note: 2147007.1 – Managing “installed but disabled” bug fixes in Database Release Updates using DBMS_OPTIM_BUNDLE has an answer to this question.
How do you solve this?
You need to end managed recovery mode on your standby, then OPEN it, use an ALTER SYSTEM command to set all the _FIX_CONTROL settings you have on the primary, then bring the standby back into MOUNT state and enable managed recovery mode again.
The MOS note says:
STANDBY DATABASE CONSISTENCY
Any “installed but disabled” execution plan bug fixes that are enabled using the “DBMS_OPTIM_BUNDLE” package will *not* be propagated by ‘redo’ to downstream databases. If you have any standby databases, then you will need to repeat the same command(s) to enable or disable fixes on all standby databases as well.
This can be done only when the standby database is in read only mode . Once it is in read-only mode , use the same steps as mentioned earlier to enable or disable the fixes .
Please remember that you need to do this every time you add new settings with DBMS_OPTIM_BUNDLE – on every standby.
Quick annotation based on your feedbacks
Well, I never said I’m the smartest here. For sure I am not. But I’m really happy that you shared your improvements to my process with me. The approach I like the most is the one Daniele shared below:
- Convert your physical standby into a snapshot standby
- Redo shipping will continue
- A GRP will be set automatically
- Then adjust the parameter since the standby is now open read/write
- Then convert it back to a physical standby
- It will automatically flashback to the GRP
- It will then consume and apply all the shipped redo to synch again
- The changes from the SPFILE will remain since the SPFILE is not affected by the flashback operation
Pretty smart – like it!
Thanks, Daniele!!
Further Links and Information
- New interesting option to have more than 255 _fix_control parameters
- Blog posts about DBMS_OPTIM_BUNDLE
- MOS Note: 2147007.1 – Managing “installed but disabled” bug fixes in Database Release Updates using DBMS_OPTIM_BUNDLE
–Mike
Hi, Mike! Thanks for the article. You can also copy the _FIX_CONTROL values from the primary database spfile to the standby database spfile. And restart the standby database. That’s it. You don’t have to open it in read-only mode.
True – thanks for this reminder, Alex!
Cheers,
Mike
Nice article.
I usually open the standby db in snapshot and run
SQL> execute dbms_optim_bundle.enable_optim_fixes(‘ON’,’BOTH’, ‘YES’);
Which will automatically set the parameters in the spfile then bounce the instance and convert back to physical standby
Hi Daniele,
I did not try this but I think you are fully right, it will keep the SPFILE changes, regardless of whether I flashback and then recovert.
Smart move, like it!
Mike
Hi Mike,
This approach has a disadvantage: for productive systems, a blackout is required, since high availability is not guaranteed during that time. Isn’t it enough to generate the parameter once (create pfile from spfile) and then set it using ALTER SYSTEM?
Hi Johannes,
I disagree slightly. Putting your standby out of managed recovery does not stop sending redo.
But Daniele had a better solution proposed: Convert it into a snapshot standby, then adjust the setting, then convert back. No HA principles are violated, at worst case your failover in the unlikely case that it happens when you do this adjustment would take longer.
Cheers,
Mike
Hi Mike,
execute dbms_optim_bundle.enable_optim_fixes(‘ON’,’BOTH’, ‘YES’);
produces _fix_control and _fix_control_1 entries at PRIMARY.
STANDBY:
Converted to snapshot db, then execute dbms_optim_bundle.enable_optim_fixes(‘ON’,’BOTH’, ‘YES’);
produces only _fix_control entry. How can I get _fix_control_1 on STANDBY?
Regards
Stephan
Hi Stephan,
are you referring to:
https://mikedietrichde.com/2025/04/22/new-interesting-underscores-for-more-than-255-_fix_controls/
??
This will happen automatically once you cross the line.
You can’t create this, the DB will do it automatically.
Thanks,
Mike