In this part of the Lab you will upgrade the
UPGR database from Oracle 18.104.22.168 to Oracle 18c. 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
- 2. Copy the initUPGR.ora
- 3. Upgrade
- 4. Postupgrade and Recompilation
- 5. Time Zone Change
- 6. Compatible Change
- 7. Manual Changes
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
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.
The log output of
preupgrade.jar asks you to run
olspreupgrade.sql to move the audit table from
SYS, change parameters and run the
Then run the
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
SGA_TARGET will only take effect after a restart.
initUPGR.ora to the target environment and create a new password file:
Now you’ll do the database upgrade of
UPGR to Oracle 18.2.0.
SPFILE and startup the database in
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:
The upgrade will take between 20-40 minutes to complete.
Recompile first with
utlrp.sql, then the
postupgrade_fixups.sql as you see from the instructions of
Startup the database in
This should be the result:
As final recommend step the time zone upgrade should be done from version 14 to 31 should happen.
Since Oracle Database 22.214.171.124 several time zone scripts get delivered by default in
Script to gives how much
TIMESTAMP WITH TIME ZONEdata there is in a database using stats info. No restart required.
Script to approximate how much
TIMESTAMP WITH TIME ZONEdata there is in a database using a
COUNT(*)for each table that has a TSTZ column. This script is useful when using
DBMS_DSTpackage or the scripts of
Time zone upgrade check script
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
As a final step, as the upgrade completed successfully, you should adjust the COMPATIBLE parameter. It does not affect the Optimizer behavior:
As we did a manual upgrade on the command line, you’ll have to adjust the information in
/etc/oratab now. Use
Change the following line from:
Then save the file.
Congratulations – you upgraded the UPGR database successfully from Oracle 126.96.36.199 to Oracle 18c.