Search This Blog

Total Pageviews

Friday, 1 January 2021

Query the last patch applied on database 11g, 12cR1, 12cR2 ,18c and 19c....

 

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



--12c
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)||
  'ACTION_TIME..................: '||ACTION_TIME        ||chr(10)||
  'STATUS.......................: '||STATUS             ||chr(10)||
  'VERSION......................: '||VERSION            ||chr(10)||
  'BUNDLE_SERIES................: '||BUNDLE_SERIES      ||chr(10)||
  'BUNDLE_ID....................: '||BUNDLE_ID          ||chr(10)||
  'INSTALL_ID...................: '||INSTALL_ID         ||chr(10)||
  'FLAGS........................: '||FLAGS              ||chr(10)||
  'LOGFILE......................: '||LOGFILE            ||chr(10)||
  'POST_LOGFILE.................: '||POST_LOGFILE       ||chr(10)||
  'DESCRIPTION..................: '||DESCRIPTION
from sys.registry$sqlpatch
order by 1
/






ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MM-YY HH24:MI';

SET LINESIZE 500  PAGESIZE 1000  SERVEROUT ON  LONG 2000000
COLUMN action_time FORMAT A14
COLUMN action FORMAT A10
COLUMN patch_type FORMAT A10
COLUMN description FORMAT A55
COLUMN status FORMAT A10
COLUMN version FORMAT A10
-- spool check_patches_19c.txt

select CON_ID,
action_time ,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;







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 patch

  • RU: Release Update

  • RUI: Release Update Increment

  • RUR: Release Update Revision

  • CU: 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.

===========


set linesize 300
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)
patch type !!!


Patch Set Updates (PSUs) Security Patch Update (SPUs) Critical Patch updates (CPUs) Interim Patch PSE MLR Exception release One-Off Hotfix Hot Patch Online Patch Security One-Off Bundle Patch Bundle Patch Updates (BPUs) Maintenance Pack Service Pack MLRs Cumulative Patch Update Release Diagnostic Patch Patch Set Singleton Merge Patch Overlay Patch Superset Patch OJVM Patch JDBC Patch ODBC Patch JDK Patch Perl Patch



  28163190   22274810 OCW JUL 2018 RELEASE UPDATE 12.2.0.1.180717 (28163             bundle_member        true                 false
                      190)





column patch_id format 9999999999
column ru_version format a15
column lob_size_md format 9999
COLUMN ru_build_ts FORMAT A20
COLUMN SUBSTR(description,1,40) FORMAT A40

set linesize 100  pagesize 300

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MM-YY HH24:MI';
ALTER SESSION SET "_EXCLUDE_SEED_CDB_VIEW" = FALSE;

SELECT
    patch_id,
    ru_version,
    TO_CHAR(ru_build_timestamp, 'MM/DD/YYYY HH24:MI:SS') AS ru_build_ts,
    round(dbms_lob.getlength(patch_directory) / 1024 / 1024) lob_size_mb
FROM
    sys.registry$sqlpatch_ru_info;

SELECT
    patch_id,
    SUBSTR(description,1,40) PATCH_DESCRIPTION,
    TO_CHAR(source_build_timestamp, 'MM/DD/YYYY HH24:MI:SS') AS patch_build_ts,
    round(dbms_lob.getlength(patch_directory) / 1024 / 1024) lob_size_mb
FROM
    sys.registry$sqlpatch
WHERE
    patch_type<>'RU';



SELECT
   con_id, round(sum(dbms_lob.getlength(patch_directory) / 1024 / 1024)) total_lob_size_mba
FROM
   containers(sys.registry$sqlpatch_ru_info)
GROUP BY
   con_id
ORDER BY
   con_id;

SELECT
   con_id, round(sum(dbms_lob.getlength(patch_directory) / 1024 / 1024)) total_lob_size_mba
FROM
   containers(sys.registry$sqlpatch)
GROUP BY
   con_id
ORDER BY
   con_id;



***************************



[oracle@ora19 ~]$ opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"
Patch  37960098     : applied on Sun Jan 04 17:08:29 GMT 2026
Patch  29585399     : applied on Thu Apr 18 08:21:33 BST 2019






  alter session set nls_date_format='dd-mm-YYYY hh24:mi';
  
alter session set "_exclude_seed_cdb_view"=FALSE;
  
  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
  
  








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
/


SET LINESIZE 500  PAGESIZE 1000  SERVEROUT ON  LONG 2000000
COLUMN action_time FORMAT A20
COLUMN action FORMAT A24
COLUMN version FORMAT A10
COLUMN comments FORMAT A75
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,action,version, id, comments, 
bundle_series FROM sys.registry$history
ORDER by action_time;




ACTION_TIME          ACTION                   VERSION            ID COMMENTS                                                                    BUNDLE_SER
-------------------- ------------------------ ---------- ---------- --------------------------------------------------------------------------- ----------
04-JAN-2026 17:28:02 RU_APPLY                 19.0.0.0.0            Patch applied from 19.27.0.0.0 to 19.28.0.0.0: Release_Update - 25070503041
                                                                    7

