Mastering Oracle 19c to 23ai Upgrade with AutoUpgrade & Custom Fixups

Upgrade Oracle Database 19c to 23ai with AutoUpgrade. Learn to implement custom fixups for a seamless, efficient database upgrade process.

Overview: Navigating the Future with Oracle Database 23ai for PeopleSoft

As a senior technology writer at TechNews Venture, I've witnessed countless technological shifts, but few are as transformative as the advent of Oracle Database 23ai. This "AI Database" represents a monumental leap forward, integrating artificial intelligence capabilities directly into the database kernel. For organizations running critical enterprise applications like Oracle PeopleSoft, the decision to upgrade from a stable Long Term Support (LTS) release like 19c to 23ai isn't merely about staying current; it's about unlocking a new paradigm of operational efficiency, advanced analytics, and intelligent application development.

Oracle Database 23ai introduces groundbreaking features such as AI Vector Search for semantic queries, JSON Relational Duality Views for flexible data models, and native Kafka integration. For PeopleSoft environments, these features hold immense potential. Imagine enhancing PeopleSoft Global Search with AI Vector Search for more intuitive and context-aware results across HR, Campus Solutions, or Financials modules. Or leveraging JSON Relational Duality to simplify integration with modern microservices architectures while maintaining the transactional integrity of PeopleSoft's underlying relational data. The performance enhancements and developer productivity gains are equally compelling.

Upgrading a PeopleSoft database, often complex and mission-critical, demands a robust, automated, and reliable process. This is where Oracle's AutoUpgrade utility shines. AutoUpgrade provides a comprehensive framework for automating the end-to-end database upgrade process, from pre-checks and fixups to the actual upgrade and post-upgrade tasks. However, real-world PeopleSoft implementations frequently involve unique configurations, customisations, or specific data integrity requirements that necessitate a more tailored approach. This article will delve into leveraging AutoUpgrade's powerful capabilities, specifically focusing on how to integrate custom fixups to address these bespoke challenges, ensuring a smooth and successful transition from Oracle Database 19c to 23ai in a PeopleSoft context.

Prerequisites for a Seamless Transition

A successful database upgrade, especially for a critical PeopleSoft environment, hinges on meticulous preparation. Skipping any of these prerequisites can lead to costly delays or, worse, data corruption.

System Requirements

Operating System: Oracle Database 23ai supports recent Linux distributions. For PeopleSoft, Oracle Linux 8 or 9 (or RHEL equivalents) is highly recommended. Ensure your OS is 64-bit and meets the minimum kernel version requirements (e.g., for Oracle Linux 8, kernel 4.18.0-193.el8 or later).
Memory (RAM): Minimum 2 GB, but for PeopleSoft production environments, 16 GB or more is standard, depending on the number of users and modules.
CPU: At least 2 cores. Production PeopleSoft systems often require 8+ cores.
Disk Space:
- Oracle Home for 23ai binaries: Approximately 10-15 GB.
- Database files: Ensure sufficient space for the existing 19c database, plus an additional 20-30% for temporary upgrade files, redo logs, and potential growth during and after the upgrade.
- Archivelog destination: Ample space for archivelogs generated during the upgrade process.
- Working directory for AutoUpgrade: At least 5 GB for logs and temporary files.
Network Configuration:
- Verify `listener.ora` and `tnsnames.ora` files are correctly configured for both the source (19c) and target (23ai) environments.
- Ensure network connectivity between the application servers (PeopleSoft application server, web server, process scheduler) and the new 23ai database server.

Software Downloads

Oracle Database 23ai Software: Download the full installation media from Oracle eDelivery or My Oracle Support (MOS). Choose the appropriate edition (Enterprise Edition is typical for PeopleSoft).
Latest AutoUpgrade Utility: While AutoUpgrade is bundled with 23ai, it's always best practice to download the latest standalone version from MOS (Doc ID 2485457.1) to ensure you have the most recent bug fixes and enhancements.
Database Patch Set Updates (PSUs) for 19c: Before upgrading to 23ai, ensure your 19c database is on a recent Release Update (RU) or Release Update Revision (RUR), ideally 19.19.0.0.0 or later. This significantly reduces the likelihood of encountering known issues during the upgrade process.

Pre-Upgrade Checks and Preparations

Full RMAN Backup: This is non-negotiable. Perform a full, consistent RMAN backup of your 19c PeopleSoft database.
```
RMAN> connect target /
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup database plus archivelog delete input;
RMAN> alter database open;
        
```
Test the restore process on a non-production environment.
Gather Performance Baselines: Collect AWR reports, Statspack data, or custom performance metrics from your 19c PeopleSoft environment. This will be crucial for post-upgrade performance validation.
Database Housekeeping:
- Resolve all invalid objects:
```
SQL> SELECT owner, object_name, object_type FROM dba_invalid_objects;
SQL> @?/rdbms/admin/utlrp.sql
                
```
- Clean up recycle bin: `PURGE DBA_RECYCLEBIN;`
- Remove old trace files, alert logs, and unnecessary diagnostic data.
Disable Application Access: Coordinate with PeopleSoft functional teams to schedule downtime. Ensure all PeopleSoft application servers, web servers, and process schedulers are shut down and disconnected from the database.
Verify `COMPATIBLE` Parameter: Ensure your 19c database's `COMPATIBLE` parameter is set to `19.0.0` or higher.
```
SQL> SHOW PARAMETER COMPATIBLE;
        
```
Run `preupgrade.jar`: Even though AutoUpgrade includes pre-checks, running the `preupgrade.jar` utility manually provides a detailed report of potential issues and recommended actions.
```
cd /path/to/23ai_oracle_home/rdbms/admin
java -jar preupgrade.jar
        
```
Review the generated `preupgrade.log` and `preupgrade_fixups.sql` files thoroughly.
Time Zone File Update: If your 19c database uses an older time zone file version, consider upgrading it prior to the 23ai upgrade, or plan to use AutoUpgrade's built-in time zone update feature.

Step-by-Step Implementation: Leveraging AutoUpgrade with Custom Fixups

The core of our upgrade strategy involves AutoUpgrade, tailored with custom fixups to address PeopleSoft-specific nuances.

Environment Setup

First, install the Oracle Database 23ai binaries into a new Oracle Home. Do NOT install into the existing 19c Oracle Home.


# Create base directory for Oracle installations
mkdir -p /u01/app/oracle
chown oracle:oinstall /u01/app/oracle

# Install 23ai binaries (e.g., using the runInstaller)
# Assuming 23ai software is in /tmp/23ai_software
cd /tmp/23ai_software
./runInstaller -silent -responseFile /tmp/23ai_software/response/db_install.rsp \
ORACLE_HOME="/u01/app/oracle/product/23.0.0/dbhome_1" \
ORACLE_BASE="/u01/app/oracle" \
INVENTORY_LOCATION="/u01/app/oraInventory" \
UNIX_GROUP_NAME="oinstall" \
oracle.install.db.OSDBA_GROUP="dba" \
oracle.install.db.OSOPER_GROUP="oper" \
oracle.install.db.OSBACKUPDBA_GROUP="backupdba" \
oracle.install.db.OSDGDBA_GROUP="dgdba" \
oracle.install.db.OSRACDBA_GROUP="racdba" \
oracle.install.db.OSKMDBA_GROUP="kmdba" \
oracle.install.db.OSASMDBA_GROUP="asmadmin" \
oracle.install.db.OSASMOPER_GROUP="asmoper" \
oracle.install.db.OSASM_GROUP="asmadmin" \
DECLINE_SECURITY_UPDATES=true

# Set up environment variables for the oracle user
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 # Common for PeopleSoft

Create a dedicated directory for AutoUpgrade logs and configuration files:


mkdir -p /u01/app/oracle/autoupgrade_logs
chown oracle:oinstall /u01/app/oracle/autoupgrade_logs

Copy the latest `autoupgrade.jar` to a convenient location, e.g., `/u01/app/oracle/autoupgrade`.

Creating the AutoUpgrade Configuration File

The `config.txt` file is the heart of the AutoUpgrade process. It defines the source and target environments, along with various upgrade parameters. For a PeopleSoft database named `PTSYS`, migrating from 19c at `/u01/app/oracle/product/19.0.0/dbhome_1` to 23ai at `/u01/app/oracle/product/23.0.0/dbhome_1`, your `config.txt` might look like this:


# AutoUpgrade Configuration File for PeopleSoft PTSYS DB Upgrade

# Global parameters (optional, can be overridden by specific database parameters)
global.autoupg_log_dir=/u01/app/oracle/autoupgrade_logs
global.target_version=23

# Database specific parameters for PTSYS
upg1.dbname=PTSYS
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.start_time=NOW
upg1.log_dir=/u01/app/oracle/autoupgrade_logs/PTSYS
upg1.upgrade_node=localhost
upg1.db_user=SYS
upg1.db_user_password=sys_password_here # Ideally use a wallet or direct prompt
upg1.run_fixups=YES
upg1.timezone_upg=YES
upg1.custom_fixups=/u01/app/oracle/autoupgrade/custom_fixups.xml
upg1.remove_underscore_parameters=YES # Recommended for 23ai for cleanup
upg1.target_listener=LISTENER_23AI # If you have a dedicated listener for 23ai
upg1.sid=PTSYS # If using SID, otherwise service_name can be used
# upg1.service_name=PTSYS_SERVICE # Alternative to sid

`upg1.dbname`: The SID of your PeopleSoft database instance.
`upg1.source_home`: The full path to your existing Oracle 19c Home.
`upg1.target_home`: The full path to your newly installed Oracle 23ai Home.
`upg1.log_dir`: A specific directory for logs related to this database upgrade.
`upg1.db_user` and `upg1.db_user_password`: Credentials for a user with `SYSDBA` privileges. For production, consider using a secure method like an Oracle Wallet or allowing AutoUpgrade to prompt for the password.
`upg1.run_fixups=YES`: Essential for AutoUpgrade to apply recommended pre- and post-upgrade fixups.
`upg1.timezone_upg=YES`: Instructs AutoUpgrade to update the database time zone files if necessary.
`upg1.custom_fixups`: This is the crucial parameter for integrating your custom scripts. It points to an XML file that defines your custom fixups.

Understanding and Implementing Custom Fixups

Custom fixups allow you to inject specific SQL scripts or shell commands into the AutoUpgrade workflow at predefined stages (e.g., `preupgrade`, `postupgrade`). This is invaluable for PeopleSoft environments where you might encounter:

Specific invalid objects related to PeopleTools or application code that need unique handling.
Deprecated database features used by older PeopleSoft versions.
Schema-specific tasks like rebuilding indexes or gathering statistics on critical PeopleSoft tables immediately after the upgrade.
Adjusting database parameters that AutoUpgrade doesn't handle automatically.

Let's create an example. Suppose a specific PeopleSoft customisation created a materialized view that becomes invalid due to a data type change in 23ai, and you need to refresh it immediately after the upgrade. Or perhaps a deprecated `_` parameter needs to be explicitly removed or adjusted.

Step 1: Create the SQL script for the fixup.

File: `/u01/app/oracle/autoupgrade/scripts/refresh_ps_mviews.sql`


-- refresh_ps_mviews.sql
-- This script refreshes specific PeopleSoft materialized views post-upgrade.

SET ECHO ON
SET SERVEROUTPUT ON

WHENEVER SQLERROR EXIT FAILURE;

ALTER SESSION SET CONTAINER = CDB$ROOT; -- If PDB
ALTER SESSION SET CURRENT_SCHEMA = SYS;

