Oracle 19c to 23ai Upgrade: AutoUpgrade with Custom Fixups [gabv]

Upgrading Oracle Database 19c to 23ai for PeopleSoft Environments: A Deep Dive with AutoUpgrade and Custom Fixups

As the digital landscape evolves, so does the demand for more intelligent, performant, and secure database systems. Oracle Database 23ai, with its groundbreaking features like AI Vector Search, JSON Relational Duality, Operational Property Graphs, and JavaScript in the Database, represents a significant leap forward. For organizations running critical applications like Oracle PeopleSoft, upgrading to 23ai isn't just about gaining access to these new capabilities; it's about ensuring the underlying data platform remains robust, secure, and capable of supporting future innovation and operational efficiency. This article, penned from the trenches of enterprise technology, will guide you through the intricate process of upgrading your Oracle Database 19c to 23ai, leveraging the powerful AutoUpgrade tool, with a particular focus on crafting custom fixups to navigate specific challenges often encountered in complex environments such as those supporting PeopleSoft.

The transition from a long-term support release like 19c to a feature-rich version like 23ai requires meticulous planning and execution. While manual upgrade paths exist, Oracle's AutoUpgrade utility has become the de-facto standard for its automation, intelligence, and reliability. It streamlines the entire upgrade lifecycle, from pre-checks and pre-fixups to the actual upgrade and post-upgrade validations, significantly reducing human error and downtime. For PeopleSoft administrators, who often manage databases with unique configurations and a vast number of objects, the ability to incorporate custom fixups into the AutoUpgrade process is invaluable, allowing for tailored solutions to potential upgrade roadblocks.

Prerequisites for a Seamless Upgrade

Before embarking on the upgrade journey, a thorough preparation phase is paramount. Skipping any of these steps can lead to unforeseen complications and extended downtime. Here's a comprehensive checklist:

• Software Acquisition:
  • Source Database: Ensure your Oracle Database 19c is healthy and running the latest recommended Release Update (RU) or Release Update Revision (RUR). While not strictly mandatory for AutoUpgrade, it minimizes known issues.
  • Target Database Binaries: Download the Oracle Database 23ai software from the Oracle Support website. This should be the full installation media for your specific operating system.
  • AutoUpgrade Tool: The autoupgrade.jar tool is typically included in the target Oracle Home ($ORACLE_HOME/rdbms/admin/autoupgrade.jar) of 21c and later. Always use the version from the *target* Oracle Home or download the latest version from MOS Note 2485457.1.
• Operating System Requirements:
  • Compatibility: Verify that your operating system (e.g., Oracle Linux 8/9, RHEL 8/9) is certified for Oracle Database 23ai.
  • Resource Allocation: Ensure sufficient CPU, memory, and swap space. Oracle 23ai generally requires more resources than 19c. A minimum of 16GB RAM is recommended for typical PeopleSoft databases, with more for larger instances.
  • Disk Space: Allocate ample disk space for the new 23ai Oracle Home (typically 10-15 GB), additional space for temporary files during the upgrade, and potential growth of database files, flashback logs, and archive logs.
• User Privileges:
  • Perform the upgrade as the oracle user.
  • root user access is required for executing root.sh scripts and potentially modifying kernel parameters.
• Network Configuration:
  • Verify network connectivity, especially for Oracle Real Application Clusters (RAC) environments or databases with remote listeners.
  • Ensure firewall rules allow communication between the database server and any application tiers (e.g., PeopleSoft application servers, web servers).
