Summary

When upgrading Confluence to 7.11+ the upgrade fails and throws an ORA-00972 error in the UI and logs.

Environment

• 7.11+
• Server or Data Center
• Oracle Database 11g R2, 12c R1 & R2, 19c

Diagnosis

Observer the following error in the UI:

Upgrade failed. Please consult the system logs for details. You will need to fix these problems, restore your database and confluence home directory to the pre upgrade state. Then retry the upgrade. Upgrade error message: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedSpacePermissionsUpgradeTask@37fee42c failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [ALTER TABLE DENORMALISED_SPACE_CHANGE_LOG MODIFY (ID DEFAULT DENORMALISED_SPACE_CHANGE_LOG_SEQ.NEXTVAL)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long

  <home-directory>/logs/atlassian-confluence.log will also call out "ORA-00972: identifier is too long"

Cause

Confluence 7.11 introduces an identifier that is longer than 30 characters and removes support for Oracle 12c R1, which has a 30-byte character limit. Oracle 12c R2 and beyond increase the limit to 128 bytes. When that error is thrown, either the Oracle database is running 12c R1 or is running in compatibility mode.

If you are running a newer version, check if compatibility mode is enabled with the following query:

SQL> SELECT name, value FROM v$parameter
         WHERE name = 'compatible';

 source: Migration issue Oracle: ORA-00972: identifier is too long

Solution

For more details about the Oracle compatibility mode please review:

• What is Oracle Database compatibility

1. Stop Confluence
2. Rollback the upgrade

3. Disable compatibility mode. Since Oracle compatible setting doesn't have on/off setting, run the below query to set the compatible version to match the current Oracle database version: 

   Example to Disable the COMPATIBLE mode in 12c R2

   SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0.1' SCOPE=SPFILE;

4. Perform the Confluence upgrade again