Liferay: Migrating from Sybase to Oracle Using Data Migration
I have been working with Liferay EE 6.2 clustered deployment which uses Sybase 15.5 database. Recently my team was directed to move from Sybase to Oracle 12C. Interestingly we migrated from Oracle 11 to Sybase 4 years ago. During that migration we used the Data Migration page built into Liferay. We had great success using the built in functionality. This page can be found: Control Panel -> Configuration -> Server Administration - > Data Migration.
With that experience we decided to use the built in functionality once again. You will need to know your JDBC driver class, URL, username and password. This connection information is for your target database. Screenshot below.
Our Liferay deployment connects to multiple databases. In the previous migration this caused the data migration page to fail. The first step was to run a migration against our development database. If your organization doesn’t regularly backup your databases, now would be a good time. After running the migration a several times we identified the following issues:
- Oracle only allows table names with lengths up to 30 characters. A few of our custom tables and the Notifications_UserNotificationEvent table exceeded the limit.
- Data from Sybase Text columns were being truncated when copied to Oracle CLOB columns.
- Data Migration page expected tables from a second database to be in the Liferay primary database. Seeing the tables were not in the primary database the migration would stop.
The first issue was solved by upgrading to the latest notifications portlet and by shortening the namespace in our service.xml files. The third issue we solved with a hack. We only used 20 tables in our external databases. We created a SQL script to create 20 tables without columns that matched the tables in the external databases. The Data Migration page would see the empty tables and copy them to the target database. They can later be dropped. The second issue was more difficult to overcome.
The Oracle CLOB columns defaulted to 4k. We had tables which contained more than 4k worth of data. During this process I had been consulting with our DBA. Using the Liferay Oracle Database Support Policy as a guide we discussed the CLOB issue and decided our best course of action would be to create the tables in the new database with a SQL script. I downloaded the source code for Liferay and went through the source code for our custom portlets. With the assistance of our DBA I was able to consolidate the SQL files and modify them to create CLOB columns that met our needs, mostly.
We still didn’t know what data sizes were in our database. To finish the SQL scripts we needed some metrics to determine what size to make the CLOB columns. I hacked together a small JAVA program to scan the database and report on all the Sybase Text columns. Below I provide the SQL I used in this program.
The first is SQL to pull table and column names for every table that had a Text column.
Find text columns
Then using the table and column name run the following queries to find the row count along with the max, min and average sizes of the text columns.
max data size
min data size
avg data size
I then consolidated this data into a text report similar to the following:
With this information I was able to modify the table creation SQL scripts to use CLOB sizes that should meet our needs. As an example I have shown the DDL that I used for the BackgroundTask table.
create table with CLOB size specified
Now that I had tables capable of holding larger data sizes it was time to test the Data Migration again. Right away we run into a problem. The Data Migration doesn’t copy data if the table already exists in the target database. The root of the problem lies in the com.liferay.portal.convert.ConvertDatabase classs’ migrateTable method (shown below).
Original migrateTable method
As can be seen if the SQL to create a table throws an exception then the table will not be populated. My solution was to copy and modify the ConvertDatabase class. My new class, call ModifiedConvertDatabase, was modified to the following.
modified migrate table method
In this modified code the table creation SQL is only run if the table doesn’t already exist. This way if the table doesn’t exist it is created and populated. If it does exist it is only populated. Now we need to configure Liferay to use our new class. Add or update the convert.processes to the following in portal-ext.properties.
With these modifications in place I had my first successful Data migration. How would I know if all the data transferred correctly? My first step was to create a SQL script that counted tables rows for all the tables. I could run this against both Oracle and Sybase. An excerpt is below. row count union
Using a diff tool I compared the results and found that they matched. Whew! Looking pretty good so far. I know my row counts are good but what about my data? Did those large clob columns transfer correctly? I ran SQL for max, min, avg against the Oracle tables and compared it to the results from the Sybase Tables. Fortunately those numbers also matched up! Finally we are done. <!–
- Oracle table definitions
- Failed to copy data if table exists in target database.
- Modified ConvertDatabase class to copy data if the class exists.
- If you are accessing an external database using the liferay service layer.
- Created SQL Script to create empty tables for tables used in external database.
- Upgraded the Notifications plugin to shorten table name for Oracle.
- Custom portlets have table names too long.
- Oracle CLOB columns defaulted to 4k.
- Wrote Java program to find sizes of text columns in sybase.