Search

Top 60 Oracle Blogs

Recent comments

From 19.6 to 19.7 on Windows

I must say this Release Update (RU) was probably the smoothest I’ve ever done. Obviously you should always read the patch notes carefully before proceeding on your own systems, but for me, it was a simple exercise. I’m posting this just to cover a couple of things that the patch notes “assume” and don’t explicitly state.

  • Shutdown everything Oracle related. I just go to “Services” and look for anything with Oracle. Also shutdown the “Distributed Transaction Coordinator service”.

This next one is key … I’ve made this mistake so many times. Open a command prompt window as administrator. If you don’t, things will progress OK for a tiny bit and then OPatch is going to throw a wobbly.

I did both the 19.7 RU and the 19.7 OJVM with OPatch, and both went through without incident.


C:\WINDOWS\system32>set PATH=%ORACLE_HOME%\perl\bin;%PATH%
C:\WINDOWS\system32>set PATH=%PATH%;%ORACLE_HOME%\OPatch
C:\WINDOWS\system32>cd D:\oracle\stage\19.7windows\30901317
C:\WINDOWS\system32>d:

D:\oracle\stage\19.7windows\30901317>opatch apply
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\19
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.19
OUI version       : 12.2.0.7.0
Log file location : C:\oracle\product\19\cfgtoollogs\opatch\opatch2020-06-05_18-52-57PM_1.log

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

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 = 'C:\oracle\product\19')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '30901317' to OH 'C:\oracle\product\19'
ApplySession: Optional component(s) 
  [ oracle.assistants.usm, 19.0.0.0.0 ] , 
  [ oracle.rdbms.ic, 19.0.0.0.0 ] , 
  [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , 
  [ oracle.has.deconfig, 19.0.0.0.0 ] , 
  [ oracle.has.cfs, 19.0.0.0.0 ] , 
  [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , 
  [ oracle.network.gsm, 19.0.0.0.0 ] , 
  [ oracle.network.cman, 19.0.0.0.0 ] ,
  [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , 
  [ oracle.ons.daemon, 19.0.0.0.0 ] , 
  [ oracle.options.olap.awm, 19.0.0.0.0 ] , 
  [ oracle.swd.oui, 12.2.0.4.0 ] , 
  [ oracle.swd.oui.core.min, 12.2.0.4.0 ] , 
  [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] ,
  [ oracle.assistants.asm, 19.0.0.0.0 ] , 
  [ oracle.usm, 19.0.0.0.0 ] , 
  [ oracle.tomcat.crs, 19.0.0.0.0 ] , 
  [ oracle.has.crs, 19.0.0.0.0 ] ,
  [ oracle.tfa, 19.0.0.0.0 ] , 
  [ oracle.has.cvu, 19.0.0.0.0 ] , 
  [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.aspnet_2, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.ntoramts, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.tfa.db, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.ntoledb.odp_net_2, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.usm.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.ntoledb, 19.0.0.0.0...

Patching component oracle.has.common, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.rdbms.plsql, 19.0.0.0.0...

Patching component oracle.has.db, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.rdbms.olap, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.clrintg.ode_net_2, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.has.rsf, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.has.common.cvu, 19.0.0.0.0...
Patch 30901317 successfully applied.
Sub-set patch [30445947] has become inactive due to the application of a super-set patch [30901317].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: C:\oracle\product\19\cfgtoollogs\opatch\opatch2020-06-05_18-52-57PM_1.log

OPatch succeeded.

D:\oracle\stage\19.7windows\30901317>cd ..

D:\oracle\stage\19.7windows>cd jvm

D:\oracle\stage\19.7windows\jvm>cd 30805684

D:\oracle\stage\19.7windows\jvm\30805684>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       : C:\oracle\product\19
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.19
OUI version       : 12.2.0.7.0
Log file location : C:\oracle\product\19\cfgtoollogs\opatch\opatch2020-06-05_18-59-04PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

D:\oracle\stage\19.7windows\jvm\30805684>
D:\oracle\stage\19.7windows\jvm\30805684>opatch apply
Oracle Interim Patch Installer version 12.2.0.1.19
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\19
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.19
OUI version       : 12.2.0.7.0
Log file location : C:\oracle\product\19\cfgtoollogs\opatch\opatch2020-06-05_19-12-41PM_1.log

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

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 = 'C:\oracle\product\19')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '30805684' to OH 'C:\oracle\product\19'

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...
Patch 30805684 successfully applied.
Log file location: C:\oracle\product\19\cfgtoollogs\opatch\opatch2020-06-05_19-12-41PM_1.log

OPatch succeeded.

D:\oracle\stage\19.7windows\jvm\30805684>

Then its a case of starting the databases in upgrade mode and then running datapatch. This is my only criticism of the Windows setup here. If I just start the services, the database is completely open, and hence needs to be shutdown entirely again so that it can be started in upgrade mode. I tried the old “oradim” command which is meant to allow me to start just the service and not the instance, ie

oradim -startup -sid db19 -starttype srvc

but I found that this also started the entire instance and database. So you might just have to tough it out and accept that its going to need a second shutdown/startup.


SQL> startup upgrade
ORACLE instance started.

Total System Global Area 4294966040 bytes
Fixed Size                  9276184 bytes
Variable Size            2113929216 bytes
Database Buffers         2164260864 bytes
Redo Buffers                7499776 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

C:\oracle\product\19\OPatch>datapatch -verbose
SQL Patching tool version 19.3.0.0.0 Production on Fri Jun  5 19:25:22 2020
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: C:\oracle\cfgtoollogs\sqlpatch\sqlpatch_44888_2020_06_05_19_25_22\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 30805684 (OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)):
  Binary registry: Installed
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed
  PDB PDB1: Not installed
  PDB PDB2: Not installed

Current state of release update SQL patches:
  Binary registry:
    19.7.0.0.0 Release_Update 200514113449: Installed
  PDB CDB$ROOT:
    Applied 19.6.0.0.0 Release_Update 200104221455 successfully on 20-MAR-20 07.53.55.650000 PM
  PDB PDB$SEED:
    Applied 19.6.0.0.0 Release_Update 200104221455 successfully on 20-MAR-20 07.53.57.153000 PM
  PDB PDB1:
    Applied 19.6.0.0.0 Release_Update 200104221455 successfully on 20-MAR-20 07.53.57.351000 PM
  PDB PDB2:
    Applied 19.6.0.0.0 Release_Update 200104221455 successfully on 20-MAR-20 07.53.57.153000 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB2
    No interim patches need to be rolled back
    Patch 30901317 (Windows Database Bundle Patch : 19.7.0.0.200414 (30901317)):
      Apply from 19.6.0.0.0 Release_Update 200104221455 to 19.7.0.0.0 Release_Update 200514113449
    The following interim patches will be applied:
      30805684 (OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684))

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

Validating logfiles...done
Patch 30901317 apply (pdb CDB$ROOT): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30901317\23471163/30901317_apply_DB19_CDBROOT_2020Jun05_19_27_50.log (no errors)
Patch 30805684 apply (pdb CDB$ROOT): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30805684\23504850/30805684_apply_DB19_CDBROOT_2020Jun05_19_27_15.log (no errors)
Patch 30901317 apply (pdb PDB$SEED): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30901317\23471163/30901317_apply_DB19_PDBSEED_2020Jun05_19_28_42.log (no errors)
Patch 30805684 apply (pdb PDB$SEED): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30805684\23504850/30805684_apply_DB19_PDBSEED_2020Jun05_19_28_34.log (no errors)
Patch 30901317 apply (pdb PDB1): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30901317\23471163/30901317_apply_DB19_PDB1_2020Jun05_19_28_41.log (no errors)
Patch 30805684 apply (pdb PDB1): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30805684\23504850/30805684_apply_DB19_PDB1_2020Jun05_19_28_34.log (no errors)
Patch 30901317 apply (pdb PDB2): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30901317\23471163/30901317_apply_DB19_PDB2_2020Jun05_19_28_41.log (no errors)
Patch 30805684 apply (pdb PDB2): SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\30805684\23504850/30805684_apply_DB19_PDB2_2020Jun05_19_28_34.log (no errors)

Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: CDB$ROOT

SQL Patching tool complete on Fri Jun  5 19:30:10 2020
C:\oracle\product\19\OPatch>


C:\oracle\product\19\OPatch>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 5 19:33:16 2020
Version 19.7.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0


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

Total System Global Area 4294966040 bytes
Fixed Size                  9276184 bytes
Variable Size            2113929216 bytes
Database Buffers         2164260864 bytes
Redo Buffers                7499776 bytes
Database mounted.
Database opened.
SQL>

One thing to be aware of once your patching is completed (and your experiences may be different) is that because the act of patching resulted in a growth of my SYSTEM, UNDO and SYSAUX tablespaces, the database engine seemed to think that it was in the middle of a large space growth activity. As a result, my space management slaves went absolutely bonkers for 2-3mins once I re-opened the database.  So I’d recommend you patch in a quiet time just in case you are also impacted. (It is mostly likely because I run my databases very lean on space because I have so many of them … I suspect a well managed database is unlikely to encounter this Smile)


SQL> @active_sess

       SID    SERIAL# USERNAME_SECONDS_ACTIVE                       STATUS   SQL_ID        PROGRAM
---------- ---------- --------------------------------------------- -------- ------------- -----------------
         5       2450 SYS (260)                                     ACTIVE                 ORACLE.EXE (W008)
         7      23944 SYS (236)                                     ACTIVE                 ORACLE.EXE (W00G)
       127      61619 SYS (257)                                     ACTIVE                 ORACLE.EXE (W009)
       130      38485 SYS (233)                                     ACTIVE                 ORACLE.EXE (W00H)
       254      33664 SYS (230)                                     ACTIVE                 ORACLE.EXE (W00I)
       369      43820 SYS (0)                                       ACTIVE   gg5tyt1pvnr47 sqlplus.exe
       372      21722 SYS (254)                                     ACTIVE                 ORACLE.EXE (W00A)
       374      46323 SYS (227)                                     ACTIVE                 ORACLE.EXE (W00J)
       495       4987 SYS (224)                                     ACTIVE                 ORACLE.EXE (W00K)
       616       1532 SYS (251)                                     ACTIVE                 ORACLE.EXE (W00B)
       619      63318 SYS (221)                                     ACTIVE                 ORACLE.EXE (W00L)
       739      43427 SYS (218)                                     ACTIVE                 ORACLE.EXE (W00M)
       860      11487 SYS (215)                                     ACTIVE                 ORACLE.EXE (W00N)
       982       7708 SYS (248)                                     ACTIVE                 ORACLE.EXE (W00C)
      1106      52591 SYS (245)                                     ACTIVE                 ORACLE.EXE (W00D)
      1227      15812 SYS (242)                                     ACTIVE                 ORACLE.EXE (W00E)
      1347      39327 SYS (263)                                     ACTIVE                 ORACLE.EXE (W007)
      1349      15601 SYS (239)                                     ACTIVE                 ORACLE.EXE (W00F)
      
      

As part of this exercise I found an awesome MOS note, which covers exactly what is fixed in each RU. Be sure to visit MOS note 2523220.1 for all the details. I am not permitted to cut/paste it here, but the image below gives you a flavour of the contents…You get each major section of the database, the bugs fixed in each RU, plus hyperlinks to the bug description. Very cool as a quick reference for seeing if a bug you are being impacted by is fixed in a particular RU.

image

Happy patching everyone!

Addenda June 17 2019:

There’s some internal discussion about whether the “upgrade” option is required for the “alter pluggable all open” command before the data patching. Some good information on that here https://mikedietrichde.com/2020/01/23/do-you-need-startup-upgrade-for-ojvm/.

Thanks to Daniel Overby Hansen for bringing this to my attention.