It is short description of our approach to fast upgrade of Omnitracker with 800GB database.
The initial configuration was not so obvious as you could think:
- Linux cluster with Oracle DB 11.2
- Windows server 2012 – CPU with 1 core and 16GB RAM
- Omnitracker 10.0.100
It was working fine for almost last 10 years without too many pitfalls. However, when there was a problem it was not so obvious to fix, because of big complexity of the system: scheduled import of data from Active Directory, several imports from csv, Excel or Access files, 20 different exports in different formats, 4 different Email interfaces with external systems (itsm, otrs, ServiceNow and proprietary event system), 10 different incoming emailboxes, 10 different ougoing emailboxes, very complex workflows with additional actions after and before every state and the best – almost non existing documentation of it. As customer cooperated with 20 different companies, which used this one system, amount of created Tickets per day reached 4000 and number of incoming emails – noncountable;). Of course every email, Ticket and RFC was important, so no data was deleted for last 10 years, which resulted in 800 GB of data in database. Most of it, were emails kept as plain text as well as HTML with images or attachments.
The idea was to upgrade this system not only to higher supported version, but to modern OS. And now the tricky part started…
Yes, the new server had 4 CPU cores and 64 GB of RAM and lots of free disk space, but it turned out that network connection between current prod server and this miracle of technology is so poor that copying database would take 5 full days using provided by Omninet solution.
First idea was to change DBMS from Oracle to MSSQL server – but as you can imagine using software as:
- Talend https://www.talend.com/
- SSMA for Oracle: https://docs.microsoft.com/en-us/sql/ssma/oracle/sql-server-migration-assistant-for-oracle-oracletosql?view=sql-server-ver15
resulted in corrupted database. It turned out that Omninet’s CopyDB tool converts some of datatypes not only to different type, but also changes the values in some of the columns…
Idea was abandoned, but we were still looking for ways of hastening the process. Finally we used following approach:
- divide this big database in two: first half – data from last 3 years as current database, the rest was designed as archive database
Both of the databases were copied using Oracle pump import to Oracle 19c: https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm
Creation of archive database was also done via deletion of „newest data” from Omnitracker using external scripts, which took almost 1 month. Then database was converted to Omnitracker ver 11.7. Total size of this DB was 400GB and time of conversion: 2h.
Creation of „new production” database was different . In order to hasten the process, all tables except UserFields580 were exported/imported to new OracleDB. Data from UserFields580 was divided horizontally – last month (all columns) and the rest of data was divided vertically into 2 parts: 1 part consisted of all columns but , fullfiling BLOB column, which normally consisted of HTML part of email with NULLs, and the second part consisted only with primary keys and BLOB column. So now we had database, which was much smaller – only emails from last month and this NULL BLOBS. This database was converted to Omnitracker ver 11.7. Subsequently table UserFields580 was filled with missing BLOB values using stored procedure, which filled data row after row. Size of this database was (without BLOBS): 150GB with conversion time 1h. Filling UserFields580 table took next 5h and then size of DB was 400GB. Total time of Omnitracker upgrade – 7,5h [including time needed for import/export of OracleDB].
Copying of Attachments, recreation of OT index and configuration of scheduled tasks, including autoarchivization task for crucial folder took 1 day – but it was done in the background and users were able to work.
The final configuration is:
- Linux cluster with Oracle DB 19c
- Windows server 2019 – CPU with 4 core and 64GB RAM
- Omnitracker 11.7