Oracle Database 19c to 23ai Upgrade using AutoUpgrade with Custom Fixups: A Deep Dive for PeopleSoft Environments
As a senior technology writer at TechNews Venture, I've witnessed countless database migrations and upgrades. Few, however, offer the blend of innovation and complexity that comes with moving an enterprise-grade system like Oracle Database 19c, particularly one underpinning critical applications like Oracle PeopleSoft, to the new Oracle Database 23ai. This isn't merely a version bump; it's a leap into a future where AI, JSON, and developer-friendly features are deeply integrated into the data layer. The journey, while rewarding, demands meticulous planning and execution, especially when leveraging Oracle's powerful AutoUpgrade utility with custom fixups to navigate the nuances of a PeopleSoft environment.
Overview: Embracing the Future with Oracle Database 23ai
Oracle Database 19c, a Long Term Support (LTS) release, has served as a stable bedrock for many organizations, including those running Oracle PeopleSoft. Its reliability and mature feature set have made it a trusted choice for mission-critical systems. However, the technological landscape evolves rapidly, and Oracle Database 23ai, an innovation release, represents a significant paradigm shift. Tagged as "The AI Database," 23ai introduces groundbreaking features such as AI Vector Search for generative AI applications, JSON Relational Duality Views, Operational Property Graphs, and a host of developer-centric enhancements like JavaScript stored procedures and native JSON data types. For PeopleSoft customers, upgrading to 23ai not only future-proofs their infrastructure but also opens doors to integrating AI-driven insights directly with their HR, Financial, and Campus Solutions data, without moving data out of the database.
The upgrade process itself, especially for complex PeopleSoft schemas, can be daunting. This is where Oracle's AutoUpgrade utility becomes indispensable. AutoUpgrade is a command-line tool designed to automate the entire database upgrade process, from pre-checks and pre-fixups to the actual upgrade and post-upgrade tasks. It significantly reduces manual effort, minimizes human error, and provides a consistent, repeatable upgrade experience. While AutoUpgrade handles a vast array of common issues, real-world enterprise environments often present unique challenges. Custom fixups empower DBAs to inject specific scripts or commands into the AutoUpgrade workflow, addressing bespoke requirements, deprecated parameters, or application-specific schema adjustments that are critical for a seamless transition, particularly in a PeopleSoft context.
This article will guide you through a detailed, publication-ready technical journey to upgrade your Oracle Database 19c to 23ai, focusing on the practical application of AutoUpgrade with an emphasis on custom fixups, all while keeping the unique considerations of an Oracle PeopleSoft environment in mind.
Prerequisites: Laying the Foundation for a Successful Upgrade
A successful database upgrade hinges on thorough preparation. Skimping on prerequisites can lead to costly delays and potential data loss. Ensure all the following are meticulously checked and prepared:
- Operating System Compatibility: Oracle Database 23ai requires a certified operating system version. For Linux, this typically means Oracle Linux 7 or 8, Red Hat Enterprise Linux 7 or 8, or SUSE Linux Enterprise Server 12 or 15. Verify your current OS meets these requirements.
- Hardware Resources:
- CPU: Ensure sufficient CPU cores for the new Oracle Home and database operations.
- RAM: Allocate adequate memory. Oracle recommends a minimum of 2GB RAM for the database instance, but production PeopleSoft environments typically require significantly more (e.g., 32GB+).
- Disk Space:
- At least 15-20 GB for the new Oracle 23ai Home (`/u01/app/oracle/product/23.0.0/dbhome_1`).
- Sufficient space in the diagnostic destination (`ADR_BASE`) for logs.
- Ample free space in the database file system (datafiles, redo logs, control files) to accommodate any growth during the upgrade and for the new database version.
- Oracle 23ai Software: Download the Oracle Database 23ai software (zip files) from the Oracle Software Delivery Cloud (OSDC) or My Oracle Support (MOS).
- Java Development Kit (JDK): AutoUpgrade requires a JDK 8 or higher. Ensure it's installed and configured correctly on the server. AutoUpgrade is bundled with 23ai, but it's always wise to check for the latest version on MOS (Doc ID 2485457.1).
- Existing 19c Database Health:
- Current Patch Level: Ensure your 19c database has the latest Release Update (RU) or Release Update Revision (RUR) applied. This minimizes known issues and ensures compatibility.
- No Invalid Objects: Run `utlrp.sql` and resolve any invalid objects in the 19c database *before* starting the upgrade.
SQL> SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID'; SQL> @?/rdbms/admin/utlrp.sql - No Pending Transactions: Ensure no long-running or uncommitted transactions.
- Statistics: Gather dictionary statistics on the 19c database.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; - Timezone Version: Update the database timezone to the latest version.
SQL> SELECT * FROM V$TIMEZONE_FILE; -- If outdated, follow MOS Note 1509653.1 to update.
- Network Configuration:
- A new Oracle Net Listener for the 23ai Home is required. It can share the same port as the 19c listener, but must be distinct.
- Ensure `tnsnames.ora` and `listener.ora` are correctly configured for both 19c and 23ai.
- User Privileges: The OS user (typically `oracle`) performing the upgrade must have full read/write/execute permissions on both the 19c and 23ai Oracle Homes, as well as the database file systems. Database login as `SYSDBA` is essential.
- PeopleSoft Application Compatibility: Crucially, verify that your PeopleTools version is certified with Oracle Database 23ai. Refer to the Oracle Support PeopleSoft Certifications page (Doc ID 130250.1) for the specific PeopleTools release and any required patches. An incompatible PeopleTools version will render the database upgrade useless for your application.
- Full Database Backup: This is arguably the MOST critical prerequisite. A full RMAN backup of your 19c database is absolutely mandatory. This provides a complete rollback point in case of unforeseen issues.
Test your backup for restorability if possible.RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/PSPROD_FULL_DB_%U'; RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Step-by-Step Implementation: The Upgrade Journey
1. Initial Setup and Environment Preparation
First, set up your new Oracle 23ai Home and prepare the environment.
# As root, create the new Oracle Home directory
sudo mkdir -p /u01/app/oracle/product/23.0.0/dbhome_1
sudo chown -R oracle:oinstall /u01/app/oracle/product/23.0.0/dbhome_1
# As oracle user, unzip the 23ai software
cd /u01/app/oracle/product/23.0.0/dbhome_1
unzip /path/to/23ai_database.zip
# Run the installer in silent mode (or interactively)
./runInstaller -silent -responseFile /path/to/db_install.rsp -ignorePrereq
# Or for interactive: ./runInstaller
# Configure the new 23ai listener (e.g., LISTENER_PS23AI)
# Add entries to /u01/app/oracle/product/23.0.0/dbhome_1/network/admin/listener.ora
# and /u01/app/oracle/product/23.0.0/dbhome_1/network/admin/tnsnames.ora
# Start the new listener
lsnrctl start LISTENER_PS23AI
Ensure your environment variables are correctly set for the 19c database during the initial analysis phase. For the upgrade itself, AutoUpgrade will manage the environment switching.
# Example environment setup for 19c (before starting AutoUpgrade)
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PSPROD
2. AutoUpgrade Configuration File (`autoupgrade.cfg`)
AutoUpgrade operates based on a configuration file. Create a file, for example, `autoupgrade.cfg`, with details about your source and target databases. This is a critical step, as it dictates the upgrade behavior.
# autoupgrade.cfg for PSPROD database upgrade
global.autoupg_log_dir=/u01/app/oracle/autoupgrade/log
global.autoupg_log_file=/u01/app/oracle/autoupgrade/log/autoupgrade_global.log
global.timezone_file=/u01/app/oracle/product/23.0.0/dbhome_1/oracore/zoneinfo/timezone_32.dat
upg1.dbname=PSPROD
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.target_listener=LISTENER_PS23AI
upg1.log_dir=/u01/app/oracle/autoupgrade/log/PSPROD
upg1.start_time=NOW
upg1.upgrade_node=localhost
upg1.run_utlrp=yes
upg1.run_fixups=yes
upg1.target_cdb=false
upg1.data_guarantee=no
upg1.remove_old_homes=no
upg1.allow_multiple_upgrades=no
upg1.redo_threads=2
upg1.parallel_processes=4
upg1.log_retention=30
upg1.autotask=enable
upg1.temp_tablespace_size=10G
Note on `upg1.target_cdb=false`: For PeopleSoft, it's common practice to use a non-CDB architecture, although recent PeopleTools versions support PDBs. If you plan to upgrade to a Container Database (CDB) with a Pluggable Database (PDB), set `upg1.target_cdb=true` and specify `upg1.pdb_name` and `upg1.pdb_admin_user` accordingly.
3. Pre-Upgrade Analysis (`-analyze`)
Before any changes are made, run AutoUpgrade in `analyze` mode. This performs comprehensive checks against the 19c database and generates a detailed report of potential issues and recommended fixups. This step is crucial for identifying problems early.
java -jar /u01/app/oracle/product/23.0.0/dbhome_1/autoupgrade/autoupgrade.jar -config autoupgrade.cfg -mode analyze
Review the generated HTML report (e.g., `PSPROD_upgrade.html` in `/u01/app/oracle/autoupgrade/log/PSPROD`). Pay close attention to sections like "Pre-upgrade Issues," "Fixup Scripts," and "Warnings." This report will guide you in creating custom fixups.
4. Custom Fixups: Tailoring the Upgrade for PeopleSoft
AutoUpgrade comes with many built-in fixups. However, in complex environments like PeopleSoft, you might encounter specific issues that require custom handling. This could be anything from a deprecated database parameter causing issues with PeopleTools, to specific schema objects that need adjustment, or even ensuring certain security settings are in place.
Let's consider a common scenario for PeopleSoft: ensuring a specific database parameter is set correctly, or cleaning up a known PeopleSoft-related issue. For instance, if you had a hidden parameter set in 19c that is now deprecated or causes issues in 23ai, or if you want to ensure the `SEC_CASE_SENSITIVE_LOGON` parameter is `FALSE` (which is often required for PeopleSoft environments using older authentication methods), you might need a custom fixup.
AutoUpgrade allows you to specify pre-upgrade and post-upgrade SQL scripts or OS commands within your configuration file. These scripts are executed by AutoUpgrade at the appropriate stage.
Example Scenario: Ensuring `SEC_CASE_SENSITIVE_LOGON` is FALSE and dropping a specific invalid PeopleSoft index.
First, create your custom fixup scripts:
# File: /u01/app/oracle/autoupgrade/fixups/pre_psprod_fixup.sql
-- This script runs BEFORE the database upgrade starts
-- Ensure SEC_CASE_SENSITIVE_LOGON is FALSE for PeopleSoft compatibility
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=SPFILE;
-- Another example: clean up a specific invalid index that AutoUpgrade might flag
-- Replace PS_MYTABLE_IDX with an actual problematic index from your analysis report
DROP INDEX PS_MYTABLE_IDX;
# File: /u01/app/oracle/autoupgrade/fixups/post_psprod_fixup.sql
-- This script runs AFTER the database upgrade completes
-- Recompile specific PeopleSoft packages if they tend to invalidate after upgrade
-- (Often utlrp.sql handles this, but for critical ones, you can be explicit)
ALTER PACKAGE SYSADM.PS_APP_PACKAGE COMPILE;
ALTER PACKAGE SYSADM.PS_COMMON_PACKAGE COMPILE;
-- Gather schema statistics for SYSADM and other PeopleSoft schemas
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYSADM', options => 'GATHER AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'PS', options => 'GATHER AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE);
Next, modify your `autoupgrade.cfg` to include these custom fixups:
# ... (existing autoupgrade.cfg content) ...
upg1.preupgrade_fixups=/u01/app/oracle/autoupgrade/fixups/pre_psprod_fixup.sql
upg1.postupgrade_fixups=/u01/app/oracle/autoupgrade/fixups/post_psprod_fixup.sql
# ... (rest of the autoupgrade.cfg content) ...
Important: The paths to your fixup scripts must be absolute and accessible by the `oracle` OS user. Always test these scripts manually on a clone of your 19c database first to ensure they execute without errors and achieve the desired outcome.
5. Running the Upgrade (`-deploy`)
Once you've analyzed the report, addressed identified issues, and integrated your custom fixups, you're ready to initiate the upgrade in `deploy` mode. This is the actual upgrade process, so ensure your backup is current and you're prepared for downtime.
java -jar /u01/app/oracle/product/23.0.0/dbhome_1/autoupgrade/autoupgrade.jar -config autoupgrade.cfg -mode deploy
AutoUpgrade will perform a final set of checks, execute pre-upgrade fixups (including your custom ones), shut down the 19c database, start it in the 23ai environment, perform the upgrade, execute post-upgrade fixups, and finally open the database in 23ai mode. Monitor the console output and the log files (`global.log`, `PSPROD_upgrade.log`) closely for any errors or warnings.
If the upgrade encounters a critical error, AutoUpgrade will pause. You can investigate the logs, rectify the issue, and then resume the upgrade:
java -jar /u01/app/oracle/product/23.0.0/dbhome_1/autoupgrade/autoupgrade.jar -config autoupgrade.cfg -mode resume -job PSPROD_upgrade
Replace `PSPROD_upgrade` with the actual job name from your log output.
6. Post-Upgrade Tasks and PeopleSoft Integration
After a successful `deploy`, perform the following crucial post-upgrade steps:
- Verify Upgrade Status:
Review the final reports.java -jar /u01/app/oracle/product/23.0.0/dbhome_1/autoupgrade/autoupgrade.jar -config autoupgrade.cfg -mode status java -jar /u01/app/oracle/product/23.0.0/dbhome_1/autoupgrade/autoupgrade.jar -config autoupgrade.cfg -mode check_upgrades - Recompile Invalid Objects: Even though AutoUpgrade runs `utlrp.sql`, it's good practice to run it again.
Ensure no invalid objects remain.SQL> @?/rdbms/admin/utlrp.sql SQL> SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID'; - Update `tnsnames.ora` and `listener.ora`: Ensure all client applications (including PeopleSoft application servers) can connect to the new 23ai database. Update the `tnsnames.ora` on your PeopleSoft application servers and process schedulers to point to the new 23ai listener and service name.
- Update PeopleSoft Configuration:
- Modify `psappsrv.cfg` (for application servers) and `psprcs.cfg` (for process schedulers) to reflect the new Oracle Home (`ORACLE_HOME` path) and potentially the new `TNS_ADMIN` path if it has changed.
- Update `configuration.properties` for PeopleSoft PIA domains (WebLogic) to point to the new database service.
- Restart all PeopleSoft domains (App Server, Web Server, Process Scheduler).
- Gather Statistics: Gather fresh statistics for the entire database.
This is vital for optimal performance on the new database version.SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(DEGREE => DBMS_STATS.AUTO_DEGREE, GATHER_SYS => TRUE, OPTIONS => 'GATHER AUTO', CASCADE => TRUE); - Test PeopleSoft Applications: Perform comprehensive functional and performance testing of all PeopleSoft modules (HR, Finance, Campus Solutions, CRM, etc.). Test critical business processes, batch jobs, and integrations. Validate reports, queries, and customizations.
- Purge Old Oracle Home: Once the new system is stable and thoroughly tested, you can decommission and remove the old 19c Oracle Home.
Security Considerations
Upgrading a database, especially one as critical as a PeopleSoft backend, necessitates a strong focus on security:
- Least Privilege: Ensure the OS user (`oracle`) and database users (e.g., `SYSADM`, `PS`) operate with the absolute minimum necessary privileges. Review and revoke any unnecessary grants.
- Network Security: Configure firewalls to restrict access to the database listener and port to only necessary application servers and administrative hosts. Use secure listeners (TCPS) if possible.
- Encryption: Implement Transparent Data Encryption (TDE) for data at rest and Oracle Net Services encryption for data in transit. 23ai offers enhanced encryption capabilities.
- Patching: Establish a regular patching schedule for your new 23ai Oracle Home to address security vulnerabilities promptly.
- Auditing: Enable and configure database auditing to track critical activities, especially those related to schema changes, user access, and sensitive data.
- Secure Configuration: Review and implement Oracle's security best practices for 23ai, including password policies, account lockout mechanisms, and default user management.
Best Practices
- Dedicated Test Environment: ALWAYS perform the upgrade on a dedicated, production-like test environment first. This allows for dry runs, custom fixup validation, and thorough PeopleSoft application testing without impacting production.
- Thorough Planning and Documentation: Document every step, command, and decision. This helps in troubleshooting and future upgrades.
- Multiple Dry Runs: Execute the upgrade process multiple times in your test environment until it is smooth and predictable. Time the upgrade process to estimate production downtime accurately.
- Robust Backup and Recovery Strategy: Beyond the initial full backup, ensure you have a clear, tested recovery plan. Understand how to restore your 19c database if the 23ai upgrade fails catastrophically.
- Downtime Planning and Communication: Plan for the necessary downtime. Communicate effectively with stakeholders and users about the upgrade schedule and its impact.
- Performance Baselining: Capture performance baselines (AWR reports, key SQL metrics) on 19c before the upgrade. Compare these baselines against 23ai post-upgrade to identify any performance regressions.
- Engage Oracle Support: If you encounter complex issues or have specific PeopleSoft-related concerns, do not hesitate to open a Service Request with Oracle Support.
- PeopleSoft Application Team Collaboration: Work closely with your PeopleSoft application administrators and developers. Their insights into application behavior and critical functionalities are invaluable for successful testing.
FAQ: Addressing Common Concerns
Here are answers to some frequently asked questions regarding this upgrade path:
Q1: What if AutoUpgrade fails during analysis or deployment? How do I troubleshoot?
A1: If AutoUpgrade fails, the first step is always to check the log files. AutoUpgrade generates detailed logs in the directory specified by `global.autoupg_log_dir` and `upg1.log_dir`. Look for keywords like "ERROR" or "FATAL" in the `global.log` and the database-specific `upgrade.log`. The HTML report generated during the `analyze` phase is also a rich source of information. Common issues include insufficient disk space, incorrect environment variables, database health issues (like invalid objects), or network connectivity problems. Once the issue is identified and resolved, you can resume the upgrade using `java -jar autoupgrade.jar -config autoupgrade.cfg -mode resume -job <job_name>`.
Q2: Can I rollback an AutoUpgrade if something goes wrong after the `deploy` phase?
A2: While AutoUpgrade has a `-restore` mode, it's primarily designed for situations where the upgrade process itself fails and AutoUpgrade can revert to a specific state. For a complete rollback after the database has been opened in 23ai and potentially modified, your primary and most reliable rollback strategy is to restore your full RMAN backup of the 19c database. This is why a comprehensive and tested backup is an absolute prerequisite. Ensure you have a clear plan for restoring the 19c database, including datafiles, control files, and redo logs, from your backup.
Q3: How does this database upgrade impact my PeopleSoft application, and what specific steps should I take for PeopleSoft post-upgrade?