RU2706482C1 - Method of replication of information in databases - Google Patents

Abstract

FIELD: information technology.

SUBSTANCE: invention relates to a method of replicating information in a database. Two computers are connected over a local area network and data is exchanged between computer databases, and, in each of two computers with ORACLE servers installed in them. A database with an unchangeable knowledge base and a variable database of clients and studies, which is made with possibility of independent changes and additions on each of two computers, is preloaded. One of the computers is transferred to the source computer by forming an ORACLE server on its ORACLE-server accessing the ORACLE-server of the other computer, which is transferred to the computer-receiver. Data are compared in their variable databases on a set of fields, which determines records in variable databases. Data which are available in the variable database of the source computer and which are not available in the variable database of the receiver computer are selected. Inserting into a variable database of a computer-receiver, and then another computer is transferred to a computer source by forming on its ORACLE-server access to ORACLE-server of the first computer, which is transferred to a computer-receiver. Said operations are performed between said source computer and computer-receiver.

EFFECT: wider field of application of the method in order to enable replication (synchronization) of databases of two independent computers.

1 cl, 4 tbl

Description

The invention relates to information technology, in particular, to methods and means for ensuring information exchange between computer systems and can be used to maintain in an identical state databases (DB) located in independent computers.

A typical practical problem that arises for computer systems containing, for example, the main (main) stationary part (for example, desktop desktop) and the mobile (peripheral) part (for example, a mobile laptop), the information in which can be changed without being connected to the Internet, and work on a laptop is in off-site mode. In this case, there is a need to regularly perform full replication / synchronization of databases loaded on computers.

A known method of information replication and a system for its implementation, described in the application [US 2005/0273474, A1, 08/08/2005], according to which, in the process of recording information in the main database, the information processing device performs transaction processing and creates logs (log files ) transactions, on the basis of which, according to pre-established rules, a sequence of calls and records to the main and replicated databases is formed and optimized, moreover, the user can refer to the replicated database as -volatile.

This ensures timely updating of the main and replicated databases while reducing the load on the recording channels and accelerating data access.

However, this method does not allow reliable transmission and replication of data in distributed computer networks.

There is also known a method of data replication [WO 2006052517, A1, 05/18/2006], which consists in processing requests in accordance with the parameters that evaluate the need to transfer this object at a given time in terms of ensuring

the necessary "quality" of information at each point in the network and the time that this object exists in the database.

The method allows to reduce the load on the data transmission network without significantly reducing the quality of service, however, it leads to delays in access to data between different network nodes and, therefore, is impractical with high requirements for the identity of information in the main and replicated databases.

There is also known a method of replicating information [US 2005289198 A1, December 29, 2005], which consists in recording transaction-changes in the first information processing system (database - source), and forming a group of such transactions that are transmitted as a message to the second system processing information in which transactions are allocated from the group and independently applied to the replica database located there, while a table of successfully completed transactions is formed, data received from the first system is stored in the second system and deleted I am a software component that checks the table of successfully completed transactions and, based on it, makes a decision when it is safe to delete a message.

The method ensures that all received data will be entered into the second system, however, it does not provide any measures to ensure the correct transmission of data via communication channels.

In addition, there is a known method of replicating information in distributed databases and a system for its implementation, described in the application [US 20050080825 A1, 04/14/2005], in which operations are reduced to the introduction of an intermediate database between the main and peripheral computer systems, moreover, the intermediate database is logically allocated part of the primary database and is connected to remote databases with a synchronization protocol that updates peripheral databases.

The indicated replication process scheme provides for the use of an intermediate database, as well as monitoring the replication process only on the side of the leading database. However, when data is transmitted in both directions, this scheme, in the General case, does not allow to ensure control of the correctness of the transmitted data.

In addition to the above, there is a known method of managing file replication in applications [RU 2349952, C2, G06F 12/00, 03/20/2009], which consists in initiating, using the first software application running on the processor, a synchronization event to synchronize the first versions with the second version make the program interface of the computer system application access the aforementioned first and second versions by means of the first software application, compare them by software the application interface information associated with each of the aforementioned first and second versions, and generate the result, report the comparison result to the first software application via the application software interface, and force the second software application running on the processor in response to the result to the application software interface, generate a user interface in accordance with the characteristic of the second software application, while the generated user interface is presented to the user in the second software application, wherein the electronic file is associated with the second software application, the first and second versions of the electronic file are opened by the second software application, and the first and second versions are opened to the user, presented to the user by the second software application synchronization option for submitted versions using the generator the user interface of the second software application, the synchronization option being provided by the second software application, receiving input from the user through the second software application through the user interface, automatically synchronizing the first and second versions using the second software application according to the user input received through user interface, and, at the same time, automatic syn chronization of the first and second versions comprises merging the first and second versions into the third version according to the input received from the user interface, while remaining in the user interface of the second software application, and overwriting the first and second versions with the third version.

The disadvantage of this method is the relatively narrow scope, which is limited to files recorded in the memory of one computer.

Also known is the method of information replication in distributed databases with competitive distribution of flows [RU 2510623, C2, G06F 12/00, 04/10/2014], which consists in the fact that, in the process of exchanging replicated data between nodes of a distributed database, exchange files are formed that are sent between the nodes of the distributed database, and based on the parameters of the exchange files and information about available network resources, they calculate the distribution of the flows of exchange files on communication devices, transmit information about the variant e distribution exchange stream files, in the form of a control signal to the inputs of the communications devices is calculated using the communication device based on the distributed algorithm metabolic routes file transfer, exchange files transmitted on certain routes.

The disadvantage of this technical solution is also a relatively narrow scope.

The closest in technical essence to the proposed one is a method of data replication between the main and peripheral computer systems containing the main and intermediate databases (DB), [RU 2315349, C1, G06 F / 00, 01.20.2008], which consists in monitoring the availability of data for replication for the main and each of the peripheral systems, the subsequent exchange of these data between the database of the main and the database of the peripheral computer systems through intermediate databases and checking the correctness of the replication performed, moreover, in the process of exchanging replicated data between the database the external and the database of the peripheral computer systems generate exchange files, which are placed in the storage of exchange files of each of the computer systems configured to store and select exchange files, verify the correctness of the data contained in them, generate and execute requests to replace incorrect data, transfer exchange files between the main and peripheral systems, they verify the correctness of the received files and, if they are incorrect, provide for the repeated transfer of exchange files from the storage, then The data from the received files is transferred to the intermediate database, the reference integrity of the data in the intermediate database is checked, and if the verification result is negative, they formulate a data replacement request and provide replacement of incorrect data, re-perform the specified verification and, if the result is positive, transfer the data to the main database, while to verify the correctness of the replication performed, hash sums of the corresponding records in the main databases of the main and peripheral computer systems are generated.

The features of the closest technical solution is that the exchange file storage contains exchange directories according to the number of peripheral computer systems, each of which contains a catalog of confirmation files processed correctly; directory of confirmation files that were processed incorrectly; archive directory of processed confirmation files; a directory of data files and confirmation files ready for transfer to the destination node; A directory of files sent to the destination node, for which a delivery confirmation has not yet arrived and the waiting time for confirmation has not been exceeded, and files not yet sent to the destination node; directory of files that were transferred and uploaded to the database of the destination node, as received confirmation; archive directory of files transferred correctly; a directory of files that were transferred correctly but were not loaded into the database of the destination node, as confirmed; a directory of transferred files for which for a long time there has been no confirmation of loading the destination node into the database; a directory of data files and confirmation files received from the destination node and ready for download to the database or analysis; directory of files received from the destination node and correctly loaded into the database; archive directory of received and uploaded files; A directory of files received from the destination node but not loaded into the database.

