Spotfire Server 14.x fails to start due to unexpected database constraints and indexes

Description:

When attempting to start a successfully installed Spotfire Server 14.x on an Oracle database that already contains a separate schema for an existing, separate Spotfire Server 11.4 instance, the new server fails to start due to database consistency verification errors. These errors indicate unexpected constraints and indexes on tables that belong to the Spotfire 11.4 schema, despite the new installation using a separate schema with a different Oracle user:

FATAL 2024-09-18T10:43:01,990+0100 [*Initialization*] spotfire.server.LifecycleManager: Spotfire Server will not be started. Database consistency verification failed. Errors found:
Unexpected constraint for table LIB_PRINCIPAL_PROPS: OracleDatabaseConstraint[
  name: FK_LPP_LIB_ITEMS
  type: R
  searchCondition: null
]
Unexpected constraint for table LIB_DATA: OracleDatabaseConstraint[
  name: FK_LIB_DATA_ITEM_ID
  type: R
  searchCondition: null
]
Unexpected index for table LIB_DATA: OracleDatabaseIndex[
  name: PK_LIB_DATA_ITEM_ID
  uniqueness: UNIQUE
]

Solution:

1. Verify that the new Spotfire Server 14.x installation is using a separate schema from the existing Spotfire  Server 11.4 instance.

2. Enable trace logging for the Spotfire Server before attempting to start it:

   Follow the instructions at: https://docs.tibco.com/pub/spotfire_server/14.0.4/doc/html/TIB_sfire_server_tsas_admin_help/server/topics/changing_the_logging_level_for_a_server_or_node_that_is_not_running.html
Use the value "ActiveConfig=logging-trace.properties" in step 5.
3. Work with your database administrator to monitor Oracle logs during Spotfire Server startup:

 - Focus on SQL queries sent by the Spotfire 14.x Oracle user.
 - Look for queries like "select * from user_constraints where table_name='LIB_DATA'" that do not include a "where owner = '<Spotfire 14.x user>'" clause.
 - If the query monitoring reveals that Spotfire is querying constraints without limiting to its own schema, report this to Revvity Signals Spotfire Support as a potential defect.

As a workaround, consider one of the following options:

 - Use a separate Oracle database instance (not a separate schema) for the new Spotfire Server 14.x installation.
 - Work with your database team to create a new, isolated schema for Spotfire Server 14.x that prevents cross-schema visibility.

If the issue persists, gather all relevant logs and database query results, then contact Revvity Signals Spotfire Support for further assistance.

Note: This issue is currently under investigation by Spotfire Support. Future updates or patches may address this problem in multi-schema Oracle deployments.