• Database Health and Preparation:
  • Database Validation: Run DB_VALIDATION checks.
  • Dictionary Statistics: Gather dictionary statistics on the 19c database:

    
                    SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
                    SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
                    

  • PeopleSoft Schema Statistics: For PeopleSoft environments, it's crucial to ensure PeopleSoft-specific schema statistics are up-to-date. While AutoUpgrade focuses on the dictionary, PeopleSoft performance often relies on accurate application schema statistics.

    
                    SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'SYSADM', OPTIONS => 'GATHER AUTO', CASCADE => TRUE, DEGREE => DBMS_STATS.AUTO_DEGREE);
                    -- Repeat for other PeopleSoft schemas if applicable
                    

  • Invalid Objects: Recompile any invalid objects in the 19c database:

    
                    SQL> @?/rdbms/admin/utlrp.sql
                    

    Verify no invalid objects remain:

    
                    SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
                    

  • Timezone File: Ensure the 19c database is using the latest timezone file. If not, update it before the upgrade. AutoUpgrade will handle timezone file updates during the upgrade, but a consistent starting point is best.

    
                    SQL> SELECT VERSION FROM V$TIMEZONE_FILE;
                    

  • PeopleSoft Application Shutdown: Critically, the PeopleSoft application (application servers, web servers, process schedulers) connected to the database must be completely shut down before initiating the database upgrade. This prevents active connections during the upgrade process and ensures data integrity.
• Comprehensive Backup: This is the single most critical prerequisite.
  • RMAN Full Backup: Perform a full RMAN backup of your 19c database. Include control file and spfile.

    
                    RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
                    RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; -- Or your organizational standard
                    RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
                    

  • Guaranteed Restore Point (GRP): Create a Guaranteed Restore Point. This allows for a fast and reliable rollback if the upgrade encounters unrecoverable issues.

    
                    SQL> CREATE RESTORE POINT PRE_UPGRADE_19C GUARANTEE FLASHBACK DATABASE;
                    

    Verify the GRP:

    
                    SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
                    

Step-by-Step Implementation: Oracle 19c to 23ai with AutoUpgrade and Custom Fixups

With prerequisites met, we can proceed with the upgrade. This process will involve preparing the 23ai Oracle Home, configuring AutoUpgrade, defining custom fixups, and executing the upgrade in stages.

1. Prepare the Target Oracle Home (Oracle Database 23ai)

First, install the 23ai software binaries without creating a database.

# Create the new Oracle Home directory
mkdir -p /u01/app/oracle/product/23.0.0/dbhome_1
chown -R oracle:oinstall /u01/app/oracle/product/23.0.0/dbhome_1

# Unzip the 23ai software
unzip LINUX.X64_23ai_database.zip -d /u01/app/oracle/product/23.0.0/dbhome_1

# Navigate to the installer directory
cd /u01/app/oracle/product/23.0.0/dbhome_1

# Run the installer for software-only installation
./runInstaller -silent -responseFile /path/to/db_install.rsp -waitForCompletion

# Example db_install.rsp content (minimal for software-only)
# ORACLE_BASE="/u01/app/oracle"
# ORACLE_HOME="/u01/app/oracle/product/23.0.0/dbhome_1"
# INVENTORY_LOCATION="/u01/app/oraInventory"
# INSTALL_TYPE="SOFTWARE_ONLY"
# SELECTED_LANGUAGES="en"
# UNIX_GROUP_NAME="oinstall"

# As root, execute root.sh
/u01/app/oracle/product/23.0.0/dbhome_1/root.sh

Ensure the environment variables for the new 23ai Oracle Home are set correctly, especially ORACLE_HOME and PATH, when working with the new binaries.

2. Prepare AutoUpgrade Configuration File (config.txt)

The config.txt file is the heart of the AutoUpgrade process. It defines the source and target environments, database parameters, and specific upgrade instructions. Create this file in a dedicated directory, for example, /home/oracle/autoupgrade_23ai.

# Example config.txt for a non-CDB to CDB upgrade (common for PeopleSoft) or PDB upgrade

global.autoupg_log_dir=/home/oracle/autoupgrade_23ai/logs
global.temp_dir=/home/oracle/autoupgrade_23ai/temp

