Oracle Database Upgrade Manually from 12.1 to 19.3

This article provides an overview of upgrading an existing non-CDB database from Oracle 12c to Oracle 19c. Upgrades can be very complicated, so this is highly recommended to read the oracle provided manual and test thoroughly before considering an upgrade of a production environment.

Oracle Database Upgradation Manual – Read More.

Upgrade Path for Oracle Database 19c:

  1. Database Upgradation Methods:

Database can be upgraded by different way. Based on your application criticality you have to decide which is most appropriate method to upgrade your database.

The following methods are most popular to upgrade Oracle Database –

  • Database Upgrade Assistant (DBUA)
  • Oracle GoldenGate
  • Oracle Data Guard
  • Manual Upgrade
  • Transportable Tablespaces
  • Oracle Data Pump
  • Oracle Streams

2. Manual Upgrade: We will upgrade database from 12c to 19c using manual upgradation method.

3. Prerequisites:

3.1 OS Packages: Install all the OS prerequisites in place by running the 19c preinstall package. On Oracle Linux this can be done by installing the preinstall package.

[root@proddb ~]# yum install -y oracle-database-preinstall-19c
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-19c.x86_64 0:1.0-2.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================
 Package                                            Arch                       Version                         Repository                      Size
====================================================================================================================================================
Installing:
 oracle-database-preinstall-19c                     x86_64                     1.0-2.el7                       ol7_latest                      19 k

Transaction Summary
====================================================================================================================================================
Install  1 Package

Total download size: 19 k
Installed size: 56 k
Downloading packages:
oracle-database-preinstall-19c-1.0-2.el7.x86_64.rpm                                                                          |  19 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-preinstall-19c-1.0-2.el7.x86_64                                                                                  1/1
  Verifying  : oracle-database-preinstall-19c-1.0-2.el7.x86_64                                                                                  1/1

Installed:
  oracle-database-preinstall-19c.x86_64 0:1.0-2.el7

Complete!

3.2 Oracle Database 19c Software Installation:

Please follow this article to install Oracle 19c software only: Oracle 19c s/w only installation

Or, If you want to install oracle software with silent mode, follow below processes –

[oracle@proddb dbhome_1]$ export ORACLE_BASE=/u01/app/oracle
[oracle@proddb dbhome_1]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@proddb dbhome_1]$ cd /u01/app/oraInventory/
[oracle@proddb dbhome_1]$ hostname
proddb.localdomain

[oracle@proddb dbhome_1]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1

[oracle@proddb dbhome_1]$ ./runInstaller -ignorePrereq -waitforcompletion -silent  \
    -responseFile ${ORACLE_HOME}/install/response/db_install.rsp               \
    oracle.install.option=INSTALL_DB_SWONLY                                    \
    ORACLE_HOSTNAME=proddb.localdomain                                         \
    UNIX_GROUP_NAME=oinstall                                                   \
    INVENTORY_LOCATION=/u01/app/oraInventory/                                  \
    SELECTED_LANGUAGES=en                             		                   \
    ORACLE_HOME=${ORACLE_HOME}                                                 \
    ORACLE_BASE=${ORACLE_BASE}                                                 \
    oracle.install.db.InstallEdition=EE                                        \
    oracle.install.db.OSDBA_GROUP=dba                                          \
    oracle.install.db.OSBACKUPDBA_GROUP=dba                                    \
    oracle.install.db.OSDGDBA_GROUP=dba                                        \
    oracle.install.db.OSKMDBA_GROUP=dba                                        \
    oracle.install.db.OSRACDBA_GROUP=dba                                       \
    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false                                 \
    DECLINE_SECURITY_UPDATES=true

Launching Oracle Database Setup Wizard...


Launching Oracle Database Setup Wizard...

The response file for this session can be found at:
 /u01/app/oracle/product/19.3.0/dbhome_1/install/response/db_2020-10-02_09-20-28PM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/InstallActions2020-10-02_09-20-28PM/installActions2020-10-02_09-20-28PM.log

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

Execute /u01/app/oracle/product/19.3.0/dbhome_1/root.sh on the following nodes:
[proddb]


Successfully Setup Software.
[root@proddb ~]# /u01/app/oracle/product/19.3.0/dbhome_1/root.sh
Check /u01/app/oracle/product/19.3.0/dbhome_1/install/root_proddb.localdomain_2020-10-02_21-23-03-087012430.log for the output of root script

4. Pre-upgrade Tasks:

4.1 Copy spfile and password file from 12c to 19c home:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/spfileorcl.ora

[oracle@proddb admin]$ cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileorcl.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/

[oracle@proddb admin]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
[oracle@proddb dbs]$ ls
hc_orcl.dat  init.ora  lkORCL  orapworcl  snapcf_orcl.f  spfileorcl.ora

[oracle@proddb dbs]$ cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl /u01/app/oracle/product/19.3.0/dbhome_1/dbs/

4.2 List of Invalid Objects:

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

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

4.3 Database Full Backup:

This is very important to take database full backup before database upgradation. If your database is very big and take longer time, then you may take archive log backup after stopping all application.

RMAN> run
{
allocate channel ch01 type disk format '/u01/backup/ch01_orcl%U.rman' ;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel ch01;
}

allocated channel: ch01
channel ch01: SID=58 device type=DISK


Starting backup at 03-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=1 STAMP=1052786523
input archived log thread=1 sequence=12 RECID=2 STAMP=1052786535
input archived log thread=1 sequence=13 RECID=3 STAMP=1052790464
input archived log thread=1 sequence=14 RECID=4 STAMP=1052790505
input archived log thread=1 sequence=15 RECID=5 STAMP=1052790628
channel ch01: starting piece 1 at 03-OCT-20
channel ch01: finished piece 1 at 03-OCT-20
piece handle=/u01/backup/ch01_orcl05vc0jr4_1_1.rman tag=TAG20201003T015028 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:03
Finished backup at 03-OCT-20

Starting backup at 03-OCT-20
channel ch01: starting compressed full datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_system_hqff0d62_.dbf
input datafile file number=00003 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_sysaux_hqfdzm30_.dbf
input datafile file number=00004 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_undotbs1_hqff1jdj_.dbf
input datafile file number=00006 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_users_hqff1h9t_.dbf
channel ch01: starting piece 1 at 03-OCT-20
channel ch01: finished piece 1 at 03-OCT-20
piece handle=/u01/backup/ch01_orcl06vc0jr7_1_1.rman tag=TAG20201003T015031 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:35
Finished backup at 03-OCT-20

Starting backup at 03-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=6 STAMP=1052790666
channel ch01: starting piece 1 at 03-OCT-20
channel ch01: finished piece 1 at 03-OCT-20
piece handle=/u01/backup/ch01_orcl07vc0jsb_1_1.rman tag=TAG20201003T015106 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 03-OCT-20

Starting Control File and SPFILE Autobackup at 03-OCT-20
piece handle=/u02/oradata/fast_recovery_area/ORCL/autobackup/2020_10_03/o1_mf_s_1052790668_hqj4bd61_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-OCT-20

released channel: ch01

4.4. Run preupgrade.jar tools:

[oracle@proddb admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin
[oracle@proddb admin]$ ls -l preupgrade.jar
-rw-r--r--. 1 oracle oinstall 725089 Apr 17  2019 preupgrade.jar

[oracle@proddb admin]$ echo $ORACLE_SID
orcl
[oracle@proddb admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@proddb admin]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/jdk/bin/
[oracle@proddb bin]$ ls -l java
-rwxr-xr-x. 1 oracle oinstall 8464 Apr 17  2019 java

[oracle@proddb bin]$ ./java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

[oracle@proddb bin]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/
[oracle@proddb admin]$ ls -l preupgrade.jar

[oracle@proddb admin]$ /u01/app/oracle/product/19.3.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
[oracle@proddb admin]$
[oracle@proddb admin]$ /u01/app/oracle/product/19.3.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-10-03T00:33:47

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  ORCL
     Container Name:  orcl
       Container ID:  0
            Version:  12.1.0.2.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  ARCHIVELOG
           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
  ================
  1.  Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4779
      MB.  Check alert log during the upgrade to ensure there is remaining free
      space available in the recovery area.

      DB_RECOVERY_FILE_DEST_SIZE is set at 4560 MB.  There is currently 4399 MB
      of free space remaining, which may not be adequate for the upgrade.

      Currently:
       Fast recovery area :  /u02/oradata/fast_recovery_area
       Limit              :  4560 MB
       Used               :  161 MB
       Available          :  4399 MB

      The database has archivelog mode enabled, and the upgrade process will
      need free space to generate archived logs to the recovery area specified
      by initialization parameter DB_RECOVERY_FILE_DEST.  The logs generated
      must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
      can cause the upgrade to not proceed.

  RECOMMENDED ACTIONS
  ===================
  2.  Upgrade Oracle Application Express (APEX) manually before the database
      upgrade.

      The database contains APEX version 4.2.5.00.08. Upgrade APEX to at least
      version 18.2.0.00.12.

      Starting with Oracle Database Release 18, APEX is not upgraded
      automatically as part of the database upgrade. Refer to My Oracle Support
      Note 1088970.1 for information about APEX installation and upgrades.

  3.  (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.1.0.2
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  4.  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                             680 MB       792 MB
      SYSTEM                             790 MB      1138 MB
      UNDOTBS1                           130 MB       448 MB

      Minimum tablespace sizes for upgrade are estimates.

  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
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/app/oracle/cfgtoollogs/orcl/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 18 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.1.0.2
      Oracle Database SQL Tuning Guide.

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

    SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
  /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
  /u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-10-03T00:33:47
[oracle@proddb admin]$

4.5. Perform all recommendation (for before upgrade) done by preupgrade.jar by above command:

Note::: Number of recommendation may vary on your database based on enabled features. Let say, you configured EM for your database, then you will get on recommendation. So, Make sure you have taken action for all recommendation to avoid any kind of issues.

4.5.1. Recompile Invalid Objects:

[oracle@proddb admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 3 00:41:54 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2020-10-03 00:42:00
... ... ...
Function dropped.

...Database user "SYS", database schema "APEX_040200", user# "98" 00:42:14
...Compiled 0 out of 3014 objects considered, 0 failed compilation 00:42:14
...271 packages
...263 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 00:42:14
...Completed key object existence check 00:42:14
...Setting DBMS Registry 00:42:14
...Setting DBMS Registry Complete 00:42:14
...Exiting validate 00:42:14

PL/SQL procedure successfully completed.

SQL> set linesize 1000
SQL> SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;

SYS/SYSTEM INVALID OBJECTS
OWNER                         |OBJECT_NAME                                                                    |OBJECT_TYPE
--------------------------------------------------------------------------------------------------------------------------------
NON SYS/SYSTEM INVALID OBJECTS
OWNER                         |OBJECT_NAME                                                                    |OBJECT_TYPE
--------------------------------------------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

4.6. Execute Pre-upgrade Fixing script generated by preupgrade.jar tools:

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-10-03 00:33:43

For Source Database:     ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  min_recovery_area_size    NO          Manual fixup required.
    2.  apex_manual_upgrade       NO          Manual fixup recommended.
    3.  dictionary_stats          YES         None.
    4.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    5.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

4.7. Stop Listener on 12c Home and Run listener from Oracle 19c home:

[oracle@proddb admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin

[oracle@proddb admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))
  )

ADR_BASE_LISTENER_OEMGC = /u01/app/oracle

[oracle@proddb admin]$ lsnrctl

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-OCT-2020 02:04:17

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/proddb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.200)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                03-OCT-2020 02:04:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/proddb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4.8. Create a Flashback Guaranteed Restore Point:

Flashback Guaranteed Restoration option will help you to restore your database from any point (if failed or need to roll backup the upgradation processes) to earlier point and it will simple to restore and will save time.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
ORCL      READ WRITE           ARCHIVELOG

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/oradata/fast_recovery_are
                                                 a
db_recovery_file_dest_size           big integer 4560M
recovery_parallelism                 integer     0
SQL> select * from V$restore_point;

no rows selected

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190SQL> SQL> SQL>
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        03-OCT-20 02.42.07.000000000 AM

5. Upgrade Tasks:

5.1 Startup 12c database in upgrade mode from oracle 19c home:

[oracle@proddb dbhome_1]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@proddb dbhome_1]$ export ORACLE_SID=orcl
[oracle@proddb dbhome_1]$ export PATH=/u01/app/oracle/product/19.3.0/dbhome_1/bin:$PATH
[oracle@proddb dbhome_1]$
[oracle@proddb dbhome_1]$ which sqlplus
/u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus
[oracle@proddb dbhome_1]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 3 02:46:24 2020
Version 19.3.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 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size             905969664 bytes
Database Buffers          637534208 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

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

5.2 Run Database Upgrade Command:

[oracle@proddb dbhome_1]$ cd $ORACLE_HOME/rdbms/admin
[oracle@proddb admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin
[oracle@proddb admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

[oracle@proddb admin]$ ls -lrt catctl.pl catupgrd.sql
-rw-r--r--. 1 oracle oinstall   7829 Feb 14  2018 catupgrd.sql
-rw-r--r--. 1 oracle oinstall 399505 Mar  2  2019 catctl.pl

[oracle@proddb admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
[oracle@proddb admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

Argument list for [catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]


/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.3.0/dbhome_1]
/u01/app/oracle/product/19.3.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.3.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.3.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20201003024855]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20201003024855/catupgrd_catcon_18252.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201003024855/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201003024855/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 1
Database Name         = orcl
DataBase Version      = 12.1.0.2.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/orcl/upgrade20201003024910/catupgrd_catcon_18252.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/orcl/upgrade20201003024910/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/orcl/upgrade20201003024910/catupgrd_*.lst] files for spool files, if any


Log file directory = [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/orcl/upgrade20201003024910]

Parallel SQL Process Count            = 4
Components in [orcl]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-107]         Start Time:[2020_10_03 02:49:28]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [orcl] Files:1    Time: 36s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl] Files:5    Time: 44s
Restart  Phase #:2    [orcl] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [orcl] Files:19   Time: 19s
Restart  Phase #:4    [orcl] Files:1    Time: 1s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [orcl] Files:7    Time: 15s
*****************   Catproc Start   ****************
Serial   Phase #:6    [orcl] Files:1    Time: 12s
*****************   Catproc Types   ****************
Serial   Phase #:7    [orcl] Files:2    Time: 15s
Restart  Phase #:8    [orcl] Files:1    Time: 6s
****************   Catproc Tables   ****************
Parallel Phase #:9    [orcl] Files:67   Time: 29s
Restart  Phase #:10   [orcl] Files:1    Time: 2s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [orcl] Files:1    Time: 80s
Restart  Phase #:12   [orcl] Files:1    Time: 2s
**************   Catproc Procedures   **************
Parallel Phase #:13   [orcl] Files:94   Time: 11s
Restart  Phase #:14   [orcl] Files:1    Time: 3s
Parallel Phase #:15   [orcl] Files:120  Time: 18s
Restart  Phase #:16   [orcl] Files:1    Time: 2s
Serial   Phase #:17   [orcl] Files:22   Time: 5s
Restart  Phase #:18   [orcl] Files:1    Time: 2s
*****************   Catproc Views   ****************
Parallel Phase #:19   [orcl] Files:32   Time: 21s
Restart  Phase #:20   [orcl] Files:1    Time: 3s
Serial   Phase #:21   [orcl] Files:3    Time: 11s
Restart  Phase #:22   [orcl] Files:1    Time: 3s
Parallel Phase #:23   [orcl] Files:25   Time: 101s
Restart  Phase #:24   [orcl] Files:1    Time: 2s
Parallel Phase #:25   [orcl] Files:12   Time: 59s
Restart  Phase #:26   [orcl] Files:1    Time: 2s
Serial   Phase #:27   [orcl] Files:1    Time: 0s
Serial   Phase #:28   [orcl] Files:3    Time: 5s
Serial   Phase #:29   [orcl] Files:1    Time: 0s
Restart  Phase #:30   [orcl] Files:1    Time: 5s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [orcl] Files:1    Time: 3s
Restart  Phase #:32   [orcl] Files:1    Time: 2s
Serial   Phase #:34   [orcl] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [orcl] Files:293  Time: 17s
Serial   Phase #:36   [orcl] Files:1    Time: 0s
Restart  Phase #:37   [orcl] Files:1    Time: 2s
Serial   Phase #:38   [orcl] Files:6    Time: 6s
Restart  Phase #:39   [orcl] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [orcl] Files:3    Time: 40s
Restart  Phase #:41   [orcl] Files:1    Time: 2s
******************   Catproc SQL   *****************
Parallel Phase #:42   [orcl] Files:13   Time: 70s
Restart  Phase #:43   [orcl] Files:1    Time: 2s
Parallel Phase #:44   [orcl] Files:11   Time: 16s
Restart  Phase #:45   [orcl] Files:1    Time: 2s
Parallel Phase #:46   [orcl] Files:3    Time: 4s
Restart  Phase #:47   [orcl] Files:1    Time: 2s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [orcl] Files:1    Time: 7s
Restart  Phase #:49   [orcl] Files:1    Time: 3s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [orcl] Files:1    Time: 16s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [orcl] Files:1    Time: 6s
Restart  Phase #:52   [orcl] Files:1    Time: 2s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [orcl] Files:2    Time: 354s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [orcl] Files:1    Time: 3s
Serial   Phase #:56   [orcl] Files:3    Time: 16s
Serial   Phase #:57   [orcl] Files:3    Time: 10s
Parallel Phase #:58   [orcl] Files:10   Time: 6s
Parallel Phase #:59   [orcl] Files:25   Time: 7s
Serial   Phase #:60   [orcl] Files:4    Time: 10s
Serial   Phase #:61   [orcl] Files:1    Time: 0s
Serial   Phase #:62   [orcl] Files:32   Time: 11s
Serial   Phase #:63   [orcl] Files:1    Time: 0s
Parallel Phase #:64   [orcl] Files:6    Time: 17s
Serial   Phase #:65   [orcl] Files:2    Time: 22s
Serial   Phase #:66   [orcl] Files:3    Time: 30s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [orcl] Files:1    Time: 2s
Serial   Phase #:69   [orcl] Files:1    Time: 5s
Parallel Phase #:70   [orcl] Files:2    Time: 41s
Restart  Phase #:71   [orcl] Files:1    Time: 3s
Parallel Phase #:72   [orcl] Files:2    Time: 5s
Serial   Phase #:73   [orcl] Files:2    Time: 9s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [orcl] Files:1    Time: 1s
Serial   Phase #:76   [orcl] Files:1    Time: 36s
Serial   Phase #:77   [orcl] Files:2    Time: 6s
Restart  Phase #:78   [orcl] Files:1    Time: 2s
Serial   Phase #:79   [orcl] Files:1    Time: 18s
Restart  Phase #:80   [orcl] Files:1    Time: 2s
Parallel Phase #:81   [orcl] Files:3    Time: 71s
Restart  Phase #:82   [orcl] Files:1    Time: 3s
Serial   Phase #:83   [orcl] Files:1    Time: 12s
Restart  Phase #:84   [orcl] Files:1    Time: 5s
Serial   Phase #:85   [orcl] Files:1    Time: 16s
Restart  Phase #:86   [orcl] Files:1    Time: 6s
Parallel Phase #:87   [orcl] Files:4    Time: 94s
Restart  Phase #:88   [orcl] Files:1    Time: 7s
Serial   Phase #:89   [orcl] Files:1    Time: 4s
Restart  Phase #:90   [orcl] Files:1    Time: 2s
Serial   Phase #:91   [orcl] Files:2    Time: 10s
Restart  Phase #:92   [orcl] Files:1    Time: 1s
Serial   Phase #:93   [orcl] Files:1    Time: 2s
Restart  Phase #:94   [orcl] Files:1    Time: 2s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [orcl] Files:1    Time: 12s
Restart  Phase #:96   [orcl] Files:1    Time: 1s
***********   Final Component scripts    ***********
Serial   Phase #:97   [orcl] Files:1    Time: 8s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [orcl] Files:1   Time: 414s
*******************   Migration   ******************
Serial   Phase #:99   [orcl] Files:1    Time: 40s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [orcl] Files:1    Time: 6s
Serial   Phase #:101  [orcl] Files:1    Time: 0s
Serial   Phase #:102  [orcl] Files:1    Time: 52s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [orcl] Files:1    Time: 23s
****************   Summary report   ****************
Serial   Phase #:104  [orcl] Files:1    Time: 7s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [orcl] Files:1    Time: 2s
Serial   Phase #:106  [orcl] Files:1    Time: 0s
Serial   Phase #:107  [orcl] Files:1     Time: 31s

------------------------------------------------------
Phases [0-107]         End Time:[2020_10_03 03:30:32]
------------------------------------------------------

Grand Total Time: 2700s

 LOG FILES: (/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/orcl/upgrade20201003024910/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/orcl/upgrade20201003024910/upg_summary.log

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

5.3 Database Upgradation Summary:

[oracle@proddb trace]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/orcl/upgrade20201003024910/upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2020 10:54:5
Database Name: ORCL

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

Oracle Server                          UPGRADED      19.3.0.0.0  00:11:48
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:03:26
Oracle XDK                             UPGRADED      19.3.0.0.0  00:00:51
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:09
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:11
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:05
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:14
Oracle Text                            UPGRADED      19.3.0.0.0  00:00:26
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:00:24
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:02:04
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:00:53
Spatial                                UPGRADED      19.3.0.0.0  00:04:49
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:09
Datapatch                                                        00:06:48
Final Actions                                                    00:06:48
Post Upgrade                                                     00:00:19

Total Upgrade Time: 00:44:18

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

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

5.4 Startup Database from Oracle 19c Home:

[oracle@proddb admin]$ echo $ORACLE_SID
orcl
[oracle@proddb admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@proddb admin]$ which sqlplus
/u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus
[oracle@proddb admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 3 11:02:55 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1107296256 bytes
Database Buffers          436207616 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> Select name, open_mode, version, status from v$database,v$instance;

NAME      OPEN_MODE            VERSION              STATUS
--------- -------------------- -------------------- ------------
ORCL      READ WRITE           19.0.0.0.0           OPEN

SQL> COL COMP_ID FOR A20
SQL> COL COMP_NAME FOR A45
SQL> COL VERSION FOR A20
SQL> SET LINESIZE 180
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           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           UPGRADED
XOQ                  Oracle OLAP API                               19.0.0.0.0           UPGRADED
OLS                  Oracle Label Security                         19.0.0.0.0           UPGRADED
APEX                 Oracle Application Express                    4.2.5.00.08          VALID
DV                   Oracle Database Vault                         19.0.0.0.0           UPGRADED

16 rows selected.

6. Post Upgradation Task:

The output from the “preupgrade.jar” lists a number of post-upgrade recommendations. Some must be manually applied. Others are incorporated into the “postupgrade_fixups.sql” script. In the following example we run all the manual operations as well as the “postupgrade_fixups.sql” script.

6.1. Following recommendation by preupgrade.jar:

=============
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 18 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.1.0.2
      Oracle Database SQL Tuning Guide.

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

    SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

6.2 (6) Upgrade the database time zone file using the DBMS_DST package:

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        18

SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SET LINESIZE 150

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%';
 
PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_PRIMARY_TT_VERSION                             18
DST_SECONDARY_TT_VERSION                           0
DST_UPGRADE_STATE                                  NONE

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE;
ORACLE instance started.

Database opened.

SQL> SET SERVEROUTPUT ON
DECLARE
  v_tz_version PLS_INTEGER;
BEGIN
  v_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('v_tz_version=' || v_tz_version);
  DBMS_DST.begin_upgrade(v_tz_version);
END;
/

v_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;  

PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_PRIMARY_TT_VERSION                             32
DST_SECONDARY_TT_VERSION                           18
DST_UPGRADE_STATE                                  UPGRADE

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Database opened.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
      v_failures   PLS_INTEGER;
    BEGIN
      DBMS_DST.upgrade_database(v_failures);
      DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : v_failures=' || v_failures);
      DBMS_DST.end_upgrade(v_failures);
      DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : v_failures=' || v_failures);
    END;
    /

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
DBMS_DST.upgrade_database : v_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : v_failures=0

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME; 

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

6.3 (7) Recreate any directory objects listed, using path names that contain no
symbolic links:

SQL> @$ORACLE_HOME/rdbms/admin/utldirsymlink.sql
No DIRECTORY OBJECTS with symlinks found.

PL/SQL procedure successfully completed.

No errors.

6.4. (8) Gather dictionary statistics:

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

6.5. (9) Gather statistics on fixed objects after the upgrade:

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

6.6. Run postupgrade_fixups.sql generated by preupgrade.jar:

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-10-03 00:33:47

For Source Database:     ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      YES         None.
    7.  dir_symlinks              YES         None.
    8.  post_dictionary           YES         None.
    9.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

6.7. Fix invalid objects:

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

  COUNT(*)
----------
      8480

SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/utlrp.sql

Session altered.

TIMESTAMP
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2020-10-03 13:28:54

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

PL/SQL procedure successfully completed.

TIMESTAMP
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2020-10-03 13:35:26

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

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

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

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

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

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

6.8. Changes COMPATIBALE parameter value to 19.0.0:

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE

SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1275068416 bytes
Database Buffers          268435456 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0
noncdb_compatible                    boolean     FALSE

6.9. Run utlusts.sql

utlusts.sql reads the view called dba_registry_log and displays the upgrade results for the database components.

SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/utlusts.sql TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2020 13:40:5
Database Name: ORCL

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

Oracle Server                             VALID      19.3.0.0.0  00:11:48
JServer JAVA Virtual Machine              VALID      19.3.0.0.0  00:03:26
Oracle XDK                                VALID      19.3.0.0.0  00:00:51
Oracle Database Java Packages             VALID      19.3.0.0.0  00:00:09
OLAP Analytic Workspace                   VALID      19.3.0.0.0  00:00:11
Oracle Label Security                     VALID      19.3.0.0.0  00:00:05
Oracle Database Vault                     VALID      19.3.0.0.0  00:00:14
Oracle Text                               VALID      19.3.0.0.0  00:00:26
Oracle Workspace Manager                  VALID      19.3.0.0.0  00:00:24
Oracle Real Application Clusters     OPTION OFF      19.3.0.0.0  00:00:00
Oracle XML Database                       VALID      19.3.0.0.0  00:02:04
Oracle Multimedia                         VALID      19.3.0.0.0  00:00:53
Spatial                                   VALID      19.3.0.0.0  00:04:49
Oracle OLAP API                           VALID      19.3.0.0.0  00:00:09
Datapatch                                                        00:06:48
Final Actions                                                    00:07:33
Post Upgrade                                                     00:00:19
Post Compile                                                     00:06:32

Total Upgrade Time: 00:46:51

Database time zone version is 32. It meets current release needs.

6.10. Drop Restore Point:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL> col name for a20
SQL> col GUARANTEE_FLASHBACK_DATABASE for a10
SQL> col TIME for a60
SQL> set lines 190
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        03-OCT-20 02.42.07.000000000 AM

SQL> drop restore point PRE_UPGRADE;

Restore point dropped.

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

no rows selected

6.11. Verify Database Registry:

SQL> COL COMP_NAME FOR A45
SQL> set linesize 200
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                     OPTION OFF
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
APEX                           Oracle Application Express                    4.2.5.00.08                    VALID
DV                             Oracle Database Vault                         19.0.0.0.0                     VALID

16 rows selected.

6.12. Add new oracle home on /etc/oratab

[root@proddb ~]# cat /etc/oratab | grep orcl
#orcl:/u01/app/oracle/product/12.1.0/dbhome_1:N
orcl:/u01/app/oracle/product/19.3.0/dbhome_1:N

6.13. Take Database Backup using RMAN:

RMAN> run
{
allocate channel ch01 type disk format '/u01/backup/ch01_orcl%U.rman' ;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel ch01;
}

allocated channel: ch01
channel ch01: SID=72 device type=DISK


Starting backup at 03-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=4 STAMP=1052790505
... ... ...
input archived log thread=1 sequence=74 RECID=64 STAMP=1052795491
channel ch01: starting piece 1 at 03-OCT-20
channel ch01: finished piece 1 at 03-OCT-20
piece handle=/u01/backup/ch01_orcl09vc236a_1_1.rman tag=TAG20201003T151834 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:15
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=75 RECID=65 STAMP=1052795498
... .... ....
input archived log thread=1 sequence=133 RECID=123 STAMP=1052839025
input archived log thread=1 sequence=134 RECID=124 STAMP=1052839114
channel ch01: starting piece 1 at 03-OCT-20
channel ch01: finished piece 1 at 03-OCT-20
piece handle=/u01/backup/ch01_orcl0avc238l_1_1.rman tag=TAG20201003T151834 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
Finished backup at 03-OCT-20

Starting backup at 03-OCT-20
channel ch01: starting compressed full datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_system_hqff0d62_.dbf
input datafile file number=00004 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_undotbs1_hqff1jdj_.dbf
input datafile file number=00003 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_sysaux_hqfdzm30_.dbf
input datafile file number=00006 name=/u02/oradata/datafile/ORCL/datafile/o1_mf_users_hqff1h9t_.dbf
channel ch01: starting piece 1 at 03-OCT-20
channel ch01: finished piece 1 at 03-OCT-20
piece handle=/u01/backup/ch01_orcl0bvc23an_1_1.rman tag=TAG20201003T152055 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:35
Finished backup at 03-OCT-20

Starting backup at 03-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=135 RECID=125 STAMP=1052839290
channel ch01: starting piece 1 at 03-OCT-20
channel ch01: finished piece 1 at 03-OCT-20
piece handle=/u01/backup/ch01_orcl0cvc23bq_1_1.rman tag=TAG20201003T152130 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 03-OCT-20

Starting Control File Autobackup at 03-OCT-20
piece handle=/u02/oradata/fast_recovery_area/ORCL/autobackup/2020_10_03/o1_mf_n_1052839291_hqkmsvqn_.bkp comment=NONE
Finished Control File Autobackup at 03-OCT-20

released channel: ch01

Master Note on metalink for Oracle Database Upgradation & Migration. Doc ID 1152016.1

Oracle Database Upgrade Path Reference List (Doc ID 730365.1)

Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c (Doc ID 2539778.1)

Leave a Reply