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
