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 :

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

    export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
    
    $ORACLE_HOME/jdk/bin/java -jar /oracle/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/preupgrade.jar
    
    
    ==================
    PREUPGRADE SUMMARY
    ==================
      /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade.log
      /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
      /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
    
    Execute fixup scripts as indicated below:
    
    Before upgrade:
    
    Log into the database and execute the preupgrade fixups
    @/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
    
    After the upgrade:
    
    Log into the database and execute the postupgrade fixups
    @/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
    
    Preupgrade complete: 2019-08-26T13:09:51
    
    
    

    Run the pre-upgrade fixup script:

    SQL> @/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
    
    Executing Oracle PRE-Upgrade Fixup Script
    
    Auto-Generated by:       Oracle Preupgrade Script
                             Version: 19.0.0.0.0 Build: 1
    Generated on:            2019-08-26 13:09:37
    
    For Source Database:     TESTDB
    Source Database Version: 12.1.0.2.0
    For Upgrade to Version:  19.0.0.0.0
    
    Preup                             Preupgrade
    Action                            Issue Is
    Number  Preupgrade Check Name     Remedied    Further DBA Action
    ------  ------------------------  ----------  --------------------------------
        1.  invalid_objects_exist     NO          Manual fixup recommended.
        2.  exclusive_mode_auth       NO          Manual fixup recommended.
        3.  case_insensitive_auth     NO          Manual fixup recommended.
        4.  underscore_events         NO          Informational only.
                                                  Further action is optional.
        5.  dictionary_stats          YES         None.
        6.  parameter_deprecated      NO          Informational only.
                                                  Further action is optional.
        7.  min_archive_dest_size     NO          Informational only.
                                                  Further action is optional.
        8.  rman_recovery_version     NO          Informational only.
                                                  Further action is optional.
    
    The fixup scripts have been run and resolved what they can. However,
    there are still issues originally identified by the preupgrade that
    have not been remedied and are still present in the database.
    Depending on the severity of the specific issue, and the nature of
    the issue itself, that could mean that your database is not ready
    for upgrade.  To resolve the outstanding issues, start by reviewing
    the preupgrade_fixups.sql and searching it for the name of
    the failed CHECK NAME or Preupgrade Action Number listed above.
    There you will find the original corresponding diagnostic message
    from the preupgrade which explains in more detail what still needs
    to be done.
    
    PL/SQL procedure successfully completed.
    
    

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

    SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
    
    
    SQL> select count(*) from dba_objects where status='INVALID';
    
      COUNT(*)
    ----------
             0
    
    

    3.Check database component status:

    set pagesize500
    set linesize 100
    select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
    
    
    
    COMP_NAME
    ----------------------------------------------------------------------------------------------------
    STATUS      VERSION
    ----------- ----------------------------------------
    JServer JAVA Virtual Machine
    VALID       12.1.0.2.0
    
    Oracle Database Catalog Views
    VALID       12.1.0.2.0
    
    Oracle Database Java Packages
    VALID       12.1.0.2.0
    
    Oracle Database Packages and Types
    VALID       12.1.0.2.0
    
    Oracle Multimedia
    VALID       12.1.0.2.0
    
    Oracle Text
    VALID       12.1.0.2.0
    
    Oracle Workspace Manager
    VALID       12.1.0.2.0
    
    Oracle XDK
    VALID       12.1.0.2.0
    
    Oracle XML Database
    VALID       12.1.0.2.0
    
    
    SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
    
    no rows selected
    
    

    4.Check timezone version:

    SQL> select * from v$timezone_file;
    
    FILENAME                VERSION     CON_ID
    -------------------- ---------- ----------
    timezlrg_18.dat              18          0
    
    

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

    SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
    
    no rows selected
    
    SQL> SELECT * FROM v$recover_file;
    
    no rows selected
    
    
    

    6.Purge recyclebin:

    SQL> purge dba_recyclebin;
    

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

     

    UPGRADE DATABASE:

    Enable the flashback on the database.

  • To enable restore , in case of failure, enable flashback option.
  • alter system set db_recovery_file_dest_size=20G scope=both;
    alter system set db_recovery_file_dest='/dumparea/FRA/' scope=both;
    alter database flashback on;
    

     

    2.Start DBUA

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