Tag: opatch lspatches

“ORA-15137: The ASM cluster is in rolling patch state” after applied 19c GI RU

PROBLEM

Just after applied the latest 19c GI July 2020 RU, any operations on diskgroups will cause the following errors :

ORA-15032: not all alterations performed
ORA-15137: The ASM cluster is in rolling patch state.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe
Check the cluster status back to Normal

-racnode1

[grid@racnode1 ~]$ kfod op=patches
List of Patches
31281355
31304218
31305087
31335188
[grid@racnode1 ~]$
ASMCMD> showclusterstate
Normal
ASMCMD>
[grid@racnode1 ~]$ crsctl query crs softwarepatch
Oracle Clusterware patch level on node racnode1 is [441346801].
[grid@racnode1 ~]$
[grid@racnode1 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [441346801] and the complete list of patches [31281355 31304218 31305087 31335188 ] have been applied on the local node. The release patch string is [19.8.0.0.0].
[grid@racnode1 ~]$

-racnode2

[grid@racnode2 ~]$ kfod op=patches
List of Patches
31281355
31304218
31305087
31335188
[grid@racnode2 ~]$
ASMCMD> showclusterstate
Normal
ASMCMD>
[grid@racnode2 ~]$ crsctl query crs softwarepatch
Oracle Clusterware patch level on node racnode2 is [441346801].
[grid@racnode2 ~]$
[grid@racnode2 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [441346801] and the complete list of patches [31281355 31304218 31305087 31335188 ] have been applied on the local node. The release patch string is [19.8.0.0.0].
[grid@racnode2 ~]$
Advertisement

How to Clone 19c Oracle RAC Home using Gold-Image

As we know “clone.pl ” script has been widely used for cloning Oracle binaries before 19c. But it is now deprecated since 19c.

This post demonstrates how to clone 19c Oracle Database RAC Home from one database server to another.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Upgrade Oracle Database by Using AutoUpgrade Utility

Contents

Introduction

This post demonstrates how to upgrade 18c database to 19c by using Oracle upgrade utility tool – AutoUpgrade.

Environment :

Primary Database Host / Database : oemnode1 / OEMREP

Standby Database Host / Database:   stbnode1 / STBOEMREP

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Final Checks

DGMGRL> show configuration;

Configuration - dg_oemrep

  Protection Mode: MaxPerformance
  Members:
  oemrep    - Primary database
    stboemrep - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> show database stboemrep

Database - stboemrep

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 30.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STBOEMREP

Database Status:
SUCCESS
[oracle@oemnode1 dbs]$ $ORACLE_HOME/OPatch/opatch lspatches

30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)
30869156;Database Release Update : 19.7.0.0.200414 (30869156)
30805684;OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)

OPatch succeeded.
SQL> set pagesize 200
SQL> set linesize 300
SQL>  select CON_ID,
        TO_CHAR(action_time, 'YYYY-MM-DD') 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_TIM   PATCH_ID PATCH_TYPE ACTION   DESCRIPTION                                           SOURCE_VERSION  TARGET_VERSION
---------- ---------- ---------- ---------- -------- ----------------------------------------------------- --------------- ---------------
         1 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         1 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0
         3 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         3 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0

 SQL> select comp_name, status,version,VERSION_FULL 
        from dba_registry

COMP_NAME                         STATUS     VERSION   VERSION_FULL
--------------------------------- ---------  --------- ------------
Oracle Database Catalog Views     VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Packages and TypesVALID      19.0.0.0.0 19.7.0.0.0
Oracle Real Application Clusters  OPTION OFF 19.0.0.0.0 19.7.0.0.0
JServer JAVA Virtual Machine      VALID      19.0.0.0.0 19.7.0.0.0
Oracle XDK                        VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Java Packages     VALID      19.0.0.0.0 19.7.0.0.0
OLAP Analytic Workspace           VALID      19.0.0.0.0 19.7.0.0.0
Oracle XML Database               VALID      19.0.0.0.0 19.7.0.0.0
Oracle Workspace Manager          VALID      19.0.0.0.0 19.7.0.0.0
Oracle Text                       VALID      19.0.0.0.0 19.7.0.0.0
Oracle Multimedia                 VALID      19.0.0.0.0 19.7.0.0.0
Spatial                           VALID      19.0.0.0.0 19.7.0.0.0
Oracle OLAP API                   VALID      19.0.0.0.0 19.7.0.0.0
Oracle Label Security             VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Vault             VALID      19.0.0.0.0 19.7.0.0.0

15 rows selected.
SQL> show con_name;

CON_NAME
----------------
EMPDBREPOS

SQL> select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

SQL> connect / as sysdba
Connected.
SQL>  select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

Check TimeZone version has been upgraded to version 32 from version 31 in 18c.

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION     CON_ID
-------------------- ------- ----------
timezlrg_32.dat           32          0


SQL> SELECT tz_version FROM registry$database;

TZ_VERSION
----------
        32

SQL> SELECT property_name, property_value
     FROM   database_properties
     WHERE  property_name LIKE 'DST_%'
  ORDER BY property_name

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT DBMS_DST.get_latest_timezone_version  from dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

Drop GRP ( Guarantee Restore Point )

SQL> select name from v$restore_point;

NAME
--------------------------------------------
AUTOUPGRADE_221145114461854_OEMREP

SQL> drop restore point AUTOUPGRADE_221145114461854_OEMREP;

Restore point dropped.

Apply Oracle Database 18.5.0.0.0.190115 and OJVM 18.5.0.0.0.190115 Release Update( RU )

This exercise describes how you can install below three patches on 18c:

  • Patch 28822489 – Oracle Database Release Update(RU) 18.5.0.0.190115
  • Patch 28864593  – OCW Release Update 18.5.0.0.190115
  • Patch 28790647 – Oracle JavaVM Component Release Update (OJVM RU) 18.5.0.0.0.190115

Apply Patch 28822489 DB RU 18.5.0.0.190115

OPatch utility

Check and make sure opatch version 12.2.0.1.14 or later

