Its very nice question for dba.

As I said, This is a very relevant topic for DBAs, as we often need to upgrade customer databases. Some applications allow downtime for maintenance, while others require zero or minimal outage during the upgrade process. Therefore, it’s essential to choose the right upgrade approach based on the application’s tolerance for downtime.

The principles and strategies discussed here can also be applied to SQL Server (MSSQL) and PostgreSQL databases, with adjustments for platform-specific tools and features.

Upgrade Planning Guide: From 19c/21c to 23ai

1. Pre-Upgrade Assessment

• If avaliable run the Oracle Database Pre-Upgrade Tool (preupgrade.jar) to detect issues.
• Be sure application certified with new database release.
• Check 3rd-party tools, drivers (e.g., JDBC/ODP.NET), and middleware compatibility before upgrade

2. Choose Upgrade Method

If you’re on 11gc and want to move higher version:

• In-Place Upgrade: Upgrade existing instance using DBUA or command line (fast, but riskier).
• Data Pump Export/Import: Good for moving to a new server/cloud or restructuring.
• Transportable Tablespaces: Best for large databases (>1TB).
• Goldengate: If you require zero or minimal outage during the upgrade process this product is so usefull.

3. Testing

• Use Real Application Testing (RAT) or SQL Performance Analyzer.
• Validate stored procedures, triggers, indexing behavior, and application logic.
• Ensure that the application runs its entire workflow on the upgraded database and completes all functional and regression tests on your test upgrade environment before moving to production.