Lesson 7

Upgrading an Oracle database from 19c to 23c is a significant undertaking that requires careful planning, execution, and validation. Oracle strongly recommends using the Oracle AutoUpgrade tool for this process, as it automates many steps, performs pre-checks, and simplifies the overall experience.
Here is a comprehensive breakdown of the steps, assuming the use of AutoUpgrade:
Phase 1: Planning and Preparation

1. Review Oracle Documentation:
   • Oracle Database 23c Upgrade Guide: This is your primary resource. Read it thoroughly to understand new features, deprecated features, compatibility changes, and specific upgrade considerations.
   • My Oracle Support (MOS) Notes: Check MOS for known issues, latest patches, and best practices for 19c to 23c upgrades. Look for "Upgrade to Oracle Database 23c" or similar master notes.
   • Certification Matrix: Verify that your operating system, hardware, and any third-party applications are certified for Oracle 23c.
2. Hardware and Software Requirements:
   • Disk Space: Ensure sufficient disk space for the new Oracle 23c software binaries, the upgraded database files, and any temporary space required during the upgrade. Oracle recommends creating a new Oracle Home for 23c.
   • Memory (RAM): Verify that your system meets the minimum RAM requirements for Oracle 23c.
   • Operating System: Ensure your OS is a supported version for Oracle 23c. Perform any necessary OS patches or kernel parameter adjustments.
   • Network: Verify network connectivity and proper DNS resolution.
3. Source Database (19c) Health Check:
   • Apply Latest 19c Release Updates (RUs) and Recommended Patches: Before upgrading, ensure your 19c database is on the latest stable RU and has all critical patches applied. This minimizes known issues during the upgrade.
   • Resolve Pending Issues: Fix any existing database corruption, logical errors, or performance issues in the 19c database.
   • Gather Statistics: Ensure all dictionary and fixed object statistics are up-to-date.
   • Validate Objects: Run DBMS_UTILITY.COMPILE_SCHEMA for all schemas to ensure no invalid objects exist.
   • Check for Deprecated Features: Identify and address any deprecated features used in 19c that are no longer supported or have changed behavior in 23c.
4. Backup, Backup, Backup! (Critical Step):
   • Full RMAN Backup: Perform a full RMAN backup of your 19c database (data files, control files, and archived redo logs). This is your ultimate rollback point.
   • Logical Backup (Optional but Recommended): Consider an expdp export of critical schemas or the entire database as an additional safety net.
   • Configuration Files Backup: Back up listener.ora, tnsnames.ora, sqlnet.ora, pfile/spfile, password files, and any other configuration files.
5. Download Oracle Database 23c Software:
   • Download the Oracle Database 23c software binaries (and any relevant Release Updates/Patches) from My Oracle Support (MOS) or Oracle Software Delivery Cloud.

1. Create New Oracle Home:
   • Install the Oracle Database 23c software into a *new, separate Oracle Home directory*. Do NOT install it into the existing 19c Oracle Home. This allows for a clean separation and simpler rollback if needed.
   • Ensure the oracle user has appropriate permissions for the new Oracle Home.
2. Apply Latest Release Updates (RUs) and Patches to 23c Home:
   • Even if you downloaded the latest full release, there might be subsequent RUs or critical patches available. Apply these to the newly installed 23c Oracle Home *before* starting the database upgrade. This ensures you're upgrading to the most stable version of 23c.

This is the recommended and most efficient method.

1. Locate AutoUpgrade Tool:
   • The autoupgrade.jar file is located in the $ORACLE_HOME/rdbms/admin/ directory of your new 23c Oracle Home.
2. Create AutoUpgrade Configuration File:
   • Create a text file (e.g., upgrade.txt) to configure AutoUpgrade. This file specifies details about your source and target databases, upgrade mode, and other parameters.
   • Example upgrade.txt (simplified):
     

     global.autoupgrade.logDir=/u01/app/oracle/autoupg_logs
     global.autoupgrade.dataDir=/u01/app/oracle/autoupg_data
     
     upg1.source_home=/u01/app/oracle/product/19.0.0/dbhome_1
     upg1.target_home=/u01/app/oracle/product/23.0.0/dbhome_1
     upg1.sid=YOUR_PDB_SID_OR_CDB_NAME
     upg1.log_dir=/u01/app/oracle/autoupg_logs/YOUR_PDB_SID_OR_CDB_NAME
     upg1.target_version=23
     upg1.start_time=NOW
     upg1.upgrade_node=localhost
     upg1.mode=DBCONCURRENT  # Or STANDALONE, ANALYZE, FIXUP, DEPLOY
     	

   • Key mode options:
     • ANALYZE: Runs pre-checks and generates a report of issues without making any changes. Always run this first!
     • FIXUP: Attempts to fix issues reported by ANALYZE.
     • DEPLOY: Performs the actual upgrade.
     • DBCONCURRENT: Allows upgrading multiple PDBs in a CDB concurrently (recommended for CDBs).
     • STANDALONE: For single instances or upgrading a CDB (all PDBs within it sequentially).
