Well, you see, this is most likely my special DBMS_OPTIM_BUNDLE week. And since I receive quite a number of questions, it may be good to discuss here about DBMS_OPTIM_BUNDLE and Out-Of-Place Patching?
Out-of-place Patching
When you patch out-of-place with a new home – which is clearly our recommendation – you may see another tiny pitfall with DBMS_OPTIM_BUNDLE.
When you check DBA_DIRECTORIES, you will find two directories being related to DBMS_OPTIM_BUNDLE.
SQL> select directory_name, directory_path from dba_directories where directory_name like '%OPTIM%' DIRECTORY_NAME DIRECTORY_PATH -------------------- -------------------------------------------------- DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19/cfgtoollogs DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19/rdbms/admin
You see the “19” in the above path – this is my 19c Oracle Home directory.
See what Jan commented to one of my earlier blog posts:
This DBMS_OPTIM_BUNDLE series prompted me to try this with one of our databases. Nice thing. It’s patched to 19.11, datapatch is executed, but dbms_optim_bundle.listBundlesWithFCFixes only knows about fix controls up to 19.8.
So what has happened?
You changed from <old-home> to <new-home>, you ran datapatch -verbose – and afterwards still some directories are not adjusted. When you call DBMS_OPTIM_BUNDLE now in <new-home>, there are two options:
- You will receive an error because <old-home> does not exist anymore
- You will be presented with a list of disabled fixes which don’t match your actual release
The latter is the issue Jan (and others commenting on the blog) saw.
Is this a bug or a feature?
Actually customers filed bugs for this behavior:
- BUG 32444753 – AUTOUPDATE OF DBMS_OPTIM_LOGDIR DBMS_OPTIM_ADMINDIR ON STARTUP WHEN CHANGING HOME
- DOC 32203585 – AUTO UPDATE OF ALL DIRECTORY OBJECTS TO USE NEW PATH WHEN CHANGING HOME
But even though this looks very simple to fix, it was marked as not that trivial to cure.
Now I did some testing and research with my environment. I will patch from 19.11.0 to 19.12.0 RU.
At first, I checked the directories in 19.11.0 before patching:
SQL> column directory_name format a20 SQL> column directory_path format a50 SQL> select directory_name, directory_path from dba_directories where directory_name like '%OPTIM%'; DIRECTORY_NAME DIRECTORY_PATH -------------------- -------------------------------------------------- DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19/cfgtoollogs DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19/rdbms/admin
You see the “19” in the name – this is my current 19.11.0 home.
I deploy a new 19.12.0 home and flip to the new one, then run datapatch. But before involking datapatch, I quickly check the directories. And they were – as expected – exactly at the same trees as before, still pointing to 19.11.0.
SQL> column directory_name format a20 SQL> column directory_path format a50 SQL> select directory_name, directory_path from dba_directories where directory_name like '%OPTIM%'; DIRECTORY_NAME DIRECTORY_PATH -------------------- -------------------------------------------------- DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19/cfgtoollogs DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19/rdbms/admin
But the magic happens when I invoke datapatch -verbose.
See how the directories got adjusted afterwards:
SQL> column directory_name format a20 SQL> column directory_path format a50 SQL> select directory_name, directory_path from dba_directories where directory_name like '%OPTIM%'; DIRECTORY_NAME DIRECTORY_PATH -------------------- -------------------------------------------------- DBMS_OPTIM_LOGDIR /u01/app/oracle/1912NEW/cfgtoollogs DBMS_OPTIM_ADMINDIR /u01/app/oracle/1912NEW/rdbms/admin
I intentionally chose a very strange home tree not under ../product/.. to make this more clear. So at least with Oracle 19.12.0 this seems to be fixed.
Solution for older releases
Thanks to Dennis who pointed me to the solution for releases where this does not get adjusted. And it is mentioned in the bugs as well. You need to run a script to fix this.
- $ORACLE_HOME/rdbms/admin/utlfixdirs.sql
The documentation Creating Additional Data Dictionary Structures – Appendix B2 in the Oracle Database Reference says:
Used after moving a database to a new Oracle home. Updates directory objects to use the new path names for the Oracle home and Oracle base directories, as defined by the new values for the
ORACLE_HOME
andORACLE_BASE
environment variables.When you run this script in the root container of a CDB, it updates directory objects in the root, as well as any Oracle-maintained directory objects in the PDBs; you must manually update any PDB directory objects that are not Oracle-maintained.
And when I run it in my database, it adjusts other paths as well:
SQL> start $ORACLE_HOME/rdbms/admin/utlfixdirs.sql Container: CDB$ROOT Current ORACLE_HOME: /u01/app/oracle/1912NEW Original ORACLE_HOME: /u01/app/oracle/product/19 ORACLE_HOME ...OLD: /u01/app/oracle/product/19 ...NEW: /u01/app/oracle/1912NEW ORACLE_OCM_CONFIG_DIR ...OLD: /u01/app/oracle/product/19/ccr/state ...NEW: /u01/app/oracle/1912NEW/ccr/state ORACLE_OCM_CONFIG_DIR2 ...OLD: /u01/app/oracle/product/19/ccr/state ...NEW: /u01/app/oracle/1912NEW/ccr/state SDO_DIR_ADMIN ...OLD: /u01/app/oracle/product/19/md/admin ...NEW: /u01/app/oracle/1912NEW/md/admin XMLDIR ...OLD: /u01/app/oracle/product/19/rdbms/xml ...NEW: /u01/app/oracle/1912NEW/rdbms/xml XSDDIR ...OLD: /u01/app/oracle/product/19/rdbms/xml/schema ...NEW: /u01/app/oracle/1912NEW/rdbms/xml/schema
I will ask the patching team whether they can include this in their readmes as well.
But … as Jan in the comments section explained, utlfixdirs.sql may not update the paths for DBMS_OPTIM_BUNDLE at least in 19.11.0. So please double-check whether the paths are set correctly until 19.11.0.
Going backwards?
We are almost done – but I was interested in the reverse case. What happens when you rollback to a lower patch bundle?
Of course, before I invoke datapatch or the above script, all directories are still pointing to my new 19.12.0 home. When I call it before invoking datapatch but using the new home already, I will receive a ton of errors, especially when I removed my 19.12.0 home meanwhile:
SQL> execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES') BEGIN dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES'); END; * ERROR at line 1: ORA-29283: invalid file operation: nonexistent file or path [29434] ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 512 ORA-29283: invalid file operation: nonexistent file or path [29434] ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at "SYS.DBMS_OPTIM_BUNDLE", line 1903 ORA-29283: invalid file operation: nonexistent file or path [29434] ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 512 ORA-29283: invalid file operation: nonexistent file or path [29434] ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at "SYS.DBMS_OPTIM_BUNDLE", line 1632 ORA-06512: at line 1
This is expected since there are no files anymore in the destination DBMS_OPTIM_BUNDLE is looking for.
So let me invoke datapatch and check again.
SQL> execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES') PL/SQL procedure successfully completed.
Wow. This works fine. I used opatch 24.
$ $OH19/OPatch/opatch version OPatch Version: 12.2.0.1.24 OPatch succeeded.
Let me check the directories.
SQL> column directory_name format a20 SQL> column directory_path format a50 SQL> select directory_name, directory_path from dba_directories where directory_name like '%OPTIM%'; DIRECTORY_NAME DIRECTORY_PATH -------------------- -------------------------------------------------- DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19/cfgtoollogs DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19/rdbms/admin
This looks pretty flawless and correct.
Finally, another check whether the script to adjust directories may still be necessary. Let me show you the full picture.
SQL> column directory_name format a25 SQL> column directory_path format a50 SQL> select directory_name, directory_path from dba_directories order by 1; DIRECTORY_NAME DIRECTORY_PATH ------------------------- -------------------------------------------------- DATA_PUMP_DIR /u01/app/oracle/admin/CDB2/dpdump/ DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19/rdbms/admin DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19/cfgtoollogs JAVA$JOX$CUJS$DIRECTORY$ /u01/app/oracle/product/19/javavm/admin/ OPATCH_INST_DIR /u01/app/oracle/product/19/OPatch OPATCH_LOG_DIR /u01/app/oracle/product/19/rdbms/log OPATCH_SCRIPT_DIR /u01/app/oracle/product/19/QOpatch ORACLE_BASE /u01/app/oracle ORACLE_HOME /u01/app/oracle/1912NEW ORACLE_OCM_CONFIG_DIR /u01/app/oracle/1912NEW/ccr/state ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/1912NEW/ccr/state SDO_DIR_ADMIN /u01/app/oracle/1912NEW/md/admin SDO_DIR_WORK XMLDIR /u01/app/oracle/1912NEW/rdbms/xml XSDDIR /u01/app/oracle/1912NEW/rdbms/xml/schema 15 rows selected.
Now it is clear that you must run
- $ORACLE_HOME/rdbms/admin/utlfixdirs.sql
every time you patched out-of-place.
Otherwise, your directories won’t be set correctly. And this may cause trouble, regardless of whether DBMS_OPTIM_BUNDLE is set correctly.
Further Links and Information
- Creating Additional Data Dictionary Structures – Appendix B2
- Documentation of DBMS_OPTIM_BUNDLE API
- DBMS_OPTIM_BUNDLE and disabling other fixes
- Should you enable _fix_controls with DBMS_OPTIM_BUNDLE
- 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
Thank you for this post. Once again it simply hit’s the nail on the head.
On addition: In 19.11 the utlfixdirs.sql script does not update the DBMS_OPTIM_* directories. So just executing that didn’t help.
But thanks to this post, I could get the DBMS_OPTIM_BUNDLE package to work.
Thanks for this hint, Jan – I will add it to the blog post.
Cheers,
Mike
Hi Mike,
Do I need to create a Enhancement Request to put these things part of datapatch (at least as an option?)
Cheers
Miguel Anjo
Hi Miguel,
yes, via an SR. SUpport than can file a bug which gets “status: 15” which is an enhancement request.
So you will receive a bug number for it.
Cheers,
Mike
Hi Mike,
Could you please provide some direction/best practices for Out-Of-Place Patching in 19C?
For databases already running in 19.8 and wanting to go to 19.14, I am considering (1) build a new Oracle Home using the installer to install 19.3 plus latest RU/OJVM patches , (2) move the database to the new OH and then run datapatch, etc…
But, I wonder if Oracle provides utilities or procedures now for a more efficient way to do this when patching many databases, specially with RAC databases.
Thank you in advance for your help,
-Pedro
Hi Pedro,
you can google quickly for advice on how to prepare your new home.
I usually use ./runInstaller -applyRU … as you can see on the blog.
Or see here:
https://mdinh.wordpress.com/2020/03/01/create-19c-gold-image/
Thanks,
Mike