HOL 18c Upgrade

In this part of the Lab you will upgrade the UPGR database from Oracle to Oracle 18.2.0. 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.

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

The environment variable $OH18 is created only for your convenience. It points always to the Oracle 18c 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;

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

You will see 2x “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.

shutdown immediate

2. Fixing the initUPGR.ora

Now you have to edit the initUPGR.ora and set one important parameter. This underscore is only necessary as we use a software label which is meant for Exadata purposes originally. With 18.3.0 mid of July 2018, Oracle Database software should be generally available on-premises on all platforms.

Use either vi or gedit (if you prefer a graphical editor) and add this parameter to your initUPGR.ora:


vi gedit

vi initUPGR.ora

gedit initUPGR.ora

Save the initUPGR.ora.

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

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

3. Upgrade

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

. upgr18
sqlplus / as sysdba

Startup the database in UPGRADE mode and create an SPFILE:

startup upgrade pfile=$OH18/dbs/initUPGR.ora
create spfile from pfile;

Start the database upgrade in parallel with catctl.pl:

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /home/oracle/logs catupgrd.sql

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 :

. upgr18
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 31 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 DSTv31

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='18.1.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 18.2.0.

===> NEXT: AWR Diff Reports