So happy. Finally, the synchronization problem between two Oracle databases is solved, especially the synchronization of the ArcSde metabase.

In a project, system stability and high availability must be achieved according to user requirements. In the original solution, dual-machine hot backup is planned.

At the beginning of the solution, it would be so troublesome and too optimistic that ArcSde + Oracle dual-machine hot backup was not implemented. I felt that it could be completed through the information.

Because dual-nic and heartbeat settings are required, normal environments cannot be tested. I want to experiment in the user's environment... But at the same time, I ignored a point. When the user made the hardware solution, the network was already redundant, and the server's Nic was used up... What should I do... On the last day before the National Day, almost no insomnia. Start searching for information on the Internet and find an alternative solution.

The synchronous replication between databases is considered first. Thank you very much.

 

Name: Zhang Yubin occupation: programmer

Address: Beijing net name: jillzhang

Expertise: Distributed Application Development

I published an article OraSyner, a component used for data replication between Oracle databases. It gave me some basic implementation ideas and reduced the time required to prepare the code architecture.

After reading this article, as the landlord said, tens of thousands of pieces of data, the performance is still good, the data volume is large, and it is certainly too much to eat, what should I do?

I. What is my goal first?

1. If the master server fails, the slave server can be quickly connected in a short time;

Problem:

• Short time? What is the data synchronization latency that the user can accept, 3 seconds?
• What is the degree of data difference? Data loss caused by server switching that users can withstand. 1 ~ Five. Is this hard to test?
• Do the two databases follow the same atomic clock? If they are inconsistent, the synchronization direction may be determined incorrectly.

2. At each synchronization, the minimum data transmission volume is generated;

Problem:

• How do I control the amount of data transmitted between databases?
• How can I obtain the changes, additions, deletions, and modifications of tables and even rows on the master server?

3. The last modification time between databases must also be compared, that is, the direction in which synchronization needs to be calculated;

Problem:

• How can I get the last modification time of a record in a table?
• How can I display the time field of Oracle to a long date, including hour: minute: Second?

4. At the same time, a master server and multiple slave servers need to be considered for synchronization;

Problem:

• If multiple databases need to be synchronized?
• If multiple databases need to be partially synchronized?

Ii. Analyze the components and test the components based on the project requirements.

Ah, for this set of things, the whole National Day has been devoted. Several times until midnight.

Fortunately, there is an environment, two servers, and one synchronization test machine. After multiple import and export operations on the database and backup operations, the ArcSde server is directly transferred to the synchronization server... During the continuous synchronization test, I found that I needed

1. Support synchronization of large object types -------------- this is okay. Quick Solution

2. Support for Oracle custom type synchronization ------------- no way has been found so far. Please give me some advice...

3. There are a lot of tables in the database, and writing synchronization files is also a very troublesome thing ------------ fortunately there was a database structure browsing program before, so we should make the synchronization file generator.

4. When the data volume is large, the performance is really awkward. ----------- I started to look for how to monitor the changes of each record. Fortunately, I have been exposed to materialized views, the materialized view log table is used to monitor changes;

5. How to obtain the exact date of each record change ----------- in the materialized view log table, the clock has a TimeSpan $ field, which records the change time, but I cannot display it with a long date, do you have any good solutions?

6. Many tables do not have a primary key and cannot create materialized view logs -------- the synchronization structure is initialized in the synchronization component, which is equivalent to embedding many parasites on another object... Bt.

7. Many tables do not have a primary key, but have a unique index or constraint, and there are multiple indexes-which index is used as the primary key field When configuring the synchronization file? Analyze the creation of Materialized view log tables

The principle of using the primary key: the number of fields is small, and the integer is preferred.

8. How to obtain new records, modify records, and delete records in materialized view logs ------ the table contains fields DMLTYPE $, OLD_NEW $, where DMLTYPE: U = Update, D = Delete, I = Insert

OLD_NEW $: N = New; O = Old; U = Update

9. the ArcSde metabase has failed many times, so that clients such as ArcCatalog cannot connect, unmount, and reinstall ArcSde. ------ Schema synchronization is still very difficult, the problem lies in the issue. There are many indexes and all index fields are treated as primary keys.

By the afternoon, we finally got it done.

Iii. System Performance

1. About 1200 more tables to be synchronized;

2. When the system is started, perform the first synchronization. Scan and initialize all registered tables first. Creating a primary key without a primary key does not create a materialized view log table. It takes about 3 minutes for the first synchronization;

3. During subsequent synchronization, only the changed tables and change records are synchronized, basically in the range of 3 to 4 seconds. I used to use multiple threads for synchronization and use the thread pool, however, when an error is reported, it is estimated that the value of the public variable has changed. I think the latency in 3, 4 seconds is acceptable for the time being. First, we should handle the problem of multi-thread synchronization after the components are relatively stable and tested for a period of time.

Iv. Summary of system issues

1. multiple servers use the local time instead of the same atomic time;

2. the row record change control is only accurate to one day, which is not safe, at least to seconds;

3. If multithreading is not used, the system performance is not efficient enough;

4. Currently, only Oracle synchronization is supported. Synchronization of the same version of ArcSde is supported;

5. Currently, synchronization of database architecture changes is not supported

6. Oracle custom type fields cannot be synchronized

5. Expansion

1. synchronize data between SQL servers with time;

2. Study synchronization between different databases

 

Download the synchronization Tool