In addition, in this technical solution, the transfer of exchange files is carried out according to the FTP protocol, the transfer of exchange files is carried out on a machine-readable medium.

The disadvantage of the closest technical solution is the relatively narrow scope, since replication is performed in a distributed system consisting of their main database and a number of peripheral databases. Therefore, without significant changes in the replication procedure, it is impossible to use the well-known technical solution for the case when at least two computers are equivalent and each of the computers acts as a destination computer or as a source computer, and at another moment they change roles and only in this case both databases will be fully synchronized. At the same time, the database consists of two parts - a knowledge base and a regularly updated database of clients and research, moreover, synchronization is performed only relative to the client database and research, because changes to the knowledge base are made centrally and the knowledge base is considered the same in different replicas.

The problem that is solved in the invention is to create a method for two computers, each of which is loaded with the same structured database with an unchanged knowledge base and independently modified and supplemented information in the client and research database, which consists in periodically synchronizing the database in both computers.

The required technical result is to expand the scope of the method of information replication in distributed databases in order to ensure the possibility of replication (synchronization) of the databases of two independent computers, each of which has the same database structure with an unchanged knowledge base and independently modified and supplemented information in customer base and research.

The problem is solved, and the required technical result is achieved by the fact that, in the method of replicating information in the databases of two computers, which consists in the fact that, during replication, both computers are connected via a local area network and exchange data between computer databases, and each from two computers with ORACLE-servers installed in them, they preload the database with an unchanged knowledge base and a variable database of clients and research, made with the possibility of independent changes and up to complements on each of the two computers, according to the invention, first one of the computers is transferred to the source computer by forming on its ORACLE server access to the ORACLE server of the other computer, which is transferred to the destination computer, the data in their mutable databases are compared fields defining records in mutable databases, select the data available in the mutable database of the source computer and not in the mutable database of the destination computer, and inserted into the mutable database of the computer iemnika and then transferred to another computer, computer source by forming at its ORACLE-server access the server ORACLE-first computer, which translates the computer-receiver, and produce the above operations between the source computer and the receiver computer.

Consider an example of the implementation of the proposed method for replicating information in databases.

In the medical research center, as a means of storing data on two independent computers, one of which is used, for example, as a mobile version, a modification of the local DBMS ORACLE installation is used. The task is to combine data from two computers and develop a mechanism that allows regular data synchronization between two ORACLE servers.

The database consists of two parts - a practically unchanged knowledge base and a regularly updated database of clients and research, made with the possibility of independent changes and additions on each of the two computers

The task involves synchronizing only the variable part of the database, since changes to the knowledge base are made centrally, and the knowledge base is considered the same in different replicas of the DBMS. Below, under the term "Database" we mean exactly the variable part of the DBMS.

The Central table of the Database is the “Research” table (ANALYSIS), all other tables are associated with it by the research identification number (research ID) with a one-to-many relationship. The exception is the “Patients” table (CLIENTS), which is associated with the “Research” table by the patient's identification number in a one-to-one relationship. An example of the tables is presented below (tables 1-4).

The database does not provide both patient uniqueness and for each new study a new entry is made in the “Patients” table, as well as the uniqueness of the study data. This feature of the Database makes it difficult to compare its two modifications on two independent computers.

It is proposed to use a set of patient ID fields + biosubstrate type ID + mineralogram ID + biosubstrate ID profile + biosubstrate sampling date + diabetes sign + evidence of obesity + sign of CVD + sign of alcohol restriction as unique data on the study within the local DBMS.

For the Patients table, the Patient ID field is an automatically incremented counter and cannot be used in a unique combination of fields to compare data between two servers. Instead, it is necessary to use a unique set of fields in the "Patients" table to uniquely identify patient data Card number + Last name + First name + Birthday + Gender.

