Oracle Database 19c to 23ai Upgrade: Mastering AutoUpgrade with Custom Fixups for PeopleSoft Environments
As a senior technology writer at TechNews Venture, I've witnessed countless organizations grapple with the complexities of database upgrades. In the world of Oracle PeopleSoft, where database stability and application compatibility are paramount, these transitions can be particularly daunting. However, with Oracle Database 23ai — the "App Simple" release — and the ever-evolving capabilities of the AutoUpgrade utility, the path to modernization is clearer than ever. This article delves into a critical aspect of this journey: upgrading your Oracle Database 19c to 23ai using AutoUpgrade, with a specific focus on leveraging custom fixups to navigate the unique challenges often presented by PeopleSoft environments.
Overview: Embracing 23ai and AutoUpgrade's Power
Oracle Database 23ai introduces a paradigm shift with features like AI Vector Search, JSON Relational Duality, Operational Property Graphs, and a host of developer-centric enhancements, all designed to simplify application development and boost performance. For PeopleSoft users, this means a more robust, intelligent, and future-proof database foundation capable of supporting demanding enterprise applications. Moving from Oracle Database 19c, a long-term support release, to 23ai requires careful planning and execution.
Enter Oracle AutoUpgrade. This powerful, command-line utility, bundled with newer Oracle Database releases (and available standalone), automates much of the upgrade process, significantly reducing manual effort, human error, and downtime. It intelligently analyzes your database, identifies potential issues, and even suggests fixes. But what happens when your PeopleSoft environment has unique configurations, custom objects, or specific dependencies that AutoUpgrade's standard checks might not fully address? This is where custom fixups become indispensable. By integrating pre-upgrade and post-upgrade scripts directly into the AutoUpgrade workflow, DBAs can tailor the upgrade process to the exact needs of their PeopleSoft application, ensuring a smooth, predictable, and successful transition.
This comprehensive guide will walk you through the entire process, from prerequisites and preparation to the execution of AutoUpgrade with bespoke custom fixups, culminating in post-upgrade validation, all within the context of a typical Oracle PeopleSoft deployment.
Prerequisites: Laying the Foundation for a Smooth Upgrade
A successful database upgrade is built on meticulous preparation. For PeopleSoft environments, this preparation involves not just database checks but also application-level considerations.
Software and Hardware Requirements
- Oracle Database 23ai Software: Download the full Oracle Database 23ai software (e.g., Enterprise Edition) from Oracle's website. Stage it on your target server.
- AutoUpgrade Utility: Ensure you are using the latest version of AutoUpgrade. While it's bundled with 23ai, it's always a good practice to download the latest standalone version from My Oracle Support (MOS Note 2485457.1) for potential bug fixes and enhancements.
- Operating System: Verify your OS (Linux, AIX, Solaris, Windows) is certified for Oracle Database 23ai. Apply all necessary OS patches and kernel parameters as per Oracle's installation guide.
- Disk Space: Allocate sufficient disk space for the 23ai ORACLE_HOME, temporary files, and especially for the AutoUpgrade logs and diagnostic files, which can be substantial. A minimum of 20-30GB for the new ORACLE_HOME and an additional 50-100GB for logs and backups is a good starting point, depending on database size.
- Memory (RAM): Ensure adequate RAM. Oracle 23ai generally requires more memory than 19c. Consult the installation guide for minimum requirements (typically 8GB+ for a production instance).
Database Health Checks and Pre-Upgrade Steps
- Apply Latest 19c Patches: Ensure your 19c database is on the latest recommended Release Update (RU) or Release Update Revision (RUR) for 19c. This minimizes known issues during the upgrade.
- Run Pre-Upgrade Information Tool: Execute
dbms_preup.sqlfrom the 23ai ORACLE_HOME on your 19c database. This script generates a detailed HTML report highlighting potential upgrade issues. Review this report thoroughly.-- Connect as SYSDBA to the 19c database sqlplus / as sysdba -- Run the pre-upgrade script from the 23ai ORACLE_HOME @/u00/app/oracle/product/23ai/dbhome/rdbms/admin/dbms_preup.sql -- Generate the report SET SERVEROUTPUT ON EXEC DBMS_PREUP.COLLECT_INFO; -- The report will be generated in the current directory or specified output directory. -- Look for preupgrade_results_*.html - Resolve Invalid Objects: Recompile any invalid objects in your 19c database. PeopleSoft often has custom objects or third-party integrations that might become invalid.
-- As SYSDBA @?/rdbms/admin/utlrp.sql - Gather Statistics: Ensure all schema statistics are up-to-date. Stale statistics can lead to poor performance post-upgrade.
-- As SYSDBA or with appropriate privileges EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYS'); EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYSTEM'); EXEC DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS => 'GATHER AUTO', GATHER_SYS => TRUE, CASCADE => TRUE); -- For PeopleSoft schemas (e.g., SYSADM) EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYSADM', OPTIONS => 'GATHER AUTO', CASCADE => TRUE); - Clean Up Recycle Bin: Empty the recycle bin.
PURGE DBA_RECYCLEBIN; - Review Database Parameters: Check for deprecated or desupported parameters in 23ai. The pre-upgrade tool will highlight many of these.
Backup and Recovery Strategy
- Full RMAN Backup: A full RMAN backup is non-negotiable. Ensure it's tested and restorable.
RMAN> shutdown immediate; RMAN> startup mount; RMAN> run { ALLOCATE CHANNEL d1 DEVICE TYPE DISK; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL d1; } RMAN> alter database open; - Guaranteed Restore Point (GRP): For minimal downtime and quick rollback, create a Guaranteed Restore Point. This allows you to revert the database to its pre-upgrade state instantly.
-- As SYSDBA ALTER DATABASE FLASHBACK ON; CREATE RESTORE POINT PRE_23AI_UPGRADE GUARANTEE FLASHBACK DATABASE;
PeopleSoft Specific Prerequisites
- Application Shutdown: Completely shut down all PeopleSoft application servers, process schedulers, web servers, and batch processes connected to the database. No active connections should be present during the upgrade.
- PeopleTools Certification: Verify that your PeopleTools version is certified with Oracle Database 23ai. Refer to My Oracle Support (MOS) for the latest certification matrices. You might need to upgrade PeopleTools before or immediately after the database upgrade.
- Customizations Review: Identify any custom SQL, triggers, or database objects that might conflict with 23ai. This is where custom fixups will prove invaluable.
Step-by-step Implementation: AutoUpgrade with Custom Fixups
This section details the core upgrade process, emphasizing the integration of custom fixups.
I. Prepare the Target Environment
- Install Oracle Database 23ai Software: Install the 23ai software into a new ORACLE_HOME. Do NOT create a database during installation.
# Example installation path /u00/app/oracle/product/23ai/dbhome - Configure TNSNAMES.ORA: Ensure your
tnsnames.oraon the 23ai ORACLE_HOME can connect to the 19c database. Also, configure an entry for the future 23ai database.# In /u00/app/oracle/product/23ai/dbhome/network/admin/tnsnames.ora # Entry for the 19c database (source) PPL19C = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ppl19c_svc) ) ) # Entry for the 23ai database (target) PPL23AI = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ppl23ai_svc) ) ) - Set Environment Variables: Set the necessary environment variables for both 19c and 23ai sessions.
# For 19c export ORACLE_HOME=/u00/app/oracle/product/19c/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export ORACLE_SID=PPL19C # For 23ai export ORACLE_HOME=/u00/app/oracle/product/23ai/dbhome export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export ORACLE_SID=PPL23AI
II. AutoUpgrade Configuration File (config.txt)
The config.txt file is the heart of the AutoUpgrade process. It defines the source and target environments, and crucially, where to insert custom fixup scripts.
Create a file named config.txt (or similar) in your AutoUpgrade working directory:
# Global parameters applicable to all databases being upgraded
global.autoupg_log_dir=/u00/app/oracle/autoupg_logs
global.temp_dir=/u00/app/oracle/autoupg_temp
global.long_uptime_threshold=7
# Database specific parameters for the upgrade from 19c to 23ai
upg1.sid=PPL19C
upg1.source_home=/u00/app/oracle/product/19c/dbhome_1
upg1.target_home=/u00/app/oracle/product/23ai/dbhome
upg1.target_version=23
upg1.log_dir=/u00/app/oracle/autoupg_logs/PPL19C
upg1.upgrade_node=localhost
upg1.start_time=NOW
upg1.timezone_upg=yes
upg1.run_utlrp=yes
upg1.keep_last_autoupgrade_logs=5
# Custom fixup scripts for PeopleSoft environment
# These scripts will be executed by AutoUpgrade at specified stages.
# The 'run' parameter can be 'yes' or 'no'.
# The 'path' specifies the full path to the script.
# The 'tag' is a logical name for the script.
# Pre-upgrade actions: Executed before the main upgrade process starts
upg1.preupgrade_actions=fixup_pre_peoplesoft_objects,fixup_pre_invalid_schema
upg1.preupgrade_actions.fixup_pre_peoplesoft_objects.run=yes
upg1.preupgrade_actions.fixup_pre_peoplesoft_objects.path=/u00/app/oracle/autoupg_scripts/custom_pre_peoplesoft.sql
upg1.preupgrade_actions.fixup_pre_peoplesoft_objects.tag=PrePeopleSoftClean
upg1.preupgrade_actions.fixup_pre_invalid_schema.run=yes
upg1.preupgrade_actions.fixup_pre_invalid_schema.path=/u00/app/oracle/autoupg_scripts/custom_pre_invalid_schema.sql
upg1.preupgrade_actions.fixup_pre_invalid_schema.tag=PreInvalidSchemaCheck
# Post-upgrade actions: Executed after the main upgrade process completes
upg1.postupgrade_actions=fixup_post_peoplesoft_config,fixup_post_recompile_objects
upg1.postupgrade_actions.fixup_post_peoplesoft_config.run=yes
upg1.postupgrade_actions.fixup_post_peoplesoft_config.path=/u00/app/oracle/autoupg_scripts/custom_post_peoplesoft_config.sql
upg1.postupgrade_actions.fixup_post_peoplesoft_config.tag=PostPeopleSoftTune
upg1.postupgrade_actions.fixup_post_recompile_objects.run=yes
upg1.postupgrade_actions.fixup_post_recompile_objects.path=/u00/app/oracle/autoupg_scripts/custom_post_recompile.sql
upg1.postupgrade_actions.fixup_post_recompile_objects.tag=PostRecompileAll
Note on `upg1.sid`: For Container Databases (CDBs) with Pluggable Databases (PDBs), you would typically specify the CDB SID. AutoUpgrade intelligently upgrades all PDBs within that CDB. If you have multiple databases to upgrade, you'd add `upg2.sid`, `upg3.sid`, etc., each with its own set of parameters and custom fixups.
III. Crafting Custom Fixup Scripts
These SQL scripts are critical for addressing PeopleSoft-specific nuances. Store them in the path specified in your `config.txt` (e.g., `/u00/app/oracle/autoupg_scripts/`).
A. Pre-Upgrade Fixup Scripts (Examples)
These scripts run *before* the main database upgrade begins. They are ideal for cleaning up, disabling features, or correcting conditions that might cause the upgrade to fail.
custom_pre_peoplesoft.sql: Addressing PeopleSoft Specific Objects/Settings
-- custom_pre_peoplesoft.sql
-- This script addresses known PeopleSoft-specific issues before the upgrade.
SET ECHO ON
SET FEEDBACK ON
SET SERVEROUTPUT ON
SPOOL /u00/app/oracle/autoupg_logs/PPL19C/custom_pre_peoplesoft.log
PROMPT Checking for problematic PeopleSoft external tables...
DECLARE
CURSOR c_ext_tables IS
SELECT owner, table_name FROM DBA_EXTERNAL_TABLES
WHERE owner IN ('SYSADM', 'PS'); -- Adjust schemas as needed
v_count NUMBER := 0;
BEGIN
FOR r_ext_table IN c_ext_tables LOOP
DBMS_OUTPUT.PUT_LINE('WARNING: External table ' || r_ext_table.owner || '.' || r_ext_table.table_name || ' found. Consider dropping or moving before upgrade.');
v_count := v_count + 1;
END LOOP;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No problematic external tables found.');
END IF;
END;
/
PROMPT Disabling specific PeopleSoft-related jobs/triggers if necessary...
-- Example: If a specific PeopleSoft job is known to interfere with upgrades, disable it.
-- This is highly dependent on your PeopleSoft customization.
-- For example, disabling a custom audit trigger temporarily.
-- SELECT owner, trigger_name, status FROM DBA_TRIGGERS WHERE table_name LIKE 'PS%' AND status = 'ENABLED';
-- ALTER TRIGGER SYSADM.PS_CUSTOM_AUDIT_TRG DISABLE;
-- No specific generic PeopleSoft objects to drop/disable pre-upgrade usually,
-- but this section is a placeholder for specific customizations.
PROMPT Checking for old optimizer statistics preferences for PeopleSoft schemas...
DECLARE
v_pref_value VARCHAR2(100);
BEGIN
SELECT DBMS_STATS.GET_PREFS('PUBLISH', 'SYSADM') INTO v_pref_value FROM DUAL;
IF v_pref_value = 'FALSE' THEN
DBMS_OUTPUT.PUT_LINE('WARNING: SYSADM schema has PUBLISH preference set to FALSE. Consider setting to TRUE post-upgrade.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not check optimizer preferences for SYSADM: ' || SQLERRM);
END;
/
SPOOL OFF
EXIT
custom_pre_invalid_schema.sql: Pre-Upgrade Invalid Object Check
-- custom_pre_invalid_schema.sql
-- Checks for invalid objects in critical schemas before upgrade and attempts to recompile.
SET ECHO ON
SET FEEDBACK ON
SET SERVEROUTPUT ON
SPOOL /u00/app/oracle/autoupg_logs/PPL19C/custom_pre_invalid_schema.log
PROMPT Checking for invalid objects in critical schemas...
DECLARE
CURSOR c_invalid IS
SELECT owner, object_name, object_type
FROM DBA_INVALID_OBJECTS
WHERE owner IN ('SYS', 'SYSTEM', 'SYSADM', 'PS'); -- Include relevant PeopleSoft schemas
v_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('Attempting to recompile invalid objects...');
FOR r_invalid IN c_invalid LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE('Recompiling ' || r_invalid.object_type || ' ' || r_invalid.owner || '.' || r_invalid.object_name);
EXECUTE IMMEDIATE 'ALTER ' || r_invalid.object_type || ' ' || r_invalid.owner || '.' || r_invalid.object_name || ' COMPILE';
v_count := v_count + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to recompile ' || r_invalid.object_type || ' ' || r_invalid.owner || '.' || r_invalid.object_name || ': ' || SQLERRM);
END;
END LOOP;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No invalid objects found/recompiled in specified schemas.');
ELSE
DBMS_OUTPUT.PUT_LINE('Attempted to recompile ' || v_count || ' invalid objects.');
END IF;
END;
/
-- Run a full recompile as a final measure
PROMPT Running utlrp.sql to recompile all objects...
@?/rdbms/admin/utlrp.sql
SPOOL OFF
EXIT
B. Post-Upgrade Fixup Scripts (Examples)
These scripts execute *after* the database has been upgraded to 23ai. They are used for final configurations, enabling features, recompiling objects, or applying specific PeopleSoft patches/updates.
custom_post_peoplesoft_config.sql: PeopleSoft Configuration and Tuning
-- custom_post_peoplesoft_config.sql
-- This script applies PeopleSoft-specific post-upgrade configurations.
SET ECHO ON
SET FEEDBACK ON ON
SET SERVEROUTPUT ON
SPOOL /u00/app/oracle/autoupg_logs/PPL19C/custom_post_peoplesoft_config.log
PROMPT Adjusting PeopleSoft related database parameters for 23ai...
-- Example: Adjusting optimizer features or adaptive features based on PeopleSoft recommendations for 23ai.
-- Always consult PeopleSoft documentation and MOS for certified parameters.
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS=FALSE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS=FALSE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='23.0.0' SCOPE=BOTH; -- Or '19.0.0' if PeopleSoft requires it initially
PROMPT Disabling specific DBMS_AUTO_TASK_ADMIN tasks until PeopleSoft validation...
-- It's often prudent to disable some automated tasks immediately post-upgrade
-- until application validation is complete.
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation_name => 'auto optimizer stats collection',
window_name => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto space advisor',
operation_name => 'auto space advisor',
window_name => 'MONDAY_WINDOW');
-- Repeat for other windows or clients as needed
DBMS_OUTPUT.PUT_LINE('Disabled some auto tasks. Re-enable after PeopleSoft validation.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error disabling auto tasks: ' || SQLERRM);
END;
/
PROMPT Updating PeopleSoft specific statistics if required...
-- While AutoUpgrade runs statistics, PeopleSoft might benefit from specific schema stats gathering.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYSADM', OPTIONS => 'GATHER AUTO', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'PS', OPTIONS => 'GATHER AUTO', CASCADE => TRUE);
SPOOL OFF
EXIT
custom_post_recompile.sql: Post-Upgrade Recompilation
-- custom_post_recompile.sql
-- Recompiles all invalid objects after the 23ai upgrade.
SET ECHO ON
SET FEEDBACK ON
SET SERVEROUTPUT ON
SPOOL /u00/app/oracle/autoupg_logs/PPL19C/custom_post_recompile.log
PROMPT Running utlrp.sql to recompile all invalid objects after upgrade...
@?/rdbms/admin/utlrp.sql
PROMPT Checking for any remaining invalid objects after recompilation...
SELECT owner, object_name, object_type, status
FROM DBA_OBJECTS
WHERE status != 'VALID'
ORDER BY owner, object_type, object_name;
SPOOL OFF
EXIT
IV. Running AutoUpgrade
Navigate to the directory where you placed the autoupgrade.jar file.
- Analyze Mode: Always start with the
analyzemode. This simulates the upgrade and generates reports without making any changes to your database.java -jar autoupgrade.jar -config config.txt -mode analyzeReview the generated reports and logs carefully in
/u00/app/oracle/autoupg_logs/PPL119C/. Pay close attention to thepreupgrade.log,autoupgrade_analyze_*.log, and the HTML reports. Address any critical warnings or errors before proceeding. - Fix Mode (Optional, for non-critical issues): AutoUpgrade can fix some issues automatically. If the analyze report suggests minor fixes, you can run in
fixmode.java -jar autoupgrade.jar -config config.txt -mode fix - Deploy Mode: Once you are confident after reviewing the analyze reports and addressing issues, proceed with the
deploymode. This performs the actual upgrade.java -jar autoupgrade.jar -config config.txt -mode deployThis command will shut down the 19c database, perform the upgrade to 23ai, execute your custom fixup scripts, and bring the database up in the new ORACLE_HOME.
- Monitor Progress: You can monitor the upgrade status from another terminal.
java -jar autoupgrade.jar -config config.txt -mode status - Handle Failures (if any): If the upgrade fails, AutoUpgrade provides a
restoremode to revert to the pre-upgrade state (leveraging the GRP if configured).java -jar autoupgrade.jar -config config.txt -mode restoreAfter restoring, analyze the logs to understand the failure, correct the underlying issue, and attempt the upgrade again.
V. Post-Upgrade Validation
After a successful deploy, perform thorough validation:
- Database Status: Connect to the new 23ai