I'd given some thought to this a while ago. The only way you are going
to be able to tell if a row changes is to have a date column on every
oracle table that indicates the last time the data changed.
You'll need some program to start up that knows the last time it ran,
and the current date, and look for any changed rows between those two
dates/times. All dates/times need to come from the database (not the OS).
After it finishes, the current date that the code generated needs to be
saved somewhere for the next iteration.
This won't work when rows are deleted from the database. If you are
lucky, and this never happens, it's not an issue. If it only happens on
a few tables, you can put triggers on that table (ON DELETE) to generate
delete-statements to store in some log-type-table that your program can
then execute on the mysql database (and then remove the rows from the
log-type-table). If you have 500 tables that can have rows deleted, then
you have a bit of work on your hands.
This isn't an elegant or simple solution, but I don't know of any
application or tool that can be used to watch Oracle tables and apply
the changes to a MySQL table in real-time or near real time.
On the flip side, because you will be batching updates/inserts/deletes
that happen over a period of time (even if it's just 10-15 minutes) and
apply them to MySQL all at once, you will be putting 10-15 minutes worth
of Oracle processing onto MySQL all at once. Unfort, this isn't an
apples-to-apples test. The Oracle statement might be, "update some_table
set some_column = 'xyz' where some_other_column='abc' and
some_third_column_id in (select some_value from some_other_table where
some_column = 12) and the mysql statements would just be a bunch of
updates keyed off the primary key. Oracle is doing way more work.
If you need to do an oranges-to-oranges comparison, then unfortunately
the only thing I can think of is to log statements in a centralized
location (the Oracle database?) and then replay them in order on the
MySQL database. Again, this could be quite a bit of work.
The third option is to take the archived redo logs and extract the SQL
from them, and replay that SQL (assuming your Oracle-SQL is
ANSI-compliant and doesn't use (+) etc for outer joins, and isn't full
of Oracle-specific functions, etc). This will only capture updates,
inserts, deletes, however; any load on your database due to
selects-statements won't be replayed on the MySQL cluster.
Check out,
http://download-east.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/logminer.htm
Note that you might have a tough time running this every 15 minutes.
All that said, MySQL Cluster is definitely a different beast. No foreign
keys, and potentially slow selects if the query needs to hit multiple
clusters to retrieve data. You also need a lot of RAM (2.1 times your
dataset, if I remember correctly), etc, etc. Some of this goes away in
MySQL 5.1.
There is a book coming out in a few weeks on the MySQL Cluster, and the
MySQL Conference in Santa Clara has a set of talks devoted to MySQL Cluster.
David
Shain Miley wrote:
Hello everyone,
I had a quick question...I am looking to move away from our dependence
on Oracle over to using a Mysql Cluster. Due to the complexity of the
move it will have to happen over a period of time, what I would like to
do is keep our mysql database in sync with our Oracle DB....this would
allow us to prove that the MySQL db can deal with the load, provide the
needed uptime,etc.
Does anyone have any thoughts on this..I don't mean I need this done
once..best case is every time something changes in an oracle table, the
change will get send to Mysql...worst case...it needs to be out of sync
by no more the 15 minutes...what do you think....is it possible?
Thanks in advance,
SKM
---------------------------------
Expecting? Get great news right away with email Auto-Check.
Try the Yahoo! Mail Beta.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
