Fix OTRS oracle to OTOBO oracle migration.

[svenoe]

The biggest issue here is, that table names seems to be uppercase, although OTRS and OTOBO lc them everywhere. It works for normal usage, as the table_name in a "SELECT x,y FROM table_name;" is handled case insensitive, but for commands which handle the tables itself "WHERE ...='table_name'" it fails.

Also some "needed" arguments are wrong for oracle, in the setup.

[bschmalhofer]

See some notes regarding Oracle and OTOBO at https://github.com/bschmalhofer/otobo-ideas/blob/master/oracle.md.

[bschmalhofer]

Everything looks find after docker system prune -aand restarting from scratch. Let's assume that this is some strange effect that came from moving /var/lib/docker. Discarding this issue.

[bschmalhofer]

Oops, looks like I closed the wrong issue. Reopening.

[bschmalhofer]

After some fiddling I got one OTRS and one OTOBO up and running with Oracle. Now, testing migration.pl, I was flabbergasted again. Under both OTRS and OTOBO the default DSN configuration in Config.pm looks like

$Self->{'DatabaseDSN'} = "DBI:Oracle://$Self->{DatabaseHost}:1521/$Self->{Database}";

This is a nice EZCONNECT connection string. But the migration wants to use a DSN like

$Param{OTRSDatabaseDSN} = "DBI:Oracle:sid=$Param{DBSID};host=$Param{DBHost};port=$Param{DBPort};";

There can be reasons to use the SID or better still to use the service declarations in tnsnames.ora, but there is no reason to use different approaches.

[bschmalhofer]

I checked when the quoting of table names was introduced. It's a blast from the recent past. Quoting was introduced as a fix for groups no longer being a valid table name. But as quoting really interferes on Oracle, I will roll that back. See also #639.

Looks like there are only two relevant instances where groups must be quoted. The other instances are related to the batch migration, which was disabled. I removed the remnants of the batch insert, just to clean up.

[bschmalhofer]

Current stumbling blcok:
` Message: ORA-02266: unique/primary keys in table referenced by enabled foreign keys (DBD ERROR: error possibly near <> indicator at char 15 in 'TRUNCATE TABLE <>article'), SQL: 'TRUNCATE TABLE article'

This is bad, as there is no global setting in Oracle, that disables foreign key checks. How could that ever have worked? The best bet might be to make a complete copy of the database and to tweak it afterwards.

TODO:

• figure out the commands for copying Oracle databases, SQL Developer or SQL Loader
• truncate the 13 skipped tables
• handle initial INSERTs of the skipped tables if there are any
• no need to change neither the character set not the collation
• no need to shorten columns
• rename the tables article_data_otrs_chat and groups, RENAME handles foreign key references already
• check whether triggers or sequences have to be adapted

[bschmalhofer]

There are some dependencies between the table that should not be migrated. But TRUNCATE should be possible when adhering to this order:

• cloud_service_config
• communication_log_obj_lookup
• communication_log_object_entry
• communication_log_object
• communication_log
• gi_debugger_entry_content
• gi_debugger_entry
• package_repository
• process_id
• scheduler_recurrent_task
• sessions
• system_data
• web_upload_cache

[bschmalhofer]

Discussed the TODO list with @svenoe . There is no need to meddle with installer.pl. The recommended sequence for Oracle -> Oracle migration is:

1. Install a fresh OTRS system with installer.pl
2. Remove the otobo schema
3. Copy the otrs schema to the new otobo schema
4. Run the database update scripts
5. Run migration.pl

The very first step is to turn the lookup hashref DBSkipTables() into an ordered list.

[bschmalhofer]

The first dummy migration has completed. However the documentation is still lacking. Next step is to test the process with real data.

[bschmalhofer]

Instructions for Oracle to Oracle migration are part of RotherOSS/doc-otobo-installation#64.

[bschmalhofer]

RotherOSS/doc-otobo-installation#64 has been merged. Closing this issue, as there are no new problem reports concerning Oracle migrations.