Search
  • Sudipta Bhaskar

How to upgrade Oracle RAC Database from 12.2.0.1 to 19.8

This post is about upgrading multitenant RAC Database from 12c to 19c.


I will attempt this in silent mode.


Following are the details of my current RAC.


Following are the main steps of upgrade.


  1. Install Oracle 19.3 db home binary.

  2. Executing preupgrade jar

  3. Performing the pre upgrade actions

  4. Backing up the database / Create a guaranteed restore point

  5. Upgrade the database

  6. Perform the post upgrade actions


Let's first create the folder structure /dboracle/app/oracle/product/19.0/dbhome_1 in both the servers.

mkdir -p /dboracle/app/oracle/product/19.0/dbhome_1
chown -R oracle:oinstall /dboracle/app/oracle/product/19.0/dbhome_1

I have downloaded the 19.3 db home binaries (LINUX.X64_193000_db_home.zip).

I have coped it to /dboracle/dbsoftware in first node and unzipped it.


Let's install the 19.3 db home binaries. I want to do this in silent mode with the help of a response file.


Apply the 19.8.0.0.200714 RU (Patch No 31305339)

[oracle@OEL7N1 dbsoftware]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -applyRU /dboracle/software/31305339
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.
Preparing the home to patch...
Applying the patch /dboracle/software/31305339...
OPatch command failed while applying the patch. For details look at the logs from /dboracle/dbsoftware/cfgtoollogs/opatchauto/.

It failed because opatch version is not meeting the requirement.


Copied latest Opatch and applied patch again.

[oracle@OEL7N1 dbsoftware]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -applyRU /dboracle/software/31305339
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.
Preparing the home to patch...
Applying the patch /dboracle/software/31305339...
Successfully applied the patch.
The log can be found at: /u01/app/oraInventory/logs/InstallActions2021-05-06_03-38-51PM/installerPatchActions_2021-05-06_03-38-51PM.log

No X11 DISPLAY variable was set, but this program performed an operation which requires it.
[oracle@OEL7N1 dbsoftware]$

Prerequisites :

[oracle@OEL7N1 dbsoftware]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54331(asmdba),54332(asmadmin)
[oracle@OEL7N1 dbsoftware]$ pwd
/dboracle/dbsoftware
[oracle@OEL7N1 dbsoftware]$ ./runInstaller -silent -executePrereqs -responseFile /home/oracle/db.rsp
Launching Oracle Database Setup Wizard...

Prerequisite checks executed successfully.

Installation :

[oracle@OEL7N1 dbsoftware]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -silent -responseFile /home/oracle/db.rsp
Launching Oracle Database Setup Wizard...

[FATAL] [INS-35954] The installer has detected that the Oracle home location provided in the response file is not correct.
   CAUSE: The Database Oracle home is the location from where the installer is executed. It has been detected that the value set in the response file is different (/dboracle/app/oracle/product/19.0/dbhome_1). Value expected is: /dboracle/dbsoftware
   ACTION: It is not required to specify ORACLE_HOME in the response file for Database installation. Alternatively, set it to the location of the installer (/dboracle/dbsoftware).

Reason : We need to copy all the binaries to the oracle home location and then run runInstaller


[oracle@OEL7N1 dbhome_1]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -silent -responseFile /home/oracle/db.rsp
Launching Oracle Database Setup Wizard...

[WARNING] [INS-13013] Target environment does not meet some mandatory requirements.
   CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/InstallActions2021-05-06_04-30-13PM/installActions2021-05-06_04-30-13PM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/InstallActions2021-05-06_04-30-13PM/installActions2021-05-06_04-30-13PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
 /dboracle/app/oracle/product/19.0/dbhome_1/install/response/db_2021-05-06_04-30-13PM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/InstallActions2021-05-06_04-30-13PM/installActions2021-05-06_04-30-13PM.log

As a root user, execute the following script(s):
        1. /dboracle/app/oracle/product/19.0/dbhome_1/root.sh

Execute /dboracle/app/oracle/product/19.0/dbhome_1/root.sh on the following nodes:
[OEL7N1, OEL7N2]


Successfully Setup Software with warning(s).

Run the mentioned script in both the nodes. I am showing example of running in node1.

[root@OEL7N1 ~]# /dboracle/app/oracle/product/19.0/dbhome_1/root.sh
Check /dboracle/app/oracle/product/19.0/dbhome_1/install/root_OEL7N1.localdomain_2021-05-06_16-56-05-046986658.log for the output of root script
[root@OEL7N1 ~]# cat /dboracle/app/oracle/product/19.0/dbhome_1/install/root_OEL7N1.localdomain_2021-05-06_16-56-05-046986658.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /dboracle/app/oracle/product/19.0/dbhome_1
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

Let's go to the next part of Executing preupgrade jar.


preupgrade.jar file is located at $ORACLE_HOME/rdbms/admin

[oracle@OEL7N1 admin]$ pwd
/dboracle/app/oracle/product/19.0/dbhome_1/rdbms/admin
[oracle@OEL7N1 admin]$ ls -ltr preupgrade.jar
-rw-r--r-- 1 oracle oinstall 726130 May  6 16:28 preupgrade.jar

Let's run the preupgrade from 12c home.

[oracle@OEL7N1 admin]$ /dboracle/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /dboracle/app/oracle/product/19.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /dboracle/upgrade
==================
PREUPGRADE SUMMARY
==================
  /dboracle/upgrade/preupgrade.log
  /dboracle/upgrade/preupgrade_fixups.sql
  /dboracle/upgrade/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 /dboracle/upgrade/ -b preup_orcl /dboracle/upgrade/preupgrade_fixups.sql

2. Review logs under /dboracle/upgrade/

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 /dboracle/upgrade/ -b postup_orcl /dboracle/upgrade/postupgrade_fixups.sql

2. Review logs under /dboracle/upgrade/

Preupgrade complete: 2021-05-06T17:36:58

Let's check the logs and take action.

[root@OEL7N1 upgrade]# cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-05-06T17:33:24

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  ORCL
     Container Name:  CDB$ROOT
       Container ID:  1
            Version:  12.2.0.1.0
     DB Patch Level:  DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.210420
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux 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]  VALID
  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.  Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with

        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;

      4 objects are INVALID.

      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

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

  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                             890 MB       892 MB
      SYSTEM                             830 MB       942 MB
      UNDOTBS1                            75 MB       439 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 1 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 ORCL container CDB$ROOT
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/dboracle/upgrade/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.  Recreate directory objects to remove any symbolic links from directory
      paths.  To identify paths that contain symbolic links before upgrading,
      use OS commands like UNIX file or WINDOWS dir.  After upgrading, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
      with symbolic links in the path.

      Found 2 user directory objects to be checked: DBMS_OPTIM_ADMINDIR,
      DBMS_OPTIM_LOGDIR.

      Starting in Release 18c, symbolic links are not allowed in directory
      object paths 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 ORCL container CDB$ROOT
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/dboracle/upgrade/postupgrade_fixups.sql


Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-05-06T17:34:55

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  ORCL
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.2.0.1.0
     DB Patch Level:  DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.210420
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux 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]  VALID
  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                             260 MB       371 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 ORCL container PDB$SEED
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/dboracle/upgrade/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.  Recreate directory objects to remove any symbolic links from directory
      paths.  To identify paths that contain symbolic links before upgrading,
      use OS commands like UNIX file or WINDOWS dir.  After upgrading, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
      with symbolic links in the path.

      Found 2 user directory objects to be checked: DBMS_OPTIM_ADMINDIR,
      DBMS_OPTIM_LOGDIR.

      Starting in Release 18c, symbolic links are not allowed in directory
      object paths 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 ORCL container PDB$SEED
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/dboracle/upgrade/postupgrade_fixups.sql


Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-05-06T17:36:29

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  ORCL
     Container Name:  PDBORCL
       Container ID:  3
            Version:  12.2.0.1.0
     DB Patch Level:  DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.210420
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux 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]  VALID
  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.

  INFORMATION ONLY
  ================
  2.  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                             370 MB       500 MB
      SYSTEM                             270 MB       380 MB
      TEMP                               132 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 ORCL container PDBORCL
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/dboracle/upgrade/preupgrade_fixups.sql

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

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

  RECOMMENDED ACTIONS
  ===================
  3.  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.

  4.  Recreate directory objects to remove any symbolic links from directory
      paths.  To identify paths that contain symbolic links before upgrading,
      use OS commands like UNIX file or WINDOWS dir.  After upgrading, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
      with symbolic links in the path.

      Found 2 user directory objects to be checked: DBMS_OPTIM_ADMINDIR,
      DBMS_OPTIM_LOGDIR.

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

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

  6.  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 ORCL container PDBORCL
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/dboracle/upgrade/postupgrade_fixups.sql