Thus, the set of fields, patient ID + biosubstrate type ID + mineralogram ID + study profile ID + biosubstrate sampling date + Sign for diabetes + Sign for obesity + Sign for CVD + Sign for alcohol restriction + Card number + Surname + First name + Birthday + Gender uniquely determine each study and can be taken as an identification combination of fields.

The “Systematic research status” field contains values characterizing the completeness of processing the research results and can be used as a criterion for updating records matching in the identification set of fields.

Many studies in each of the compared databases are defined by a combination of fields that uniquely identify each study. The task is to combine these sets, while in the intersection of sets it is necessary to leave the studies whose processing is as complete as possible (the degree of "completeness" of the study is determined by the value in the System status of the study field). It is assumed that studies matching the combination of fields for identification and with the same value in the System status field of the study are identical and they are not compared.

We denote a lot of research in the compared databases A and B.

To combine sets, you must do the following:

- find the difference between the sets of AVB;

- add the resulting difference to the set B;

- in the area of intersection of sets A∩B find records that have different values of the System status of the research field;

- update in the set B those records for which there are records with a “large completion” in the intersection area by the value in the System status field of the study and matching the rest of the fields for identifying the study;

- repeat the described actions, swapping the database A and B.

Since the “Research” table (ANALYSIS) is associated with a large number of other tables detailing the analyzes performed, by the research identification number (research ID), and this number is internal for each Database, that is, different studies can be designated by the same value of the research ID field on different databases, when transferring information from one database to another, it is necessary to calculate the corresponding value of the study ID, and use it in related tables.

A DATABASE LINK database object named MASTER is the connection of the local server to the remote database.

Configuring triggers on the Patients and Research tables. To do this, you need to change the auto-increase of the primary key of the table so that the calculation of the new key occurs only when the key is not filled. The calculation of new primary key values during the data merge task will not occur through a trigger, but inside the merge program, so that the primary key value can be used when adding data from related tables.

Queries to compare NEWDATA and UPDDATA tables.

NEWDATA - a query that calculates the difference between the sets of studies on the local server and the server connected via DBLink MASTER. The result is presented in pairs (ID_ANALYSIS, IDCLIENTS) that define studies that are not in the ANALYSIS @ MASTER table.

UPDDATA is a query that finds those studies on the local server, for which the value of the System research status (STATUS) field is greater than the value of the System research status (STATUS) field on a server connected via DBLink MASTER. The result is presented as pairs (ID_ANALYSIS, IDCLIENTS).

The SYNCHRONIZE table is needed to prepare data for synchronization. Aggregates data from NEWDATA and UPDDATA queries. It contains new record identifiers that will be inserted into the corresponding tables of the MASTER server. It also marks the records to be inserted when manually selecting records.

The SYNCHRONIZEPARAMETERS table is a table with settings for automatically starting synchronization.

Thus, the proposed method achieves the required technical result, which consists in expanding the scope of the method for replicating information in distributed databases in order to enable replication (synchronization) of the databases of two independent computers, each of which is loaded with the same database structure with an invariable database knowledge and independently changed and supplemented information in the customer base and research. This allows replication not under conditions when there are main and peripheral computer systems, but when both systems are equivalent. This improves the accuracy and speed of replication.

Claims (1)

The method of information replication in databases is that during replication two computers are connected over a local network and exchange data between computer databases, and each of the two computers with ORACLE servers installed in them preload the database with an unchanged knowledge base and a variable database of clients and research, made with the possibility of independent changes and additions on each of the two computers, characterized in that at first one of the computers is transferred to a computer ter-source by forming on its ORACLE server access to the ORACLE server of another computer, which is transferred to the receiving computer, compare the data in their mutable databases by the set of fields that define the records in the mutable databases, select the data available in the mutable database data of the source computer and not in the variable database of the destination computer, and inserted into the variable database of the destination computer, and then another computer is transferred to the source computer by forming an image on its ORACLE server connection to the ORACLE server of the first computer, which is transferred to the destination computer, and perform the above operations between these source computers and the destination computer.