Anuj Singh Oracle DBA: 1 Jan 2021

Friday, 1 January 2021

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 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.

===========

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)

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

create sql patch !!!!!!!!

https://anuj-singh.blogspot.com/search?q=dbms_sqldiag.create_sql_patch

Anuj Singh Oracle DBA

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....

What to do if the status of a datapatch action was not SUCCESS due to finding non-ignorable errors (Doc ID 1635056.1)

Oracle DBA

anuj blog Archive