13 Migration of EMM Database to MariaDB or Oracle DBMS

Since EMM 22.10 was the last version supporting MySQL 5.7, we recommend migrating to the open source DBMS MariaDB, because you only have to exchange the DBMS software, which is little effort. You may also migrate to the commercial Oracle DBMS instead, but this requires more effort (and additional budget to pay for the Oracle software).

13.1 Migrating from MySQL to MariaDB

If you want to migrate from MySQL to MariaDB, we recommend this procedure:

stop all EMM services and the MySQL DBMS
export the database content to have a backup just in case, for instance using command mysqldump:
# mysqldump -aCceQx --hex-blob --routines --triggers -u root -p -r emm.sql emm
change your DBMS from MySQL 5.7 to MariaDB 10.2 (because these versions offer the maximum of compatibility, see https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/moving-from-mysql)
run mariadb-upgrade to migrate the DB structure from MySQL to MariaDB
change file dbcfg in directory /opt/agnitas.com/etc for MariaDB
start MariaDB
start EMM services

Make sure that the database user used by EMM has the required rights. If the user name is agnitas, we recommend to create the user this way:

CREATE USER 'agnitas'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON emm._ TO agnitas;
GRANT SUPER ON *.* TO agnitas;
FLUSH PRIVILEGES;

If the EMM database does not work correctly, import it from your backup instead (for instance using command mysql)

Now, that you are running EMM with MariaDB 10.2, which is no longer supported, we recommend to update to a later version of MariaDB. See section MariaDB for the version we use ourselves for the EMM public cloud.

MariaDB recommends these steps to upgrade a MariaDB version:

Modify the repository configuration, so the system's package manager installs the required version.
Stop MariaDB.
Uninstall the old version of MariaDB.
Install the new version of MariaDB.
Make any desired changes to configuration options in option files, such as my.cnf. This may include removing any options that are no longer supported.
Start MariaDB.
Run mariadb-upgrade.

13.2 Migrating from MySQL to Oracle DBMS

If you want to migrate the EMM database from MySQL to Oracle DBMS, you can not simply exchange the DBMS software, but you have to export the EMM database from the MySQL DBMS into files and import the data from the files into the Oracle DBMS.

We recommend the use of Java export and import tools DbExport.jar and DbImport.jar.

The process to migrate an EMM database from MySQL DBMS to Oracle DBMS requires eight steps in a certain order. Please read the description for all eight steps carefully before you start the migration to get a feeling how the migration works and how long it will take. We strongly recommend to execute the database migration in a testing environment first, before starting with the production environment.

Please make sure that you have only one EMM database running on the old system and on the new system. If you have a second EMM database running for testing and grant EMM access to both databases, this will cause trouble when you try to save data in the EMM database!

13.2.1 Step 1: Set up New Database

Initially, re-create all tenants of the MySQL-based EMM database in the GUI of the EMM instance using the Oracle DBMS. This creates all required database tables, indices, constraints, triggers and procedures so that the import script can later load the data exported from MySQL into the empty tables of this tenant.

To make sure that the Oracle database user you use has the required rights, we recommend to create it this way:

CREATE USER dbuser IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE TO dbuser;
GRANT CREATE SESSION, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, CREATE SYNONYM, ALTER SESSION, UNLIMITED TABLESPACE TO dbuser;
ALTER USER <dbuser> DEFAULT tablespace data_default;

Replace placeholder <dbuser> with the name of your database user.

To make sure the company IDs of the tenants in the Oracle database are identical with the company IDs of the tenants in the MySQL database, you should create the new tenants in the Oracle database in the same order as they were created in the MySQL database.

The first tenant with ID 1 is usually the master tenant to manage the whole EMM instance. If there is a tenant you do not want to migrate, just create an empty tenant in the Oracle database. This makes sure that the company ID of the next tenant you will create is incremented by 1.

Alternatively, if you want to skip several company IDs and if you do not want to create lots of empty tenants, you can adjust the sequence creating the company IDs so that it continues with a certain ID:

DROP SEQUENCE company_tbl_seq;
CREATE SEQUENCE company_tbl_seq START WITH <next_id> INCREMENT BY 1;

Just replace placeholder <next_id> with the ID of the next tenant you want to create.

13.2.2 Step 2: Add Customized Columns and Tables

Customized Columns:

Retrieve in the EMM GUI in sub-menu Profile fields of menu Recipients all of your custom fields (the ones with a delete button at the end of the line). Make a screenhot of the settings for each field and re-create these profile fields in the same tenant of the new Oracle-based EMM instance.

