Database upgrade can be performed either using manual or DBUA. Below are the steps for upgrading oracle database from oracle 12c to 19c version using DBUA.

Important points:

  • Direct upgrade to 19  can be performed from 11.2.0.4,12.1.0.2,12.2.0.1 & 18c .
  • Compatible parameter should be at minimum 11.2.0
  • Post upgrade , oracle default accounts ( whose password has not been reset before upgrade), will be locked and set to NO AUTHENICATE MODE.
  • Post upgrade, you may not be able to login to the existing users with the password, because of new authentication method. To fix this, sqlnet.ora file need to be update(details explained at the end of this article).

Current environment details:

DATABASE TYPE – STAND ALONE

DATABASE NAME – TESTDB

DATABASE VESION – 12.1.0.2

CURRENT ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1

NEW ORACLE_HOME = /oracle/app/oracle/product/19.0.0.0/dbhome_1

 

Install oracle 19c ORACLE_HOME:

unzip the binary and run runInstaller.sh

mkdir -p /oracle/app/oracle/product/19.0.0.0/dbhome_1

./runInstaller.sh

 

 

 

 

 

 

 

 

 

 

 

PRE-UPGRADE CHECK :

  1. Run preupgrade tool script

preupgrade.jar tool file is available with the oracle database binary. Run this to do the precheck

Run the pre-upgrade fixup script:

2.Run utlrp.sql:( to compile invalid objects) 

3.Check database component status:

4.Check timezone version:

5.Check files in backup mode:(should return zero rows)

6.Purge recyclebin:

As pre-check is successful . Now we will proceed with the upgrade

 

UPGRADE DATABASE:

Enable the flashback on the database.

    1. To enable restore , in case of failure, enable flashback option.

 

2.Start DBUA

export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1/
cd $ORACLE_HOME/bin
./dbua

 

 

 

 

 

 

 

 

 

 

 

 

We can pause and resume the upgrade during the process also.

 

Upgrade completed successfully.

POST CHECK:

3. Updating sqlnet.ora file

Post upgrade, you might not be able to connect to the existing users with the passwords. So to fix this add SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 to sqlnet.ora file

export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1

cd $ORACLE_HOME/network/admin

4. Once you have confirmed that upgrade is successful and there is no rollback, you can drop the restore point.

5. Updating compatible parameter post upgrade.

Once upgrade is successful , do testing on your database . Once testing is successful you can update the compatible parameter. However once compatible parameter is updated, database cannot be downgraded. So always do proper testing and take a full backup before updating the compatible parameter.

  • Take fullbackup of the database.
  • Update compatible parameter