3. Run AutoUpgrade in ANALYZE Mode:
   • Navigate to your 23c Oracle Home's rdbms/admin directory.
   • Execute:
     

     java -jar autoupgrade.jar -config upgrade.txt -mode ANALYZE
     

   • Review the generated logs and reports carefully. Address any reported issues (e.g., deprecated features, invalid objects, insufficient space).
4. Run AutoUpgrade in FIXUP Mode (if necessary):
   • If ANALYZE identified fixable issues, you can run:
     

     java -jar autoupgrade.jar -config upgrade.txt -mode FIXUP
     

   • Review logs to ensure fixes were applied successfully.
5. Run AutoUpgrade in DEPLOY Mode (the actual upgrade):
   • Ensure you have a valid backup before proceeding.
   • Execute:

     java -jar autoupgrade.jar -config upgrade.txt -mode DEPLOY
             

   • AutoUpgrade will perform the following (among other tasks):
     • Shut down the 19c database.
     • Start the database in UPGRADE mode using the 23c Oracle Home.
     • Run necessary upgrade scripts (catctl.pl, dbupgrade).
     • Compile invalid objects.
     • Open the database in READ WRITE mode.
     • Perform post-upgrade checks.
   • Monitor the console output and the log files generated by AutoUpgrade very closely.

Phase 4: Post-Upgrade Tasks and Validation