# Define the upgrade for a specific database (e.g., PSFIN)
upg1.dbname=PSFIN
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.target_version=23
upg1.sid=PSFIN
upg1.log_dir=/home/oracle/autoupgrade_23ai/logs/PSFIN
upg1.db_unique_name=PSFIN
upg1.upgrade_node=localhost
upg1.start_time=now
upg1.timezone_file=FULL
upg1.alter_session_parameters=
upg1.upg_options=-D " -n"
upg1.run_utlrp=yes
upg1.remove_underscore_parameters=yes
upg1.listeners=LISTENER_PSFIN # If you have a specific listener for this DB

# If converting a non-CDB to PDB in a new CDB
# upg1.target_cdb=CDB23AI # Name of the new CDB to create or use
# upg1.target_pdb_name=PSFIN_PDB # Name of the PDB to create
# upg1.cdb_init_file=/u01/app/oracle/product/23.0.0/dbhome_1/dbs/initCDB23AI.ora # Path to init file for the new CDB

# Custom fixups section - crucial for PeopleSoft environments
# This tells AutoUpgrade where to find your custom scripts
upg1.pre_upgrade_fixups_dir=/home/oracle/autoupgrade_23ai/fixups/pre
upg1.post_upgrade_fixups_dir=/home/oracle/autoupgrade_23ai/fixups/post

Key parameters explained:

• global.autoupg_log_dir: Directory for AutoUpgrade's global logs.
• upg1.dbname: The database name.
• upg1.source_home: Path to the 19c Oracle Home.
• upg1.target_home: Path to the 23ai Oracle Home.
• upg1.target_version: The target database version (e.g., 23).
• upg1.sid: The SID of the database to be upgraded.
• upg1.log_dir: Dedicated log directory for this specific database upgrade.
• upg1.timezone_file=FULL: Instructs AutoUpgrade to update the timezone file to the latest version.
• upg1.pre_upgrade_fixups_dir and upg1.post_upgrade_fixups_dir: These are critical for defining directories where AutoUpgrade will look for your custom SQL scripts to run before and after the main upgrade process.

3. Custom Fixups for PeopleSoft/Specific Scenarios

Custom fixups are SQL scripts executed by AutoUpgrade at specific points (pre-upgrade, post-upgrade) to address environment-specific issues or apply desired configurations. For PeopleSoft, these might include:

• Handling Deprecated Parameters: Some 19c parameters might be deprecated or behave differently in 23ai. A pre-upgrade fixup can remove or adjust these.
• Recompiling Specific PeopleSoft Objects: While AutoUpgrade runs utlrp.sql, if you know of specific PeopleSoft objects or schemas that are particularly sensitive or prone to invalidation during upgrades, you might want to force a recompile.
• Adjusting PeopleSoft-Specific Database Settings: For example, ensuring optimizer_adaptive_features is set correctly for PeopleSoft workloads, or modifying cursor_sharing behavior if PeopleSoft customizations rely on specific literal matching.
• Cleaning Up Old Objects: Removing remnants of old PeopleTools upgrades or custom objects that are no longer needed.

Let's create an example custom fixup. Imagine a scenario where a specific 19c parameter, _optimizer_extended_stats_usage_control, was set in the PeopleSoft environment for a particular reason, but it's known to cause issues or is no longer relevant in 23ai. Or perhaps, you want to ensure a specific PeopleSoft schema is recompiled immediately post-upgrade.

Create the directory structure:

mkdir -p /home/oracle/autoupgrade_23ai/fixups/pre
mkdir -p /home/oracle/autoupgrade_23ai/fixups/post

Example Pre-Upgrade Fixup (remove_deprecated_param.sql): This script would be placed in /home/oracle/autoupgrade_23ai/fixups/pre. It attempts to remove a specific deprecated parameter from the SPFILE.

-- File: /home/oracle/autoupgrade_23ai/fixups/pre/remove_deprecated_param.sql
SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
    v_param_name VARCHAR2(100) := '_optimizer_extended_stats_usage_control';
    v_value VARCHAR2(500);
