Removing patch history from registry$sqlpatch table forcefully

Verify Patch history before run datapatch

SQL>select name,open_mode from v$database;

NAME   OPEN_MODE
--------- --------------------
ORADBA1   READ WRITE

SQL>set linesize 400
col ACTION_TIME for a30
col DESCRIPTION for a70
col BUNDLE_SERIES for a20
select patch_id,action,status,action_time,description from DBA_REGISTRY_SQLPATCH;

PATCH_ID ACTION STATUS  ACTION_TIME      DESCRIPTION
-------- ------ ------- ---------------------------- -------------------------------------------------------
25433286 APPLY  SUCCESS 16-OCT-18 04.23.28.979000 PM WINDOWS DB BUNDLE PATCH 12.1.0.2.170228(64bit):25433286

Run datapatch to update latest patch details

[oracle@ORAHOST1]cd $ORACLE_HOME/OPatch
[oracle@ORAHOST1] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Thu May 14 07:15:13 2020
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/cfgtoollogs/sqlpatch/sqlpatch_54383_2020_05_14_07_15_13/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 190115 in the binary registry and ID 170228 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    28729169 (DATABASE PATCH SET UPDATE 12.1.0.2.190115)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 28729169 apply: SUCCESS
  logfile: /u01/app/cfgtoollogs/sqlpatch/28729169/22626239/28729169_apply_ORADBA1_2020May14_07_15_26.log (no errors)
SQL Patching tool complete on Thu May 14 07:15:45 2020


As per above datapatch output PATCH:190115 applied successfully.

[oracle@ORAHOST1] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 07:16:37 2020

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


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

SQL> select name,open_mode from v$database;

NAME   OPEN_MODE
--------- --------------------
ORADBA1   READ WRITE

SQL> 
set linesize 400
col ACTION_TIME for a30
col DESCRIPTION for a70
col BUNDLE_SERIES for a20
select patch_id,action,status,action_time,description from DBA_REGISTRY_SQLPATCH;

PATCH_ID ACTION STATUS  ACTION_TIME      DESCRIPTION
-------- ------ ------- ---------------------------- -------------------------------------------------------
25433286 APPLY  SUCCESS 16-OCT-18 04.23.28.979000 PM WINDOWS DB BUNDLE PATCH 12.1.0.2.170228(64bit):25433286
28729169 APPLY SUCCESS 14-MAY-20 07.15.45.383792 AM DATABASE PATCH SET UPDATE 12.1.0.2.190115


Seems PATCH 170228 Still exists hence ORADB1 recently refreshed with PROD DB where PROD DB running with 170228 PATCH
Datapatch will not be able to remove 170228 entry from registryv$sqlpatch as 170228 Patch binaries doesn't exists in ORADBA1 database Oracle Home
I have removed manually from registryv$sqlpatch and ran datapatch again to avoid descrepencies.

SQL>delete from registry$sqlpatch where PATCH_ID='25433286';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select patch_id,action,status,action_time,description from DBA_REGISTRY_SQLPATCH;

PATCH_ID ACTION STATUS  ACTION_TIME              DESCRIPTION
-------- ------ ------- ---------------------------- -----------------------------------------
28729169 APPLY SUCCESS 14-MAY-20 07.15.45.383792 AM DATABASE PATCH SET UPDATE 12.1.0.2.190115

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ORAHOST1] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch >./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Thu May 14 07:17:39 2020
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/cfgtoollogs/sqlpatch/sqlpatch_58879_2020_05_14_07_17_39/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 190115 in the binary registry and ID 190115 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  Nothing to apply

SQL Patching tool complete on Thu May 14 07:17:50 2020
[oracle@ORAHOST1] /u01/app/oracle/product/12.1.0/dbhome_1/OPatch >sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 07:17:54 2020

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


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

SQL> select name,open_mode from v$database;

NAME   OPEN_MODE
--------- ----------
ORADBA1   READ WRITE

SQL> set linesize 400
col ACTION_TIME for a30
col DESCRIPTION for a70
col BUNDLE_SERIES for a20
select patch_id,action,status,action_time,description from DBA_REGISTRY_SQLPATCH;

PATCH_ID ACTION STATUS  ACTION_TIME      DESCRIPTION
-------- ------ ------- ---------------------------- -----------------------------------------
28729169 APPLY SUCCESS 14-MAY-20 07.15.45.383792 AM DATABASE PATCH SET UPDATE 12.1.0.2.190115