Take great care to not confuse the IDs of the tenants and to use exactly the same names and settings for each profile field again, so that all required profile fields are exactly re-created in the corresponding tenant (like for example the settings for profile field histories). Otherwise, the data import will fail later because of missing matching fields or incompatible settings. (Of course, you may change settings of profile fields later, but only after the migration has been completed.)

If you have lots of custom fields, you can use our ProfileFieldMigration tool instead. Syntax for this tool is

java -jar '/<path_to_tool>/ProfileFieldMigration.jar' <company_id> <source_db_vendor> <source_db_host:source_db_port> <source_db_user> <source_db_password> <destination_db_host:destination_db_port> <destination_db_user> <destination_db_password>

Example:

java -jar '/home/console/ProfileFieldMigration.jar' 2 mysql emm-source:3306 myuser mypw emm-target:1521 orauser orapw

Customized Tables:

If you use reference tables or voucher code tables in your tenant, you have to re-create those tables and their fields in the Oracle-based EMM database in the same way: In the EMM GUI go to menu Data management, sub-menu Reference tables and make a screenshot of the settings for every existing reference tables including the list of field details (field name, type, default value and field is allowed to be empty). Finally, re-create those tables including their fields on the EMM instance using the Oracle DBMS with exactly the same settings to make sure that the data import will not fail later due to missing matching fields or incompatible settings. (Of course, you may change settings for reference tables later, but only after the migration has been completed.)

If you have lots of reference tables, you can use our ReferenceTableMigration tool instead. Syntax for this tool is

java -jar '/<path_to_tool>/ReferenceTableMigration.jar' <company_id> <source_db_vendor> <source_db_host:source_db_port> <source_db_user> <source_db_password> <destination_db_host:destination_db_port> <destination_db_user> <destination_db_password>

Example:

java -jar '/home/console/ReferenceTableMigration.jar' 2 mysql emm-source:3306 myuser mypw emm-target:1521 orauser orapw

If you use highly customized reference tables structures like (virtual) alias reference tables or EMM system tables mapped to reference tables, please consult the AGNITAS support team for migration advice.

13.2.3 Step 3: Export Data

Before you export the EMM database, stop all EMM backend and frontend services (except for the mailer service who does not access the database directly) on the MySQL-based instance to avoid read and especially write operations during the export.

Export all tables of the EMM database from your MySQL DBMS like this:

java -jar DbExport.jar mysql <hostname_of_dbms> -secure emm <username> <password> -export "*" -output "<path_to_output_files>" -z -v -dateFormat "dd.MM.yyyy" -dateTimeFormat "dd.MM.yyyy HH:mm:ss"

Please replace all placeholders indicated by angle brackets with the appropriate values. Option -secure is only required when you use an encrypted connection to your DBMS. This command will generate a zip file for every table of the EMM database.

You can get extensive information on the usage of DbExport.jar with

java -jar DbExport.jar -help

13.2.4 Step 4: Adjust Data

Due to some differences in the database schema of the MySQL-based EMM database and the Oracle-based EMM database, you have to make some manual adjustments in the export files which are listed below:

Delete the following files (in case they exist) so that the tables are removed before the import starts and do not overwrite data in the new database:

agn_dbversioninfo_tbl.csv.zip
ahv_<cid>_tbl.csv.zip
ahvencrypt_<cid>_tbl.csv.zip
campaign_editor_tbl
cust_<cid>_devicehistory_tbl.csv.zip
deliver_<cid>_tbl.csv.zip
mailtrack_process_tbl.csv.zip
mailtrack_process_<id>_tbl.csv.zip (<id> is a placeholder for any two letters)
mia_lastrun_tbl.csv.zip
prevent_table_drop.csv.zip
ref_vc_vtest_<cid>_tbl.csv.zip
stats_period_tbl.csv.zip
undo_id_seq.csv.zip
ws_admin_binding_tbl
ws_admin_tbl

where <cid> is the placeholder for the ID of the tenant.

If you have deleted tenants in your old EMM database, you should delete the following tables in case they still exist:

cust <cid>_ban_tbl
customer_<cid>_binding_tbl
customer_<cid>_tbl
hst_customer_<cid>_binding_tbl
hst_customer_<cid>_tbl
interval_track_<cid>_tbl
mailtrack_<cid>_tbl
onepixellog_<cid>_tbl
onepixellog_device_<cid>_tbl
rdir_traffic_agr_<cid>_tbl
rdir_traffic_amount_<cid>_tbl
rdirlog_<cid>_ext_link_tbl
rdirlog_<cid>_tbl
rdirlog_<cid>_val_alpha_tbl
rdirlog_<cid>_val_num_tbl
rdirlog_userform_<cid>_tbl
success_<cid>_tbl

Again, <cid> is the placeholder for the ID of the (deleted) tenant.