BEGIN
    SELECT value INTO v_value FROM V$PARAMETER WHERE NAME = v_param_name AND ISMODIFIED != 'FALSE';
    IF v_value IS NOT NULL THEN
        EXECUTE IMMEDIATE 'ALTER SYSTEM RESET ' || v_param_name || ' SCOPE=SPFILE';
        DBMS_OUTPUT.PUT_LINE('Parameter ' || v_param_name || ' reset successfully in SPFILE.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Parameter ' || v_param_name || ' not found or not modified in SPFILE.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Parameter ' || v_param_name || ' not found in V$PARAMETER.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error resetting parameter ' || v_param_name || ': ' || SQLERRM);
END;
/

Example Post-Upgrade Fixup (recompile_apps_objects.sql): This script would be placed in /home/oracle/autoupgrade_23ai/fixups/post. It ensures all objects in the SYSADM schema (common for PeopleSoft) are recompiled.

-- File: /home/oracle/autoupgrade_23ai/fixups/post/recompile_apps_objects.sql
SET ECHO ON;
SET SERVEROUTPUT ON;

BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(SCHEMA => 'SYSADM', COMPILE_ALL => TRUE);
    DBMS_OUTPUT.PUT_LINE('SYSADM schema objects recompiled.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error recompiling SYSADM schema: ' || SQLERRM);
END;
/

-- Also run utlrp.sql again for good measure, if not already handled by AutoUpgrade
-- @?/rdbms/admin/utlrp.sql

AutoUpgrade will execute all .sql files found in these directories in alphabetical order.

4. Run AutoUpgrade in Analyze Mode

The analyze mode is crucial for identifying potential issues before committing to the upgrade. It performs comprehensive pre-checks and generates a detailed report.

# Set the ORACLE_HOME to the *source* 19c home for initial setup
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Navigate to the directory where autoupgrade.jar is located (use the one from the 23ai home)
cd /u01/app/oracle/product/23.0.0/dbhome_1/rdbms/admin/

# Execute AutoUpgrade in analyze mode
java -jar autoupgrade.jar -config /home/oracle/autoupgrade_23ai/config.txt -mode analyze

Review the generated logs in /home/oracle/autoupgrade_23ai/logs/PSFIN. Pay close attention to the prechecks.log and status.html reports. Address any WARNINGs or ERRORs before proceeding.

5. Run AutoUpgrade in Fixups Mode (Optional but Recommended)

If AutoUpgrade identifies any critical pre-upgrade fixups, you can apply them in fixups mode. This step applies the fixups identified by AutoUpgrade's internal checks, not your custom ones (which are run during deploy). This step is useful for resolving known issues that AutoUpgrade can automatically address.

java -jar autoupgrade.jar -config /home/oracle/autoupgrade_23ai/config.txt -mode fixups

Again, review the logs to ensure fixups were applied successfully.

6. Run AutoUpgrade in Deploy Mode

This is the actual upgrade step. AutoUpgrade will shut down the 19c database, perform the upgrade to 23ai, and then restart it.

java -jar autoupgrade.jar -config /home/oracle/autoupgrade_23ai/config.txt -mode deploy

Monitor the console output and the logs in /home/oracle/autoupgrade_23ai/logs/PSFIN. The upgrade process can take several hours depending on the database size and complexity. AutoUpgrade logs provide detailed progress, including the execution of your custom fixups.

Important: Do not interrupt the deploy mode once it has started. AutoUpgrade is designed to handle failures and provide rollback options, but manual intervention during the core upgrade can complicate recovery.

7. Post-Upgrade Steps

Once AutoUpgrade completes, perform these essential checks and tasks:

• Verify Database Status:

  
          export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
          export PATH=$ORACLE_HOME/bin:$PATH
          sqlplus / as sysdba
          SQL> SELECT STATUS, VERSION FROM V$INSTANCE;
          SQL> SELECT COMP_ID, COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;
          

  All components in DBA_REGISTRY should show a VALID status.
• Run utlrp.sql: Even if AutoUpgrade runs it, it's good practice to run it again.

  
          SQL> @?/rdbms/admin/utlrp.sql
          

