How to upgrade an Oracle database from 12c to 19c

Upgrading the Oracle database from 12c to 19c requires first verifying system compatibility, performing a full backup, running pre-upgrade tools and fixing problems; choosing an in-place or off-site upgrade method, using DBUA or manual scripts to implement the upgrade; after upgrading, recompile invalid objects, collect statistics, verify components, and test application functions.

Upgrading an Oracle database from 12c to 19c is a significant step that brings performance improvements, new features, and extended support. The process requires careful planning and execution to ensure data integrity and minimal downtime. Below are the key steps and best practices for a successful upgrade.

1.Pre-Upgrade Preparation

Before starting the upgrade, verify that your current environment meets the requirements for Oracle 19c.

• Check System Requirements: Ensure your operating system, hardware, and kernel parameters are compatible with Oracle 19c.
• Review Support Matrix: Confirm that your version of 12c (eg, 12.1 or 12.2) supports direct upgrade to 19c. Direct upgrades from 12.1 may require intermediate steps or patches.
• Backup Your Database: Perform a full backup including datafiles, control files, and the recovery area. Use RMAN for a consistent backup.
• Run Pre-Upgrade Information Tool: Execute the preupgrade.jar script from the Oracle 19c home to identify potential issues. This tool generates fix-up scripts and recommendations.
• Resolve Issues Reported: Address deprecated parameters, invalid objects, or missing components flagged by the pre-upgrade tool.

2. Upgrade Methods

Oracle offers several methods to perform the upgrade. Choose the one that fits your availability and infrastructure needs.

• In-Place Upgrade: Upgrade the existing 12c Oracle Home directly to 19c. Install Oracle 19c software first, then use Database Upgrade Assistant (DBUA) or manual SQL scripts to upgrade the database in place.
• Out-of-Place Upgrade: Create a new Oracle 19c home and migrate the database using Data Pump, transportable tablespaces, or physical standby switchover (rolling upgrade).
• Using DBUA: The Database Upgrade Assistant simplifies the process with a GUI, automatically handling prerequisites, parallel upgrades, and post-upgrade tasks.
• Manual Upgrade via SQL*Plus: For greater control, shut down the 12c database, start it in UPGRADE mode using the 19c binaries, and run catctl.pl and catuppst.sql to upgrade the data dictionary.

3. Perform the Upgrade

Execute the chosen upgrade method carefully.

• Shut Down 12c Database: Stop all services and listeners associated with the 12c instance.
• Start with 19c Oracle Home: Set ORACLE_HOME to the 19c installation and start the database in UPGRADE mode.
• Run the Upgrade Scripts: Use catctl.pl (parallel upgrade utility) to execute the upgrade. Example: perl catctl.pl -noui catupgrd.sql
• Monitor Logs: Check the generated logs (eg, catupgrd0.log) for errors or warnings during the upgrade process.

4. Post-Upgrade Tasks

After the upgrade completes, validate and optimize the upgraded database.

• Recompile Invalid Objects: Run utlrp.sql to recompile any invalid PL/SQL packages or views.
• Update Optimizer Statistics: Gather dictionary statistics using dbms_stats.gather_dictionary_stats.
• Verify Components: Query dba_registry to confirm all components are valid and at the correct version.
• Test Applications: Validate connectivity and functionality of dependent applications.
• Remove Old Oracle Home: Once confirmed stable, deinstall the 12c Oracle Home using Oracle Universal Installer (OUI).
• Enable New 19c Features: Take advantage of features like Automatic Indexing, Active Data Guard enhancements, and improved JSON support.

Upgrading from Oracle 12c to 19c doesn't have to be disruptive if you follow a structured approach. Test the procedure in a non-production environment first, document every step, and keep rollback plans ready. With proper preparation, most upgrades complete smoothly.

The above is the detailed content of How to upgrade an Oracle database from 12c to 19c. For more information, please follow other related articles on the PHP Chinese website!