Query the last patch applied on database 11g, 12cR1, 12cR2 ,18c and 19c....
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
And .....
for CDB
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)||
'CON_ID.....................: '||CON_ID
--'LOGFILE....................: '||LOGFILE ||Chr(10)||
--'RU LOGFILE.................: '||RU_LOGFILE ||Chr(10)
from cdb_registry_sqlpatch
order by 1
======================================================
INSTALL ID.................: 1
PATCH_ID...................: 28090523
PATCH UID..................: 22329768
PATCH TYPE.................: RU
ACTION.....................: APPLY
STATUS.....................: SUCCESS
ACTION TIME................: 29-DEC-20 02.44.51.571596 PM
DESCRIPTION................: Database Release Update : 18.3.0.0.180717 (28090523)
FLAGS......................: N
SOURCE VERSION.............: 18.1.0.0.0
SOURCE BUILD_DESCRIPTION...: Feature Release
SOURCE BUILD_TIMESTAMP.....:
TARGET VERSION.............: 18.3.0.0.0
TARGET BUILD DESCRIPTION...: Release_Update
TARGET BUILD TIMESTAMP.....: 28-JUN-18 09.43.00.000000 AM
======================================================
INSTALL ID.................: 2
PATCH_ID...................: 27923415
PATCH UID..................: 22239273
PATCH TYPE.................: INTERIM
ACTION.....................: APPLY
STATUS.....................: SUCCESS
ACTION TIME................: 29-DEC-20 02.48.29.679234 PM
DESCRIPTION................: OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
FLAGS......................: NJ
SOURCE VERSION.............: 18.3.0.0.0
SOURCE BUILD_DESCRIPTION...: Release_Update
SOURCE BUILD_TIMESTAMP.....: 28-JUN-18 09.43.00.000000 AM
TARGET VERSION.............: 18.3.0.0.0
TARGET BUILD DESCRIPTION...: Release_Update
TARGET BUILD TIMESTAMP.....: 28-JUN-18 09.43.00.000000 AM
2 rows selected.
From cdb_registry_sqlpatch
=====================================================
INSTALL ID.................: 1
PATCH_ID...................: 29517242
PATCH UID..................: 22862832
PATCH TYPE.................: RU
ACTION.....................: APPLY
STATUS.....................: SUCCESS
ACTION TIME................: 18-MAR-23 02.58.38.960124 PM
DESCRIPTION................: Database Release Update : 19.3.0.0.190416 (29517242)
FLAGS......................: N
SOURCE VERSION.............: 19.1.0.0.0
SOURCE BUILD_DESCRIPTION...: Feature Release
SOURCE BUILD_TIMESTAMP.....:
TARGET VERSION.............: 19.3.0.0.0
TARGET BUILD DESCRIPTION...: Release_Update
TARGET BUILD TIMESTAMP.....: 10-APR-19 12.27.20.000000 PM
CON_ID.....................: 1
===
SET LINESIZE 500 PAGESIZE 1000 SERVEROUT ON LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN bundle_series FORMAT A4
COLUMN comments FORMAT A30
COLUMN description FORMAT A70
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
COLUMN version FORMAT A10
SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
===
The XML patch descriptor found under $ORACLE_HOME/sqlpatch/bug id/bug uid/bug id.xml
bundledata_<series>.xml found under $ORACLE_HOME/rdbms/admin.
Bundle patches are marked with 'B' in the flags column of dba_registry_sqlpatch.
FLAGS!!!!
U: Patch requires upgrade mode
J: Patch is a JVM patch
F: Patch was installed using -force
B: Patch is a bundle patch
PATCH_TYPE
Type of the patch. Possible values:
INTERIM: Interim patchRU: Release UpdateRUI: Release Update IncrementRUR: Release Update RevisionCU: Cumulative Update
set lines 132 verify off head off feedback on long 1000
select distinct
'======================================================' ||chr(10)||
'PATCH_ID...................: '||PATCH_ID ||Chr(10)||
'PATCH UID..................: '||PATCH_UID ||Chr(10)||
'ACTION.....................: '||ACTION ||Chr(10)||
'STATUS.....................: '||STATUS ||Chr(10)||
'ACTION TIME................: '||ACTION_TIME ||Chr(10)||
'DESCRIPTION................: '||DESCRIPTION ||Chr(10)||
'FLAGS......................: '||FLAGS ||Chr(10)||
'LOGFILE....................: '||LOGFILE ||Chr(10)
from dba_registry_sqlpatch
order by 1
What to do if the status of a datapatch action was not SUCCESS due to finding non-ignorable errors (Doc ID 1635056.1)
use this !!!
$ORACLE_HOME/OPatch/datapatch -verbose
$ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Fri Jan 1 14:05:52 2021
Copyright (c) 2012, 2018, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_65502_2021_01_01_14_05_52/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Warning: PDB ANUJ is in mode MOUNTED and will be skipped.
Warning: PDB PDB2 is in mode MOUNTED and will be skipped.
Warning: PDB VIHAAN is in mode MOUNTED and will be skipped.
Warning: PDB VIHAAN2 is in mode MOUNTED and will be skipped.
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
Binary registry: Installed
PDB CDB$ROOT: Applied successfully on 29-DEC-20 02.48.29.679234 PM
PDB PDB$SEED: Applied successfully on 29-DEC-20 03.44.41.663023 PM
Current state of release update SQL patches:
Binary registry:
18.3.0.0.0 Release_Update 1806280943: Installed
PDB CDB$ROOT:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 29-DEC-20 02.44.51.571596 PM
PDB PDB$SEED:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 29-DEC-20 03.42.14.995595 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
SQL Patching tool complete on Fri Jan 1 14:06:05 2021
============================================================================
set lines 132 verify off head off feedback on long 1000
select distinct
'======================================================' ||chr(10)||
'PATCH_ID...................: '||PATCH_ID ||Chr(10)||
'PATCH UID..................: '||PATCH_UID ||Chr(10)||
'ACTION.....................: '||ACTION ||Chr(10)||
'STATUS.....................: '||STATUS ||Chr(10)||
'ACTION TIME................: '||ACTION_TIME ||Chr(10)||
'DESCRIPTION................: '||DESCRIPTION ||Chr(10)||
'FLAGS......................: '||FLAGS ||Chr(10)||
'LOGFILE....................: '||LOGFILE ||Chr(10)
from dba_registry_sqlpatch
order by 1
======================================================
PATCH_ID...................: 29517242
PATCH UID..................: 22862832
ACTION.....................: APPLY
STATUS.....................: SUCCESS
ACTION TIME................: 18-MAR-23 02.58.38.960124 PM
DESCRIPTION................: Database Release Update : 19.3.0.0.190416 (29517242)
FLAGS......................: N
LOGFILE....................: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_ORCLDP_CDBROOT_2023Mar18_14_56_39
.log
set lines 132 verify off head off feedback on long 1000
select distinct
'======================================================'||chr(10)||
'id...........................: '||id ||Chr(10)||
'ACTION.......................: '||ACTION ||Chr(10)||
'ACTION_TIME..................: '||ACTION_TIME ||Chr(10)||
'NAMESPACE....................: '||NAMESPACE ||Chr(10)||
'VERSION......................: '||VERSION ||Chr(10)||
'BUNDLE_SERIES................: '||BUNDLE_SERIES||Chr(10)||
'COMMENTS.....................: '||COMMENTS
from registry$history ---<<<<---DBA_REGISTRY_HISTORY does not get updated when he applies a Bundle Patch
order by 1
======================================================
id...........................:
ACTION.......................: BOOTSTRAP
ACTION_TIME..................:
NAMESPACE....................: DATAPATCH
VERSION......................: 12.2.0.1
BUNDLE_SERIES................:
COMMENTS.....................: RDBMS_12.2.0.1.0_LINUX.X64_170125
======================================================
id...........................:
ACTION.......................: BOOTSTRAP
ACTION_TIME..................:
NAMESPACE....................: DATAPATCH
VERSION......................: 18
BUNDLE_SERIES................:
COMMENTS.....................: RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627
======================================================
Or !!
set serverout on
exec dbms_qopatch.get_sqlpatch_status;
SQL> set serverout on
SQL> exec dbms_qopatch.get_sqlpatch_status;
Patch Id : 28090523
Action : APPLY
Action Time : 29-DEC-2020 14:44:51
Description : Database Release Update : 18.3.0.0.180717 (28090523)
Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/28090523/22329768/28090523_apply_ORCL_CDBROOT_2020Dec29_14_44_14.log
Status : SUCCESS
Patch Id : 27923415
Action : APPLY
Action Time : 29-DEC-2020 14:48:29
Description : OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/27923415/22239273/27923415_apply_ORCL_CDBROOT_2020Dec29_14_48_26.log
Status : SUCCESS
PL/SQL procedure successfully completed.
set linesize 300
select patch_id,action,action_time,status,con_id from cdb_registry_sqlpatch;
PATCH_ID ACTION ACTION_TIME STATUS CON_ID
---------- --------------- --------------------------------------------------------------------------- ------------------------- ----------
29249637 APPLY 12-APR-20 09.18.11.794215 AM SUCCESS 1
29314339 APPLY 12-APR-20 09.18.14.935601 AM SUCCESS 1
29249637 APPLY 12-APR-20 04.01.26.501126 AM SUCCESS 3
29314339 APPLY 12-APR-20 04.01.29.657573 AM SUCCESS 3
29249637 APPLY 12-APR-20 04.01.26.501126 AM SUCCESS 4
29314339 APPLY 12-APR-20 04.01.29.657573 AM SUCCESS 4
6 rows selected.
===========
with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.*
from a,
xmltable('InventoryInstance/patches/*'
passing a.patch_output
columns
patch_id number path 'patchID',
patch_uid number path 'uniquePatchID',
description varchar2(80) path 'patchDescription',
constituent number path 'constituent',
patch_type varchar2(20) path 'patchType',
rollbackable varchar2(20) path 'rollbackable',
sql_patch varchar2(8) path 'sqlPatch',
DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'
) x
PATCH_ID PATCH_UID DESCRIPTION CONSTITUENT PATCH_TYPE ROLLBACKABLE SQL_PATC DBSTARTMOD
---------- ---------- -------------------------------------------------- ----------- -------------------- -------------------- -------- ----------
29249637 22802302 OJVM RELEASE UPDATE: 12.2.0.1.190416 (29249637) singleton true true normal
29314339 22821655 Database Apr 2019 Release Update : 12.2.0.1.190416 singleton true true normal
(29314339)
28163190 22274810 OCW JUL 2018 RELEASE UPDATE 12.2.0.1.180717 (28163 bundle_member true false
190)
