DBMS_OPTIM_BUNDLE and Out-Of-Place Patching

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?

Photo by Didssph on Unsplash

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:

1. You will receive an error because <old-home> does not exist anymore
2. 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 and ORACLE_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