At the DOAG Conference in November in Nürnberg in November 2016 a customer asked me right after my talk about “Upgrade to Oracle Database 12.2. – Live and Uncensored” why the DBA_REGISTRY_HISTORY does not get updated when he applies a Bundle Patch and follows all instructions including the “./datapatch -verbose” call.
I was wondering as well and asked him to open an SR. Which he did. And he received the message from Support that it is not supposed to appear in Oracle 12c anymore this way but only in DBA_REGISTRY_SQLPATCH. Now I dug a bit deeper internally to get a clear statement (thanks to Carol (my boss) and Rae (my teammate) and Scott (the man who owns datapatch) for following up!).
Patch Query in Oracle Database 11g
Tim Hall has published this simple and quite helpful script to query applied PSUs and BPs in Oracle Database 11g:
Script to monitor DBA_REGISTRY_HISTORY
And the output in my environment looked like this:
ACTION_TIME ACTION NAMESPE VERSION ID COMMENTS BUN -------------------- ------- ------- -------- ------- -------------------- --- 01-JUL-2016 15:24:56 APPLY SERVER 11.2.0.4 160419 PSU 11.2.0.4.160419 PSU 21-OCT-2016 17:40:32 APPLY SERVER 11.2.0.4 161018 PSU 11.2.0.4.161018 PSU
But running the same script on Oracle Database 12.1.0.2 returnes (as for the customer) “no rows selected“.
Patch Query for Oracle Database 12c
Since Oracle Database 12.1.0.1 we use DBA_REGISTRY_SQLPATCH instead of DBA_REGISTRY_HISTORY to track PSUs and BPs applied to the database. I used this script: check_patches.sql.
My output in Oracle Database 12.1.0.2 looks like this:
ACTION_TIME ACTION STATUS DESCRIPTION VERSION PATCH_ID BUND -------------------- ------- -------- -------------------- -------- -------- ---- 21-OCT-2016 17:29:36 APPLY SUCCESS DBP: 12.1.0.2.161018 12.1.0.2 24340679 DBBP
when using this tiny script:
SET LINESIZE 400 SET PAGESIZE 100 COLUMN action_time FORMAT A20 COLUMN action FORMAT A10 COLUMN status FORMAT A10 COLUMN description FORMAT A40 COLUMN version FORMAT A10 COLUMN bundle_series FORMAT A10 SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time, action, status, description, version, patch_id, bundle_series FROM sys.dba_registry_sqlpatch ORDER by action_time;
But the question remains if – as in Oracle Database 12.1.0.1 – both views should get updated.
Explanation
In 11.2.0.4, we used the script catbundle.sql to apply bundle patches. It uses DBA_REGISTRY_HISTORY only. For 12.1.0.1 with the introduction of datapatch, we now have the (much better) DBA_REGISTRY_SQLPATCH. This is used for both, bundle and non-bundle patches. In Oracle Database 12.1.0.1. for bundle patches we actually called catbundle internally, so in 12.1.0.1 both registries were updated for bundle patches.
Starting in 12.1.0.2, however, only DBA_REGISTRY_SQLPATCH is queried and updated for bundle and non
bundle patches.
Update [Dec 23, 2016]
After discussing this and other issues with the owners of datapatch my teammate Rae logged a bug for this issue as we believe both views should be updated as it happened in 12.1.0.1. Bug# 25269268 tracks the issue.
–Mike
Thanks ! Mike
Looks like the bundle_series is dropped in 19c, so I am using your query and modified little bit
SET LINESIZE 400
SET PAGESIZE 100
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN source_version FORMAT A10
COLUMN target_version FORMAT A10
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS action_time,
action,
status,
description,
SOURCE_VERSION,
TARGET_VERSION,
patch_id
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
Thanks a lot!
Cheers,
Mike
Actually the 18c /and now also 19c/ scripts had the correct sql already 🙂
Cheers,
Mike
Hi
I am using following sql 🙂
set lines 132 verify off head off feedback on long 1000
select distinct
‘======================================================’ ||chr(10)||
‘INSTALL ID……………..: ‘||INSTALL_ID ||Chr(10)||
‘PATCH_ID……………….: ‘||PATCH_ID ||Chr(10)||
‘PATCH UID………………: ‘||PATCH_UID ||Chr(10)||
‘PATCH TYPE……………..: ‘||PATCH_TYPE ||Chr(10)||
‘ACTION…………………: ‘||ACTION ||Chr(10)||
‘STATUS…………………: ‘||STATUS ||Chr(10)||
‘ACTION TIME…………….: ‘||ACTION_TIME ||Chr(10)||
‘DESCRIPTION…………….: ‘||DESCRIPTION ||Chr(10)||
‘FLAGS………………….: ‘||FLAGS ||Chr(10)||
‘SOURCE VERSION………….: ‘||SOURCE_VERSION ||Chr(10)||
‘SOURCE BUILD_DESCRIPTION…: ‘||SOURCE_BUILD_DESCRIPTION ||Chr(10)||
‘SOURCE BUILD_TIMESTAMP…..: ‘||SOURCE_BUILD_TIMESTAMP ||Chr(10)||
‘TARGET VERSION………….: ‘||TARGET_VERSION ||Chr(10)||
‘TARGET BUILD DESCRIPTION…: ‘||TARGET_BUILD_DESCRIPTION ||Chr(10)||
‘TARGET BUILD TIMESTAMP…..: ‘||TARGET_BUILD_TIMESTAMP ||Chr(10)
–‘LOGFILE………………..: ‘||LOGFILE ||Chr(10)||
–‘RU LOGFILE……………..: ‘||RU_LOGFILE ||Chr(10)
from dba_registry_sqlpatch
order by 1
regards
Anuj singh
spot on in 12c
Hi,
it is not so clear for me the new fields SOURCE_VERSION AND TARGET_VERSION instead of VERSION, could you help me to understand?
This is from my 19.x Database, can you explain better what this means?
Many thanks
select PATCH_ID, PATCH_UID, SOURCE_VERSION, TARGET_VERSION, ACTION, ACTION_TIME, STATUS, DESCRIPTION from dba_registry_sqlpatch order by action_time;
PATCH_ID PATCH_UID SOURCE_VER TARGET_VER ACTION ACTION_TIME STATUS DESCRIPTION
———- ———- ———- ———- ———- —————————— ———- ————————————————————
30869156 23493838 19.1.0.0.0 19.7.0.0.0 APPLY 06-OCT-20 06.53.17.640967 PM SUCCESS Database Release Update : 19.7.0.0.200414 (30869156)
30805684 23401476 19.1.0.0.0 19.1.0.0.0 APPLY 06-OCT-20 06.53.17.646688 PM SUCCESS OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)
cheers
Mario
Hi Mario,
apart from being this VERY old from 3 years ago (you should patch urgently) the source_version is the patch version from before patching (in your case, 19.1.0), and the target_version is the version you patched to (19.7.0 in this case). This has been done on Oct 6, 2020 with SUCCESS.
Cheers
Mike
Many thanks ☺️ clear as usual … and yes, I’m patching in these days to 19.18 🤗
Thanks Mario!
Cheers
Mike