How to upgrade DB from 12.1 to 19.3

Oracle Database 12.1 to 19.3 Upgrade Steps

This document provides a step-by-step guide for upgrading an Oracle Database from version 12.1 to 19.3. It outlines key actions, from software installation to post-upgrade tasks, to ensure a successful and efficient upgrade process.

Pre-Upgrade Preparations

1. Install Oracle 19c Software:

   • Install the Oracle Database 19.3.0 software binaries into a new Oracle Home directory. Do not install it over your existing 12.1 Oracle Home. This new home will be referred to as 19.3_Home.

2. Remove Obsolete init Parameters:

   • Review your current init.ora or SPFILE for any parameters that are no longer supported or are obsolete in Oracle 19c. Remove or adjust these parameters as necessary. Refer to Oracle documentation for a complete list of obsolete parameters.

3. Stop the Listener:

   • Before proceeding with the database upgrade, stop the Oracle Listener associated with your 12.1 database.

   • lsnrctl stop

4. Gather Dictionary and Fixed Objects Statistics:

   • It is critical to have up-to-date dictionary and fixed object statistics before starting the upgrade. This helps the upgrade process itself and ensures optimal performance post-upgrade.

   EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
   
   

5. Empty Recycle Bin:

   • Purge the recycle bin to avoid potential issues during the upgrade.

   PURGE DBA_RECYCLEBIN;
   
   

6. Check and Update Time Zone File:

   • Verify the current time zone file version and update it to the latest version compatible with Oracle 19c if necessary. This is crucial for consistent time zone handling.

   • Refer to Oracle Support Note "Updating the Time Zone File and Timestamp with Time Zone Data in Oracle Database" for detailed instructions.

7. Run Pre-Upgrade Information Tool:

   • Execute the preupgrade.jar tool from the 19c Oracle Home, pointing it to your 12.1 database. This tool performs a comprehensive analysis of your database for potential upgrade issues and generates fix-up scripts.

   (12.1_Home)/jdk/bin/java -jar (19.3_Home)/rdbms/admin/preupgrade.jar FILE TEXT DIR /home/oracle/upgrade
   
   

   • After running the tool, execute the generated fix-up script:

   @/home/oracle/upgrade/preupgrade_fixups.sql
   
   

   • Review the preupgrade.log and preupgrade_info.txt files for any remaining warnings or manual actions required.

Upgrade Execution

8. Stop RAC Instances (if applicable):

   • If you are upgrading a Real Application Clusters (RAC) database, stop the second (and subsequent) RAC instances.

   • Disable the cluster on the first instance and then stop the first instance.

9. Set Environment Variables for 19c:

   • Ensure your environment variables (especially ORACLE_HOME and PATH) are set to point to the new 19.3 Oracle Home.

10. Start Database in Upgrade Mode:

    • Start the database from the 19.3 Oracle Home in upgrade mode.

    sqlplus / as sysdba
    startup upgrade
    
    

11. Invoke Database Upgrade:

    • Execute the dbupgrade utility from the 19.3 Oracle Home. This command initiates the actual database upgrade process.

    (19.3_Home)/bin/dbupgrade
    
    

    • Monitor the output of this command closely for any errors or warnings.

Post-Upgrade Actions

• After dbupgrade completes, the database will typically shut down.

• Start the database in normal mode from the 19.3 Oracle Home.

• Run the post-upgrade scripts and perform necessary post-upgrade checks as recommended by Oracle documentation (e.g., catupgrade.sql, utlrp.sql).

• Re-enable cluster services and start all RAC instances if applicable.

• Perform performance analysis and tuning as outlined in the "Oracle 19c Upgrade Checklist and Best Practices" document, including AWR comparisons, STS analysis, and SPM configuration.

Following these steps carefully will help ensure a successful upgrade of your Oracle 12.1 database to 19.3.