Manual upgrading Oracle database 11gR2 to 18c

If you want to know how we upgrade an 11.2.0.4 database to 18.0.0.0.0 using DBUA, click here.

This article explains how to upgrade 11.2.0.3 and above, and 12.1.0.1, 12.2.0.1 to 18.0.0.0.0 manually.

Manually upgrading to 18c

There are many DBAs who don’t want to follow the recommended way of an upgrade using DBUA (Database Upgrade Assistant). Following is the method of manually upgrading the 11gr2 database to 18c. I will be using here 11.2.0.4 to upgrade to 18.1.0.0.0, on Linux (x64_86) platform.

I already install new software for Oracle 18c

Below is my environment detail

DBNAME  – MIG18C

CURRENT ORACLE_HOME – /u01/app/oracle/product/11.2.0.4/db_1

NEW ORACLE_HOME – /u01/app/oracle/product/18.0.0.0/db_1

PRE-UPGRADE STEPS:

1. Run the preupgrade tool

Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin.

SYNTAX

$OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar -FILE DIR $OS_DIRECTORY

 Run the preupgrade_fixup.sql

To get rid of from the warnings. Steps are below

Again re-run preupgrade_fixup.sql.

Shutdown the database and take full database backup. Alternatively, you can turn on the flashback database and create a Guaranteed Restore Point which can be used to rollback if upgrade process fails.

Copy the parameter and password file from the 11g home to the 18c home.

Note: I am using Read Only Home

Set new ORACLE HOME of 18c location

Run catctl.pl

If used catctl.pl, the output of this upgrade process would be similar to the following. For this example, log files would be in /tmp folder as specified in upgrade command. For conventional running of catupgrd.sql from SQLPLUS, please see the spool file for output or any errors

-n is used for parallelism (new feature on Oracle 12c)

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

For the output log, click here

Run “postupgrade_fixups.sql” which was created earlier.

To fix the manual error related to timezone

The database is using timezone datafile version 14 and the target 18.1.0.0.0 database ships with timezone datafile version 31. Oracle recommends using the most recent timezone data.  For further information, refer to My Oracle Support Note 1585343.1

Or we can do manually from here

To get rid of from the warnings. Steps are below

Execute utlu122s.sql

Execute catuppst.sql

Lastly, execute utlrp.sql script to compile if there are any invalid objects

Set COMPATIBLE parameter value to 18.0.0 and also start the listener with a new home.

Restart the database

Cheers!!!

Upgradation Successfully Done

Stay tuned for More articles on Oracle 18c 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.