HOL 19c Upgrade

MAIN INDEX 19C HOL » AWR Diff Reports »

In this part of the Lab you will upgrade the UPGR database from Oracle to Oracle 19c. You can find detailed steps including the output for a Multitenant upgrade here for your information only in case you can’t complete the lab here.


1. Preupgrade

Execute the preupgrade.jar against the UPGR database. Execute it in an xterm, then open a 2nd xterm (or tab in the xterm) with SQL*Plus to run the necessary changes to the UPGR database.

Now let’s start!

. upgr
java -jar $OH19/rdbms/admin/preupgrade.jar TEXT TERMINAL

The environment variable $OH19 is created only for your convenience. It points always to the Oracle 19c Home.

Please read the output and follow all advice.

Important: When it says AUTOFIXUP it means the preupgrade_fixups.sql script will do this task for you. No manual action required in this case.

Open to the 2nd xterm or an xterm TAB and open SQL*plus. Execute the necessary steps in the UPGR database including the preupgrade_fixups.sql as the final step before shutting down the database. The preupgrade_fixups.sql script will fix all the AUTOFIXUP marked messages for you.

. upgr
sqlplus / as sysdba

The log output of preupgrade.jar asks you to run olspreupgrade.sql to move the audit table from SYSTEM to SYS, change parameters and run the preupgrade_fixups.sql.


alter system set processes=300 scope=spfile;

alter system set sga_target=1G scope=spfile;

create pfile from spfile;

Then run the preupgrade_fixups.sql:


This run will take a few minutes as dictionary statistics will be refreshed.

You will see 4x “NO” as result of the preupgrade_fixups.sql. No worries. Your database is using the currently loaded SPFILE. Changes to parameters such as PROCESSES and SGA_TARGET will only take effect after a restart. And you can safely ignore the “Informational Only” messages at this stage. In real environments you should take notice of those.

shutdown immediate

2. Copy the initUPGR.ora

Copy the initUPGR.ora to the target environment and create a new password file:

cp $ORACLE_HOME/dbs/initUPGR.ora $OH19/dbs
. upgr19
orapwd file=orapwUPGR password=oracle entries=10 format=12

3. Upgrade

Now you’ll do the database upgrade of UPGR to Oracle 19.3.0.

sqlplus / as sysdba

Create an SPFILE and startup the database in UPGRADE mode:

create spfile from pfile;
startup upgrade;

Start the database upgrade in parallel with dbupgrade which is a wrapper for $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /home/oracle catupgrd.sql:

dbupgrade -l /home/oracle/logs

The upgrade will take between 20-40 minutes to complete.

4. Postupgrade and Recompilation

Recompile first with utlrp.sql, then the postupgrade_fixups.sql as you see from the instructions of preupgrade.log :

. upgr19
sqlplus / as sysdba

Startup the database in NORMAL mode:


This should be the result:

5. Time Zone Change

As final recommend step the time zone upgrade should be done from version 14 to 32 should happen.

Since Oracle Database several time zone scripts get delivered by default in $ORACLE:HOME/rdbms/admin:

  • ?/rdbms/admin/utltz_countstats.sql
    Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.
  • ?/rdbms/admin/utltz_countstar.sql
    Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of
    utlz_upg_check.sql and utlz_upg_apply.sql scripts.

  • ?/rdbms/admin/utltz_upg_check.sql
    Time zone upgrade check script

  • ?/rdbms/admin/utltz_upg_apply.sql
    Warning: This script will restart the database and adjust time zone data.


You can identify the most recent time zone patch being available with

Now execute the time zone change from TZ v14 to TZ v31.The database will be restarted twice.


The result should be:

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.

Now exit SQL*Plus and start a new session:


sqlplus / as sysdba

Compatible Change

As a final step, as the upgrade completed successfully, you should adjust the COMPATIBLE parameter. It does not affect the Optimizer behavior:

alter system set COMPATIBLE='19.0.0' scope=spfile;
shutdown immediate

Manual Change of /etc/oratab

As we did a manual upgrade on the command line, you’ll have to adjust the information in /etc/oratab now. Use vi or gedit:

vi gedit

vi /etc/oratab

gedit /etc/oratab

Change the following line from:


Then save the file.

Congratulations – you upgraded the UPGR database successfully from Oracle to Oracle 19c.

MAIN INDEX 19C HOL » AWR Diff Reports »
Share this: