DBMS_OPTIM_BUNDLE and disabling another fix

Yes, DBMS_OPTIM_BUNDLE week it is. As I received so many good questions and input after my blog post whether you should enable _fix_controls with DBMS_OPTIM_BUNDLE or not, now I can easily fill the rest of the week with new content. Today it will be about a customer’s question regarding DBMS_OPTIM_BUNDLE and disabling another fix.

Photo by Drew Patrick Miller on Unsplash

Disabling another fix

The setup is pretty simple. Stefan wanted to disable a different fix while he had all fixes from the most recent RU enabled on purpose. Sounds simple but has a dangerous pitfall.

This is what Stefan did. At first he enabled all fixes with DBMS_OPTIM_BUNDLE. In my example, I do this in 19.11.0. below:

execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')

This will get me a lengthy _fix_control string in my SPFILE. That’s expected.

*._fix_control='29331066:1','28965084:1','28776811:1','28498976:1','28567417:1','28558645:1','29132869:1','29450812:1','29687220:1','29939400:1','30232638:1','30001331:0','29304314:1','29930457:1','30028663:1','28144569:1','28776431:1','27261477:1','31069997:1','31077481:1','28602253:1','29653132:0','29937655:1','30347410:1','30602828:1','30896685:0','29487407:1','30998035:1','30786641:1','31444353:0','30486896:1','28999046:1','30902655:1','30681521:1','29302565:1','30972817:1','30222669:1','31668694:1','31001490:1','30198239:7','30980115:1','30616738:0','31895670:0','19138896:1','31670824:0','9876287:1','30564898:1','32075777:0','30570982:1','32037237:1','30927440:1','30822446:1','24561942:1','31625959:1','31579233:1','29696242:1','31626438:1','30228422:1','17295505:1','29725425:1','30618230:1','30008456:1','30537403:1','30235878:1','30646077:1','29657973:1','29712727:1','20922160:1','30006705:1','29463553:1','30751171:1','31009032:1','30063629:1','30207519:1','31517502:1','30617002:1','30483217:1','30235691:1','30568514:1','28414968:3','32014520:1','30249927:1','31580374:1','29590666:0','29435966:1','28173995:1','29867728:1','30776676:1','26577716:1','30470947:1','30979701:1','30483184:1','31001295:1','31191224:1','31974424:1','29385774:1','28234255:3'#added through dbms_optim_bundle package

Now he wanted to disabled one additional fix, not even covered by DBMS_OPTIM_BUNDLE since they saw an issue. And the workaround given by Oracle Support was “disable this fix”.

alter system set "_fix_control"='27268249:0';

Some of you may already guess what happens now I assume.

Ouch – this leads to the looong string gone and being replaced by this short one in my SPFILE:

*._fix_control='27268249:0'

“Sure thing, that’s expected“, some will say. And I agree with you.

But the danger here is that you may not be aware of this change happening in the background. With good intentions you thought you keep all the 58 fixes on, and you turned one additional fix off.

In fact, you turned 1 fix off on purpose, and in addition 58 others off as well which you thought were still kept on. And all this with a single command. This is not obvious to everybody.

Until 19.11.0 – and from 19.12.0

Basically, the only option you have until Oracle 19.11.0 is to manipulate the string manually by yourself. Either you create a PFILE from the SPFILE, edit the one setting, then start the database with this new PFILE and write it into an SPFILE again. And I agree, this is a very ugly workaround as it requires a restart.

Or take the long string from above, edit it and use the entire long string in an ALTER SYSTEM command such as:

alter system set "_fix_control"='27268249:0','29331066:1','28965084:1','28776811:1','28498976:1','28567417:1','28558645:1','29132869:1','29450812:1','29687220:1','29939400:1','30232638:1','30001331:0','29304314:1','29930457:1','30028663:1','28144569:1','28776431:1','27261477:1','31069997:1','31077481:1','28602253:1','29653132:0','29937655:1','30347410:1','30602828:1','30896685:0','29487407:1','30998035:1','30786641:1','31444353:0','30486896:1','28999046:1','30902655:1','30681521:1','29302565:1','30972817:1','30222669:1','31668694:1','31001490:1','30198239:7','30980115:1','30616738:0','31895670:0','19138896:1','31670824:0','9876287:1','30564898:1','32075777:0','30570982:1','32037237:1','30927440:1','30822446:1','24561942:1','31625959:1','31579233:1','29696242:1','31626438:1','30228422:1','17295505:1','29725425:1','30618230:1','30008456:1','30537403:1','30235878:1','30646077:1','29657973:1','29712727:1','20922160:1','30006705:1','29463553:1','30751171:1','31009032:1','30063629:1','30207519:1','31517502:1','30617002:1','30483217:1','30235691:1','30568514:1','28414968:3','32014520:1','30249927:1','31580374:1','29590666:0','29435966:1','28173995:1','29867728:1','30776676:1','26577716:1','30470947:1','30979701:1','30483184:1','31001295:1','31191224:1','31974424:1','29385774:1','28234255:3';

This adds now the fix the customer wanted to disable at first place.

