SQL query based data and structure uniformity maintenance in heterogeneous database environment

Abstract

Database synchronization is a complex process in heterogeneous database environment. Maintaining the uniformity of data and its structure is a fundamental problem of data synchronization. ODBC compliant database supports generic SQL query. Most of the ODBC compliant databases support SQL query capturing. As a result, we can capture the SQL query from source database and pass this SQL query to target database. We have used Oracle database as source database and any ODBC compliant database as target database. There are many ways to capture SQL query from Oracle database. We have used Oracle audit to capture all SQL query. Oracle audit knows what is going on in Oracle environment. By enabling Oracle audit, we can capture all DDL and DML SQL query which are changing the database. A process runs continuously in the back end to read SQL query from dba_audit_trail object and passes SQL query to target system through HTTP. Another process processes the SQL query in the target system. Some errors may happen in target database for data type mismatch or SQL query function mismatch. To avoid these errors, we have used two lookup tables and adapt the SQL query so that it matches the data types and query functions of the target database. Then we have executed changed query in the target database. Hence, the method works to keep data and database structure uniform among heterogeneous databases.