Upgrade Oracle 11g to 18c

Environment

OS: CEntOS 6.10

Oracle: Oracle Database 11.2.0.4 SE with JUL2019 PSU Applied

Memory: 4G

HDD: 100G

Prerequisites

Verify all prerequisites packages are installed on the server. Install oracle-database-preinstall-18c package before upgrading. If you have internet access on the server you can download and install the package with yum command or you can download package from internet and install manually. Use link below to download it manually.

• Oracle-database-preinstall-18c-EL6
• Oracle-database-preinstall-18c-EL7

Execute yum command to download and install pre-install package.

yum -y install oracle-database-preinstall-18c

Create new Oracle Home for  Oracle 18c.

mkdir -p /u01/app/oracle/product/18.0.0/dbhome_1

chown -R oracle:oinstall /u01/app/oracle/product/18.0.0/dbhome_1/

Extract 18c source file to newly created Oracle Home.

su - oracle

cd /media/sf_Softwares/Oracle

unzip LINUX.X64_180000_db_home.zip -d /u01/app/oracle/product/18.0.0/dbhome_1

Add new Oracle Home setting to /etc/oratab. Make sure to comment or remove the older OH.

vi /etc/oratab

Modify response file.

Once extract is completed, change directory to 18c Oracle Home. Change environment variables and set recently created directory as ORACLE_HOME. Copy response file to some other location. This will preserve original respose file. Modify respose file till the Grid Option.

Set all the parameters value till you reach Grid Options.

. oraenv

cd $ORACLE_HOME

cd install/response/

cp db_install.rsp ~oracle/

cd ~oracle/

vi db_install.rsp 

Modify response file and fill-up all the required parameter values.  Before updating response file it has contents as below.

Once it has been modified and updated it looks as below.

Execute command below to start installation

./runInstaller -silent -ignorePrereq -waitforcompletion -responseFile ~oracle/db_install.rsp SECURITY_UPDATES_VIA_MYORACLESUPPORT=false DECLINE_SECURITY_UPDATES=true

Execute root.sh as  user root described in installation output.

/u01/app/oracle/product/18.0.0/dbhome_1/root.sh

Download and extract pre-upgrade file to new Oracle home location.

Download the latest "preupgrade.jar" file from MOS 884522.1.

cd /media/sf_Softwares/Oracle/

ls

unzip preupgrade_181_cbuild_7_lf.zip -d /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/

Executed pre-upgrade jar before upgrade. Make sure all issue reported should be fixed before upgrading database to latest version.

Output should be something like below. Fixes are available at the buttom.

Fix all the issues and execute preupgrade_fixups.sql

@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

Recompile all invalid objects

@?/rdbms/admin/utlrp

Stop database and listener process that is running from 11g

shutdown immediate

lsnrctl stop

Copy network configuration files, spfile and password files to new ORACLE_HOME.

Start listener and database in upgrade mode

Change environments to 18c

Start listener

Start database in upgrade mode

lsnrctl start

sqlplus / as sysdba

startup upgrade

Set following environments. Without these environment variable I had encountered error.

cat >> .bashrc

export LANG=en_US.UTF-8

export LC_ALL="en_US.UTF-8"

source .bash_profile 

echo $LANG

echo $LC_ALL

Error I have encountered:

[oracle@oratest ~]$ cat /u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20190625090048/catupgrd_datapatch_upgrade.log

SQL Patching tool version 18.0.0.0.0 Production on Tue Jun 25 09:23:57 2019

Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18753_2019_06_25_09_23_57/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Bootstrapping registry and package to current versions...done

Error: prereq checks failed!

verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18753_2019_06_25_09_23_57/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Tue Jun 25 09:24:04 2019

[oracle@ oratest ~]$

Start Oracle database upgrading

Change directory to 18c ORACLE_HOME/bin

execute dbupgrade

cd $ORACLE_HOME/bin

./dbupgrade

Start database once up gradation is completed. Check timezone file version. If version of the file is less than 31 then apply timezone fix.

sqlplus / as sysdba

startup

SELECT * FROM v$timezone_file;

Top apply timezone fix start database in upgrade mode.

shutdown immediate

startup upgrade

Execute Timezone fix

SET SERVEROUTPUT ON

DECLARE

  l_tz_version PLS_INTEGER;

BEGIN

  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

  DBMS_DST.begin_upgrade(l_tz_version);

END;

/

Start database normally and apply upgrades

shutdown immediate

startup

SET SERVEROUTPUT ON

DECLARE

  l_failures   PLS_INTEGER;

BEGIN

  DBMS_DST.upgrade_database(l_failures);

  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);

  DBMS_DST.end_upgrade(l_failures);

  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);

END;

/

Verify timezone file version.

SELECT * FROM v$timezone_file;

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A20

SELECT property_name, property_value

FROM   database_properties

WHERE  property_name LIKE 'DST_%'

ORDER BY property_name;

Execute post upgrade fixups once upgrade completes.

@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql