Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: May 31, 2024 - 6:07 pm UTC

Last updated: June 10, 2024 - 6:51 am UTC

Version: 19.19.0.0

Viewed 100+ times

You Asked

Hi TOM,

Sorry for asking this basic question (After two hours of searching, I am unable to find the answer)

Let's say I have run opatch in an ora19 home to patch the binaries.
But then I "forgot" to run datapatch.

Is there any dictionary view to tell me that I "forgot"?

BR
Peter

and Connor said...

Check out DBA_REGISTRY_SQLPATCH

SQL> select patch_id, status
  2  from DBA_REGISTRY_SQLPATCH;

  PATCH_ID STATUS
---------- -----------------------
  34110698 SUCCESS
  34468137 SUCCESS
  35681617 SUCCESS
  35962857 SUCCESS

Rating

  (2 ratings)

Comments

datapatch

Mikhail Velikikh, June 06, 2024 - 10:16 am UTC

> But then I "forgot" to run datapatch.
> ...
> Check out DBA_REGISTRY_SQLPATCH

datapatch populates DBA_REGISTRY_SQLPATCH. If it was not run, you will have nothing there.
SQL> !$ORACLE_HOME/OPatch/opatch lspatches
36233263;Database Release Update : 19.23.0.0.240416 (36233263)
35926646;OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646)
35967489;OCW RELEASE UPDATE 19.22.0.0.0 (35967489)

OPatch succeeded.

SQL>
SQL> select patch_id, status from dba_registry_sqlpatch;

  PATCH_ID STATUS
---------- -------------------------
  35926646 SUCCESS
  35943157 SUCCESS

SQL>
SQL> select patch_id, description, sql_patch
  2    from xmltable('InventoryInstance/patches/*'
  3                   passing dbms_qopatch.get_opatch_lsinventory
  4           columns
  5             patch_id int path 'patchID',
  6             description varchar2(255) path 'patchDescription',
  7             sql_patch varchar2(10) path 'sqlPatch')
  8  /

  PATCH_ID DESCRIPTION                                                            SQL_PATCH
---------- ---------------------------------------------------------------------- ----------
  36233263 Database Release Update : 19.23.0.0.240416 (36233263)                  true
  35926646 OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646)                       true
  35967489 OCW RELEASE UPDATE 19.22.0.0.0 (35967489)                              true


To determine whether you need to run datapatch, pass the prereq argument to it (datapatch -prereq), or compare DBA_REGISTRY_SQLPATCH against your binary registry as it is demonstrated above. For example, patch 36233263 is an SQL patch which is not in DBA_REGISTRY_SQLPATCH. Hence, it needs to be applied by datapatch.
Connor McDonald
June 10, 2024 - 6:51 am UTC

Nice input

Datapatch - When you are not DBA, but something else

A reader, June 07, 2024 - 6:57 pm UTC

Thanks Mikhail,

You have given valuable input - And sort of confirmed that this is not straight forward

I've been asked to "confirm" that all databases are at a certain patch level, and that all data patches have been applied, also. Let's just say I am auditor without much knowledge on the subject (I am actually Developer crossing certain lines)

I will work from your valued input, thanks!

//Peter