Oracle 19c | Applying Patch

Much of the times I use latest Oracle technologies for learning & they hardly make to any of the PRODUCTION environments those I support at work. Recently I wanted to see how to patch Oracle 19c installation on Windows and to be frank, it was buttery smooth.

Let us quickly see how to apply a patch to a Windows installation of Oracle 19c database.

Shutdown all instances & Windows services for Oracle. Make sure you have taken adequate number of backups for the database(s).

Check you computer’s PATH variable. Insure you have %ORACLE_HOME%\perl\bin, in my case “D:\Oracle\19c\perl\bin” as the first entry for PERL.

This is how the PATH information before I make the above changes to it.

Here 11g 32Bit client is the first Oracle software in the PATH, we will modify it like below

Once the patching done, you can rollback the PATH variable according to your requirements.

Visit Oracle support and download the patch, the latest patch for Oracle Database 19c is “32409154”. Patch is only available to customers with a valid support contract.

Extract the archive.

I’ve extracted the archive within the Download folder and the path is

C:\Users\xxxxxxx\Downloads\Oracle19c_Patch\p32409154_190000_MSWIN-x86-64

Now open an elevated command prompt and switch to the folder with patch number as it’s name. Check the below image for details.

Now we are ready to apply the patch.

Call the opatch utility from here, like given example below.

Microsoft Windows [Version 10.0.19043.985]
(c) Microsoft Corporation. All rights reserved.

C:\Oracle19c_Patch\p32409154_190000_MSWIN-x86-64\32409154>d:\Oracle\19c\OPatch\opatch apply
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : d:\Oracle\19c
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : d:\Oracle\19c\cfgtoollogs\opatch\opatch2021-06-23_10-47-41AM_1.log

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

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 = 'd:\Oracle\19c')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32409154' to OH 'd:\Oracle\19c'
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.swd.oui, 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.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.usm, 19.0.0.0.0 ] , [ oracle.swd.oui.core.min, 19.0.0.0.0 ] , [ oracle.tomcat.crs, 19.0.0.0.0 ] , [ oracle.options.olap, 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.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 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.sdo.locator.jrf, 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.xdk.rsf, 19.0.0.0.0...

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

Patching component oracle.dbjava.jdbc, 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.rdbms.lbac, 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.has.db, 19.0.0.0.0...

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

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

Patching component oracle.xdk.xquery, 19.0.0.0.0...

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

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.dbdev, 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.scheduler, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 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.mgw.common, 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.odbc.ic, 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 32409154 successfully applied.
Log file location: d:\Oracle\19c\cfgtoollogs\opatch\opatch2021-06-23_10-47-41AM_1.log

OPatch succeeded.

As usual, based on your computer’s hardware capabilities, the patch would finish sooner or later. There will be 2 prompts those you need to say Yes in order to progress the patching.

Simple & neat right? Share your experience in the comments area.

Windows | Upgrade Oracle Database 12c to 19c

Our business application is hosted on Linux. I love the way the OS is fine tuned by Oracle for the database & without arguments, I must say, Oracle is perfect on Linux.

Does that stop me from installing the latest Oracle database & experimenting them on Windows? Nay, from the known days I’ve used Windows OS for PC & I am sure, will be retiring using one. I’ve made many attempts to install and configure many unsupported Oracle products on Windows & have a VERY good success record ;)

Yet, I never ever upgraded Oracle databases on Windows to next version as I never had a database that needed to be backed up or restored when a new version was tried out. This time I decided to change that habit and to upgrade my existing 12c CDB database to 19c following some documents specifically written for Linux!

Well, regardless whether you are a Linux or Windows person, trust me, the instructions provided for either platform is 99% applicable on both. So, translating the instructions on Linux for Windows are NOT truly difficult, in case if you are IN for some geeky stuffs.

So let us quickly assess my environment. I had 12c CDB with one PDB, which I don’t remember whether I created during the software installation or later. As usual, the following instructions are for a lab setup. That means, you must make sure that you make proper backups prior moving them to a Production environment. My TEST database didn’t have any data, just the sample schemas.

Install Oracle Database 19c. Choose “Software Only” option. That means do not create a database along with the installation. You may refer my earlier post for Oracle 19c installation here.

Now, let us see how to upgrade our 12c database to 19c. There are many prerequisites for a successful upgrade. Oracle provides many tools to insure a successful upgrade.

Start your 12c database and open an elevated command prompt.

From your 12c database home aka %ORACLE_HOME%\bin folder execute prerequisites collection program. Example

D:\Oracle\product\12.2.0\dbhome_1\bin>java -jar D:\Oracle\product\19.3.0\rdbms\admin\preupgrade.jar TEXT TERMINAL

TEXT TERMINAL will force the program to output all the instructions to the current console. You should copy the output to a text file & go through the findings. Against the pre-upgrade instructions, you must satisfy each one of them & re-run the prerequisites collection once again. If you are upgrading a Container database with pluggable databases, the post-upgrade activities will be listed per database.

A Sample output as below:

D:\Oracle\product\12.2.0\dbhome_1\bin>java -jar D:\Oracle\product\19.3.0\rdbms\admin\preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:45:58

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB12C
     Container Name:  CDB$ROOT
       Container ID:  1
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Microsoft Windows x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             470 MB       500 MB
      SYSTEM                             800 MB       915 MB
      TEMP                                33 MB       150 MB

      Minimum tablespace sizes for upgrade are estimates.

  4.  No action needed.

      Using default parallel upgrade options, this CDB with 2 PDBs will first
      upgrade the CDB$ROOT, and then upgrade at most 2 PDBs at a time using 2
      parallel processes per PDB.

      The number of PDBs upgraded in parallel and the number of parallel
      processes per PDB can be adjusted as described in Database Upgrade Guide.

  5.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container CDB$ROOT
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  7.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  8.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  9.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container CDB$ROOT
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql


Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:46:22

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB12C
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Microsoft Windows x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  TRUE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             330 MB       500 MB
      SYSTEM                             250 MB       358 MB
      TEMP                                64 MB       150 MB
      UNDOTBS1                           100 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container PDB$SEED
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  6.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  7.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container PDB$SEED
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql


Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:46:22

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB12C
     Container Name:  SCT
       Container ID:  3
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Microsoft Windows x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             350 MB       500 MB
      SYSTEM                             250 MB       358 MB
      TEMP                                64 MB       150 MB
      UNDOTBS1                           100 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container SCT
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  6.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  7.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB12C container SCT
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade.log
  D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade_fixups.sql
  D:\Oracle\cfgtoollogs\cdb12c\preupgrade\postupgrade_fixups.sql

Execute fixup scripts across the entire CDB:

Before upgrade:

1. Execute preupgrade fixups with the below command
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\rdbms\admin %ORACLE_HOME%\rdbms\admin\catcon.pl -l D:\Oracle\cfgtoollogs\cdb12c\preupgrade\ -b preup_cdb12c D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade_fixups.sql

2. Review logs under D:\Oracle\cfgtoollogs\cdb12c\preupgrade\

After the upgrade:

1. Execute postupgrade fixups with the below command
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\rdbms\admin %ORACLE_HOME%\rdbms\admin\catcon.pl -l D:\Oracle\cfgtoollogs\cdb12c\preupgrade\ -b postup_cdb12c D:\Oracle\cfgtoollogs\cdb12c\preupgrade\postupgrade_fixups.sql

2. Review logs under D:\Oracle\cfgtoollogs\cdb12c\preupgrade\

Preupgrade complete: 2020-11-20T17:46:32

D:\Oracle\product\12.2.0\dbhome_1\bin>

Once all the prerequisites are met, we can use the GUI tool “Database Upgrade Assistant” to upgrade our existing database(s). For this exercise, I will be upgrading my 12c Container database with one PDB

Select the 12c database that you want to upgrade. Please note, my database hardly had any data & was not on archive log mode. If you are planning to upgrade a production database that has archive log enabled, take proper backups prior proceeding.

I’ve an exclusive user account “oracle” for all Oracle installations. If you have used another user account, provide it at this step.

As my database doesn’t have archive logging, my best choice is to create an Offline RMAN backup. You may choose another option from the available.

