Oracle database timed synchronization to SQL Server method

1, two different servers;

2, from oracle10g timing synchronization (2:00 every day) to sqlserver2005;

3, in security considerations, customer service does not provide sqlserver2005 account password, only we provide data sets;

4, both the program language is Java;

Under these conditions I have proposed several sets of proposals for reference only:

A: If the Oracle database tutorial can be accessed on a client server, Oracle will provide the interface to the client server, return to the client server to synchronize the data, and store the client server operational data in the sqlserver2005. Interface Code Description: Remotely Establish JDBC Connection Oracle (Temporary account), query Oracle-provided view, and access customer's data set, make jar package, call interface implementation method to obtain data from client side.

B: If the Oracle side cannot be accessed by the client server, the data will need to be synchronized into the XML and accessed by the Customer service server with its XML (HTTP or socket), and the Oracle side provides the resolution mechanism to return the dataset.

C: Different from open source database synchronization software (Google results: Symmetricds, Opendbdiff (for SQL Server))

The previous three scenarios can be synchronized from Oracle to sqlserver2005, but if there is a large amount of data that may result in data loss in midstream or slower synchronization, the following scenarios are established:

Set up a synchronization table and place the fields in the data table required by the client into the synchronization table, such as:

User table: Wid,userid,password

User Sync table: wid,userid,password,no,operationtype,operationdate

You can see three more fields in the Sync table: no,operationtype,operationdate

No is serial number, generated by Oracle sequence, Operationtype for operation type, 0 for new, 1 for modification, 2 for deletion, operationdate for operation date, take current full time (month and day)

Create a user table trigger, and if the user table has an action, place the client's required user tables fields in a synchronized table, if the new value inserts the Operationtype into 0, and so on

On the customer service server side, Oracle provides the interface query synchronization table to get the result set and discuss with customer service how to operate the data

This allows you to implement what data has been modified to synchronize which data, reduce the pressure on both sides of the server