KR101825910B1 - Heterogeneous database synchronization system and method using neutral data based on redo log analysis - Google Patents

Abstract

The present invention relates to a system and a method for heterogeneous database synchronization using neutral data based on redo log analysis. The system for heterogeneous database synchronization using neutral data based on redo log analysis comprises: a first database management system (DBMS) having a database having a subscriber role, and storing a redo log of a first structure; at least one second database management system (DBMS) having a database having a provider role, and storing a redo log of a second structure incompatible with the first structure; and a data repeater to relay data transceived for synchronization between the first and the second DBMS. The first DBMS restores a redo log generated when data are updated in a database to original update data to store information of the restored update data in neutral data having an identical structure regardless of a type of a DBMS to transmit the information to a DBMS of a subscriber role.

Description

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a data synchronization system and a method for synchronizing data in a heterogeneous database using neutral data based on redo log analysis,

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to data synchronization between heterogeneous databases, and more particularly, to a system and method for synchronizing data in a heterogeneous database using neutral data based on redo log analysis.

Database synchronization is one of the techniques used for sharing and backing up data between two or more physically separate databases. This synchronization technique can propagate the corresponding data changes to all databases so that all the databases have the same data when data is inserted, updated and deleted in one of the databases. Therefore, it is a suitable technology for sharing one data in several databases.

If one of the databases in the database synchronization structure loses all of its own data due to a natural disaster, an external intrusion, or a physical failure, human and time resources are required to recover the lost data, which causes a great loss.

In the asynchronous case, the database backs up the data at regular intervals. However, there is a problem in that data updating between the time of backing up and the time of data loss can not be reflected. Synchronization propagates data to multiple databases whenever data is updated, so data loss can be prevented and damage minimized when any of the above occurs in any one database.

Existing database synchronization technologies perform synchronization between databases in one direction, propagating data changes from one database to another. Accordingly, there is a problem in that when data is changed in the database receiving the data change, that is, the destination database, the change can not be propagated to another database.

And, existing database synchronization technologies perform synchronization between the same database management systems (DBMS). If the types of DBMSs are different, the types of queries and data types used in each DBMS are different, so synchronization between databases using different DBMSs is partially impossible.

Currently, there are dozens of DBMSs used in various applications such as industry, industry, and agriculture. DBMSs in these applications can not always be the same model, but synchronization between databases is essential in the same situation as the merger of two companies using different DBMSs.

Existing database synchronization technologies recognize updated data in the database based on Change Data Capture (CDC) method. The CDC method is classified into redo log method and trigger method.

The redo log method is a method of analyzing redo logs recorded in a separate space after transactions are processed in the database, restoring them to original data, and propagating them to other databases. Alternatively, the trigger method is a method of setting a trigger on a target database and a table of a DBMS to detect an update of data in a target database and a table, and to propagate updated data to other databases.

However, the database synchronization techniques based on the trigger method have a drawback that the load of the DBMS that sets the triggers increases as the number of the databases and the tables in which the triggers are set increases. Accordingly, in a system for synchronizing a plurality of databases, the redo log method is more suitable than the trigger method.

The types (types) of redo logs accumulated in a separate storage space by the DBMS are classified into a user redo log according to a user query and a system redo log according to a DBMS system configuration change. In the present invention, only a user redo log is extracted and analyzed do.

The structure of the redo log includes a redo log header and updated data. The redo log header includes a system change number (SCN), a log length, a type of log, and a transaction ID , And it is possible to distinguish insertion, update and deletion of data depending on the type of redo log. At this time, the extraction of the redo log is sequentially performed in the system change number order.

Therefore, heterogeneous DBMS maintains redo log of different structure which are incompatible with each other. Therefore, duplication or duplication of existing redo log and propagation to other DBMS is used only for data synchronization between DBMSs of the same type. In particular, since DBMSs only process queries written according to the syntax of standard SQL, they can be executed regardless of the type of DBMS, but in the case of data types that are unique to each DBMS, such as user-defined types, It has different structure of SQL syntax, and this SQL syntax can not be processed by other types of DBMS.

It is an object of the present invention to provide a data synchronization system and method of a heterogeneous database which supports synchronization between heterogeneous databases and can always have the same data in various databases at the time of updating data.

