Photo by Caleb Woods on Unsplash
I’m tempted to copy and paste my blog post about DBMS_OPTIM_BUNDLE from February 2019, and exchange only 12.2.0.1 with 19c. But in this case the root cause is different. So this blog post is meant for Oracle 19.3.0 to be precise – in case you miss DBMS_OPTIM_BUNDLE … again … ?!?
Information about DBMS_OPTIM_BUNDLE
You will find a lot of information about DBMS_OPTIM_BUNDLE, what it does, since which release it exists and much more in these blog posts:
- In case you are missing DBMS_OPTIM_BUNDLE (Feb 11, 2019)
- DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c (Mar 8, 2019)
- DBMS_OPTIM_BUNDLE – only available in Oracle 12.1.0.2 … or not? (Jul 25, 2017)
- Package differences between Oracle 11.2.0.4 and Oracle 12.1.0.2 (Jun 23, 2015)
- Bug 27009164 – support for dbms_optim_bundle for CDBs
- Bug 29259128 – REGRESSION: PACKAGE SYS.DBMS_OPTIM_BUNDLE MISSING FROM RU OCT. 2018 (unpublished bug – hence no link added)
Where’s DBMS_OPTIM_BUNDLE in Oracle 19.3.0?
Several colleagues from ACS Support and Consulting called or mailed me the past weeks with a question: Where is DBMS_OPTIM_BUNDLE in Oracle 19.3.0? And all of them were right: It is not there.
To make things a bit more complicated, the well known workaround did not work either. In previous release you could create the package with:
@?/rdbms/admin/dbmsoptim.sql @?/rdbms/admin/prvtoptim.plb
But these scripts don’t exist in Oracle 19c.
Big confusion.
What has happened?
Actually the creation of DBMS_OPTIM_BUNDLE via these two scripts was just a workaround. As the package has to exist since Oracle 12.1.0.2, the only file where it must be created within is catproc.sql. I won’t explain why it didn’t make it into catproc. But for Oracle 19c the code got changed. And once the code was adjusted, a problem with PRAGMA got detected. This led to the unfortunate situation that the package is not in 19.3.0. And of course, the scripts don’t exist either as they are not meant to be there anymore.
But … this is not a real issue as there are no optimizer fixes in 19.3.0 to enable or disable with DBMS_OPTIM_BUNDLE.
The package will appear again in 19.4.0 – and will be in catproc.sql for databases created since 19.4.0.
–Mike
Hi Mike,
We already opened an SR with Oracle Support, just to inform you.
When an ‘out-of-place’ patch takes place, the outcome of “execute dbms_optim_bundle.listBundlesWithFCFixes;” is wrong. Root-cause seems to be that the directories DBMS_OPTIM_LOGDIR and DBMS_OPTIM_ADMINDIR (and some others as well) are not automatically reflecting the actual oracle home from where the database is started.
Example:
OWNER DIRECTORY_NAME DIRECTORY_PATH
———- —————————— ————————————————————
SYS XMLDIR /lfs/oracle/ora19600V0/rdbms/xml
SYS XSDDIR /lfs/oracle/ora19600V0/rdbms/xml/schema
SYS OPATCH_INST_DIR /lfs/oracle/ora19900V0/OPatch
SYS ORACLE_OCM_CONFIG_DIR2 /lfs/oracle/ora19600V0/ccr/state
SYS ORACLE_BASE /lfs/oracle
SYS ORACLE_HOME /lfs/oracle/ora19600V0
SYS ORACLE_OCM_CONFIG_DIR /lfs/oracle/ora19600V0/ccr/state
SYS DATA_PUMP_DIR /lfs/oracle/ora19900V0/rdbms/log/
SYS DBMS_OPTIM_LOGDIR /lfs/oracle/ora19600V0/cfgtoollogs
SYS DBMS_OPTIM_ADMINDIR /lfs/oracle/ora19600V0/rdbms/admin
SYS OPATCH_SCRIPT_DIR /lfs/oracle/ora19900V0/QOpatch
SYS OPATCH_LOG_DIR /lfs/oracle/ora19900V0/rdbms/log
SYS JAVA$JOX$CUJS$DIRECTORY$ /lfs/oracle/ora19900V0/javavm/admin/
08:02:31 SQL> execute dbms_optim_bundle.listBundlesWithFCFixes;
bundleId: 190719, bundleName: 19.4.0.0.190719DBRU
bundleId: 191015, bundleName: 19.5.0.0.191015DBRU
08:02:41 SQL> create or replace directory DBMS_OPTIM_ADMINDIR as ‘/lfs/oracle/ora19900V0/rdbms/admin’;
Directory created.
08:03:15 SQL> execute dbms_optim_bundle.listBundlesWithFCFixes;
bundleId: 190719, bundleName: 19.4.0.0.190719DBRU
bundleId: 191015, bundleName: 19.5.0.0.191015DBRU
bundleId: 200414, bundleName: 19.7.0.0.200414DBRU
bundleId: 200714, bundleName: 19.8.0.0.200714DBRU
bundleId: 201020, bundleName: 19.9.0.0.201020DBRU
PL/SQL procedure successfully completed.
This will happen when the old directory has been removed from the operating system:
07:41:43 SQL> execute dbms_optim_bundle.listBundlesWithFCFixes;
BEGIN dbms_optim_bundle.listBundlesWithFCFixes; END;
*
ERROR at line 1:
ORA-20002: ORA-20002: ORA-20002: Error stat’ing file bundlefcp_DBBP.xml, getDocument Error, listBundles Error
ORA-06512: at “SYS.DBMS_OPTIM_BUNDLE”, line 1611
ORA-06512: at line 1
Thanks Dennis,
can you share the SR number with me as well? Email or comment on the blog, either way is fine.
Thanks for the update,
Mike
Hi Mike,
SR 3-24458722701 : Patch Mgmnt | 19.9 | listBundlesWithFCFixes is not listing the current version of PSU/RU
Grz,
Dennis
Will check it, Dennis.
Cheers,
Mike
Hi Dennis, what is the outcome of your SR? Ive the same issue too.
Is there any workaround?
Thx®ards
Axel D,
Hi Dennis, what is the outcome of your SR? Ive the same issue too.
Is there any workaround?
Thx®ards
Axel D,
Same issue here on 19.9 on Exadata using OEDA Golden images
Oh no … 🙁
Mike
Advice is to call utlfixdirs.sql after changing the Oracle home directory.
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/creating-additional-data-dictionary-structures.html#GUID-571DA1D1-5274-4763-99B4-B1FF60E79F9F
Thanks Dennis – I wonder about one thing:
When I move from 19.11.0 to 19.12.0 with out-of-place patching, the directory gets updated correctly:
SQL> select directory_path from dba_directories where directory_name like ‘DBMS_OPTIM%’;
DIRECTORY_PATH
——————————————————————————–
/u01/app/oracle/product/1912/cfgtoollogs
/u01/app/oracle/product/1912/rdbms/admin
I guess, in your environment, that didn’t happen correctly with earlier bundle patches, right?
Thanks for sharing, cheers!
Mike
Found the issue in My environment we patch out of place and the
DBMS_OPTIM_ADMINDIR and LOGDIR were pointing to the wrong Oracle Home in my case a 19.5 instead of the 19.9 one
Ouch – and thanks for the update!
Cheers,
Mike