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

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

SQL Patching tool version 12.2.0.0.0 on Fri May 8 16:09:48 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_5359.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

Bootstrapping registry and package to current versions...done

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.

[i] User below query to check the sqlpatch status 

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

select dbms_sqlpatch.verify_queryable_inventory from dual                                       

ERROR at line 1:

ORA-04063: package body "SYS.DBMS_SQLPATCH" has errors

[ii] Try tp compile the sqlpatch using prvtsqlpatch.plb

SQL> @prvtsqlpatch.plb

Session altered.

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY DBMS_SQLPATCH:

LINE/COL ERROR

-------- -----------------------------------------------------------------

226/9    PL/SQL: SQL Statement ignored
226/16   PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB
244/5    PL/SQL: SQL Statement ignored
250/25   PL/SQL: ORA-00932: inconsistent datatypes: expected CLOB got -

Session altered.

[iii] Drop sys.registery$sqlpatch table 

SQL>create table registry$sqlpatch_org as select * from registry$sqlpatch ;

SQL> drop table sys.registry$sqlpatch

  2  ;

Table dropped.

[iv]recreate the table by using the 

SQL> @catsqlreg.sql

Session altered.

Table created.

View created.

Synonym created.

Grant succeeded.

PL/SQL procedure successfully completed.

Grant succeeded.

Synonym created.

Session altered.

SQL>

[v]compile the sqlpatch 

SQL> @prvtsqlpatch.plb

Session altered.

Package body created.

No errors.

Session altered.

SQL>

[vi]Query the SQL patch 

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-20013: DBMS_QOPATCH ran mostly in non install area

[vii] select the datapatch directories and  drop recreate the directories 

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

SQL> drop directory OPATCH_INST_DIR;

Directory dropped.

SQL> drop directory OPATCH_LOG_DIR;

Directory dropped.

SQL> drop directory OPATCH_SCRIPT_DIR;

Directory dropped.

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

[Viii]check the datapath with prereq parameter 

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

[IX] Run the datapatch with verbose 

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

SQL Patching tool version 12.2.0.0.0 on Fri May 8 18:40:04 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_8716.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

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Patch 20204035 ():

Installed in binary and the SQL registry

Patch 20887355 ():

Installed in the binary registry only

Patch 22731026 ():

Installed in binary and the SQL registry

Patch 27264965 ():

Installed in the binary registry only

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

The following patches will be applied:

20887355 ()

27264965 ()

Installing patches...

Patch installation complete. Total patches installed: 2

Validating logfiles...

Patch 20887355 apply: SUCCESS

logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/20887355/18990781/20887355_apply_OMCLONE_2020May08_18_40_35.log (no errors)

Patch 27264965 apply: SUCCESS

logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/27264965/21812491/27264965_apply_OMCLONE_2020May08_18_40_38.log (no errors)

SQL Patching tool complete on Fri May 8 18:48:51 2020