It is another object of the present invention to provide a data synchronization system and method of a heterogeneous database which can recover data using a synchronizing database without loss of data when one database stops operating due to external factors.

According to an aspect of the present invention, there is provided a data synchronization system for a heterogeneous database, the system including a database having a role of a subscriber, the database synchronization system comprising: a first database management unit storing a redo log of a first structure; System (DBMS); At least one second database management system (DBMS) having a database having a provider role and storing a redo log of a second structure incompatible with the first structure, The first DBMS restores the redo log, which is generated when data is updated in the database, to the original update data, and stores the restored update data in the DBMS model And stores the data in the neutral data having the same structure, and transmits the data to the DBMS serving as the subscriber.

According to another aspect of the present invention, there is provided a data synchronization system for a heterogeneous database including a database having a role as a subscriber or a provider and storing a redo log of a different structure incompatible with each other In a plurality of database management systems (DBMSs), each database management system (DBMS) restores the redo log generated at the time of data update in the database to the original update data and stores the information of the restore update data in the DBMS And a synchronizer for storing the data in the neutral data having the same structure and transmitting the data to another database management system (DBMS).

According to another aspect of the present invention, there is provided a method for synchronizing data in a heterogeneous database, the method comprising: receiving data between a plurality of database management systems (DBMSs) storing different redo logs of incompatible structures; In the synchronization method, when the database data is updated in one DBMS, restoring the redo log generated due to the data update to original update data; Storing the restored update data in the neutral data having the same structure regardless of the model of the DBMS to generate neutral data; And transmitting the generated neutral data to a DBMS of another model to perform data synchronization.

The present invention supports the synchronization between different products, that is, a heterogeneous database, so that when data update occurs, it is possible to always have the same data in various databases, and when one database is stopped due to external factors In this case, there is an advantage that the data can be recovered by using the synchronizing database without data loss.

In addition, according to the present invention, the information of the synchronization period update data is stored in separate neutral data and transmitted / received, thereby resolving the structural difference between the query type and the data type among the heterogeneous DBMSs and effecting synchronization among various DBMSs.

1 is a configuration diagram of a data synchronization system (DBMS) of a heterogeneous database according to an embodiment of the present invention;
Figure 2 shows a structure of neutral data.
3 is a flowchart illustrating a redo log restoration operation of a synchronizer according to an embodiment of the present invention;
FIG. 4 is a flowchart showing a countermeasure according to a failure of a synchronizer when a role of a database is a provider in the present invention. FIG.
5 is a flowchart showing a synchronization operation (S240) for missing data in FIG.
6 is a flowchart showing a countermeasure according to a failure of a synchronizer when a role of a database is a subscriber in the present invention.
7 is a flowchart showing a countermeasure according to a failure of a data repeater during data synchronization in the present invention.

In general, a heterogeneous database management system (DBMS) maintains redo logs of different structures that are incompatible with each other. When different DBMSs are used, the type of query used by each DBMS and the data type It is not easy to synchronize databases using different DBMSs.

Therefore, the present invention supports synchronization between heterogeneous databases, so that the same data can always be retained in a plurality of databases at the time of updating the data. The redo log recorded in each DBMS is analyzed to identify updated data and propagate it to other DBMSs We propose an integrated synchronization method that updates the same data in different databases.

According to one embodiment of the present invention, the present invention restores redo logs generated when data updates occur in databases managed by a DBMS to original update data, and stores information of the restored update data in the same , And then transmits the neutral data to DBMSs of different models to perform data synchronization.

To this end, in the present invention, each DBMS is composed of one or more databases having a role as a subscriber or a provider. When a transaction is generated in the database and a redo log according to the transaction is generated, the generated redo log is analyzed and restored And transmits the neutral data transmitted from the synchronizer of one DBMS to the other synchronizers when there is a plurality of DBMSs, . ≪ / RTI >

1 is a configuration diagram of a data synchronization system (DBMS) of a heterogeneous database according to an embodiment of the present invention.

As shown in FIG. 1, a heterogeneous database synchronization system according to an embodiment of the present invention includes a DBMS 100 and a synchronizer 200.

The DBMS 100 includes a database 10 in which a redo log file is recorded. The type of the database 10 may be different for each DBMS.

The synchronizer 200 includes a redo log finder 20, a redo log restorer 21, a data transmitter-receiver 22, and a data translator. (23).

The redo log searcher 20 extracts a redo log from the redo log file 11 recorded in the database 10 of the DBMS 200, and copes with and stores the redo log.

The redo log restorer 21 extracts and analyzes the redo log from the redo log file 21 recorded in the database 10 of the DBMS 100 and restores the redo log as updated data. To this end, the redo log restorer 21 maintains information on the redo log structure of each DBMS, reads the redo logs from the redo log file 11 recorded in each DBMS using the information, And stores information about the included update data in neutral data.

The data transceiver 22 transmits update data to another DBMS through the data relay 300 or receives data updated by another DBMS from the data relay 300. [

The data converter 23 converts the received neutral data into a query of the DBMS 100 and records the converted data in the database 10.

The data repeater 300 collects data transmitted from the synchronizers 200 of the DBMS 100 and propagates the data to the other synchronizers. The data repeater 300 receives the data updated from the neutral data received from the synchronizers 200 The role of the database where the data is stored (provider or subscriber) can be identified.

In the present invention, one of a publisher and a subscriber is set for databases managed by a DBMS installed with a synchronizer, and only the synchronizer installed in the DBMS of the corresponding database is updated as a data repeater So that only the synchronizer installed in the DBMS of the database set as the subscriber receives the data update.

In the present invention, the redo logs are expressed in binary form and include updates of data stored in the database 10. The update of the data includes the updated data, the type of the updated data, the table name including the updated data, the database name including the updated data, the DML type, the transaction identifier, and the transaction completion time. It is called neutral data.

On the other hand, DBMSs of different models in terms of SQL syntax use different structures of SQL syntax as they use different data types. Therefore, there is a problem in that data synchronization between existing DBMSs through the SQL propagation can not be performed between the different DBMSs. In order to solve this problem, in the present invention, information of the synchronization period update data is stored in the neutral data having the same structure regardless of the model of the DBMS to synchronize the data.

In addition, since the structure of the redo log that records the contents of the data updates differs from each other in terms of the redo log structure, the data synchronization between the existing DBMSs through redo log duplication or duplication Data synchronization between heterogeneous DBMSs can not be performed. In order to solve this problem, in the present invention, information of the synchronization period update data is stored in the neutral data having the same structure regardless of the model of the DBMS to synchronize the data.

In an embodiment of the present invention, the synchronizer 200 installed in DBMSs of different models has an IP address that can be distinguished from each other. Accordingly, the data transmitted between the synchronizer 200 and the data repeater 300 includes the neutral data including the update of the data and the IP address of each synchronizer.

In one embodiment of the present invention, when synchronizing a plurality of DBMSs, a redo log of a database set as a provider is generated as neutral data in the synchronizer 200 installed in the corresponding DBMS, The neutral data is propagated only to the synchronizers installed in the DBMS managing the database set as the subscriber via the data relay 300. [ In order to transmit the neutral data, the data relay 300 stores the IP addresses of the synchronizers 200, and stores the names of the databases and functions set as a provider or a subscriber.

Various embodiments according to the present invention will be described with reference to the accompanying drawings.

1, when a database 10 managed by the DBMS 100 is provided, when a query of a client is performed in the database 10, the DBMS 100 performs a transaction including the query in the database 10 And accumulates a redo log indicating update of data in the redo log file 11.

The redo log restorer 21 analyzes the redo log based on the system change number within a time range allowed by the DBMS 100, obtains the updated data, and transmits the generated neutral data to the data transceiver 22.

The data transceiver 22 transmits the transmitted neutral data and the identifier (e.g., IP address) of the synchronizer 200 to the data relay 300. If a transmission stop signal is received from the data repeater 300, the data transceiver 22 stops transmission until it receives a resume signal from the data repeater 308. [ Alternatively, if the system change number (SCN) of the transmitted neutral data is received from the data repeater 300, it is determined that the neutral data has propagated normally. At this time, the transmission of the neutral data is carried out separately from the reception of the SCN.

Each time the data repeater 300 receives the neutral data from the data transceiver 22, the data repeater 300 propagates the SCN of the neutral data to the corresponding data transceiver 22. The data repeater 300 inquires the database name based on the identifier (e.g., IP address) included in the received neutral data, and transmits the neutral data to the synchronizers of all the DBMSs managing the database set as the subscriber. In other words, if the SCN of the neutral data is received from the synchronizer, the next neutral data is propagated. Otherwise, if the SCN is not received within a predetermined time, the synchronizer is determined to be out of order.

Accordingly, the synchronizers of the plurality of DBMSs set as the subscribers receive the neutral data transmitted by the data repeater 300. This will be described with reference to FIG.

Referring to FIG. 1, when the neutral data is received from the data repeater 300 through the Ethernet network, the data transceiver 22 included in the synchronizer 200 of a plurality of DBMSs set as a subscriber stores the received SCN of the neutral data as data And transfers the received neutral data to the data converter 23.

The data converter 23 converts the neutral data received from the data transceiver 22 into a query that can be performed in the DBMS 100 installed in the synchronizer 200 and then executes the query in the DBMS 100 Perform database synchronization.

2 is a diagram showing a structure of neutral data.

As shown in FIG. 2, the neutral data structure includes the updated data, the type of the updated data, the name of the table containing the updated data, the name of the database containing the updated data, the DML type, Time.

3 is a flowchart illustrating a redo log restoration operation of a synchronizer according to an embodiment of the present invention.

3, the redo log restorer 21 searches for the redo log file 11 stored at a different location in the database 10 for each DBMS 100 (S100), reads the read log file 11 Check if it has been modified. If the redo log file 11 is checked as a result of the check, the redo log recorded during a predetermined period (e.g., most recent) is extracted from the redo log file 11 (S110). At this time, the extraction of the redo log is sequentially performed in the system change number order.

Once the redo log is extracted, the redo log restorer 21 checks the type (type) of the extracted redo log (S120). If the extracted redo log is a redo logon according to the user query, After the information of the data is extracted, the role (setting) of the database 100 is confirmed (S130). The information of the updated data includes the updated data, the type of the updated data, the table name including the updated data, the database name including the updated data, the DML type, the transaction identifier, and the transaction completion time.

If the current database 100 is set to 'provider' (S140), the redo log restorer 21 stores the extracted information and configures the neutral data as shown in FIG. 2 (step S130) S150). At this time, the redo log restorer 21 can determine the information stored in the neutral data according to the DML type of the redo log. To this end, the redo log restorer 21 determines the DML type of the redo log (S160). If the DML type of the extracted redo log is an UPDATE statement, the redo log restorer 21 stores the pre-update data, the post-update data, and each data type in the neutral data (S170) Otherwise, only the types of the updated data and the updated data are stored in the neutral data (S180).

If all the information of the redo log is stored in the neutral data by the above process, the redo log restorer 21 transmits the neutral data to the data transceiver 22 (S190).

Therefore, according to the present invention, the redo log structure is different depending on the data type. Therefore, the redo log structure according to each data type is known, and data is restored according to each data type in the redo log extracted from the redo log file .

4 is a flowchart showing a countermeasure according to the failure of the synchronizer when the role of the database is the provider in the present invention.

As shown in FIG. 4, the synchronizer 200 can check its own state, that is, a normal state or a failure state where the database stops due to an external factor (S200). If the synchronizer 200 returns from the fault state to the normal state (S210), the synchronizer 200 transmits a recovery signal to the data repeater 300 (S220). The data repeater 300 transmits the SCN and the resume signal of the last synchronized neutral data among the neutral data transmitted by the synchronizer 200 to the synchronizer 200 (S230).

When the resume signal is received, the synchronizer 200 performs a synchronization procedure to be described later to synchronize the missing data (S240).

5 is a flowchart showing a synchronization operation (S240) for missing data.

Referring to FIG. 5, the synchronizer 200 stops the operation of the redo log restorer 21 and returns the SCN of the redo log that has been restored so far to perform synchronization with the missing data (S241). In step S242, the synchronizer 200 extracts the table identifier from the redo log corresponding to the SCN received together with the resume signal from the data repeater 300 using the redo log searcher 21, and adds the table identifier to the table list. Thereafter, a temporary SCN (tmpSCN) is generated and initialized to the SCN received from the data relay 300 (S243).

Then, the synchronizer 200 searches the redo log having the subsequent SCN value of the initialized tmpSCN to initialize it to the tmpSCN value (S244), and checks whether the tmpSCN value is greater than the recovered SCN (S245) If the value is greater than the recovered SCN, the redo log retriever 217 stops the redo log retriever S107 and resumes the operation of the redo log restorer 21 (S246). Otherwise, if the tmpSCN value is less than or equal to the restored SCN (S310), the synchronizer 200 restores the redo log having the tmpSCN value to generate neutral data (S247). At this time, the synchronizer 200 checks whether the table identifier of the generated neutral data exists in the table list, and transmits the neutral data to the data transceiver 22 only if it exists (S248). On the other hand, if it does not exist in the table list, it searches the redo log with the SCN value after tmpSCN.

The synchronization procedure (S240) of FIG. 5 may be performed in the same manner as the failure countermeasure of the synchronizer and the failure of the data repeater according to the case where the role of the database is the subscriber as described below.

6 is a flowchart illustrating a countermeasure according to the failure of the synchronizer when the role of the database is a subscriber in the present invention.

1 and 6, the data repeater 300 transmits the neutral data received from the synchronizer 200 of the provider DBMS 100 to a different subscriber DBMS 100, more specifically, through the Ethernet network And propagates to synchronizers of the DBMS 100 (S300). Thereafter, the data repeater 300 checks whether the SCN for the propagated neutral data is received from all synchronizers within a predetermined time determined by the manager (S310, S320).

If the SCN is not received from all the synchronizers within the predetermined period of time, the data repeater 300 determines that the synchronizer that has not transmitted the SCN is failed (S330), transmits a transmission stop signal to the synchronizer determined as the failure (S340), the state is continuously checked.

The state of the synchronizer determined as the failure can be determined by whether the synchronizer S100 connects to the data repeater 300 to determine whether or not the recovery is possible. If it is determined that the synchronizer has recovered as a failure (S350, S360), the data relay 300 transmits the SCN of the failed neutral data along with the resume signal to the synchronizer (S370). Then, the synchronizer recovered from the failure performs synchronization according to the synchronization procedure (S240) of FIG.

7 is a flowchart showing a countermeasure according to the failure of the data repeater in the present invention.

7, after the synchronizer 200 transmits the neutral data to the data repeater 300 (S400), the synchronizer 200 determines whether the SCN of the neutral data is received from the data repeater 300 within a predetermined time set by the administrator It is possible to determine whether the data repeater is faulty (S400 to S420). At this time, if the SCN of the neutral data is not received within a predetermined time, it is determined that the data repeater 300 has failed. If it is determined that the data repeater 300 has failed, all the synchronizers 200 stop their operation (S430).

After the data repeater S108 is recovered from the failure, the data repeater 300 transmits the SCN and the resume signal of the neutral data last transmitted (synchronized) by each synchronizer to each synchronizer S100, S100 resumes operation according to the received resume signal, and performs synchronization according to the synchronization procedure (S240) of FIG. 5 (S450).

As described above, the present invention supports synchronization between different products, i.e., heterogeneous databases, so that data can be always updated in a plurality of databases by reflecting the update of data, If the operation is stopped, there is an advantage that it can be recovered by using the database which is being synchronized without data loss.

In addition, the present invention can solve the structural difference between the query type and the data type between heterogeneous DBMSs by storing information of the synchronization period update data in separate neutral data and transmitting / receiving them, thereby achieving synchronization between various DBMSs.

The above-described data synchronization system and method of a heterogeneous database according to the present invention can be applied to the configurations and methods of the above-described embodiments in a limited manner, but the embodiments can be implemented without changing their technical ideas or essential features It will be understood that the invention may be embodied in other specific forms. Therefore, it should be understood that the above-described embodiments are to be considered in all respects as illustrative and not restrictive.

10: Database 11: Redo Log Files
20: redo log retriever 21: redo log restor
22: data transceiver 24: data converter
100: Database Management System (DBMS) 200: Synchronizer
300: Data Repeater

Claims (14)

A first database management system (DBMS) having a database having a subscriber role and storing a redo log of the first structure;
At least one second database management system (DBMS) having a database having a provider role and storing a redo log of a second structure incompatible with the first structure;
And a data relay for relaying data transmitted and received for synchronization between the first and second DBMSs,
The first DBMS restores the redo log generated when the data update occurs in the database to the original update data and stores the restored update data in the neutral data having the same structure regardless of the model of the DBMS, And transmitting the data to the DBMS. 2. The method of claim 1, wherein the first and second DBMSs
And a synchronizer for analyzing a redo log generated when a transaction occurs in the database to restore the update data and storing information of the updated update data in the neutral data and transmitting the data to the data relay. . 3. The apparatus of claim 2, wherein the data repeater
Recognizing the role of the database storing the updated data from the received neutral data, propagating the neutral data to the synchronizer of the DBMS having the database serving as the subscriber, and recording the identifier of the restored redo log in each database Wherein the data synchronization system comprises: 3. The method of claim 2, wherein the information of the restored updated data is
A database name including the updated data, a DML type, a transaction identifier, and a transaction completion time. The data synchronization system according to claim 1, . 3. The apparatus of claim 2, wherein the synchronizer
A redo log restorer for restoring the generated redo log to original update data and storing restored data in neutral data as a structure;
A data transceiver for transmitting the neutral data transferred from the redo log restorer to the data relay or receiving the neutral data from the data relay;
A data converter for converting the neutral data received by the data transceiver into a query usable in the corresponding DBMS; And
And a redo log searcher for searching and restoring only a specific redo log when the synchronizer fails. 6. The apparatus of claim 5, wherein the data transceiver
And transmits the identifier of the synchronizer together with the neutral data to the data relay, and receives the system change number (SCN) of the neutral data from the data relay. 1. A plurality of database management systems (DBMSs) for storing redo logs of different structures that are incompatible with each other, including a database having a subscriber or a provider role,
Each database management system (DBMS)
The redo log generated at the time of updating the data in the database is restored to the original update data and the information of the restoration update data is stored in the neutral data having the same structure irrespective of the model of the DBMS and transmitted to another DBMS And a synchronizer for synchronizing the synchronization of the data. The data relay system according to claim 7, further comprising a data repeater for determining the role of the database storing the updated data from the transmitted neutral data and relaying the neutral data to another database management system (DBMS) A heterogeneous database of data synchronization systems. 8. The method of claim 7, wherein the information of the restored updated data is
A database name including the updated data, a DML type, a transaction identifier, and a transaction completion time. The data synchronization system according to claim 1, . 8. The apparatus of claim 7, wherein the synchronizer
A redo log restorer for restoring the generated redo log to original update data and storing restored data in neutral data as a structure;
A data transceiver for transmitting the neutral data transferred from the redo log restorer to the data relay or receiving the neutral data from the data relay;
A data converter for converting the neutral data received by the data transceiver into a query usable in the corresponding DBMS; And
And a redo log searcher for searching and restoring only a specific redo log when the synchronizer fails. 11. The apparatus of claim 10, wherein the data transceiver
And transmits the identifier of the synchronizer together with the neutral data to the data relay, and receives the system change number (SCN) of the neutral data from the data relay. 1. A method for synchronizing data between a plurality of database management systems (DBMSs) storing redo logs of different structures incompatible with each other,
Restoring a redo log generated due to data update into original update data when the database data is updated in one DBMS;
Storing the restored update data in the neutral data having the same structure regardless of the model of the DBMS to generate neutral data; And
And transmitting the generated neutral data to a DBMS of a different type to perform data synchronization. 13. The method of claim 12, wherein the information of the restored updated data is
A database name including the updated data, a DML type, a transaction identifier, and a transaction completion time. The data synchronization method according to claim 1, wherein the updated data includes the updated data, the updated data type, the updated table name, . 13. The method of claim 12, wherein generating the neutral data comprises:
Extracting a redo log in a predetermined time from the redo log file;
Determining a DML type of the extracted redo log; And
If the DML type of the redo log is an UPDATE statement, storing the pre-update data, post-update data, and each data type in the neutral data, and if not, storing only the updated data and the updated data type in the neutral data The data synchronization method comprising:

Images