1. Review Upgrade Logs:
   • Examine the AutoUpgrade logs (job_<job_id>/*.log) for any errors, warnings, or unaddressed issues.
2. Database Sanity Checks:
   • Status: Verify the database instance is up and running in 23c.

             SELECT status FROM v$instance;
             

   • Version: Confirm the database version.

             SELECT * FROM v$version;
             

   • Component Status: Check the status of all database components.

             SELECT comp_name, version, status FROM dba_registry;
             

   • Invalid Objects: Recompile any remaining invalid objects.

             @?/rdbms/admin/utlrp.sql
             

   • Data Dictionary Health: Run utlprp.sql and utldbv.sql to ensure data dictionary consistency.
   • Timezone: Verify timezone files are up-to-date.
3. Update Configuration Files:
   • Modify listener.ora and tnsnames.ora to point to the new 23c Oracle Home and services.
   • Update any application connection strings.
   • Adjust pfile/spfile parameters as needed for 23c, considering new parameters and deprecated ones.
4. Gather New Statistics:
   • Run DBMS_STATS.GATHER_DICTIONARY_STATS and DBMS_STATS.GATHER_SCHEMA_STATS for all schemas to ensure the optimizer has up-to-date information for the new 23c environment.
5. Application Testing:
   • Critical Step: Thoroughly test all applications that connect to the database. Verify functionality, performance, and data integrity. This often involves a multi-stage testing process (e.g., unit, integration, user acceptance testing).
   • Pay close attention to queries, stored procedures, and any application logic that might be affected by 23c changes.
6. Performance Baseline and Tuning:
   • Establish a new performance baseline for the 23c database.
   • Monitor AWR reports and adjust initialization parameters or SQL statements for optimal performance.
   • Consider utilizing new 23c features that could improve performance (e.g., JSON enhancements, vector database capabilities).
7. Housekeeping:
   • Remove the old 19c Oracle Home after successful validation and a defined rollback period.
   • Clean up old log files and temporary spaces.

• Test Environment: Always perform a full dry run of the upgrade in a non-production test environment that mirrors your production setup.
• Downtime: Plan for adequate downtime. Even with AutoUpgrade, the database will be unavailable during parts of the process.
• Communication: Keep stakeholders informed about the upgrade schedule, progress, and any potential impacts.
• Rollback Plan: Have a clear and tested rollback plan in case of unforeseen issues. This relies heavily on your pre-upgrade backup.
• Expertise: If you're not experienced with Oracle upgrades, consider engaging Oracle Support or a qualified consultant.

By following these comprehensive steps, especially by leveraging the Oracle AutoUpgrade tool and performing thorough testing, you can significantly increase the chances of a successful and smooth upgrade from Oracle 19c to Oracle 23c.

Now that you have completed this module, you should be able to:
In this module, you have learned how to install and configure different stages of the database life cycle.

1. Identify improvements to configuration

The RPM-based installation method is one of the simplest and most efficient ways to install Oracle Database 23c Free – Developer Release on supported Linux systems (e.g., Oracle Linux, RHEL).
🔧 Prerequisites

1. Supported OS: Oracle Linux 8+, RHEL 8+, or compatible distributions.
2. Minimum RAM: 2 GB (4 GB recommended)
3. Disk Space: ~10 GB for installation
4. User and Groups: Oracle will automatically create the oracle user and required groups (dba, oinstall).

🧭 Step-by-Step Installation Guide

1. Download the RPM Package

   From Oracle’s official site: 👉 Oracle 23c Free RPM Download
   
   Example file:
   oracle-database-free-23c-1.0-1.el8.x86_64.rpm

2. Install the RPM

   Run as root or with sudo:

   sudo dnf localinstall oracle-database-free-23c-1.0-1.el8.x86_64.rpm

   This installs the software under:

   /opt/oracle/product/23c/dbhomeFree

3. Configure the Database

   Run the configuration script:

   sudo /etc/init.d/oracle-free-23c configure

---

It will:

• Set up a listener
• Create a starter database
• Set environment variables
• Print the temporary password for the SYS and SYSTEM users

Example output:

Database configuration completed successfully!
Connect to Oracle Database using one of the following connect strings:
   SQL> sqlplus sys/<password>@localhost:1521/FREEPDB1 as sysdba

🔁 Optional: Enable Auto-start

sudo systemctl enable oracle-free-23c
sudo systemctl start oracle-free-23c

🧪 Verify Installation
As the `oracle` user:

. /opt/oracle/product/23c/dbhomeFree/bin/oraenv
sqlplus / as sysdba

📌 Notes

• The RPM method is non-customizable during installation — it's optimized for ease.
• For more advanced or production environments, use the traditional zip install method.

In Oracle Database 23c, the `opatch` utility is the official Oracle tool for managing and inspecting Oracle software patches and components installed in an Oracle Home.
🧰 What Is OPatch?
`opatch` is a command-line utility located in the `$ORACLE_HOME/OPatch` directory. It is used for:

• Viewing installed patches
• Applying interim (one-off) patches
• Rolling back patches
• Checking inventory of installed software

To inspect installed patches and components in Oracle 23c, the opatch utility is the preferred tool. For example:

$ORACLE_HOME/OPatch/opatch lsinventory

This command provides detailed information about the Oracle Home, including applied patches and their statuses. It's a more reliable method than using OUI for this purpose.

Oracle Interim Patch Installer version 12.2.0.1.38
Copyright (c) 2023, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/23c/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 12.2.0.1.38
OUI version       : 23.0.0.0.0
Log file location : /u01/app/oracle/product/23c/dbhome_1/cfgtoollogs/opatch/opatch2025-05-23_09-15-23AM_1.log

Lsinventory Output
--------------------------------------------------------------------------------
Local Machine : server1.oracle.com

Installed Top-level Products (1):

Oracle Database 23c                                              23.3.0.0.0
There are 2 products installed in this Oracle Home.

Interim patches (2) :
Patch 34678910     : applied on Fri Apr 12 2025 12:00:00 PM EDT
   Created on 9 Apr 2025, 08:30:00 PST
   Bugs fixed: 33323321, 34215671
Patch 34899234     : applied on Fri Apr 19 2025 05:43:22 PM EDT
   Created on 17 Apr 2025, 04:45:00 PST
   Bugs fixed: 34115609

--------------------------------------------------------------------------------

🔍 Optional: View Only Patch IDs
To extract just the patch numbers:

opatch lsinventory -patch_id

📎 Additional Tips:

• You must run `opatch` as the Oracle software owner (e.g., oracle).
• Do not run as root, unless applying patches requiring root scripts.
• Use the latest version of OPatch compatible with Oracle 23c — Oracle periodically releases updates.

You were introduced to the following terms in this module.

1. Universal Installer: a software tool that is invoked to install or remove Oracle software and any software that is packaged for installation using Oracle's Software Packager tool.
2. Multiple Oracle Homes: this allows you to install more than one version of Oracle software on the same machine. The paths andother operating system variables are set according to the currently selected Oracle home.
   Oracle 23c (and Oracle Database in general) fully supports "multiple Oracle Homes" on the same system.
   Here's why and what it means:
   • Coexistence of Releases: You can install different versions of Oracle Database (e.g., Oracle 19c, Oracle 21c, and Oracle 23c) on the same server, each in its own distinct Oracle Home directory. This is crucial for environments that need to support applications built on different database versions, or for performing phased upgrades.
   • Isolation: Each Oracle Home is an independent installation of the Oracle software binaries. This provides isolation between different database instances, preventing conflicts and allowing you to manage and patch them separately.
   • Optimal Flexible Architecture (OFA): Oracle highly recommends following OFA guidelines when setting up multiple Oracle Homes. OFA promotes a standardized directory structure that simplifies administration and maintenance, especially in environments with many databases or different versions.
   • Upgrade Scenarios: When upgrading to a new Oracle Database release (like 23c), you typically install the new software into a new Oracle Home and then upgrade your existing databases to run on that new home. This minimizes downtime and provides a fallback in case of issues.
   
   In summary, the concept of "Multiple Oracle Homes" is a fundamental and supported feature of Oracle Database, including Oracle 23c, allowing for flexible and robust deployments.
3. Optimal Flexible Architecture (OFA): the Oracle-recommended standard that defines the naming conventions and directory structure for the location of Oracle software executable files and database storage files.

In the next module, you will review the concept of table joins and see how Oracle implements outer joins and adds new functionality with special query formats.