[oracle@oemnode1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.19

OPatch succeeded.
[oracle@oemnode1 ~]$

Current Patches Applied onto ORACLE_HOME

[oracle@oemnode1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
27908644;UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171
27923415;OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
28090553;OCW RELEASE UPDATE 18.3.0.0.0 (28090553)
28090523;Database Release Update : 18.3.0.0.180717 (28090523)

OPatch succeeded.

Check interim patches conflicts

[oracle@oemnode1 ~]$ cd 28822489
[oracle@oemnode1 28822489]$ $ORACLE_HOME/OPatch/opatch  \
                    prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/Product/18.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/Product/18.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.19
OUI version       : 12.2.0.4.0
Log file location : /u01/app/oracle/Product/18.0.0/dbhome_1/
                cfgtoollogs/opatch/opatch2020-04-03_21-19-18PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@oemnode1 28822489]$

Patch Installation

[oracle@oemnode1 28822489]$  $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/Product/18.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/Product/18.0.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.19
OUI version : 12.2.0.4.0
Log file location : /u01/app/oracle/Product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-04-03_21-28-36PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28822489

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/Product/18.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28822489' to OH '/u01/app/oracle/Product/18.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.assistants.asm, 18.0.0.0.0 ] , [ oracle.net.cman, 18.0.0.0.0 ] , [ oracle.ons.daemon, 18.0.0.0.0 ] , [ oracle.tfa, 18.0.0.0.0 ] , [ oracle.crs, 18.0.0.0.0 ] , [ oracle.network.cman, 18.0.0.0.0 ] , [ oracle.assistants.usm, 18.0.0.0.0 ] , [ oracle.assistants.server.oui, 18.0.0.0.0 ] , [ oracle.has.crs, 18.0.0.0.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.oracore.rsf, 18.0.0.0.0...
Patching component oracle.rdbms, 18.0.0.0.0...
Patching component oracle.dbjava.jdbc, 18.0.0.0.0...
Patching component oracle.dbjava.ic, 18.0.0.0.0...
Patching component oracle.network.listener, 18.0.0.0.0...
Patching component oracle.assistants.acf, 18.0.0.0.0...
Patching component oracle.rdbms.rsf.ic, 18.0.0.0.0...
Patching component oracle.server, 18.0.0.0.0...
Patching component oracle.ctx, 18.0.0.0.0...
Patching component oracle.ons, 18.0.0.0.0...
Patching component oracle.rdbms.deconfig, 18.0.0.0.0...
Patching component oracle.rdbms.util, 18.0.0.0.0...
Patching component oracle.sdo.locator.jrf, 18.0.0.0.0...
Patching component oracle.xdk.parser.java, 18.0.0.0.0...
Patching component oracle.assistants.server, 18.0.0.0.0...
Patching component oracle.rdbms.crs, 18.0.0.0.0...
Patching component oracle.rdbms.rman, 18.0.0.0.0...
Patching component oracle.xdk, 18.0.0.0.0...
Patching component oracle.ctx.atg, 18.0.0.0.0...
Patching component oracle.dbjava.ucp, 18.0.0.0.0...
Patching component oracle.install.deinstalltool, 18.0.0.0.0...
Patching component oracle.rdbms.dbscripts, 18.0.0.0.0...
Patching component oracle.rdbms.rsf, 18.0.0.0.0...
Patching component oracle.xdk.rsf, 18.0.0.0.0...
Patching component oracle.network.client, 18.0.0.0.0...
Patching component oracle.rdbms.install.plugins, 18.0.0.0.0...
Patching component oracle.sdo, 18.0.0.0.0...
Patching component oracle.rdbms.oci, 18.0.0.0.0...
Patching component oracle.ctx.rsf, 18.0.0.0.0...
Patching component oracle.oraolap.dbscripts, 18.0.0.0.0...
Patching component oracle.assistants.deconfig, 18.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 18.0.0.0.0...
Patching component oracle.precomp.rsf, 18.0.0.0.0...
Patching component oracle.network.rsf, 18.0.0.0.0...
Patching component oracle.sqlplus.ic, 18.0.0.0.0...
Patching component oracle.sdo.locator, 18.0.0.0.0...
Patching component oracle.nlsrtl.rsf.core, 18.0.0.0.0...
Patching component oracle.sqlplus, 18.0.0.0.0...
Patching component oracle.javavm.client, 18.0.0.0.0...
Patching component oracle.ldap.owm, 18.0.0.0.0...
Patching component oracle.ldap.security.osdt, 18.0.0.0.0...
Patching component oracle.rdbms.install.common, 18.0.0.0.0...
Patching component oracle.precomp.lang, 18.0.0.0.0...
Patching component oracle.precomp.common, 18.0.0.0.0...
Patch 28822489 successfully applied.
Sub-set patch [28090523] has become inactive due to the application of a super-set patch [28822489].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/Product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-04-03_21-28-36PM_1.log

OPatch succeeded.

Apply Patch 28864593  – OCW Release Update 18.5.0.0.190115

Run prepatch.sh

[oracle@oemnode1 28864593]$ custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
custom/scripts/prepatch.sh completed successfully.
[oracle@oemnode1 28864593]$

Apply OCW patch 28864593

[oracle@oemnode1 28864593]$ $ORACLE_HOME/OPatch/opatch napply -local -oh $ORACLE_HOME -id 28864593
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/Product/18.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/Product/18.0.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.19
OUI version : 12.2.0.4.0
Log file location : /u01/app/oracle/Product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-04-04_13-11-10PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28864593

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/Product/18.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28864593' to OH '/u01/app/oracle/Product/18.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.crs, 18.0.0.0.0 ] , [ oracle.has.crs, 18.0.0.0.0 ] , [ oracle.has.cvu, 18.0.0.0.0 ] , [ oracle.has.deconfig, 18.0.0.0.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.server, 18.0.0.0.0...

Patching component oracle.rdbms, 18.0.0.0.0...

Patching component oracle.has.db, 18.0.0.0.0...

Patching component oracle.has.common, 18.0.0.0.0...

Patching component oracle.has.common.cvu, 18.0.0.0.0...

Patching component oracle.has.rsf, 18.0.0.0.0...
Patch 28864593 successfully applied.
Sub-set patch [28090553] has become inactive due to the application of a super-set patch [28864593].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/Product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-04-04_13-11-10PM_1.log

OPatch succeeded.

Run postpatch.sh

[oracle@oemnode1 28864593]$ custom/scripts/postpatch.sh -dbhome $ORACLE_HOME
Reading /u01/app/oracle/Product/18.0.0/dbhome_1/install/params.ora..
Reading /u01/app/oracle/Product/18.0.0/dbhome_1/install/params.ora..
Found uninstantiated /u01/app/oracle/Product/18.0.0/dbhome_1/crs/sbs/srvctl.sbs file, copying to /u01/app/oracle/Product/18.0.0/dbhome_1/bin/srvctl
Parsing file /u01/app/oracle/Product/18.0.0/dbhome_1/bin/srvctl
Parsing file /u01/app/oracle/Product/18.0.0/dbhome_1/bin/srvconfig
Parsing file /u01/app/oracle/Product/18.0.0/dbhome_1/bin/cluvfy
Verifying file /u01/app/oracle/Product/18.0.0/dbhome_1/bin/srvctl
Verifying file /u01/app/oracle/Product/18.0.0/dbhome_1/bin/srvconfig
Verifying file /u01/app/oracle/Product/18.0.0/dbhome_1/bin/cluvfy
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/srvctl
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/srvconfig
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/cluvfy
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/diskmon.bin
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/lsnodes
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/osdbagrp
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/rawutl
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/srvm/admin/ractrans
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/srvm/admin/getcrshome
Reapplying file permissions on /u01/app/oracle/Product/18.0.0/dbhome_1/bin/crsdiag.pl
Postpatch completed successfully
[oracle@oemnode1 28864593]$

Patch Post-Installation Instructions

[oracle@oemnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 3 21:46:04 2020
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3221221872 bytes
Fixed Size 8901104 bytes
Variable Size 805306368 bytes
Database Buffers 2399141888 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> quit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

[oracle@oemnode1 ~]$ cd $ORACLE_HOME/OPatch
[oracle@oemnode1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Fri Apr 3 21:46:48 2020
Copyright (c) 2012, 2018, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_11020_2020_04_03_21_46_48/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)

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 26-MAR-20 05.51.52.699750 PM
PDB EMPDBREPOS: Applied successfully on 26-MAR-20 05.51.54.759763 PM
PDB PDB$SEED: Applied successfully on 26-MAR-20 05.51.53.740088 PM

Current state of release update SQL patches:
Binary registry:
18.5.0.0.0 Release_Update 1812161059: Installed
PDB CDB$ROOT:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 26-MAR-20 05.51.52.694743 PM
PDB EMPDBREPOS:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 26-MAR-20 05.51.54.754836 PM
PDB PDB$SEED:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 26-MAR-20 05.51.53.734922 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED EMPDBREPOS
No interim patches need to be rolled back
Patch 28822489 (Database Release Update : 18.5.0.0.190115 (28822489)):
Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.5.0.0.0 Release_Update 1812161059
No interim patches need to be applied

Installing patches...
Patch installation complete. Total patches installed: 3

Validating logfiles...done
Patch 28822489 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28822489/22669539/28822489_apply_OEMREP_CDBROOT_2020Apr03_21_47_05.log (no errors)
Patch 28822489 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28822489/22669539/28822489_apply_OEMREP_PDBSEED_2020Apr03_21_47_41.log (no errors)
Patch 28822489 apply (pdb EMPDBREPOS): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28822489/22669539/28822489_apply_OEMREP_EMPDBREPOS_2020Apr03_21_47_41.log (no errors)
SQL Patching tool complete on Fri Apr 3 21:48:03 2020
[oracle@oemnode1 OPatch]$
[oracle@oemnode1 OPatch]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 3 21:55:31 2020
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-04-03 21:56:10

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2020-04-03 21:56:12

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>

Patch 28790647 – OJVM RU 18.5.0.0.0.190115

Check interim patches conflict.

[oracle@oemnode1 28980105]$ cd 28790647
[oracle@oemnode1 28790647]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/Product/18.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/Product/18.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.19
OUI version       : 12.2.0.4.0
Log file location : /u01/app/oracle/Product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-04-04_13-21-27PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@oemnode1 28790647]$

Apply OJVM patch

[oracle@oemnode1 28790647]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/Product/18.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/Product/18.0.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.19
OUI version : 12.2.0.4.0
Log file location : /u01/app/oracle/Product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-04-04_13-27-45PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28790647

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/Product/18.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28790647' to OH '/u01/app/oracle/Product/18.0.0/dbhome_1'

Patching component oracle.javavm.server, 18.0.0.0.0...

Patching component oracle.javavm.server.core, 18.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 18.0.0.0.0...

Patching component oracle.rdbms, 18.0.0.0.0...

Patching component oracle.javavm.client, 18.0.0.0.0...
Patch 28790647 successfully applied.
Sub-set patch [27923415] has become inactive due to the application of a super-set patch [28790647].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/Product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-04-04_13-27-45PM_1.log

OPatch succeeded.
[oracle@oemnode1 28790647]$

Post-installation of OJVM patch

[oracle@oemnode1 28790647]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Apr 4 13:30:47 2020
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 3221221872 bytes
Fixed Size 8901104 bytes
Variable Size 805306368 bytes
Database Buffers 2399141888 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
[oracle@oemnode1 28790647]$ cd $ORACLE_HOME/OPatch
[oracle@oemnode1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Sat Apr 4 13:32:08 2020
Copyright (c) 2012, 2018, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_8329_2020_04_04_13_32_08/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)

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: Not installed
PDB CDB$ROOT: Applied successfully on 26-MAR-20 05.51.52.699750 PM
PDB EMPDBREPOS: Applied successfully on 26-MAR-20 05.51.54.759763 PM
PDB PDB$SEED: Applied successfully on 26-MAR-20 05.51.53.740088 PM
Interim patch 28790647 (OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647)):
Binary registry: Installed
PDB CDB$ROOT: Not installed
PDB EMPDBREPOS: Not installed
PDB PDB$SEED: Not installed

