Installing DSpace 7.0 with Oracle (and possible fixes)

[marianovelasco]

We have experienced problems installing DSpace 7.0 with Oracle 18 (both on Linux). Even though the developing team has made an effort to mantain Oracle as an alternate way to backend DSpace, the installation process seems to not work as is and some fixes are needed to get it done.

The steps below are meant to explain how to get a fresh install of DSpace with Oracle (no previous data). We have applied them too to migrate from versión 5 to 7. At migrating from a previous version of DSpace with existing data, the process is quite similar (almost the same), but some steps change a bit or depend on the version of DSpace you previoisly had and also on the data you could hold on table schema_version (this is the table that Flyway uses to keep track of schema changes through versions). For clarity, we will explain here the fresh installation process. When previous data exists (migrating from a previous version), although the steps are almost the same, it would be better explained on another issue.

The main issue at installation with Oracle stands at class DatabaseUtils. During installation (specially at migration), the method executeSql is called to execute sql queries or scripts against the undelying DBMS. The problem with Oracle is that using that method only a single query is admitted at each invocation, whereas Postgres admits an entire script with multiple sql queries at a time. In the Oracle case, if more than one sql query is passed it will fail with ORA-00933: SQL command not properly ended

Instead of building an empty 7.0 shema directly, the fresh install creates an older empty schema and then it goes step by step migrating it to 7. We assume it is done this way to make the same installation process compatible when previous data exists. These migration steps are normally based on a set of SQLs queries or scripts. These scripts are applied in two forms; mostly using Flyway but some are applied using specific classes. Flyway has no problem at applying several queries at a time against Oracle, but when a specific class is used instead, it tries to pass several queries at a time using a single executeSql and, as we mentioned above, it fails with Oracle.

The workaround, without recoding, is to let Flyway to run all the neccesary sql scripts, bypassing the taylor made migration classes. Fortunately, all specific migration classes call external sql files, instead of having the queries embed on them, so they can be easily moved to Flyway.

These are the steps that we have followed to fix it.

We start by doing the initial setup:

• dependencies installed
• Oracle up and running, user DSPACE7 created with permissions
• local.cfg correctly configured

Once executed the mvn (mvn -Ddb.name=oracle package) and the ant (ant fresh_install), we tried to execute the command 'dspace database migrate', that issued several errors (from now on, we will refer to the directory [dspace-src]/dspace-api/src/main/resources/org/dspace/storage/rdbms as [rdbms]).

There are two kinds of issues with the migration:

• Specific classes for migration steps have problems executing sql scripts with more than a query in it on Oracle. In this case, the solution is to move the multiple sql scripts to the directory [rdbms]/sqlmigration/oracle to let Flyway execute them, and substitute each one of them with a new one containing only the query 'select 1 from dual' The class wiill be executed, but nothing will be actually done. Later Flyway will perform the intended update.
• There are typos and wrong data types in some scripts. In that case, we correct them

The problems we have detected are in the following scripts:

1. [rdbms]/xmlworkflow/oracle/xml_workflow_migration.sql

Workaround:

• copy it to [rdbms]/sqlmigration/oracle/V5_7_2014_01_01__XMLWorkflow_Migration.sql (name changed to be executed in the proper order)
• edit the original to include only the line 'select 1 from dual'

2. [rdbms]/xmlworkflow/oracle/data_workflow_migration.sql

Workaround:

• move it to [rdbms]/sqlmigration/oracle/V5_7_2014_01_02__Xdata_XMLWorkflow_Migration.sql (name changed to be executed in the proper order)
• edit the original to include only the line 'select 1 from dual'

3. [rdbms]/sqlmigration/workflow/oracle/xmlworkflow/V6.0_2015.08.11__DS-2701_Xml_Workflow_Migration.sql

Workaround:

• move it to [rdbms]/sqlmigration/oracle/V6.0_2015.08.11__DS-2701_Xml_Workflow_Migration.sql (keeping its name)
• edit the original to include only the line 'select 1 from dual'

4. [rdbms]/sqlmigration/workflow/oracle/xmlworkflow/V7.0_2018.04.03__upgrade_workflow_policy.sql

Workaround:

• move it to [rdbms]/sqlmigration/oracle/V7.0_2018.04.04__upgrade_workflow_policy.sql (name changed to avoid a message complaining about a migration step already done)
• edit the original to include only the line 'select 1 from dual'

5. [rdbms]/sqlmigration/oracle/V7.0_2018.04.16__dspace-entities.sql

Workaround:

• change 'uuid' by 'RAW(16)' in lines 50 and 52
• comment line 59 (Oracle already has created the index entity_type_label_idx)

6. [rdbms]/sqlmigration/oracle/V7.0_2019.07.31__Retrieval_of_name_variant.sql

Workaround:

• change 'VARCHAR' by 'VARCHAR2(4000)' in lines 14 and 15
• change 'RENAME' by 'RENAME COLUMN' in lines 17 and 18

7. [rdbms]/sqlmigration/oracle/V7.0_2019.11.13__relationship_type_copy_left_right.sql

Workaround:

• change 'BOOLEAN DEFAULT FALSE' by 'NUMBER(1) DEFAULT 0' in lines 13 and 14

8. [rdbms]/sqlmigration/metadata/oracle/V7.0_2020.10.31__CollectionCommunity_Metadata_Handle.sql

Workaround:

• move it to [rdbms]/sqlmigration/oracle/V7.0_2020.11.01__CollectionCommunity_Metadata_Handle.sql (name changed to avoid a message complaining about a migration step already done)
• edit the original to include only the line 'select 1 from dual'

Important: Beware that a new clean build is required because changes in all these files are incorporated at compile and build time. The safer way could be:

mvn -Ddb.name=oracle clean package
ant update_code
ant fresh_install

Once fixed all these scripts, migration works fine and we finish with an empty database with a correct schema.

[tdonohue]

Closing, as I believe these have been fixed in #8028 (which was just merged). If any fixes are still necessary, please open up a new ticket or PR to help us resolve them.