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 impactful as the evolution of the Oracle Database. The recent general availability of Oracle Database 23ai marks a significant milestone, ushering in a new era of AI-driven data management, JSON Relational Duality, Graph capabilities, and a host of developer-centric features. For organizations running mission-critical applications like Oracle PeopleSoft, the transition from the venerable Oracle Database 19c—a long-term support release—to 23ai is not merely an upgrade; it's a strategic move to future-proof their enterprise backbone and unlock unprecedented capabilities.
Oracle Database 19c has been the steadfast workhorse for many PeopleSoft environments, providing stability and robust performance. However, 23ai, while currently an innovation release, introduces paradigm-shifting features that promise to revolutionize how applications interact with data. Imagine PeopleSoft leveraging AI Vector Search to quickly find relevant policy documents or HR records based on semantic similarity, or utilizing JSON Relational Duality Views to simplify complex data access patterns for modern front-ends while maintaining the transactional integrity of the underlying relational schema. These aren't distant dreams; they are the realities enabled by 23ai.
The upgrade process itself, especially for a complex application like PeopleSoft with its intricate schema and dependencies, can appear daunting. This is where Oracle’s AutoUpgrade utility shines. AutoUpgrade is an invaluable, command-line driven tool designed to simplify and automate database upgrades, minimizing downtime and reducing human error. It intelligently handles pre-upgrade checks, performs the actual database upgrade, and executes post-upgrade tasks. However, in environments with unique configurations, customizations, or specific application requirements—like PeopleSoft often has—the standard AutoUpgrade process might need a helping hand. This is where the power of custom fixups comes into play, allowing DBAs to inject application-specific scripts and commands into the upgrade lifecycle, ensuring a smooth and PeopleSoft-compatible transition to 23ai.
This article will provide a detailed, publication-ready guide for upgrading your Oracle Database 19c, supporting a PeopleSoft application, to 23ai using AutoUpgrade, with a particular focus on how to leverage custom fixups to address PeopleSoft-specific challenges. We’ll delve into the prerequisites, a step-by-step implementation, crucial security considerations, and best practices to ensure a successful and robust upgrade.
Prerequisites for a Seamless Transition
Before embarking on any database upgrade, meticulous preparation is paramount. For a PeopleSoft environment, this preparation involves not only database-centric checks but also careful consideration of the application layer. Skipping any of these steps can lead to significant delays or, worse, a failed upgrade.
Source Database (Oracle Database 19c)
- Patching Level: Ensure your 19c database is on the latest available Release Update (RU) or Release Update Revision (RUR). Oracle strongly recommends this to minimize the likelihood of encountering known issues during the upgrade. For instance, being on 19.22.0.0.240116 or higher is ideal.
- Database Health Checks:
- Invalid Objects: All schema objects, especially within PeopleSoft schemas (e.g., SYSADM, PPLTOOLS), must be valid. Run `UTLRP.SQL` multiple times if needed.
SQL> @?/rdbms/admin/utlrp.sql - Dictionary Statistics: Gather fresh dictionary statistics to ensure the optimizer has accurate information for upgrade scripts.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; - Database Validation: Use Database Validation Utility (DBV) to check for block corruptions.
- Timezone File: The 19c database should have an up-to-date timezone file (e.g., v39 or higher). AutoUpgrade can handle this, but pre-emptively updating it simplifies the process.
SQL> SELECT * FROM V$TIMEZONE_FILE; - Character Set: Ensure the database character set is AL32UTF8. While not strictly mandatory for 23ai, it is the universal character set and best practice for modern Oracle deployments, especially with PeopleSoft's global reach.
- No Pending Transactions: Ensure no long-running or uncommitted transactions are active before starting the upgrade.
COMPATIBLEParameter: TheCOMPATIBLEinitialization parameter in 19c should be set to19.0.0or higher.
- Invalid Objects: All schema objects, especially within PeopleSoft schemas (e.g., SYSADM, PPLTOOLS), must be valid. Run `UTLRP.SQL` multiple times if needed.
- Database Size: Determine the current size of your PeopleSoft database. This will help estimate disk space requirements for the 23ai target.
Target Database Software (Oracle Database 23ai)
- New Oracle Home: Oracle recommends installing 23ai into a clean, separate Oracle Home. Do NOT install into the existing 19c Oracle Home. A typical path might be `/u01/app/oracle/product/23.0.0/dbhome_1`.
- Disk Space: Allocate sufficient disk space for the new Oracle Home (typically 10-15 GB for software) and additional space for the upgraded database, temporary tablespaces, and redo logs. A general rule of thumb is 2-3x the size of your current database for the upgrade process, including backups.
- Operating System Prerequisites: Ensure the underlying OS (e.g., Oracle Linux 8 or 9) meets 23ai requirements regarding kernel parameters, packages, and library versions. Use the Oracle Preinstallation RPMs if available for your OS.
- Environment Variables: Correctly set up environment variables for the 23ai Oracle Home, including
ORACLE_HOME,PATH, andLD_LIBRARY_PATH.
AutoUpgrade Tool
- Location: The
autoupgrade.jartool is typically found within the `$ORACLE_HOME/rdbms/admin/` directory of the target 23ai installation. It's always best practice to use the version that ships with the target database release or download the latest version from My Oracle Support. - Java Requirements: AutoUpgrade requires a Java Development Kit (JDK) 8 or higher. The JDK bundled with the Oracle Home is usually sufficient.
PeopleSoft-Specific Considerations
- Application Downtime: Plan for a complete shutdown of all PeopleSoft application servers, web servers (PIA), process schedulers, and batch jobs. Communicate this downtime clearly to stakeholders.
- PeopleTools Compatibility: Crucially, verify that your PeopleTools version is certified to run on Oracle Database 23ai. Oracle publishes a certification matrix on My Oracle Support. For example, PeopleTools 8.60 or later might be required. If your PeopleTools version is older, you may need a PeopleTools upgrade *before* or *after* the database upgrade, or even concurrently, depending on the specific matrix.
- Customizations: Document all PeopleSoft customizations. While the database upgrade typically doesn't directly impact application customizations in PeopleSoft (as they are usually stored within the database schema), it's vital to be aware of them for post-upgrade testing.
- Application Server/Web Server Configuration: Ensure your PeopleSoft application servers and web servers can connect to the new database version. This might involve updating JDBC drivers or client libraries on the application tier post-upgrade.
Backup Strategy (Non-Negotiable)
- Full RMAN Backup: Perform a full, consistent RMAN backup of your 19c database, including datafiles, control files, and archived redo logs.
RMAN> SHUTDOWN IMMEDIATE; RMAN> STARTUP MOUNT; RMAN> BACKUP DATABASE PLUS ARCHIVELOG; RMAN> ALTER DATABASE OPEN; - Configuration Files: Backup critical configuration files like `listener.ora`, `tnsnames.ora`, `sqlnet.ora`, and the 19c `init.ora` or SPFILE.
PDB$SEEDBackup (if CDB): If your 19c database is a Container Database (CDB), ensurePDB$SEEDis healthy and backed up.
Step-by-Step Implementation: Upgrading 19c to 23ai with Custom Fixups
1. Prepare the Source 19c Database
Assuming you've completed the prerequisite health checks, ensure the database is ready for the upgrade. Connect as SYSDBA and run the following checks:
-- Check for invalid objects
SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID' AND owner IN ('SYSADM', 'PPLTOOLS', 'SYSMAN', 'DBSNMP', 'OUTLN', 'APPQOSSYS', 'ORACLE_OCM', 'DIP', 'XDB', 'OLAPSYS', 'MDSYS', 'CTXSYS', 'WMSYS', 'ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA', 'APEX_040200', 'APEX_050000', 'FLOWS_FILES');
-- Check for pending transactions (should be empty)
SELECT s.sid, s.serial#, s.username, s.status, t.start_time, t.last_call_et, t.used_urec, t.used_ublk
FROM v$transaction t JOIN v$session s ON t.addr = s.taddr;
-- Gather dictionary statistics (again, just to be sure)
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- Update timezone file (if not already done)
-- This usually involves running `catbundle.sql` after applying the patch.
-- Example for checking current version:
-- SQL> SELECT * FROM V$TIMEZONE_FILE;
-- If an update is needed, consult Oracle documentation for the specific patch and steps.
Ensure all PeopleSoft application services are stopped before proceeding.
2. Install Oracle Database 23ai Software
Install the 23ai database software into a new, clean Oracle Home. This should be a software-only installation. Do not create a database at this stage.
# Assuming you've unzipped the 23ai software into /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 in silent mode for software-only installation
./runInstaller -silent -responseFile /path/to/db_install.rsp -ignorePrereqFailure \
ORACLE_HOME="/u01/app/oracle/product/23.0.0/dbhome_1" \
ORACLE_BASE="/u01/app/oracle" \
UNIX_GROUP_NAME="oinstall" \
INVENTORY_LOCATION="/u01/app/oraInventory" \
SELECTED_INSTALL_TYPE="SOFTWARE_ONLY" \
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.OSRESOURCE_GROUP="resourcedba" \
SECURITY_UPDATES_VIA_MYORACLESUPPORT="false"
After the installation, execute the root scripts as instructed by the installer (e.g., `root.sh`).
3. Configure AutoUpgrade with Custom Fixups
Navigate to the AutoUpgrade utility within your new 23ai Oracle Home. Create a configuration file, for instance, `ppldevdb_upgrade.txt`, to define your upgrade parameters. The critical aspect here is defining custom fixups for PeopleSoft.
First, let's locate the AutoUpgrade tool:
# Set environment for the target 23ai home
export ORACLE_HOME=/u01/app/oracle/product/23.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
# AutoUpgrade is typically here:
cd $ORACLE_HOME/rdbms/admin
Now, create your `ppldevdb_upgrade.txt` configuration file:
# ppldevdb_upgrade.txt
# AutoUpgrade configuration for PeopleSoft PPLDEVDB from 19c to 23ai
global.autoupg_log_dir=/u01/app/oracle/autoupgrade_logs/PPLDEVDB_23ai
# Define the database to be upgraded
upg1.sid=PPLDEVDB
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.logdir=/u01/app/oracle/autoupgrade_logs/PPLDEVDB_23ai/upg1
upg1.target_version=23
upg1.timezone_upg=YES
upg1.run_utlrp=YES
upg1.run_catcpu=YES
upg1.upgrade_node=localhost
upg1.target_pdb_name=PPLDEV_PDB # If converting non-CDB to CDB, specify PDB name.
upg1.target_cdb=YES # Indicate target is a CDB.
upg1.target_container_name=CDB23AI # Name of the new CDB container (if target is new CDB)
# Specify custom fixups for PeopleSoft
# These scripts will run at specific phases of the upgrade.
# It's crucial for PeopleSoft to recompile objects and perform application-specific adjustments.
# Example: A custom pre-upgrade script to capture baseline or fix minor issues
upg1.custom_fixups=pre_ppl_fixup.sql@PREUPGRADE
# Example: A custom post-upgrade script for PeopleSoft schema recompilation and other tasks
# This is where the core PeopleSoft-specific adjustments would happen.
upg1.custom_fixups=post_ppl_fixup.sql@POSTUPGRADE
upg1.custom_fixups=/u01/app/oracle/autoupgrade_scripts/ppl_post_upgrade_config.sql@POSTUPGRADE
Let's elaborate on the custom fixups. For a PeopleSoft environment, you might need scripts that:
- Recompile invalid objects: Even after AutoUpgrade's standard recompilation, PeopleSoft schemas might have specific objects that need another pass, or objects that become invalid due to new optimizer features or data dictionary changes.
- Update PeopleSoft-specific statistics: While AutoUpgrade gathers dictionary stats, PeopleSoft often benefits from focused statistics gathering on key application tables after an upgrade.
- Adjust application-specific parameters: Although less common, some PeopleSoft environments might have specific database parameter requirements that need verification or adjustment in 23ai.
- Run PeopleSoft-provided utilities: Historically, PeopleSoft sometimes provided SQL scripts to run post-database upgrades. While less frequent with modern PeopleTools, it's a good place to include any such requirements.
Here’s an example of what `post_ppl_fixup.sql` might contain:
-- /u01/app/oracle/autoupgrade_scripts/post_ppl_fixup.sql
-- This script runs as SYSDBA.
-- It addresses PeopleSoft-specific post-upgrade tasks.
SPOOL /u01/app/oracle/autoupgrade_logs/PPLDEVDB_23ai/post_ppl_fixup.log
PROMPT Recompiling invalid objects in PeopleSoft schemas...
-- Recompile SYSADM schema
ALTER SESSION SET CURRENT_SCHEMA = SYSADM;
EXEC DBMS_UTILITY.COMPILE_SCHEMA('SYSADM', FALSE);
-- Recompile PPLTOOLS schema (if applicable or other custom schemas)
ALTER SESSION SET CURRENT_SCHEMA = PPLTOOLS;
EXEC DBMS_UTILITY.COMPILE_SCHEMA('PPLTOOLS', FALSE);
-- Reset to default schema
ALTER SESSION SET CURRENT_SCHEMA = SYS;
PROMPT Checking for any remaining invalid objects after initial recompilation...
SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = 'INVALID' AND owner IN ('SYSADM', 'PPLTOOLS');
-- Gather specific statistics for critical PeopleSoft tables (example)
PROMPT Gathering statistics for critical PeopleSoft tables...
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SYSADM', 'PS_JOB', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE);
DBMS_STATS.GATHER_TABLE_STATS('SYSADM', 'PS_PERSONAL_DATA', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE);
DBMS_STATS.GATHER_TABLE_STATS('SYSADM', 'PS_LEDGER', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE);
-- Add more critical PeopleSoft tables as needed
END;
/
PROMPT Verifying database parameters relevant to PeopleSoft...
-- Example: Check if a specific parameter is set
-- SELECT name, value FROM v$parameter WHERE name = 'optimizer_adaptive_features';
-- You might add logic here to alter parameters if they are not at desired values for PeopleSoft.
PROMPT Custom PeopleSoft post-upgrade script completed.
SPOOL OFF
EXIT
Place these custom SQL scripts in a directory accessible by the Oracle user (e.g., `/u01/app/oracle/autoupgrade_scripts/`). Ensure they have correct permissions.
4. Perform Pre-Upgrade Analysis
Before committing to the upgrade, run AutoUpgrade in `analyze` mode. This step is crucial as it identifies potential issues and provides recommendations, including pre- and post-fixup scripts.
# Ensure you are in the 23ai Oracle Home's rdbms/admin directory
cd /u01/app/oracle/product/23.0.0/dbhome_1/rdbms/admin
# Run AutoUpgrade in analyze mode
java -jar autoupgrade.jar -config ppldevdb_upgrade.txt -mode analyze
Review the generated log files and HTML reports carefully. The reports are typically located in the `global.autoupg_log_dir` specified in your config file (e.g., `/u01/app/oracle/autoupgrade_logs/PPLDEVDB_23ai/`). Pay close attention to any `WARNING` or `ERROR` messages, especially those related to PeopleSoft schemas or objects. Address any identified issues before proceeding.
5. Execute the Upgrade
Once the analysis is clean, and you have addressed all warnings, you can proceed with the actual upgrade. Ensure you have a full, recoverable backup of your 19c database. Shutdown all PeopleSoft application services and listener for the 19c database.
# Ensure you are in the 23ai Oracle Home's rdbms/admin directory
cd /u01/app/oracle/product/23.0.0/dbhome_1/rdbms/admin
# Execute the upgrade in deploy mode
java -jar autoupgrade.jar -config ppldevdb_upgrade.txt -mode deploy
Monitor the progress using the AutoUpgrade console output and the log files. AutoUpgrade progresses through several phases:
- PREUPGRADE: Performs final checks, creates restore points, and runs any custom pre-upgrade fixups.
- DRAIN: Shuts down the source database.
- DBUPGRADE: Starts the target database with the new Oracle Home, runs DBUA equivalent scripts, and performs the core database upgrade.
- POSTUPGRADE: Performs post-upgrade tasks like recompiling invalid objects, updating dictionary statistics, and crucially, running your custom post-upgrade fixups for PeopleSoft.
If any custom fixup script fails, AutoUpgrade will typically pause, allowing you to manually troubleshoot and fix the issue before resuming or rolling back.
6. Post-Upgrade Steps
After AutoUpgrade reports a successful deployment:
- Verify Database Health:
SQL> SELECT version FROM v$instance; SQL> SELECT name, open_mode FROM v$database; SQL> SELECT comp_id, comp_name, version, status FROM dba_registry; SQL> SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID'; -- Should be minimal or empty - Update
COMPATIBLEParameter: After verifying 23ai functionality, you can update theCOMPATIBLEparameter to23.0.0. This enables all new 23ai features.SQL> ALTER SYSTEM SET COMPATIBLE = '23.0.0' SCOPE=SPFILE; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP; - Gather Dictionary Statistics: Re-gather dictionary statistics after the database is fully open and compatible.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; - Update PeopleSoft Configuration:
- Update the TNS entry for PeopleSoft application servers and web servers to point to the new 23ai database instance.
- Verify and update any database client drivers (e.g., JDBC) on the PeopleSoft application and web servers to be compatible with 23ai.
- Start the PeopleSoft listener, application servers, and web servers.
- Perform comprehensive functional testing of the PeopleSoft application, including critical business processes, batch jobs, and user interfaces.
- Check PeopleSoft logs for any database connectivity or SQL errors.
Security Considerations
Security must be an integral part of your upgrade planning and execution:
- Least Privilege: Ensure the operating system user running AutoUpgrade (typically `oracle`) has only the necessary permissions. Avoid running as `root` unless explicitly required for specific root scripts.
- Secure Configuration File: The `ppldevdb_upgrade.txt` file should not contain clear-text passwords. AutoUpgrade supports Oracle Wallet for secure credential storage. For example, you can create a wallet and add credentials using `mkstore` or `orapki` and then reference it in the config file.
- Network Security: Ensure network communication between the database server and any client tools (like AutoUpgrade) is secured, ideally within a private, firewalled subnet.
- Review New 23ai Security Features: Oracle Database 23ai introduces enhanced security features. Review these and plan for their adoption, such as improved default settings, new roles, or auditing capabilities.
- Auditing: Maintain an audit trail of all upgrade activities, including who performed the upgrade, when, and any issues encountered. AutoUpgrade logs are critical for this.
Best Practices
- Test Environment First: NEVER perform a production upgrade without successfully completing it in a non-production environment (Dev, Test, UAT). This allows you to refine your custom fixups and identify PeopleSoft-specific challenges.
- Document Everything: Keep a detailed log of all steps, commands, and their output. This documentation is invaluable for troubleshooting and future upgrades.
- Review AutoUpgrade Documentation: The Oracle Database Upgrade Guide and AutoUpgrade documentation are your authoritative sources. Always refer to the latest versions.
- Consider CDB Architecture: If your 19c database is a non-CDB, use the AutoUpgrade feature to convert it to a PDB within a new 23ai CDB. This simplifies future upgrades and patching.
- Engage PeopleSoft Teams: Collaborate closely with your PeopleSoft functional and technical teams throughout the planning, testing, and post-upgrade validation phases. They are critical for application-level testing.
- Plan for Rollback: Despite all precautions, have a clear rollback plan. A full RMAN backup is your primary safety net.
- Performance Baselines: Capture performance baselines (AWR reports, SQL Monitoring) on 19c before the upgrade. Compare these with post-upgrade baselines on 23ai to identify any performance regressions.
Frequently Asked Questions
Q1: Can I upgrade a non-CDB 19c to a CDB 23ai using AutoUpgrade?
Yes, absolutely. AutoUpgrade is designed to facilitate this transition seamlessly. In your `config.txt` file, you would specify `upg1.target_cdb=YES` and `upg1.target_pdb_name` to define the name of the Pluggable Database (PDB) that your non-CDB will become within the new