Current state of release update SQL patches:
Binary registry:
18.5.0.0.0 Release_Update 1812161059: Installed
PDB CDB$ROOT:
Applied 18.5.0.0.0 Release_Update 1812161059 successfully on 03-APR-20 09.48.00.268791 PM
PDB EMPDBREPOS:
Applied 18.5.0.0.0 Release_Update 1812161059 successfully on 03-APR-20 09.48.01.866401 PM
PDB PDB$SEED:
Applied 18.5.0.0.0 Release_Update 1812161059 successfully on 03-APR-20 09.48.01.071569 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED EMPDBREPOS
The following interim patches will be rolled back:
27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415))
No release update patches need to be installed
The following interim patches will be applied:
28790647 (OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647))

Installing patches...
Patch installation complete. Total patches installed: 6

Validating logfiles...done
Patch 27923415 rollback (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27923415/22239273/27923415_rollback_OEMREP_CDBROOT_2020Apr04_13_32_21.log (no errors)
Patch 28790647 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28790647/22592236/28790647_apply_OEMREP_CDBROOT_2020Apr04_13_32_39.log (no errors)
Patch 27923415 rollback (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27923415/22239273/27923415_rollback_OEMREP_PDBSEED_2020Apr04_13_32_40.log (no errors)
Patch 28790647 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28790647/22592236/28790647_apply_OEMREP_PDBSEED_2020Apr04_13_32_45.log (no errors)
Patch 27923415 rollback (pdb EMPDBREPOS): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27923415/22239273/27923415_rollback_OEMREP_EMPDBREPOS_2020Apr04_13_32_40.log (no errors)
Patch 28790647 apply (pdb EMPDBREPOS): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28790647/22592236/28790647_apply_OEMREP_EMPDBREPOS_2020Apr04_13_32_45.log (no errors)
SQL Patching tool complete on Sat Apr 4 13:32:46 2020
[oracle@oemnode1 OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Apr 4 13:33:09 2020
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3221221872 bytes
Fixed Size 8901104 bytes
Variable Size 805306368 bytes
Database Buffers 2399141888 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-04-04 13:34:51

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2020-04-04 13:34:52

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>

Final checks

[oracle@oemnode1 OPatch]$ ./opatch lspatches
28790647;OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647)
28864593;OCW RELEASE UPDATE 18.5.0.0.0 (28864593)
28822489;Database Release Update : 18.5.0.0.190115 (28822489)
27908644;UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171

OPatch succeeded.
[oracle@oemnode1 OPatch]$
SQL>select COMP_NAME, VERSION, STATUS from dba_registry order by 1;

COMP_NAME                                VERSION        STATUS
---------------------------------------- ------------- --------------
JServer JAVA Virtual Machine             18.0.0.0.0    VALID
OLAP Analytic Workspace                  18.0.0.0.0    VALID
Oracle Database Catalog Views            18.0.0.0.0    VALID
Oracle Database Java Packages            18.0.0.0.0    VALID
Oracle Database Packages and Types       18.0.0.0.0    VALID
Oracle Database Vault                    18.0.0.0.0    VALID
Oracle Label Security                    18.0.0.0.0    VALID
Oracle Multimedia                        18.0.0.0.0    VALID
Oracle OLAP API                          18.0.0.0.0    VALID
Oracle Real Application Clusters         18.0.0.0.0    OPTION OFF
Oracle Text                              18.0.0.0.0    VALID
Oracle Workspace Manager                 18.0.0.0.0    VALID
Oracle XDK                               18.0.0.0.0    VALID
Oracle XML Database                      18.0.0.0.0    VALID
Spatial                                  18.0.0.0.0    VALID
15 rows selected.
SQL> select PATCH_ID, PATCH_TYPE, ACTION, STATUS, DESCRIPTION 
      from dba_registry_sqlpatch order by 1;

 PATCH_ID  PATCH_TYPE ACTION     STATUS    DESCRIPTION
---------- ---------- ---------- --------- ------------------------
  27923415 INTERIM    APPLY      SUCCESS   OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
  27923415 INTERIM    ROLLBACK   SUCCESS   OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
  28090523 RU         APPLY      SUCCESS   Database Release Update : 18.3.0.0.180717 (28090523)
  28790647 INTERIM    APPLY      SUCCESS   OJVM RELEASE UPDATE: 18.5.0.0.190115 (28790647)
  28822489 RU         APPLY      SUCCESS   Database Release Update : 18.5.0.0.190115 (28822489)

Lists all the inactive patches

Those patches have been superseded by other superset patch(es)

[oracle@oemnode1 OPatch]$ $ORACLE_HOME/OPatch/opatch lsinv -inactive

...
..
.
Inactive patches (3) :

Patch 27923415 : applied on Mon Oct 08 10:19:18 AEDT 2018
Unique Patch ID: 22239273
Patch description: "OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)"
Created on 15 Jul 2018, 10:33:22 hrs PST8PDT
Bugs fixed:
27304131, 27539876, 27952586, 27642235, 27636900, 27461740

Patch 28090553 : applied on Mon Oct 08 10:17:51 AEDT 2018
Unique Patch ID: 22256940
Patch description: "OCW RELEASE UPDATE 18.3.0.0.0 (28090553)"
Created on 12 Aug 2018, 23:01:26 hrs PST8PDT
...
..
.
Patch 28090523 : applied on Mon Oct 08 10:17:15 AEDT 2018
Unique Patch ID: 22329768
Patch description: "Database Release Update : 18.3.0.0.180717 (28090523)"
Created on 14 Jul 2018, 00:03:50 hrs PST8PDT
...
..
.
OPatch succeeded.

How to Apply Grid Infrastructure RU/RUR Patches Before Upgrading GI from 12.2.0.1 to 18c

While upgrading GI from 12.2.0.1 to 18c, in order to minimize the downtime and reduce the impact on PROD environment,  Release Update(RU) or Release Update Reversion(RUR) patches can be applied onto new 18c GI image before setting up 18c GI.

Please note this way is different from what we normally do to apply RU/RUR patches according to patch README.html instruction.

  • RU/RUR can only be applied before 18c GI is configured (in other word, before root.sh or rootupgrade.sh is executed).
  • In this upgrade case, RU/RUR patches can only be applied onto the new 18c GI home,  patches are not to be applied to the existing (current) 12.2.0.1 GI home.
  • “opatchauto” and “opatch apply” must NOT be used as the new 18c GI home hasn’t been configured yet.

Here is the example of applying both RU/RUR patches sequentially against new 18c GI before starting to upgrade GI 12.2.0.1 to 18c.

Those following two patches will be applied on 18c GI gold imagine:

  • Patch 28828717 – GI Release Update 18.5.0.0.190115
  • Patch 29231062 – GI Release Update Revision 18.5.1.0.190416

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe