I did some research on the materialized view and materialized view log, by trace the refresh procedure of Oracle, I found out how materialized view works. Then I started to write a Perl script to replicate rows from Oracle to MySQL based on materialized view log which help me to capture the changed rows, it took me half of day to get the first version done. The interesting thing is that it worked fine when I were testing it. In this release, the table name can be different between source and target database, but the column name must be same (column order is not important), and table must have a primary key, and must create a materialized view log with "with primary key, sequence" option on the table.
Then we need to write a configuration file to maintain the tables which need to be replicated, it contains four columns (split by pouch "#") :
1, source table name in Oracle.
2, primary key columns, split by ",".
3, the materialized view log table name, usually "MLOG$_tablename".
4, the target table name in MySQL.
Now start testing by running the script refresh_mysql.pl :
C:\AnySQL>perl refresh_mysql.pl -s anysql#anysql#Oracle:test -t root#mysql#mysql:test -c myrefresh.conf
02/13 12:52:25 - 1 tables will be processed.
02/13 12:53:06 - Start replication from T_MVLOG to T_MVLOG with sequence 10196 to 10199 ...
02/13 12:53:07 - End replication from T_MVLOG to T_MVLOG with sequence 10196 to 10199.
02/13 12:53:26 - Start replication from T_MVLOG to T_MVLOG with sequence 10200 to 10203 ...
02/13 12:53:27 - End replication from T_MVLOG to T_MVLOG with sequence 10200 to 10203.
02/13 12:54:11 - Start replication from T_MVLOG to T_MVLOG with sequence 10204 to 10204 ...
02/13 12:54:12 - End replication from T_MVLOG to T_MVLOG with sequence 10204 to 10204.
02/13 12:56:13 - Start replication from T_MVLOG to T_MVLOG with sequence 10205 to 10207 ...
02/13 12:56:14 - End replication from T_MVLOG to T_MVLOG with sequence 10205 to 10207.
02/13 13:11:41 - Start replication from T_MVLOG to T_MVLOG with sequence 10208 to 10211 ...
02/13 13:11:42 - End replication from T_MVLOG to T_MVLOG with sequence 10208 to 10211.
I inserted some rows to the demo table in Oracle, and perform a query to verify :
SQL> SELECT * FROM T_MVLOG;
COL1 COL2
-------------------- ------------------------------
MLOG$_T_MVLOG TABLE
MV_T_MVLOG2 TABLE
RUPD$_T_MVLOG TABLE
T_MVLOG TABLE
After waiting 3 to 5 seconds, I query the demo table in MySQL, and get the expected result, it's really working .
mysql> select * from t_mvlog;
+---------------+-------+
| COL1 | COL2 |
+---------------+-------+
| MLOG$_T_MVLOG | TABLE |
| MV_T_MVLOG2 | TABLE |
| RUPD$_T_MVLOG | TABLE |
| T_MVLOG | TABLE |
+---------------+-------+
4 rows in set (0.00 sec)
The next to do is to perform more test, and test under higher load pressure. And there are lots to be improved. I don't tell you how to install the Perl and DBD-Oracle DBD-MySQL here, that's not my job.