14-JUN-2025 11:00:09 RU_APPLY                 19.0.0.0.0            Patch applied from 19.3.0.0.0 to 19.27.0.0.0: Release_Update - 250406131139
                     BOOTSTRAP                19                    RDBMS_19.28.0.0.0DBRU_LINUX.X64_250704




SET LINESIZE 500  PAGESIZE 1000  SERVEROUT ON  LONG 2000000

COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A60
COLUMN source_version FORMAT A13
COLUMN target_version FORMAT A13
COLUMN version_full FORMAT A13
COLUMN comp_id FORMAT A8


alter session set "_exclude_seed_cdb_view"=FALSE;

-- spool check_patches_19.txt

 select CON_ID,
        TO_CHAR(action_time, 'DD-MM-YYYY') AS action_time,
        PATCH_ID,
        PATCH_TYPE,
        ACTION,
        DESCRIPTION,
        SOURCE_VERSION,
        TARGET_VERSION
   from CDB_REGISTRY_SQLPATCH
  order by CON_ID, action_time, patch_id
;


  CON_ID ACTION_TIME    PATCH_ID PATCH_TYPE ACTION     DESCRIPTION                                                  SOURCE_VERSIO TARGET_VERSIO
---------- ------------ ---------- ---------- ---------- ------------------------------------------------------------ ------------- -------------
         1 04-01-2026     37960098 RU         APPLY      Database Release Update : 19.28.0.0.250715 (37960098)        19.27.0.0.0   19.28.0.0.0
         1 14-06-2025     37642901 RU         APPLY      Database Release Update : 19.27.0.0.250415 (37642901)        19.1.0.0.0    19.27.0.0.0
         2 04-01-2026     37960098 RU         APPLY      Database Release Update : 19.28.0.0.250715 (37960098)        19.27.0.0.0   19.28.0.0.0
         2 14-06-2025     37642901 RU         APPLY      Database Release Update : 19.27.0.0.250415 (37642901)        19.1.0.0.0    19.27.0.0.0
         2 25-06-2025     37642901 RU         APPLY      Database Release Update : 19.27.0.0.250415 (37642901)        19.1.0.0.0    19.27.0.0.0




select CON_ID,
       COMP_ID,
       VERSION_FULL,
       STATUS
  from CDB_REGISTRY
 order by CON_ID, COMP_ID
;




column patch_id format 9999999999
column ru_version format a15
column lob_size_md format 9999
COLUMN ru_build_ts FORMAT A20
COLUMN SUBSTR(description,1,50) FORMAT A40

set linesize 100  pagesize 300

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD/MM/YYYY HH24:MI';
ALTER SESSION SET "_EXCLUDE_SEED_CDB_VIEW" = FALSE;

SELECT
    patch_id,
    ru_version,
    TO_CHAR(ru_build_timestamp, 'DD/MM/YYYY HH24:MI:SS') AS ru_build_ts,
    round(dbms_lob.getlength(patch_directory) / 1024 / 1024) lob_size_mb
FROM   sys.registry$sqlpatch_ru_info
;



   PATCH_ID RU_VERSION      RU_BUILD_TS          LOB_SIZE_MB
----------- --------------- -------------------- -----------
   29517242 19.3.0.0.0      10/04/2019 12:27:20            4
   37642901 19.27.0.0.0     06/04/2025 13:11:39          194
   37960098 19.28.0.0.0     05/07/2025 03:04:17          203


SELECT
    patch_id,
    SUBSTR(description,1,40) PATCH_DESCRIPTION,
    TO_CHAR(source_build_timestamp, 'DD/MM/YYYY HH24:MI:SS') AS patch_build_ts,
    round(dbms_lob.getlength(patch_directory) / 1024 / 1024) lob_size_mb
FROM  sys.registry$sqlpatch
WHERE
    patch_type<>'RU'
;



SELECT
   con_id, round(sum(dbms_lob.getlength(patch_directory) / 1024 / 1024)) total_lob_size_mba
FROM
   containers(sys.registry$sqlpatch_ru_info)
GROUP BY
   con_id
ORDER BY
   con_id;


    CON_ID TOTAL_LOB_SIZE_MBA
---------- ------------------
         1                401
         2                397



SELECT
   con_id, round(sum(dbms_lob.getlength(patch_directory) / 1024 / 1024)) total_lob_size_mba
FROM
   containers(sys.registry$sqlpatch)
GROUP BY
   con_id
ORDER BY
   con_id;



    CON_ID TOTAL_LOB_SIZE_MBA
---------- ------------------
         1
         2








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;



alter system set nls_date_format='dd.mm.yyyy hh24:mi:ss' ;

col DESCRIPTION for a65
select
con_id,
drs.patch_id,
drs.description,
drs.action,
--cast(drs.action_time as date) action_date
drs.action_time
from
cdb_registry_sqlpatch drs
where
1=1
order by
con_id,drs.action_time desc







=============


create sql patch !!!!!!!!
https://anuj-singh.blogspot.com/search?q=dbms_sqldiag.create_sql_patch
Posted by at 4 comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive