Upgrading to Oracle Database 23ai (previously referred to as Oracle 23c) involves several important steps and considerations, particularly since it's a long-term support (LTS) release with significant new features focused on AI integration, JSON relational duality, enhancements to developer productivity, and support for next-generation applications.
Key Considerations Before Upgrading
Check Compatibility:
Ensure your current version is eligible for a direct upgrade (Oracle supports direct upgrade from 19c and later).
Upgrade Paths for Oracle Database 23ai
Flow:
Use Oracle’s Database Pre-Upgrade tool (autoupgrade.jar) to assess readiness.
Understand New Features
⦁ AI Vector Search: Integrates with vector databases for generative AI apps.
⦁ JSON Relational Duality: Combines JSON flexibility with relational integrity.
⦁ SQL Enhancements: New syntax and functions for developers.
⦁ Automatic Storage and Performance Tuning Enhancements.
Environment Preparation
⦁ Check Version Compatibility & Run Pre-Upgrade Checks
⦁ Verify Database Integrity, Check for corruption and Ensure DB_BLOCK_CHECKING, DB_LOST_WRITE_PROTECT are appropriately set
⦁ Check for Deprecated & Desupported Features
⦁ Backup your entire database.
⦁ Check Tablespaces and Undo Settings, Ensure enough free space in SYSTEM, SYSAUX, UNDO, TEMP, verify undo retention
⦁ Clean Up Database, Remove unused components and Clean up invalid objects
⦁ Gather Statistics
⦁ Test the upgrade in a non-production environment first.
⦁ Review hardware and OS requirements—Oracle 23ai might need updated system libraries or OS versions.
⦁ Schedule Downtime
Plan for appropriate downtime & Notify all users and application teams
High-Level Upgrade Steps
1. Download and Install Oracle 23ai Software
Download from Oracle Downloads.
Install the software in a new Oracle Home (don’t overwrite the old installation).
https://www.oracle.com/database/free/
https://container-registry.oracle.com/ords/f?p=113:4:13231839633032:::4:P4_REPOSITORY,AI_REPOSITORY,AI_REPOSITORY_NAME,P4_REPOSITORY_NAME,P4_EULA_ID,P4_BUSINESS_AREA_ID:1863,1863,Oracle%20Database%20Free,Oracle%20Database%20Free,1,0&cs=36CoLngVeCfTqAcdt8kdPDM-LpQVv8Uo3CBH8ErXg0v77WTlB-7wybwTkPbbebs7VLvuHqMB8Nog36SbD-jwehA
2. Run Pre-Upgrade Checks
$ java -jar autoupgrade.jar –version
⦁ java -jar autoupgrade.jar -mode analyze -sourcehome <OLD_ORACLE_HOME> -targethome <NEW_ORACLE_HOME> -sid <DB_SID>
⦁ Fix any issues identified by the report.
3. Perform the Upgrade
You can do this using AutoUpgrade, DBUA (GUI-based), or manual methods. AutoUpgrade is the preferred method.
---> java -jar autoupgrade.jar -mode deploy -config upgrade_config.cfg
Where upgrade_config.cfg includes parameters like:
global.autoupg_log_dir=/u01/app/oracle/upgradelogs
upg1.source_home=/u01/app/oracle/product/19c
upg1.target_home=/u01/app/oracle/product/23ai
upg1.sid=ORCL
upg1.log_dir=/u01/app/oracle/upgradelogs/ORCL
upg1.upgrade_node=localhost
4. Post-Upgrade Steps
Run dbupgdiag.sql and utlrp.sql to recompile invalid objects.
- Validate application functionality.
- Update statistics and test performance.
- Begin using new features gradually after stability is confirmed.
Post-upgrade tasks
After upgrading to Oracle 23ai, it's essential to perform a series of post-upgrade tasks to ensure your system is stable, optimized, and ready to take advantage of the new features. Here's a checklist of recommended tasks:
1. Validate the Upgrade
Check upgrade logs:
--> catupgrd0.log, autoupgrade logs, or DBUA logs (depending on method).
--> Run dbupgdiag.sql (included in Oracle-supplied scripts) to analyze the upgrade status.
Confirm the new version:
--> SELECT * FROM v$version;
2. Gather Statistics
Gather dictionary stats:
--> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Gather fixed object stats:
-->EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Gather system and object stats if not already done during upgrade:
--> EXEC DBMS_STATS.GATHER_DATABASE_STATS;
3. Recompile Invalid Objects
--> Run the recompilation script:
@$ORACLE_HOME/rdbms/admin/utlrp.sql
Check for invalid objects:
--> SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID';
4. Test Application Compatibility
⦁ Thoroughly test all applications for performance and compatibility.
⦁ Validate custom PL/SQL, Java code, and any dependencies.
5. Backup the Upgraded Database
Take a full RMAN backup immediately after a successful upgrade:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
6. Enable/Review New 23ai Features
Consider enabling features like:
⦁ AI Vector Search (if applicable to your use case).
⦁ JSON Relational Duality Views.
⦁ SQL Enhancements like new syntax or built-in functions.
7. Update Environment Variables and Scripts
Update ORACLE_HOME, PATH, TNS_ADMIN, etc.
Modify any custom scripts, monitoring tools, or backup jobs to point to the new Oracle version paths.
8. Review and Tune Initialization Parameters
Some deprecated/obsolete parameters may need to be removed.
SELECT name, value, isdefault, isdeprecated FROM v$parameter;
9. Remove Obsolete Components (Optional)
Consider removing unused or deprecated components:
SELECT comp_id, comp_name, version, status FROM dba_registry;
10. Enable New Optimizer Features (Optional, After Testing)
Oracle may retain older optimizer behavior for compatibility. Consider explicitly enabling the latest behavior:
ALTER SYSTEM SET optimizer_features_enable='23.0.0' SCOPE=SPFILE;
Source:
https://docs.oracle.com/en/database/oracle/oracle-database/23/fppad/upgrading-oracle-grid-infrastructure-to-a-later-release.html
AutoUpgrade - My Oracle Support Doc ID 2485457.1
Upgrade Paths
From Version Direct Upgrade to 23ai?
19c -----> Yes
18c -------> Use intermediate upgrade to 19c first
No comments:
Post a Comment