Upgrade Oracle database manually from 12c to 19c

Manually Upgrade database from 12c to 19c

Description

In this article I will demonstrate an overview on manually upgrading Oracle database from 12.2.0.1 to 19.3.0.0 on Oracle Linux 7 64bit platform. As the source database version is 12.2.0.1, we can go for a direct upgrade to 19c.

Below Oracle versions can be directly upgraded to 19c. Refer this document

  • 11.2.0.4
  • 12.1.0.2
  • 12.2.0.1
  • 18c

Below is the high level steps:

  1. Installing Oracle 19.3.0.0 binaries
  2. Executing the preupgrade jar tool
  3. Performing the preupgrade actions
  4. Backing up the database / Create a guaranteed restore point
  5. Upgrade the database
  6. Perform the postupgrade actions

Environment Details:

Source 		Hostname:		new12c
		Database version:	12.2.0.1
		Database Name:		dev12c
		ORACLE_HOME:		/u01/app/oracle/product/12.2.0/db_1


Target 		Hostname:		new12c 
		Databaes Version:	19.3.0.0
		Database name:		dev12c
		ORACLE_HOME:		/u01/app/oracle/product/19.3.0/dbhome_1

1. Installing Oracle 19c binaries.

I have already completed the installation of 19c, Refer here for step by step installation of Oracle 19.3.0.0 on Linux 7 64bit.

2. Executing the pre-upgrade jar tool

Pre-upgrade information tool is used to determine the instance readiness before upgrading the database. The pre-upgrade script will generate the fix for many issues before you upgrade to new Oracle home.

The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar

Run the pre-upgrade tool
$ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar

Make sure to run the tool from source ORACLE_HOME.

$ORACLE_BASE/product/12.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade

Output:

export ORACLE_SID=db12c
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1

[oracle@new12c db12c]$ $ORACLE_BASE/product/12.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade/
==================
PREUPGRADE SUMMARY
==================
  /u01/preupgrade/preupgrade.log
  /u01/preupgrade/preupgrade_fixups.sql
  /u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-11-17T02:21:12
[oracle@new12c db12c]$

Detailed output is generated in DIR path, in my case /u01/preupgrade/preupgrade.log

[oracle@new12c preupgrade]$ cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-17T02:21:12

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  DB12C
     Container Name:  db12c
       Container ID:  0
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  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 4625
      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 4096 MB.  There is currently 4089 MB
      of free space remaining, which may not be adequate for the upgrade.

      Currently:
       Fast recovery area :  /u01/app/oracle/fast_recovery_area/db12c
       Limit              :  4096 MB
       Used               :  7169 KB
       Available          :  4089 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.  (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.

  3.  (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
  ================
  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                             470 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 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 DB12C
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/preupgrade/preupgrade_fixups.sql

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

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

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

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

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

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

      Some directory object path names may currently contain symbolic links.

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

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

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

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

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

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

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

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

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

    SQL>@/u01/preupgrade/postupgrade_fixups.sql


[oracle@new12c preupgrade]$

3. Performing the pre-upgrade actions

Run the preupgrade_fixups.sql

SQL> @/u01/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-11-17 02:21:02

For Source Database:     DB12C
Source Database Version: 12.2.0.1.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.  dictionary_stats          YES         None.
    3.  pre_fixed_objects         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.

SQL> SQL>

The preupgrade_fixups.sql output lists multiple recommendations which has to be fix manually. We will fix the below recommendations manually and re-run the preupgrade_fixups.sql

    1.  min_recovery_area_size    NO          Manual fixup required.
    2.  dictionary_stats          YES         None.
    3.  pre_fixed_objects         YES         None.
    4.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    5.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

Recommendations 1 and 4 has to be fixed manually.

Here I am ignoring point No: 5 rman_recovery_verison as there is no recovery catalog configured in my environment.

Recommendation 1: min_recovery_area_size

    1.  Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4625
      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 4096 MB.  There is currently 4089 MB
      of free space remaining, which may not be adequate for the upgrade.

      Currently:
       Fast recovery area :  /u01/app/oracle/fast_recovery_area/db12c
       Limit              :  4096 MB
       Used               :  7169 KB
       Available          :  4089 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.

Action: Increase the DB_RECOVERY_FILE_DEST_SIZE parameter

sqlplus "/as sysdba"
alter system set db_recovery_file_dest_size=6000M scope=both;

Recommendation 4: tablespaces_info

  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                             470 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

Action: increase the datafile size

alter database datafile '/u01/app/oracle/oradata/db12c/system01.dbf' resize 912M;
alter database datafile '/u01/app/oracle/oradata/db12c/sysaux01.dbf' resize 500M;
alter database datafile '/u01/app/oracle/oradata/db12c/undotbs01.dbf' resize 440M;
alter database tempfile '/u01/app/oracle/oradata/db12c/temp01.dbf' resize 200M;

Now re-run the preupgrade_fixups.sql

SQL> @/u01/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-11-17 02:21:02

For Source Database:     DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  dictionary_stats          YES         None.
    2.  pre_fixed_objects         YES         None.
    3.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    4.  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.

SQL> exit

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

It is recommended to take a backup of database using RMAN or create a guaranteed restore point. Ensure proper failback plan in place.

Creating a guranteed restore point:

create restore point before_upgrade_19c guarantee flashback database;

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

NAME                 GUARANTEE TIME
-------------------- --------- --------------------------------
BEFORE_UPGRADE_19C   YES        17-NOV-20 04.07.59.000000000 AM

4. Upgrading the database

Once the pre-upgrade actions are completed, shutdown the database to start the upgrade process

sqlplus /as sysdba
shutdown immediate;
exit

Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME

[oracle@new12c preupgrade]$ cp $ORACLE_HOME/dbs/orapwdb12c /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@new12c preupgrade]$ cp $ORACLE_HOME/dbs/spfiledb12c.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/

[oracle@new12c preupgrade]$ cp $ORACLE_HOME/network/admin/listener.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin

[oracle@new12c preupgrade-2]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@new12c dbs]$ ls -l *db12c*
-rw-r-----. 1 oracle oinstall 3584 Nov 17 04:47 orapwdb12c
-rw-r-----. 1 oracle oinstall 3584 Nov 17 04:47 spfiledb12c.ora

Stop listener running on 11g home and start it from 19c home

[oracle@ol7-dev ~]$ lsnrctl stop

[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$ lsnrctl start

Start the database from 19c ORACLE_HOME and start the upgrade.

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus /as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;

--output--
[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$
[oracle@ol7-dev ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 8 04:48:01 2020
Version 19.3.0.0.0

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size                  8896064 bytes
Variable Size             805306368 bytes
Database Buffers          436207616 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select name,open_mode,status from v$database, v$instance;

NAME      OPEN_MODE            STATUS
--------- -------------------- ------------
DB12C     READ WRITE           OPEN MIGRATE
SQL> exit

Run the DB Upgrade utility

$ORACLE_HOME/bin/dbupgrade

Output:

[oracle@new12c db12c]$ $ORACLE_HOME/bin/dbupgrade

Argument list for [/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
.
.
.
<output truncated>
.
.
.
------------------------------------------------------
Phases [0-107]         End Time:[2020_11_17 11:56:33]
------------------------------------------------------

Grand Total Time: 3781s

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

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

Grand Total Upgrade Time:    [0d:1h:3m:1s]
[oracle@new12c db12c]$

Check the upgrade summary log,

[oracle@new12c db12c]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/db12c/upgrade20201117105328/upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    11-17-2020 11:56:0
Database Name: DB12C

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

Oracle Server                          UPGRADED      19.3.0.0.0  00:25:05
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:02:26
Oracle XDK                             UPGRADED      19.3.0.0.0  00:02:20
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:29
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:22
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:12
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:38
Oracle Text                            UPGRADED      19.3.0.0.0  00:01:20
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:01:16
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:03:11
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:01:34
Spatial                                UPGRADED      19.3.0.0.0  00:12:38
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:27
Datapatch                                                        00:07:25
Final Actions                                                    00:07:35
Post Upgrade                                                     00:00:25

Total Upgrade Time: 01:00:39

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.

Grand Total Upgrade Time:    [0d:1h:3m:1s]
[oracle@new12c db12c]$

DB is in shutdown state after the dbupgrade process. Start the database. Check the database component status.

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup
SELECT name, open_mode, status, version from v$database, v$instance;

Output:

output:
[oracle@new12c flashback]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@new12c flashback]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@new12c flashback]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 23:50:53 2020
Version 19.3.0.0.0

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size            1040187392 bytes
Database Buffers          100663296 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>


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

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

Execute Post-Upgrade Status Tool, utlusts.sql

@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT

SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    11-18-2020 00:15:2
Database Name: DB12C

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

Oracle Server                          UPGRADED      19.3.0.0.0  00:25:05
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:02:26
Oracle XDK                             UPGRADED      19.3.0.0.0  00:02:20
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:29
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:22
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:12
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:38
Oracle Text                            UPGRADED      19.3.0.0.0  00:01:20
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:01:16
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:03:11
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:01:34
Spatial                                UPGRADED      19.3.0.0.0  00:12:38
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:27
Datapatch                                                        00:07:25
Final Actions                                                    00:07:35
Post Upgrade                                                     00:00:25

Total Upgrade Time: 01:00:39

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

SQL>

Recompile the INVALID Objects using utlrp.sql

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

5. Performing the post-upgrade actions

Connect to sqlplus and run the postupgrade_fixups.sql script

sqlplus / as sysdba
@/u01/preupgrade/postupgrade_fixups.sql


--output--SQL> @/u01/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-11-17 02:21:12

For Source Database:     DB12C
Source Database Version: 12.2.0.1.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      NO          Manual fixup recommended.
    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.
SQL>

Output from postupgrade_fixups.sql recommends the below,

  • Upgrade the timezone file version
  • Gather statistics on Fixed objects.

Upgrade the database timezone file

Scripts to upgrade Timezone file versions is available under ORACLE_HOME/rdbms/admin directory from Oracle 18c onwards.

To get how much TIMESTAMP WITH TIME ZONE date is there in database using stats info.
$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

Get the approximate TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column
$ORACLE_HOME/rdbms/admin/utltz_countstar.sql

Timezone upgrade check script
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
   
Timezone aply script.
 $ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

Upgrade steps:

sqlplus / as sysdba
SELECT version FROM v$timezone_file;
@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;

--Output:
SQL> SELECT version FROM v$timezone_file;

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

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

Session altered.
 .
 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
.
.
<output truncated>
.
.
17/11/2020 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
 Total numrows of non-SYS TSTZ columns is : 8
 There are in total 20 non-SYS TSTZ columns.
 Total Minutes elapsed : 0

Session altered.

SQL>

SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Session altered.

 .
 Estimating amount of TSTZ data using COUNT(*).
 This might take some time ...
.
.
<output truncated>
.
.
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
 Total count * of non-SYS TSTZ columns is :  8
 There are in total 20 non-SYS TSTZ columns.
 Total Minutes elapsed : 0

Session altered.

SQL>

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

Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.

SQL>
SQL> @?/rdbms/admin/utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size            1006632960 bytes
Database Buffers          134217728 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size            1006632960 bytes
Database Buffers          134217728 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
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
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.
SQL>

Check the updated timezone version

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        32

1 row selected.

Gather statistics on fixed objects

Connect to sqlplus as sys user and execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

sqlplus / as sysdba
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
exit

--output
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL>

Now re-run the postupgrade_fixups.sql

sqlplus / as sysdba
 @/u01/preupgrade/postupgrade_fixups.sql
exit

--output
SQL> @/u01/preupgrade/postupgrade_fixups.sql
No errors.
No errors.
No errors.
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-11-17 02:21:12

For Source Database:     DB12C
Source Database Version: 12.2.0.1.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.
SQL>

Drop the restore point

Once the upgrade is successfull, drop the restore point otherwise at some point you will run out of space.

select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;

--output
SQL> select name from v$restore_point;

NAME
-------------------
BEFORE_UPGRADE_19C

SQL> drop restore point BEFORE_UPGRADE_19C;

Restore point dropped.

Update the compatible parameter

Note: After the upgrade, database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.

alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible;


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

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

Total System Global Area 1157627168 bytes
Fixed Size                  8895776 bytes
Variable Size             973078528 bytes
Database Buffers          167772160 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0

Database has been successfully upgraded to 19c.

[oracle@ol7-dev ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 13 20:07:34 2020
Version 19.3.0.0.0

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


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

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

NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
ORADEV    READ WRITE           19.0.0.0.0

SQL>

Hope this helps…