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

ouafDatabasePatch.sh gives java.sql.SQLException: ORA-01017: invalid username/password; logon denied

java.sql.SQLException: ORA-01017: invalid username/password; logon denied

When we are trying to apply a patch in CCB we had came across the below error as invalid username and password, as the hashed password might not be in sync with the application layer and database layer, we need to perform the below steps to update the password and proceed through the patching 

Step 1 trying to apply the database patch

[appccbt@omccbtst ORACLE]$ sh ouafDatabasePatch.sh


Enter the target database type (O/M/D) [O]: O


Enter the username that owns the schema: CISADM

Enter the password for the CISADM user:


Enter the name of the Oracle Database Connection String: omccbtst.<domain>.com:1590:OMTRAIN

Couldn't connect to database ORACLE omccbtst.<domain>.com:1590:OMTRAIN CISADM : java.sql.SQLException: ORA-01017: invalid username/password; logon denied

Step 2: update the database patch by using invokeDBUpdatePatch.sh command from the ouaf home/bin

Once the command had been executed we need to provide the new password as the input 

[appccbt@omccbtst bin]$ sh invokeDBUpdatePatch.sh -p
invokeDBUpdatePatch.sh: option requires an argument -- p
INFO: Loaded file:/u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/standalone/config/hibernate.properties from classpath: {hibernate.connection.password=ENCKS(EaodgSKfOmYz0giX+yN/8QliK1eRwWl5FLkIC9BYFWk=), hibernate.ucp.min_size=1, hibernate.ucp.connection_wait_timeout=5, hibernate.query.substitutions=true 'Y', false 'N', hibernate.cache.use_second_level_cache=false, hibernate.show_sql=false, hibernate.ucp.inactive_connection_timeout=300, hibernate.jdbc.batch_size=30, hibernate.jdbc.fetch_size=100, hibernate.ucp.max_size=30, hibernate.max_fetch_depth=2, hibernate.connection.release_mode=on_close, hibernate.ucp.max_idle_time=0, hibernate.connection.username=CISADM, hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver, hibernate.ucp.max_statements=50, hibernate.connection.provider_class=com.splwg.shared.common.UCPConnectionProvider, hibernate.ucp.jmx_enabled=false, hibernate.transaction.factory_class=org.hibernate.transaction.JDBCTransactionFactory, hibernate.dialect=org.hibernate.dialect.Oracle10gDialect, hibernate.connection.url=jdbc:oracle:thin:@//omccbtst.<domain>.com:1590/OMTRAIN, hibernate.query.factory_class=org.hibernate.hql.internal.classic.ClassicQueryTranslatorFactory}
INFO: Loaded file:/u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/standalone/config/spl.properties from classpath: {spl.runtime.cobol.sql.cursoredCache.maxRows=10, com.oracle.ouaf.system.keystore.hmac_key_alias=ouaf.system.hmac, spl.tools.loaded.applications=base,ccb,cm, spl.runtime.environ.init.dir=/u06/app/oracle/product/fmw/ouaf/OMTCCB/etc, spl.runtime.environ.SPLEBASE=/u06/app/oracle/product/fmw/ouaf/OMTCCB, com.oracle.ouaf.system.keystore.mode=CBC, spl.geocodeDatasource.url=, calendar.japanese.eras=name:Taisho,abbr:T,since:1912-07-30,endDate:1926-12-24,japAbbr:大正;name:Showa,abbr:S,since:1926-12-25,endDate:1989-01-07,japAbbr:昭和;  name:Heisei,abbr:H,since:1989-01-08,endDate:,japAbbr:平成, ouaf.batch.onlineLogDir=/u06/app/oracle/product/fmw/ouaf/sploutput/OMTCCB, com.oracle.ouaf.system.truststore.passwordFileName=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_truststore_pass, com.oracle.ouaf.flush.jndi.provider.url=t3s://omccbtst.<domain>.com:8004, com.oracle.ouaf.flush.jms.disabled=true, com.oracle.ouaf.truststore.type=JCEKS, spl.runtime.cobol.cobrcall=false, com.oracle.ouaf.flush.jms.connection=jms/OUFlushConnectionFactory, com.oracle.ouaf.system.keystore.file=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_keystore, com.oracle.ouaf.flush.jms.requestTopic=jms/OUFlushRequestTopic, jmx.remote.x.password.file=scripts/ouaf.jmx.password.file, com.oracle.ouaf.xsl.dir=file:////u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/xai/schemas, spl.runtime.environ.SPLOUTPUT=/u06/app/oracle/product/fmw/ouaf/sploutput/OMTCCB, spl.runtime.sql.highValue=, jmx.remote.x.access.file=scripts/ouaf.jmx.access.file, com.oracle.ouaf.system.database.disableTagging=false, spl.runtime.oracle.statementCacheSize=300, spl.runtime.cobol.sql.fetchSize=150, spl.runtime.options.allowSystemDateOverride=true, com.oracle.ouaf.system.keystore.passwordFileName=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_storepass, com.oracle.XPath.LRUSize=, spl.geocodeDatasource.contextFactory=weblogic.jndi.WLInitialContextFactory, spl.runtime.service.extraInstallationServices=CILTINCP, com.oracle.ouaf.xsd.dir=file:////u06/app/oracle/product/fmw/ouaf/OMTCCB/splapp/xai/schemas, spl.runtime.options.isFCFEnabled=false, com.oracle.ouaf.system.keystore.padding=PKCS5Padding, spl.runtime.cobol.encoding=UTF8, spl.runtime.cobol.sql.cache.maxTotalEntries=1000, ouaf.database.session.setInstallationTimeZone=false, ouaf.accessiblity.features=false, com.oracle.XPath.flushTimeout=, spl.runtime.performSignedNumberValidation.C1=false , com.oracle.ouaf.flush.jms.responseTopic=jms/OUFlushResponseTopic, spl.geocodeDatasource.password=, spl.geocodeDatasource.user=, com.oracle.ouaf.system.truststore.file=/u06/app/oracle/product/fmw/ouaf/OMTCCB/ks/.ouaf_truststore, spl.runtime.utf8Database=true, com.oracle.ouaf.system.keystore.alias=ouaf.system, spl.runtime.cobol.sql.disableQueryCache=false, com.splwg.schema.newValidations.C1=false, com.splwg.schema.newValidations.F1=true, com.oracle.ouaf.system.keystore.type=JCEKS, com.oracle.ouaf.flush.jndi.factory.initial=weblogic.jndi.WLInitialContextFactory, spl.runtime.environ.isWebExpanded=false}
Enter the password  (or hit ENTER to quit):
Re-enter the value:
 -   2020-02-18 14:52:33,308 [main] INFO  (shared.common.DBUpdatePatchingCredentials) DB Config Info record in the table CI_WFM_OPT has been successfully updated has been successfully updated with new password.
 -   2020-02-18 14:52:33,311 [main] INFO  (shared.common.DBUpdatePatchingCredentials) Committed changes.

[appccbt@omccbtst bin]$

Step 3 Run ouafDatabasePatch.sh command to apply the patch in CCB database

[appccbt@omccbtst ORACLE]$ sh ouafDatabasePatch.sh


Enter the target database type (O/M/D) [O]:


Enter the username that owns the schema: CISADM

Enter the password for the CISADM user:


Enter the name of the Oracle Database Connection String: omccbtst.<domain>.com:1590:OMTRAIN

Target Schema is a Production Schema



Ready to process patches, Do you want to continue? (Y/N): Y

Working Directory: OMTRAIN001

***********************************

Setting up language file: OMTRAIN001/CDXPatch.lang
exit value: 0

Applying 23621236 ...

Writing to log file: OMTRAIN001/log23621236.log



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

--Applying patch 23621236 at 02-18-2020 14:54:21 using $LastChangedRevision: 43302 $

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


--Copying language information



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

--Patch 23621236 applied successfully at 02-18-2020 14:54:21

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


Patch applied successfully..

0
[appccbt@omccbtst ORACLE]$