My 12c database had a LISTENER that is listening to 1521 & by mistake I have selected a PORT 5500. Please adjust your PORT number.

Above I said how I wrongly selected a PORT number for LISTENER, I’ve selected the same PORT number for the Enterprise Manager also. Well, later I corrected the mistake. So be careful while you select PORT numbers.

Sit back, watch a movie or go for a walk. The following activities are going to take considerable time. Bigger your database, more time needed to complete the upgrade. I’ve pretty a beast as desktop and whole upgrade took almost couple of hours. Based on your available resources, the upgrade might take longer times.

Okay, I missed taking the last Screenshot. Once the upgrade finished, you can start with post-upgrade activities as they are listed with the prerequisites output.

That’s all folks. Have comments? Please send them to me.

Install Oracle Database 19c on Windows 10

This time let us see how to install Oracle database 19c on Windows 10. This installation is not much different from Oracle 18c installation.

You can download the 19c Installation media for Windows from the below link.

Database Software Downloads | Oracle

Once downloaded, you have to extract the package to a folder. Please note, the extracted folder or the folder from which you will execute “Setup.exe” is going to be 19c Oracle Home. So, name the folder wisely. For example, I have extracted the files to a folder D:\Oracle\19c

You can start the installation by right clicking the Setup.exe and running as “Administrator”.

This will kick start the installation.

We will not create a database along with the software installation. Hence select “Set Up Software Only”

I suggest you to create a normal user (no administrator privileges) “oracle”, that will be used for all your future Oracle software installations. If you don’t have an account explicitly created yet for Oracle, the installation gives you an interface to create one. It’s your choice.

Please remember, earlier I said, the folder where you have extracted the installation media is going to be Oracle 19c home, aka Oracle Home. Rename the folder with an appropriate name before going ahead with the installation.

You will be asked to provide a location for Oracle Base. To content within the Oracle Home, you can specify a path like “D:\Oracle\19c\database” or “D:\Oracle\19c\db” or a folder name of your choice.

After few basic checks, the installation will proceed now. I strongly suggest you to assign a STATIC IP for your computer, prior continuing.

That’s it. No hacks, no modifications to config files or anything such to get the software installed on Windows!.

You can use “Database Configuration Assistant” for creating new database. We will see how to create a new database now. This time we will be creating a CDB (Container Database) and one PDB (Pluggable Database). You may go ahead with creating a single instance database when asked to choose between.

Select “Advanced Configuration”

You can choose between a normal database or Container database with PDB at next step. If you don’t wish to create the database as a container, uncheck the option “Create as Container database”. If you chose to continue with creating your database as container, I suggest you to leave the defaults as it is, other than the PDB name. If you need more PDBs, may add later to the container.

Leave the defaults, unless you know what you are doing.

If you are setting up the database for the LAB/Learning purposes, you may ignore these. However, for production instances, you must configure the Recovery area & setup archiving.

You can create a LISTENER for your database now. If you other Oracle database LISTENERs already running, choose a port number that wouldn’t raise a conflict.

Optionally you can setup Database Vault & Label Security at this step ;)

Okay, we need your attention here. Oracle automatically allocates 40% of total available physical memory for the newly created database. As I mentioned in the previous step, if you have more than one database running from the same computer, you must adjust the available memory to avoid choking the resources.

You may leave the defaults untouched here & you can change this value as sys admin when needed.

WebLogic Repos insist upon “AL32UTF8” Character Set & unless you have specific reasons to change the Character set, leave the defaults. If you are planning to migrate a legacy application that uses a different character set like Arabic (AR8MSWIN1256), please read this post

As per the post, you can use a different character set for a PDB starting from Oracle database versions 12.2.

We do love Scott bit too much, if yes, go with sample schemas (Trust me, you need it)

Enterprise Manager configuration is totally optional. If you choose to, will install an Express edition of Oracle Enterprise Manager (means more resources)

As usual, for a LAB environment I will go with same password for all accounts.

I would suggest you to accept the defaults at next step & change the passwords and unlock accounts as and when needed.

That should complete the installation and creation of database for 19c. Have comments? Please post them to me.