PROMPT Refreshing PeopleSoft custom materialized view: PS_CUSTOM_MV1
BEGIN
    DBMS_MVIEW.REFRESH('SYSADM.PS_CUSTOM_MV1', 'C');
    DBMS_OUTPUT.PUT_LINE('Materialized view PS_CUSTOM_MV1 refreshed successfully.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error refreshing PS_CUSTOM_MV1: ' || SQLERRM);
        RAISE;
END;
/

PROMPT Checking for other invalid objects in SYSADM schema...
SELECT owner, object_name, object_type
FROM dba_invalid_objects
WHERE owner = 'SYSADM';

EXIT SUCCESS;

Step 2: Create the XML definition for the custom fixup.

File: `/u01/app/oracle/autoupgrade/custom_fixups.xml`


<?xml version="1.0" encoding="UTF-8"?>
<fixups>
  <fixup name="RefreshPeopleSoftMViews"
         description="Refresh specific PeopleSoft materialized views after upgrade"
         stage="POSTUPGRADE"
         type="SQL">
    <script>/u01/app/oracle/autoupgrade/scripts/refresh_ps_mviews.sql</script>
    <database_user>SYS</database_user>
    <database_password>sys_password_here</database_password> <!-- Consider wallet for production -->
    <error_on_failure>true</error_on_failure>
    <timeout>600</timeout>
  </fixup>

  <fixup name="CheckPeopleSoftSchemaInvalidObjects"
         description="Check for invalid objects in PeopleSoft schemas after upgrade"
         stage="POSTUPGRADE"
         type="SQL">
    <script>
      <![CDATA[
        SET ECHO ON
        SET SERVEROUTPUT ON
        PROMPT Checking for invalid objects in PeopleSoft schemas (SYSADM, SYSMAIN etc.)...
        SELECT owner, object_name, object_type, status
        FROM dba_objects
        WHERE status = 'INVALID'
          AND owner IN ('SYSADM', 'SYSMAIN', 'PPLSOFT')
        ORDER BY owner, object_type, object_name;
        EXIT SUCCESS;
      ]]>
    </script>
    <database_user>SYS</database_user>
    <database_password>sys_password_here</database_password>
    <error_on_failure>false</error_on_failure> <!-- Don't stop upgrade if just reporting invalids -->
    <timeout>120</timeout>
  </fixup>

  <fixup name="GatherPeopleSoftSchemaStats"
         description="Gather statistics for critical PeopleSoft schemas post-upgrade"
         stage="POSTUPGRADE"
         type="SQL">
    <script>
      <![CDATA[
        SET ECHO ON
        SET SERVEROUTPUT ON
        PROMPT Gathering schema statistics for SYSADM...
        BEGIN
            DBMS_STATS.GATHER_SCHEMA_STATS(
                ownname => 'SYSADM',
                degree => DBMS_STATS.AUTO_DEGREE,
                cascade => TRUE,
                options => 'GATHER AUTO',
                estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                block_sample => TRUE,
                method_opt => 'FOR ALL COLUMNS SIZE AUTO',
                no_invalidate => FALSE
            );
            DBMS_OUTPUT.PUT_LINE('Statistics gathered for SYSADM schema.');
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error gathering stats for SYSADM: ' || SQLERRM);
                RAISE;
        END;
        /
        EXIT SUCCESS;
      ]]>
    </script>
    <database_user>SYS</database_user>
    <database_password>sys_password_here</database_password>
    <error_on_failure>true</error_on_failure>
    <timeout>3600</timeout>
  </fixup>

</fixups>

`name`: A unique identifier for the fixup.
`stage`: When the fixup should run (`PREUPGRADE`, `POSTUPGRADE`, `PRECHECKS`, `POSTCHECKS`). `POSTUPGRADE` is common for PeopleSoft-specific adjustments.
`type`: `SQL` for SQL scripts, `SHELL` for shell scripts.
`script`: Path to the SQL or shell script, or inline `CDATA` for SQL.
`database_user`, `database_password`: Credentials for executing the script.
`error_on_failure`: If `true`, AutoUpgrade stops if the fixup fails.
`timeout`: Maximum time in seconds for the fixup to run.

Executing AutoUpgrade

With the configuration file and custom fixups in place, you can now run AutoUpgrade.

1. Analyze Mode: This is crucial for identifying potential issues without making any changes to the database. It generates detailed reports.


cd /u01/app/oracle/autoupgrade
java -jar autoupgrade.jar -config config.txt -mode ANALYZE

Review the generated logs in `/u01/app/oracle/autoupgrade_logs/PTSYS/PTSYS_upg.log` and the HTML report for any warnings or errors. Address all critical issues before proceeding.

2. Fixups Mode (Optional, if pre-upgrade fixups are defined): If AutoUpgrade or your custom fixups define `PREUPGRADE` or `PRECHECKS` actions that involve making changes, you can run them separately.


java -jar autoupgrade.jar -config config.txt -mode FIXUPS

3. Deploy Mode: This performs the actual upgrade. It creates a guaranteed restore point (GRP), performs the upgrade, and runs post-upgrade fixups.


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

This command will shut down the 19c database, perform the upgrade to 23ai, and then restart the database in the new Oracle Home. Monitor the console output and the log files (`PTSYS_upg.log`, `PTSYS_deploy.log`) meticulously.

If the upgrade fails, AutoUpgrade provides options to `RESTORE` to the GRP or `ABORT` the process.

Post-Upgrade Steps

Once AutoUpgrade reports a successful `DEPLOY`, there are a few manual checks and PeopleSoft-specific tasks:

Verify Database Status:


sqlplus / as sysdba
SQL> SELECT status FROM v$instance;
SQL> SELECT name, open_mode FROM v$database;
SQL> SELECT count(*) FROM dba_invalid_objects;

Ideally, `dba_invalid_objects` should return 0, or a very low number related to non-critical objects.

Update Listener and TNS Entries: Adjust `listener.ora` on the database server and `tnsnames.ora` on all PeopleSoft application servers, web servers, and process schedulers to point to the new 23ai database service.


# Example listener.ora entry for 23ai
LISTENER_23AI =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = your_db_host)(PORT = 1523))
    )
  )

SID_LIST_LISTENER_23AI =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PTSYS)
      (ORACLE_HOME = /u01/app/oracle/product/23.0.0/dbhome_1)
      (SID_NAME = PTSYS)
    )
  )

# Example tnsnames.ora entry for PeopleSoft
PTSYS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = your_db_host)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PTSYS)
    )
  )

Reload the listener: `lsnrctl reload LISTENER_23AI`

Gather Optimizer Statistics: Crucial for good performance in 23ai.


sqlplus / as sysdba
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(GATHER_SYS => TRUE, OPTIONS => 'GATHER AUTO');

PeopleSoft Application-Specific Tasks:
- Start PeopleSoft application servers, web servers, and process schedulers.
- Clear application server and web server caches.
- Run PeopleSoft Application Engine programs for database consistency checks or specific post-upgrade tasks defined by your PeopleTools version.
- Thoroughly test all PeopleSoft modules (HR, Financials, Campus, CRM, etc.) and customisations.
Performance Validation: Compare AWR reports and key PeopleSoft transaction times against your baselines. Tune as necessary.

Security Considerations During and After Upgrade

Security is paramount, especially when dealing with sensitive PeopleSoft data.

Least Privilege: Ensure the `oracle` OS user and the database user used for AutoUpgrade (`SYS`) have only the necessary privileges. Avoid running AutoUpgrade as `root`.
Credential Management: Do not hardcode passwords in `config.

Mastering Oracle 19c to 23ai Upgrade with AutoUpgrade & Custom Fixups