Part of DBA routine is patch databases by quarterly, what is that means? 

It means that Oracle is a software and it is hard to hear, but Oracle database has bugs and secutiry vulnerabilities, so is good idea patch regularly your production databases with RU and RUR, you can
find more information about this change on nomenclature that occurred on 12cR2 at this of link on blog of the master https://mikedietrichde.com/2017/11/07/ru-rur-recommendations-facts/

So, usually a patch came with 2 different parts, first part is the binaries (libraries, programs and etc), you must be familiar with opatch utility to apply a patch on the Oracle Home, and the second part is the data dictionary corrections. 

To execute this part, since Oracle version 12cR1 we use datapatch to apply, this tool is amazing, really like to use it. So, the datapatch utility, is part of OPatch and the purpose of usage of this tool is apply scripts against Oracle data dictionary.

In this article I will show to you, how to query the correct views on the database to find informations about 11g, and 12c (family) databases, let’s say like that.

So, on version 11g, after apply a PSU patch on database, after run the script catbundle.sql, you can query this view with SYS account, to check information about the past PSUs applied:

— 11g version

set lin 1000
col action form a12
col version form a40
col comments form a70
col action_date form a20

select comments, action, to_char(action_time,’DD/MM/RR HH24:MI:SS’) action_date, version
from registry$history
order by action_date;

COMMENTS ACTION ACTION_DATE VERSION
———————————————————————- ———— ——————– —————————————-
Patch applied from 18.1.0.0.0 to 18.3.0.0.0 RU_APPLY 20/03/19 14:36:26 18.0.0.0.0
OJVM RU post-install APPLY 20/03/19 14:37:42 18.3.0.0.180717OJVMRU
RAN jvmpsu.sql jvmpsu.sql 20/03/19 14:37:42 18.3.0.0.180717OJVMRU
RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627 BOOTSTRAP 18

For the version 12R1 and onwards you will need to use another view and depending against wich version are running you can filter by PSU ou RELEASE words on column description:

— For 12c and 18c
set lin 1000
col action form a12
col version form a40
col description form a85
col action_date form a20

select description, action, to_char(action_time,’DD/MM/RR HH24:MI:SS’) action_date, ‘ ‘ version
from dba_registry_sqlpatch;

DESCRIPTION ACTION ACTION_DATE VERSION
————————————————————————————- ———— ——————– —————————————-
Database Release Update : 18.3.0.0.180717 (28090523) APPLY 20/03/19 14:37:46
OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415) APPLY 20/03/19 14:37:46

Obs.: There is no more version column, I keep that just for have compatibility with some old scripts.

To query:
— 12cR1
where upper(description) like ‘DATABASE PSU%’;

— 12cR2 onwards
where upper(descrition) like ‘DATABASE RELEASE%’;

I hope this brief note can help you to find more details how to query and check if the version applied on binaries matches the version of the database level of patch.

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

All the best,
Rodrigo Mufalani