How to regularly synchronize Oracle databases to SqlServer

1. Two different servers;

2. synchronize data from oracle10g at regular intervals (every day) to sqlserver2005;

3. For security considerations, the customer service does not provide the sqlserver2005 account password. We only need to provide the dataset;

4. Both programming languages are java;

Under these conditions, I have proposed several solutions for your reference only:

A: If the oracle database tutorial can be accessed on the client server, the oracle side provides an interface to the client server for implementation and returns the synchronized data to the client server, the operation data on the client server is stored in sqlserver2005. interface code Description: remotely establishes a jdbc connection to oracle (temporary account), queries the view provided by the oracle party, and accesses the dataset required by the customer, compress the jar package and obtain the data by calling the interface implementation method.

B: If the oracle side cannot be accessed by the client server, the data to be synchronized must be placed in xml and accessed by the customer service server (http or socket ), oracle provides a resolution mechanism to return data sets.

C: Different implementations are achieved through open-source database synchronization software (google results: symmetricds, opendbdiff (for sqlserver ))

The preceding three solutions can be used to synchronize data from oracle to sqlserver2005. However, if the data volume is large, the data may be lost or the synchronization speed may be slow. Therefore, the following solutions are developed:

Create a synchronization table and place the fields in the data table required by the customer into the synchronization table, for example:

User table: wid, userid, password

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

The synchronization table has three more fields: no, operationtype, and operationdate.

No is the sequence number generated by the oracle sequence, operationtype is the operation type, 0 is the addition, 1 is the modification, 2 is the deletion, operationdate is the operation date, take the current complete time (year, month, day, minute, second)

Create a user table trigger. If the user table has an operation, place the user table fields required by the customer into the synchronization table. If the user table is added, insert the operationtype value to 0, and so on.

On the Customer Service server side, oracle provides interfaces to query the synchronization table and obtain the result set. It also negotiates with the customer service about how to operate the data.

In this way, you can synchronize the modified data, reducing the pressure on both servers.