Many database vendors and freeware projects support some sort of replication scheme.
Usually this is Master-Slave replication where both master and slave are the same type.
Another scheme is Multi-Master replication when updates at any master get transported to
the other master databases. So far Oracle does not support any replication schemes with
freeware DBMS, that is why there are many ways to accomplish this tasks.
These notes will discuss master-slave replication between oracle and freeware databases
as Mysql and PostgreSql using java API dbCreateTables.java and dbUpdateTable.java
initially written by R.Huges and D.Waters and modified by S.Lebedeva.
Why freeware DBMS:
Cost - Freeware databases are attractive for having subset of
data that can be easily accessed.
Network latency - Its a lot faster to query a local database
as opposed to one on another continent.
Problems:
Vendor differences
SQL is only partially standardized
Data types are not standardized either
Different reserved words
Databases change over time: changes in schema means that the replica's schema must change too
Databases can get really big, wich means extra resources for monitoring and backups
A java program, dbCreateTables.java queries the oracle schema and duplicates it on Mysql.
To create modified schema on freeware database, name of excluded columns can be specified in a property file
dbCreateTables.java will populate data in the created tables unless "schema" only will be specified in a property file.
dbCreateTables writes metadata into a table called myTables and number of records and date in myUpdates each time
it creates a new table.
Same Oracle table from different oracle schemes can be merged in one freeware table when key-word "merge" is in a property file
Schema population
Populating data in freeware database is represented in the form of sql insert statements.
In property file new parameter Filter is implemented in a form of where-clause to update freeware tables with subset of data
dbUpdateTables.java queries a freeware table called myUpdates to see when the last time a table
listed in myTables has been updated. If there is no entry in myTables, the whole table needs to be copied over.
dbUpdateTables.java reports its activity in myUpdates table, number of rows transported can be verified.
Periodic Updates
Not implemented yet.
Reliability
Schema creating and initial population works fairly easy
Check for dublicate records when primary or unique keys created in freeware table
Schema populating works as INSERTS only, DELETES and UPDATES needs further implementation
Error handlings are implemented to cover both Mysql and Postgres jdbc error messages
Verification/Monitoring of a production system will take dedicated manpower
Mail inquiries to: css-dsg Last
modified: June,18 2003