• Gather Dictionary Statistics:

  
          SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
          SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
          

• Update PeopleSoft Configuration:
  • TNSNames.ora: Update the tnsnames.ora file on all PeopleSoft application servers, web servers, and process schedulers to point to the new 23ai listener and potentially the new service name if it changed (e.g., if you converted to PDB).
  • Database Configuration: Review any PeopleSoft-specific database configuration parameters (e.g., in psdb.txt or similar files within your PeopleSoft domain configurations) to ensure they are compatible and optimized for 23ai.
• PeopleSoft Application Startup and Testing:
  • Start the PeopleSoft application servers, web servers, and process schedulers.
  • Perform comprehensive functional testing of all critical PeopleSoft modules. This should include common transactions, batch processes, reporting, and integrations.
  • Monitor performance closely in the initial days post-upgrade.
• Remove Guaranteed Restore Point: Once you are confident in the stability of the upgraded database, remove the GRP.

  
          SQL> DROP RESTORE POINT PRE_UPGRADE_19C;
          

Security Considerations

Upgrading to a new database version is an opportune moment to review and enhance your security posture:

• Principle of Least Privilege: Ensure all database users, including the PeopleSoft schema owner, have only the necessary privileges.
• Secure Target Oracle Home: Verify file system permissions on the new 23ai Oracle Home.
• Network Security: Update firewall rules if necessary. Configure listener security (e.g., valid node checking).
• Patching: Establish a regular patching schedule for 23ai, including Release Updates (RUs) and Security Updates.
• Auditing: Review and enhance your database auditing policies, leveraging 23ai's advanced auditing capabilities.
• Transparent Data Encryption (TDE): Consider implementing TDE for sensitive PeopleSoft data at rest, if not already in place. 23ai offers enhanced TDE management.
• New Security Features: Explore and potentially implement new security features in 23ai such as SQL Firewall, enhancements to Data Redaction, and improved privilege analysis.

Best Practices for a Smooth Upgrade

• Test, Test, Test: Always perform the upgrade in a non-production environment (e.g., Development, Test, QA) that mirrors production as closely as possible. Conduct multiple dry runs.
• Document Everything: Keep a detailed log of all steps, commands, and observations. This is invaluable for troubleshooting and future upgrades.
• Engage Oracle Support: For complex environments or unusual issues, proactively engage with Oracle Support. They can provide guidance and assist with critical problems.
• Monitor Resources: During the upgrade, monitor CPU, memory, and I/O utilization to identify potential bottlenecks.
• Understand Rollback: Be familiar with AutoUpgrade's rollback mechanism and, more importantly, how to restore from your RMAN backup and Guaranteed Restore Point in case of catastrophic failure.
• Keep AutoUpgrade Updated: Always use the latest version of autoupgrade.jar, as Oracle frequently releases updates with bug fixes and new features.
• PeopleSoft Customizations: Be aware that extensive PeopleSoft customizations might introduce unique challenges. Thorough testing post-upgrade is crucial to validate these customizations.
• Post-Upgrade Optimization: After the upgrade, review AWR reports and SQL Tuning Advisor recommendations to optimize performance on 23ai.

Frequently Asked Questions (FAQ)

Q1: Can I upgrade a Pluggable Database (PDB) directly using AutoUpgrade?

Absolutely. AutoUpgrade fully supports upgrading Pluggable Databases (PDBs). If your 19c database is a PDB, AutoUpgrade can directly upgrade it within its existing CDB or migrate it to a new 23ai CDB. If your 19c database is a non-CDB, AutoUpgrade can also convert it into a PDB within a new 23ai Container Database (CDB), which is a common and recommended architecture for PeopleSoft environments moving forward.

Q2: What should I do if the AutoUpgrade process fails midway?

AutoUpgrade is designed with resilience in mind. If it fails, it will attempt to rollback to the pre-upgrade state. You can check the status using java -jar autoupgrade.jar -config config.txt -mode status. If