Navigating and Resolving Common Challenges in Oracle Database 19c Upgrades

Navigating and Resolving Common Challenges in Oracle Database 19c Upgrades

While the upgrade to Oracle Database 19c brings powerful new capabilities, it is not uncommon for experienced professionals to encounter a variety of issues. These challenges can range from straightforward configuration errors to more complex, application-specific problems. A detailed understanding of these potential pitfalls and their solutions is crucial for a smooth and efficient upgrade. This guide expands on common issues and provides deeper insights into their causes and fixes.

---

1. Character Set Issues

A often-overlooked but critical aspect of an upgrade is the database character set. Oracle 19c has specific requirements that can cause problems if not addressed pre-upgrade.

Symptoms:

• The pre-upgrade utility reports a character set that is not supported.

• Errors during the upgrade indicate a failure to convert data.

• Data corruption or display issues after the upgrade, especially with NVARCHAR2, NCHAR, and NCLOB data types.

Troubleshooting:

• Check the national character set: The National Character Set (NLS_NCHAR_CHARACTERSET) is distinct from the main database character set. In 19c, it must be either UTF8 or AL16UTF16. Use the following query to check your current setting:

  SQL

  SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';
  

• Convert the character set: If your character set is not a supported one, you must perform a conversion before the upgrade. This is a complex procedure that may require a full database export and import, or using the ALTER DATABASE CHARACTER SET command in a controlled environment. A test run is absolutely essential to validate the conversion and ensure no data loss or corruption occurs.

---

2. Password Case Sensitivity and Security

Oracle 19c enforces stronger security standards, which can impact applications and users relying on older, case-insensitive password behavior.

Symptoms:

• Users are unable to log in to the database after the upgrade, even with the correct password.

• Application services that authenticate with the database fail to connect.

• The error ORA-28040: No matching authentication protocol may appear, although it can also have other causes.

Troubleshooting:

• Enable case-sensitive passwords: The recommended long-term solution is to migrate to case-sensitive passwords. This aligns with modern security practices. For applications like Oracle E-Business Suite, there are specific Metalink notes (e.g., Doc ID 1581584.1) that detail the steps for this migration.

• Review sqlnet.ora: If an immediate client-side upgrade or password change is not feasible, you can adjust the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter in the sqlnet.ora file on the server. Setting it to an older version (e.g., 11) can allow older clients to connect, but this should only be a temporary measure as it weakens security.

---

3. Optimizer Behavior Changes and Query Performance

A common complaint after an upgrade is that a previously fast query is now slow. The optimizer in 19c has been enhanced, and it may choose a different, and sometimes less optimal, execution plan.

Symptoms:

• Queries that performed well on the source database are now running significantly slower.

• The new execution plan involves different join methods (e.g., Hash Join instead of Index Range Scan) or access paths.

• AWR reports show a change in top SQL statements by elapsed time.

Troubleshooting:

• Gather dictionary and fixed object statistics: Immediately after the upgrade, a crucial step is to gather new statistics. The 19c optimizer relies on up-to-date information to make informed decisions.

  SQL

  EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
  

• Utilize SQL Plan Management (SPM): For mission-critical queries, you can "lock in" the old, good execution plan. Capture the plans on the source database and then migrate them to the 19c environment. SPM will ensure the optimizer uses the stable, verified plan.

• Tune the specific query: Use tools like DBMS_XPLAN to compare the old and new execution plans. You can then use hints or a SQL patch to guide the optimizer toward a more efficient plan.

• Check deprecated parameters: Verify that you have not carried over any initialization parameters from the old database that are now deprecated or have different default values in 19c. This can significantly impact optimizer behavior.

---

4. Database Links and Connections

After an upgrade, database links that connect to other databases (especially older versions) may fail. This is often due to the same security protocol mismatches mentioned earlier.

Symptoms:

• ORA-01017: invalid username/password; logon denied on a database link.

• ORA-12541: TNS:no listener or other TNS errors, even when the listener is running.

Troubleshooting:

• Update TNSNAMES.ORA: Ensure the TNSNAMES.ORA file in the new 19c Oracle Home is correctly configured for all database links. The file may also need to be updated on client machines.

• Check the listener: Confirm that the listener in the 19c Oracle Home is running and is configured to accept connections for the upgraded database.

• Address the authentication protocol: If the database link connects to a pre-12c database, you may need to configure the sqlnet.ora file on the 19c server to allow older authentication protocols.

---

5. Issues with Non-CDB to CDB Conversion

Oracle 19c is the last long-term support release that allows a non-CDB architecture. Starting with Oracle 21c, the multitenant architecture is mandatory. Many professionals choose to convert to a Container Database (CDB) and Pluggable Database (PDB) structure during the 19c upgrade. This process introduces its own set of potential issues.

Symptoms:

• Upgrade fails with errors related to the non-CDB to CDB conversion.

• PDBs fail to open or show invalid status.

• ORA-01722: invalid number if you attempt to upgrade a non-CDB without converting it to a multitenant architecture in later releases.

Troubleshooting:

• Use the dbca utility: When converting a non-CDB to a PDB, the dbca utility is the recommended tool. It simplifies the process and handles most of the complexities.

• Check the pre-upgrade report: The pre-upgrade utility will identify potential conflicts and issues with converting to a multitenant architecture. It will recommend actions like removing UTL_FILE_DIR or other deprecated features that are incompatible with the new structure.

• Validate PDBs: After the conversion, check the status of your PDBs. Use ALTER PLUGGABLE DATABASE ALL OPEN; and verify their status in V$PDBS.

By systematically working through these potential issues, you can significantly reduce upgrade downtime and ensure a smooth transition to Oracle Database 19c.

---

Video Resources

This video provides an excellent deep dive into the most common errors encountered during a 19c upgrade with DBUA and how to troubleshoot them effectively.

Oracle 19c - DBUA common errors

A detailed explanation of why the Oracle Optimizer might change its mind after an upgrade and how to use SQL Plan Management to maintain performance stability.

Oracle: SQL Performance After Upgrades