utlfixdirs.sql

It's a script that comes with every $ORACLE_HOME and it's located in $ORACLE_HOME/rdbms/admin/ folder. It recreates all DBA_DIRECTORIES that are created by Oracle software and should be referencing the current $ORACLE_HOME.

We should use it every time we change $ORACLE_HOME. That means after migrations and after out-of-place upgrades, which is generally the preferred method of upgrading. You can read more on downsides of in-place patching on Mike Dietrich's blog.

This script is available in versions >= 19c.

OPatch

Interestingly, OPatch directories are fixed automatically on instance startup without running any scripts or commands other than startup. This applies only to OPATCH_%_DIR directories. So, it should be safe to run OPatch even before you run utlfixdirs.sql.

Regardless, I still believe it to be a good practice to fix directories before datapatch because .sql scripts that are run by datapatch could, in theory, need something from other, non-opatch directories (e.g. maybe from SDO_DIR_ADMIN directory).

READ ONLY PDBs

All Oracle maintained directories that point to paths in $ORACLE_HOME are created/replaced by Oracle using sharing=metadata. So, after running utlfixdirs.sql you will have those directories fixed even in PDBs that are open as READ ONLY. That includes PDB$SEED.

Example:

SQL> @?/rdbms/admin/utlfixdirs.sql

Container: CDB$ROOT

Current  ORACLE_HOME: /oracle/db_ee/19.24.0/dbhome_2
Original ORACLE_HOME: /oracle/db_ee/19.24.0/dbhome_1

DBMS_OPTIM_ADMINDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/rdbms/admin
...NEW: /oracle/db_ee/19.24.0/dbhome_2/rdbms/admin
DBMS_OPTIM_LOGDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/cfgtoollogs
...NEW: /oracle/db_ee/19.24.0/dbhome_2/cfgtoollogs
ORACLE_HOME
...OLD: /oracle/db_ee/19.24.0/dbhome_1
...NEW: /oracle/db_ee/19.24.0/dbhome_2
ORACLE_OCM_CONFIG_DIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/ccr/state
...NEW: /oracle/db_ee/19.24.0/dbhome_2/ccr/state
ORACLE_OCM_CONFIG_DIR2
...OLD: /oracle/db_ee/19.24.0/dbhome_1/ccr/state
...NEW: /oracle/db_ee/19.24.0/dbhome_2/ccr/state
SDO_DIR_ADMIN
...OLD: /oracle/db_ee/19.24.0/dbhome_1/md/admin
...NEW: /oracle/db_ee/19.24.0/dbhome_2/md/admin
XMLDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/rdbms/xml
...NEW: /oracle/db_ee/19.24.0/dbhome_2/rdbms/xml
XSDDIR
...OLD: /oracle/db_ee/19.24.0/dbhome_1/rdbms/xml/schema
...NEW: /oracle/db_ee/19.24.0/dbhome_2/rdbms/xml/schema

PL/SQL procedure successfully completed.

References

• Official doc: Creating Additional Data Dictionary Structures

The contents of this blog post were tested on database version 19.24. Feel free to leave a comment if you find that described behavior changes with newer versions.