Friday, May 8, 2020

Resolving Datapatch ORA-20013 Error – DBMS_QOPATCH ran mostly in non install area

When applying Oracle Database patches using datapatch, you might run into an error that halts patching. One such issue occurs when the Queryable Inventory is not set up correctly, triggering this message:

ORA-20013: DBMS_QOPATCH ran mostly in non install area

In this post, we will examine the root cause and explain how to fix this issue by correcting directory objects in the database.

While running datapatch -prereq from the $ORACLE_HOME/OPatch directory, you see:This means that the DBMS_QOPATCH package is unable to read from the correct directories where patching information is stored.


oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -prereq
SQL Patching tool version 12.2.0.0.0 on Fri May  8 18:32:30 2020
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_7949.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'
for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 for information on how to resolve the above errors.

SQL Patching tool complete on Fri May  8 18:32:31 2020
oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >sqlplua
sh: sqlplua:  not found.
oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 8 18:32:37 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-20013: DBMS_QOPATCH ran mostly in non install area

While running datapatch -prereq from the $ORACLE_HOME/OPatch directory, you see:This means that the DBMS_QOPATCH package is unable to read from the correct directories where patching information is stored.


Step 1: Check the Current Directory Objects


SQL>  select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME like '%OPATCH%';

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
OPATCH_LOG_DIR
/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

OPATCH_SCRIPT_DIR
/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

OPATCH_INST_DIR
/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch

While running datapatch -prereq from the $ORACLE_HOME/OPatch directory, you see:This means that the DBMS_QOPATCH package is unable to read from the correct directories where patching information is stored.

OPATCH_INST_DIR is pointing to a wrong or outdated path, such as $ORACLE_HOME/QOpatch instead of $ORACLE_HOME/OPatch.

Step 2: Drop the Invalid Directory Objects


SQL> drop directory OPATCH_INST_DIR;

Directory dropped.

SQL> drop directory OPATCH_LOG_DIR;

Directory dropped.

SQL> drop directory OPATCH_SCRIPT_DIR;

Directory dropped.

Step 3: Recreate the Directory Objects Correctly


SQL> create directory OPATCH_INST_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/OPatch';

Directory created.

SQL> create directory OPATCH_LOG_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch';

Directory created.

SQL> create directory OPATCH_SCRIPT_DIR as '/u03/app/oracle/product/12.1.0/dbhome_1/QOpatch';

Directory created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


Step 4: Re-run datapatch -prereq


oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -prereq

SQL Patching tool version 12.2.0.0.0 on Fri May  8 18:38:26 2020
Copyright (c) 2014, Oracle.  All rights reserved.
Connecting to database...OK
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_8130.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    20887355 ()
    27264965 ()

SQL Patching tool complete on Fri May  8 18:39:02 2020
oraclone @ omdevdb/u03/app/oracle/product/12.1.0/dbhome_1/OPatch >

 Conclusion

This issue occurs when the database cannot locate or access the patch-related directories. Ensuring that OPATCH_* directory objects point to the correct locations within $ORACLE_HOME is critical for a successful patch application.

Always refer to MOS Note 1609718.1 for official guidance on resolving queryable inventory errors.

It's a good habit to check and recreate OPATCH directories after cloning environments or copying ORACLE_HOMEs between servers.