I have run utlrp to compile the invalid objects and the following script.

[oracle@OEL7N1 admin]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /dboracle/upgrade/ -b preup_orcl /dboracle/upgrade/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/dboracle/upgrade//preup_orcl_catcon_20480.lst]
catcon: See [/dboracle/upgrade//preup_orcl*.log] files for output generated by scripts
catcon: See [/dboracle/upgrade//preup_orcl_*.lst] files for spool files, if any

catcon.pl: completed successfully

Let's go the next step.of backing up the database and creating a guranteed restore point.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Next log sequence to archive   21
Current log sequence           21

SQL> create restore point before_upgrade_19c guarantee flashback database;

Restore point created.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                   GUA TIME
---------------------- --- -----------------------------------
BEFORE_UPGRADE_19C     YES 07-MAY-21 06.44.00.000000000 AM

Some more checks before upgrade


Check the timezone version.


SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        26

Disable scheduler jobs.

set pagesize 2000
set lines 2000
set long 99999
select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

OWNER                JOB_NAME                       ENABL STATE
-------------------- ------------------------------ ----- ---------------
SYS                  PURGE_LOG                      TRUE  SCHEDULED
SYS                  FILE_WATCHER                   FALSE DISABLED
SYS                  PMO_DEFERRED_GIDX_MAINT_JOB    TRUE  SCHEDULED
SYS                  CLEANUP_NON_EXIST_OBJ          TRUE  SCHEDULED
SYS                  CLEANUP_ONLINE_IND_BUILD       TRUE  SCHEDULED
SYS                  CLEANUP_TAB_IOT_PMO            TRUE  SCHEDULED
SYS                  FILE_SIZE_UPD                  TRUE  SCHEDULED
SYS                  ORA$AUTOTASK_CLEAN             TRUE  SCHEDULED
SYS                  HM_CREATE_OFFLINE_DICTIONARY   FALSE DISABLED
SYS                  DRA_REEVALUATE_OPEN_FAILURES   TRUE  SCHEDULED
SYS                  ORA$PREPLUGIN_BACKUP_JOB       FALSE DISABLED
SYS                  BSLN_MAINTAIN_STATS_JOB        TRUE  SCHEDULED
SYS                  FGR$AUTOPURGE_JOB              FALSE DISABLED
SYS                  RSE$CLEAN_RECOVERABLE_SCRIPT   TRUE  SCHEDULED
SYS                  SM$CLEAN_AUTO_SPLIT_MERGE      TRUE  SCHEDULED
SYS                  LOAD_OPATCH_INVENTORY          FALSE DISABLED
SYS                  XMLDB_NFS_CLEANUP_JOB          FALSE DISABLED
SYS                  CLEANUP_TRANSIENT_TYPE         TRUE  SCHEDULED
SYS                  CLEANUP_TRANSIENT_PKG          TRUE  SCHEDULED
SYS                  CLEANUP_ONLINE_PMO             TRUE  SCHEDULED
SYS                  LOAD_OPATCH_INVENTORY_2        FALSE DISABLED
SYS                  LOAD_OPATCH_INVENTORY_1        FALSE DISABLED
ORACLE_OCM           MGMT_CONFIG_JOB                TRUE  SCHEDULED
ORACLE_OCM           MGMT_STATS_CONFIG_JOB          TRUE  SCHEDULED

24 rows selected.


Verify system and sys default tablespace.(Both should be system tablespace)

SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');  2    3

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM
SYSTEM                         SYSTEM

Check whether there are any externally authenticated SSL users

SQL> SELECT name FROM sys.user$
  2  WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';  3

no rows selected

Review and Remove any unnecessary hidden parameters.

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

no rows selected

Upgrade Process :


Stop database from 12.2 oracle home and remove the database from oracle restart.

[oracle@OEL7N1 ~]$ srvctl config
orcl
[oracle@OEL7N1 ~]$ srvctl status database -d orcl -v
Instance orcl1 is running on node oel7n1 with online services pdborcl_srv. Instance status: Open.
Instance orcl2 is running on node oel7n2 with online services pdborcl_srv. Instance status: Open.
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl stop database -d orcl
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl status database -d orcl -v
Instance orcl1 is not running on node oel7n1
Instance orcl2 is not running on node oel7n2
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl remove database -d orcl -f

Let's set 19c environment.

[oracle@OEL7N1 ~]$ echo $ORACLE_HOME
/dboracle/app/oracle/product/19.0/dbhome_1
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ echo $PATH
/dboracle/app/oracle/product/19.0/dbhome_1:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/dboracle/app/oracle/product/12.2.0/dbhome_1/bin

Need to copy spfile and pwd file in dbs location of 19c home.

[oracle@OEL7N1 ~]$ . oraenv
ORACLE_SID = [orcl1] ? +ASM1
The Oracle base has been set to /u01/app/grid
[oracle@OEL7N1 ~]$ asmcmd cp +DATA/ORCL/PARAMETERFILE/spfile.272.1070782447 /dboracle/app/oracle/product/19.0/dbhome_1/dbs/spfileorcl1.ora
copying +DATA/ORCL/PARAMETERFILE/spfile.272.1070782447 -> /dboracle/app/oracle/product/19.0/dbhome_1/dbs/spfileorcl1.ora
[oracle@OEL7N1 ~]$ asmcmd cp +DATA/ORCL/PASSWORD/pwdorcl.256.1070782021 /dboracle/app/oracle/product/19.0/dbhome_1/dbs/orapworcl1
copying +DATA/ORCL/PASSWORD/pwdorcl.256.1070782021 -> /dboracle/app/oracle/product/19.0/dbhome_1/dbs/orapworcl1

[oracle@OEL7N1 ~]$ export ORACLE_SID=orcl1
[oracle@OEL7N1 ~]$ export ORACLE_HOME=/dboracle/app/oracle/product/19.0/dbhome_1
[oracle@OEL7N1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@OEL7N1 ~]$ which sqlplus
/dboracle/app/oracle/product/19.0/dbhome_1/bin/sqlplus
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 13:13:27 2021
Version 19.8.0.0.0

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1577055360 bytes
Fixed Size                  9135232 bytes
Variable Size             553648128 bytes
Database Buffers         1006632960 bytes
Redo Buffers                7639040 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Process ID: 32539
Session ID: 176 Serial number: 31358


SQL> alter system set cluster_database=false scope=spfile sid='*';
ERROR:
ORA-03114: not connected to ORACLE


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
sql
[oracle@OEL7N1 ~]$ sql

SQLcl: Release 19.1 Production on Fri May 07 13:23:18 2021

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

Username? (''?) / as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area   1577055360 bytes
Fixed Size                    9135232 bytes
Variable Size               553648128 bytes
Database Buffers           1006632960 bytes
Redo Buffers                  7639040 bytes
Database mounted.
SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@OEL7N1 ~]$ echo $ORACLE_HOME
/dboracle/app/oracle/product/19.0/dbhome_1
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 13:25:30 2021
Version 19.8.0.0.0

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1577055360 bytes
Fixed Size                  9135232 bytes
Variable Size             553648128 bytes
Database Buffers         1006632960 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> alter pluggable database all open upgrade;
Pluggable database altered.


SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;SQL> SQL> SQL>

DB_NAME   HOST_NAME                      DATABASE_ROLE    OPEN_MODE  LOG_MODE     DB_VERSION        LOGINS     DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
ORCL      OEL7N1.localdomain             PRIMARY          READ WRITE ARCHIVELOG   19.0.0.0.0        RESTRICTED 07-MAY-2021 13:25:46

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MIGRATE    YES
         3 PDBORCL                        MIGRATE    YES
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE  CDB VERSION           STATUS
--------- ---------- --- ----------------- ------------
ORCL      READ WRITE YES 19.0.0.0.0        OPEN MIGRATE

Now, Actual Upgrade.

[oracle@OEL7N1 bin]$ pwd
/dboracle/app/oracle/product/19.0/dbhome_1/bin
[oracle@OEL7N1 bin]$ ./dbupgrade
=== Took a long time. Pasting last few lines of log

catcon::sureunlink: verify that the file really no longer exists

catcon::sureunlink: confirmed that /dboracle/app/oracle/product/19.0/dbhome_1/cfgtoollogs/orcl/upgrade20210507133748/catupgrdpdborcl_catcon_kill_sess_16618_ALL.sql no longer exists after 1 attempts

catcon::catconWrapUp: done


     Time: 7355s For CDB$ROOT
     Time: 14641s For PDB(s)

Grand Total Time: 21996s

 LOG FILES: (/dboracle/app/oracle/product/19.0/dbhome_1/cfgtoollogs/orcl/upgrade20210507133748/catupgrdcdbroot*.log)

Upgrade Summary Report Located in:
/dboracle/app/oracle/product/19.0/dbhome_1/cfgtoollogs/orcl/upgrade20210507133748/upg_summary.log

Grand Total Upgrade Time:    [0d:6h:6m:36s]

Let's check the upgrade summary

[oracle@OEL7N1 upgrade20210507133748]$ cat upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    05-07-2021 15:38:3
Container Database: ORCL
[CON_ID: 1 => CDB$ROOT]

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED      19.8.0.0.0  00:41:45
JServer JAVA Virtual Machine           UPGRADED      19.8.0.0.0  00:06:03
Oracle XDK                             UPGRADED      19.8.0.0.0  00:02:25
Oracle Database Java Packages          UPGRADED      19.8.0.0.0  00:00:29
OLAP Analytic Workspace                UPGRADED      19.8.0.0.0  00:00:54
Oracle Label Security                  UPGRADED      19.8.0.0.0  00:00:34
Oracle Database Vault                  UPGRADED      19.8.0.0.0  00:01:43
Oracle Text                            UPGRADED      19.8.0.0.0  00:02:51
Oracle Workspace Manager               UPGRADED      19.8.0.0.0  00:03:54
Oracle Real Application Clusters       UPGRADED      19.8.0.0.0  00:00:02
Oracle XML Database                    UPGRADED      19.8.0.0.0  00:05:30
Oracle Multimedia                      UPGRADED      19.8.0.0.0  00:03:09
Spatial                                 LOADING      19.8.0.0.0  00:19:03
Oracle OLAP API                         INVALID      19.8.0.0.0  00:00:36
Datapatch                                                        00:25:59
Final Actions                                                    00:26:26
Post Upgrade                                                     00:00:38

Total Upgrade Time: 01:57:48 [CON_ID: 1 => CDB$ROOT]

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.


Oracle Database Release 19 Post-Upgrade Status Tool    05-07-2021 17:55:5
Container Database: ORCL
[CON_ID: 2 => PDB$SEED]

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                             VALID      19.8.0.0.0  01:00:24
JServer JAVA Virtual Machine              VALID      19.8.0.0.0  00:03:58
Oracle XDK                                VALID      19.8.0.0.0  00:02:04
Oracle Database Java Packages             VALID      19.8.0.0.0  00:00:25
OLAP Analytic Workspace                   VALID      19.8.0.0.0  00:01:03
Oracle Label Security                     VALID      19.8.0.0.0  00:00:18
Oracle Database Vault                     VALID      19.8.0.0.0  00:04:50
Oracle Text                               VALID      19.8.0.0.0  00:01:01
Oracle Workspace Manager                  VALID      19.8.0.0.0  00:01:06
Oracle Real Application Clusters          VALID      19.8.0.0.0  00:00:00
Oracle XML Database                       VALID      19.8.0.0.0  00:04:27
Oracle Multimedia                         VALID      19.8.0.0.0  00:01:19
Spatial                                   VALID      19.8.0.0.0  00:13:23
Oracle OLAP API                           VALID      19.8.0.0.0  00:00:34
Datapatch                                                        00:22:18
Final Actions                                                    00:22:41
Post Upgrade                                                     00:02:15
Post Compile                                                     00:11:36

Total Upgrade Time: 02:12:56 [CON_ID: 2 => PDB$SEED * ]
Asterisks denotes compilation time has been included during the upgrade process.

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.


Oracle Database Release 19 Post-Upgrade Status Tool    05-07-2021 19:44:1
Container Database: ORCL
[CON_ID: 3 => PDBORCL]

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED      19.8.0.0.0  00:43:31
JServer JAVA Virtual Machine           UPGRADED      19.8.0.0.0  00:04:51
Oracle XDK                             UPGRADED      19.8.0.0.0  00:03:41
Oracle Database Java Packages          UPGRADED      19.8.0.0.0  00:00:36
OLAP Analytic Workspace                UPGRADED      19.8.0.0.0  00:01:02
Oracle Label Security                  UPGRADED      19.8.0.0.0  00:00:30
Oracle Database Vault                  UPGRADED      19.8.0.0.0  00:05:33
Oracle Text                            UPGRADED      19.8.0.0.0  00:00:39
Oracle Workspace Manager               UPGRADED      19.8.0.0.0  00:00:57
Oracle Real Application Clusters       UPGRADED      19.8.0.0.0  00:00:02
Oracle XML Database                    UPGRADED      19.8.0.0.0  00:04:35
Oracle Multimedia                      UPGRADED      19.8.0.0.0  00:01:17
Spatial                                 LOADING      19.8.0.0.0  00:14:41
Oracle OLAP API                           VALID      19.8.0.0.0  00:00:45
Datapatch                                                        00:18:56
Final Actions                                                    00:19:19
Post Upgrade                                                     00:02:22

Total Upgrade Time: 01:45:50 [CON_ID: 3 => PDBORCL]

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.


Upgrade Times Sorted In Descending Order

Total Upgrade Time: 02:12:56 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 01:57:48 [CON_ID: 1 => CDB$ROOT]
Total Upgrade Time: 01:45:50 [CON_ID: 3 => PDBORCL]
Grand Total Upgrade Time:    [0d:6h:6m:36s]
[oracle@OEL7N1 upgrade20210507133748]$

Let's check the version and component state.


SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> col COMP_ID for a10
SQL> col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL>

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      UPGRADED
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      UPGRADED
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      UPGRADED
XML        Oracle XDK                               19.0.0.0.0      UPGRADED
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      UPGRADED
APS        OLAP Analytic Workspace                  19.0.0.0.0      UPGRADED
RAC        Oracle Real Application Clusters         19.0.0.0.0      UPGRADED
XDB        Oracle XML Database                      19.0.0.0.0      UPGRADED
OWM        Oracle Workspace Manager                 19.0.0.0.0      UPGRADED
CONTEXT    Oracle Text                              19.0.0.0.0      UPGRADED
ORDIM      Oracle Multimedia                        19.0.0.0.0      UPGRADED
SDO        Spatial                                  19.0.0.0.0      LOADING
XOQ        Oracle OLAP API                          19.0.0.0.0      INVALID
OLS        Oracle Label Security                    19.0.0.0.0      UPGRADED
DV         Oracle Database Vault                    19.0.0.0.0      UPGRADED

15 rows selected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED


SQL> set serveroutput on
SQL> exec sys.VALIDATE_SDO();

PL/SQL procedure successfully completed.

SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME='Spatial';

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
SDO        Spatial                                  19.0.0.0.0      VALID


Run the following from both cdb and pdb


@$ORACLE_HOME/rdbms/admin/catuppst.sql 

Let's run the post upgrade script and utlrp.

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /dboracle/upgrade/ -b postup_orcl /dboracle/upgrade/postupgrade_fixups.sql    

@?/rdbms/admin/utlrp

After running utlrp, pdb components are fine.

COMP_ID                        COMP_NAME                                VERSION         STATUS
------------------------------ ---------------------------------------- --------------- -----------
CATALOG                        Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC                        Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM                         JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML                            Oracle XDK                               19.0.0.0.0      VALID
CATJAVA                        Oracle Database Java Packages            19.0.0.0.0      VALID
APS                            OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC                            Oracle Real Application Clusters         19.0.0.0.0      VALID
XDB                            Oracle XML Database                      19.0.0.0.0      VALID
OWM                            Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT                        Oracle Text                              19.0.0.0.0      VALID
ORDIM                          Oracle Multimedia                        19.0.0.0.0      VALID
SDO                            Spatial                                  19.0.0.0.0      VALID
XOQ                            Oracle OLAP API                          19.0.0.0.0      VALID
OLS                            Oracle Label Security                    19.0.0.0.0      VALID
DV                             Oracle Database Vault                    19.0.0.0.0      VALID

15 rows selected.

SQL> show con_name

CON_NAME
------------------------------
PDBORCL


All components are fine in cdb also.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID                        COMP_NAME                                VERSION         STATUS
------------------------------ ---------------------------------------- --------------- --------------------------------------------
CATALOG                        Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC                        Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM                         JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML                            Oracle XDK                               19.0.0.0.0      VALID
CATJAVA                        Oracle Database Java Packages            19.0.0.0.0      VALID
APS                            OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC                            Oracle Real Application Clusters         19.0.0.0.0      VALID
XDB                            Oracle XML Database                      19.0.0.0.0      VALID
OWM                            Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT                        Oracle Text                              19.0.0.0.0      VALID
ORDIM                          Oracle Multimedia                        19.0.0.0.0      VALID
SDO                            Spatial                                  19.0.0.0.0      VALID
XOQ                            Oracle OLAP API                          19.0.0.0.0      VALID
OLS                            Oracle Label Security                    19.0.0.0.0      VALID
DV                             Oracle Database Vault                    19.0.0.0.0      VALID

15 rows selected.

All components are in valid state in pdb seed also.


Let's fix the timezone now.


Download DST for timezone upgrade scripts from Doc ID 1585343.1

sudip@DESKTOP-RDIUEUO MINGW64 /c/Softwares
$ scp DBMS_DST_scriptsV1.9.zip oracle@192.168.126.11:/dboracle/software
oracle@192.168.126.11's password:
DBMS_DST_scriptsV1.9.zip                                                                                                                                      100%   16KB   1.0MB/s   00:00


[oracle@OEL7N1 software]$ cd timezone
[oracle@OEL7N1 timezone]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 16095 May  8 05:09 DBMS_DST_scriptsV1.9.zip
[oracle@OEL7N1 timezone]$ unzip *
Archive:  DBMS_DST_scriptsV1.9.zip
   creating: DBMS_DST_scriptsV1.9/
  inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql
  inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql
  inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql
  inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql
[oracle@OEL7N1 timezone]$ ls -ltr
total 16
drwxr-xr-x 2 oracle oinstall   107 Mar 14  2018 DBMS_DST_scriptsV1.9
-rw-r--r-- 1 oracle oinstall 16095 May  8 05:09 DBMS_DST_scriptsV1.9.zip
[oracle@OEL7N1 timezone]$ cd DBMS_DST_scriptsV1.9/
[oracle@OEL7N1 DBMS_DST_scriptsV1.9]$ ls -ltr
total 68
-rw-r--r-- 1 oracle oinstall 31010 Aug 22  2014 upg_tzv_check.sql
-rw-r--r-- 1 oracle oinstall 19502 Aug 22  2014 upg_tzv_apply.sql
-rw-r--r-- 1 oracle oinstall  6294 Jan  8  2015 countstarTSTZ.sql
-rw-r--r-- 1 oracle oinstall  7213 Mar 17  2018 countstatsTSTZ.sql

Let's run it.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> @countstatsTSTZ.sql
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first...
Note: empty tables are not listed.
Stat date  - Owner.Tablename.Columnname - num_rows
02/05/2021 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
02/05/2021 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
02/05/2021 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
02/05/2021 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
02/05/2021 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
02/05/2021 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
02/05/2021 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
07/05/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
07/05/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
07/05/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
02/05/2021 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
02/05/2021 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
02/05/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
07/05/2021 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
07/05/2021 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
07/05/2021 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
02/05/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
02/05/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
07/05/2021 - SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 2
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
07/05/2021 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
07/05/2021 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
08/05/2021 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3
08/05/2021 - SYS.KET$_CLIENT_TASKS.LG_DATE - 3
08/05/2021 - SYS.KET$_CLIENT_TASKS.LT_DATE - 3
08/05/2021 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
08/05/2021 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
07/05/2021 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 23674
07/05/2021 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
02/05/2021 - SYS.RADM_FPTM$.TSWTZ_COL - 1
02/05/2021 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
02/05/2021 - SYS.REG$.REG_TIME - 2
07/05/2021 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 167
08/05/2021 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
08/05/2021 - SYS.SCHEDULER$_JOB.END_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 24
08/05/2021 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.START_DATE - 24
07/05/2021 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 82
07/05/2021 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 82
07/05/2021 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 82
02/05/2021 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
02/05/2021 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
08/05/2021 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 4
08/05/2021 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 4
08/05/2021 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 4
08/05/2021 - SYS.STATS_TARGET$.END_TIME - 1056
08/05/2021 - SYS.STATS_TARGET$.START_TIME - 1056
02/05/2021 - SYS.TAB_STATS$.SPARE6 - 1137
07/05/2021 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 176
07/05/2021 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 176
07/05/2021 - SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - 1
07/05/2021 - SYS.WRI$_ALERT_OUTSTANDING.