13.2.5 Step 5: Import Data

Before you import the EMM database, stop all EMM backend and frontend services (except for the mailer service who does not access the database directly) on the Oracle-based instance to avoid read and especially write operations during the export.

Import the content of the EMM database into the tables of your Oracle DBMS like this:

java -jar DbImport.jar oracle <hostname_of_dbms>:<port> -secure <SID> <username> <password> -table "*" -import "<path_to_input_files/*.csv.zip>" -i clearinsert -deactivatefk -v -l -dateFormat "dd.MM.yyyy" -dateTimeFormat "dd.MM.yyyy HH:mm:ss"

Please replace all placeholders indicated by angle brackets with the appropriate values. The default SID of the EMM database is emm. Option -secure is only required when you use an encrypted connection to your DBMS.

You can get extensive information on the usage of DbImport.jar with

java -jar DbImport.jar -help

If the import tool does not find the corresponding database table for a certain zip file to import, it will not start the import process and list the name of the zip file. Now, you can check whether the table is really missing in the EMM database (like a reference table) or if the import file holds only the data of a temporary table (usually indicated by prefix or suffix tmp in the table's name). If the latter is the case, you can safely delete the zip file. (If in doubt, please contact the AGNITAS support team.)

Alternatively to the command line mode you can use command

java -jar ~/apps/DbImport.jar -l -gui

to start the GUI of the import tool and you can enter all required parameters there like username, password, hostname, port (optional), dbvendor ("oracle") and security of connection.

For table name use wildcard "*" and for the import path the same path you used for the export. Import mode should be "CLEARINSERT" and data format "CSV". We also recommend to check boxes "foreign key constraint deactivate" and "overwrite values with null".

13.2.6 Step 6: Check for database fields which are too small

When you create a text based data field in MySQL and define a length of, for example, 100 characters, you can really save 100 characters in this database field. But Oracle, for historical reasons, creates a database field with a length of 100 bytes. If you store only ASCII characters in this field, it would make no difference. But if you store UTF-8 encoded characters, a single character may need up to 4 bytes of storage.

This can lead to the situation that the content of a MySQL database field which uses UTF-8 characters does not fit into the corresponding field of the Oracle database. When you use the import tool in GUI mode, it lists all failed imports with the name of the import file and text ERROR (<description of error cause>).

In this case, go to subdirectory importlogs/ and check the file which could not be imported. It will contain an error message like

SQL error: ORA-12899: value too large for column "CLIENT"."NAME_TBL"."DATA" (actual: 202, maximum: 200)

This error message indicates, that a value in the import file for field data is 202 bytes long, but field data in the Oracle database has only a capacity of 200 bytes.

In this case you should increase the size of database field data in table name_tbl with the following SQL statement to the minimum of 202 needed bytes:

ALTER TABLE name_tbl MODIFY data VARCHAR2(202);

13.2.7 Step 7: Adjust Sequences Values

Before you start using the new EMM database, adjust the already existing Oracle sequences, because for historical reasons we use sequences instead of auto-increments for Oracle. To do this, use Oracle's sqlplus to execute script update_sequences.sql in directory /home/console/release/emm/<emm_version>/WEB-INF/sql/oracle/ of the server running the GUI service with command

/path/to/sqlplus <username>@<SID> @/home/console/release/emm/<emm_version>/WEB-INF/sql/oracle/update_sequences.sql

Please make sure that the EMM version you use for placeholder <emm_version> is the same version as the EMM version running in your production environment with the EMM database you want to migrate. To be able to execute this script you need the database permissions of the EMM database user.

13.2.8 Step 8: Convert SQL of Target Groups

Depending on the complexity of a target group the resulting SQL code may contain DBMS specific commands or syntax. Therefore, log into the EMM GUI as super user emm-master, select the client whose target groups are to be converted in menu Administration, sub-menu Clients, activate the slider named Regenerate all targetgroup definitions once in tab Intern of box Settings and click button Save to finish the conversion.

Please be aware that any target group with status "locked" will not be converted. Therefore, you should unlock all locked target groups before converting and you may lock them again afterwards.

13.2.9 Migration Completed

Congratulations, you have successfully completed the migration of your EMM database from MySQL to Oracle DBMS. After you have restarted EMM, you can start working with your new Oracle-based instance of EMM.

13.2.10 Troubleshooting

If trackable links do no longer work for a certain tenant after migration, the reason could be the content of database field secret_key in table company_tbl. This field is used to encrypt data in trackable links and if a tenant was created with a EMM version before 25.11, it can contain non-ASCII characters. These non-ASCII characters may be changed by the migration process. Therefore, please compare the content of this field in the old database with the new database and correct the content in the new database if necessary.