But from Oracle 19.12.0 the API got extended (thanks to Shveta for telling me). Now you don’t have to manipulate the string manually by yourself with all the risk involved.

Now you can manipulate or add settings without overwriting the entire string:

SQL> exec dbms_optim_bundle.set_fix_controls('27268249:0','*', 'BOTH','YES');

PL/SQL procedure successfully completed.

This leads to:

*._fix_control='27268249:0','28567417:0','28558645:0','29132869:0','30028663:0','31077481:0','29487407:0','31444353:0','29302565:0','31625959:0','31579233:0','30207519:0','30979701:0','28234255:0','29450812:0','30001331:0','29653132:0','30602828:0','30786641:0','30486896:0','30972817:0','19138896:0','30927440:0','30646077:0','29712727:0','30617002:0','30568514:0','30249927:0','29590666:0','30483184:0','31961578:0','31496840:0','22387320:0','30681521:0','9876287:0','30822446:0','24561942:0','31626438:0','30751171:0','31009032:0','28414968:0','30470947:0','31821701:0','32107621:0','28498976:0','30232638:0','29937655:0','30896685:0','31668694:0','31001490:0','30980115:0','30616738:0','30483217:0','26577716:0','28965084:0','28144569:0','28776431:0','30222669:0','30570982:0','29696242:0','30228422:0','17295505:0','30618230:0','29657973:0','30063629:0','28173995:0','31001295:0','31974424:0','31459242:0','29304314:0','27261477:0','30902655:0','30198239:0','32037237:0','29725425:0','30008456:0','30235878:0','30235691:0','29435966:0','29867728:0','30776676:0','28708585:0','30142527:0','31143146:0','29939400:0','31069997:0','32075777:0','30537403:0','20922160:0','30006705:0','29463553:0','31517502:0','29385774:0','31082719:0','26758837:0','30781970:0','29331066:0','28776811:0','29687220:0','29930457:0','28602253:0','30347410:0','30998035:0','28999046:0','31895670:0','31670824:0','30564898:0','32014520:0','31580374:0','31191224:0','31558194:0'#added through dbms_optim_bundle package

As you can see, the additional setting ‘27268249:0’ has been placed in front, the other _fix_control settings are preserved.

See also the documentation of the API.

And what happens with the next RU?

As we all know, life doesn’t end with this RU. The next RU is just a quarter away. So let me patch quickly to 19.12.0, then enable the new fixes and see what happens to the fix I added manually.

My guts feeling tells me that “my” disabled fix will be wiped out. Let’s see.

I enable all the new fixes in Oracle 19.12.0:

execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')

And now look here at the very end of this lengthy string:

*._fix_control='29331066:1','28965084:1','28776811:1','28498976:1','28567417:1','28558645:1','29132869:1','29450812:1','29687220:1','29939400:1','30232638:1','30001331:0','29304314:1','29930457:1','30028663:1','28144569:1','28776431:1','27261477:1','31069997:1','31077481:1','28602253:1','29653132:0','29937655:1','30347410:1','30602828:1','30896685:0','29487407:1','30998035:1','30786641:1','31444353:0','30486896:1','28999046:1','30902655:1','30681521:1','29302565:1','30972817:1','30222669:1','31668694:1','31001490:1','30198239:7','30980115:1','30616738:0','31895670:0','19138896:1','31670824:0','9876287:1','30564898:1','32075777:0','30570982:1','32037237:1','30927440:1','30822446:1','24561942:1','31625959:1','31579233:1','29696242:1','31626438:1','30228422:1','17295505:1','29725425:1','30618230:1','30008456:1','30537403:1','30235878:1','30646077:1','29657973:1','29712727:1','20922160:1','30006705:1','29463553:1','30751171:1','31009032:1','30063629:1','30207519:1','31517502:1','30617002:1','30483217:1','30235691:1','30568514:1','28414968:3','32014520:1','30249927:1','31580374:1','29590666:0','29435966:1','28173995:1','29867728:1','30776676:1','26577716:1','30470947:1','30979701:1','30483184:1','31001295:1','31191224:1','31974424:1','29385774:1','28234255:3','31459242:0','31082719:1','28708585:1','31821701:1','32107621:1','26758837:1','31558194:1','30781970:0','30142527:1','31143146:1','31961578:0','31496840:1','22387320:1','27268249:0'#added through dbms_optim_bundle package

The customer disabled ‘27268249:0’ – and it is kept. Only the order got changed. While it was in first place as I added it manually to the string before, it is now at the end of the string.

So my guts feeling was wrong – and DBMS_OPTIM_BUNDLE does it right. And of course it correctly added the 13 fixes from Oracle 19.12.0 as well.

Further Links and Information

• Should you enable _fix_controls with DBMS_OPTIM_BUNDLE
• Documentation of DBMS_OPTIM_BUNDLE API
• You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0
• In case you miss DBMS_OPTIM_BUNDLE … again
• In case you miss DBMS_OPTIM_BUNDLE in 12.2
• DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c
• DBMS_OPTIM_BUNDLE – only available in 12.1.0.2, isn’t it?

–Mike