JP2004302919A - Replication system and program therefor - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a replication system and a program therefor which can set complex extraction conditions, customize an internal structure and lighten the load of a disk capacity of a master server, by using a proprietary replication function. <P>SOLUTION: The system comprises an extracting conditions database for storing and holding the extracting the conditions for extracting data to be registered into each replica database from a master database; an extracting means for extracting changed data to be registered into each replica database, respectively, based on the extracting conditions stored and held in the extracting condition database, when the registered data in the master database is changed; and a changed data transmitting means for transmitting the changed data to each replica terminal, in response to requests from respective replica terminals, respectively. <P>COPYRIGHT: (C)2005,JPO&NCIPI

Description

[0001]
TECHNICAL FIELD OF THE INVENTION
The present invention relates to a replication system and a program for a replication system used for an RDBMS (Relational Database System).
[0002]
[Prior art]
FIG. 7 is a diagram schematically showing a hardware configuration and a data flow of a conventional replication system. Here, a replication function (snapshot) used in a replication system of Oracle (registered trademark) which is a product of the RDBMS will be described.
[0003]
As shown in FIG. 7, the master server 1 and the snapshot terminals 2 to 4 are connected by a network (not shown). A master Data Base 1A (hereinafter abbreviated as DB) and snapshot DBs 2A to 4A are arranged in the master server 1 and the snapshot terminals 2 to 4, respectively. The contents are transmitted to the snapshot terminals 2 to 4, and the registered contents of the snapshot DBs 2A to 4A are changed.
[0004]
Specifically, the master server 1 generates a snapshot log 1a composed of the changed data. The snapshot log 1a is transmitted to each of the snapshot terminals 2 to 4 as snapshots 2a to 4a (2a, 3a, 4a).
Each time the master server 1 changes the data content of the master DB 1A, it outputs the changed data as a snapshot log 1a. The snapshot terminals 2 to 4 periodically read the snapshot log 1a of the master server 1 and change (refresh) the snapshots 2a to 4a (for example, see Patent Document 1).
[0005]
[Patent Document 1]
JP-A-2002-116939 (pages 3 to 6, FIG. 1)
[0006]
[Problems to be solved by the invention]
However, the conventional replication system has the following problems.
Each of the snapshot terminals 2 to 4 often has different data. In such a case, the extraction conditions for the snapshots 2a to 4a can be set to some extent, but complicated extraction conditions cannot be set. For this reason, the snapshots 2a to 4a are simple copies of the master DB 1A.
[0007]
Further, as described above, since one type of the snapshot log 1a is provided for each master server, each of the snapshot terminals 2 to 4 has to capture unnecessary data, and the refresh time is long. This was remarkable especially when a low-speed line was used.
[0008]
The snapshot log 1a is not deleted unless refresh is performed on all the snapshot terminals 2 to 4. For this reason, if there are the snapshot terminals 2 to 4 that do not periodically refresh, the period during which the snapshot log 1 a of the master server 1 is held becomes longer, and the disk capacity of the master server 1 is reduced.
[0009]
The present invention has been made in order to solve the above-described problems. In particular, by using a unique replication function without using the replication function of Oracle, it is possible to set complicated extraction conditions and internal processing. It is an object of the present invention to provide a replication system and a program for a replication system which enables customization of the structure and has a light load on the disk capacity of the master server.
[0010]
[Means for Solving the Problems]
The replication system of the present invention includes a master server connected to a master database, and a replica terminal connected to the master server through a network and connected to a plurality of replica databases that register the same data as the registered data of the master database. An extraction condition database that stores and retains extraction conditions for extracting data to be registered in each replica database from the master database, and a registered data of the master database is changed. Extracting means for extracting change data to be registered in each replica database based on the extraction conditions stored and held in the extraction condition database; and transmitting the change data to each replica terminal in response to a request from each replica terminal. And a changing data transmission means for transmitting to.
[0011]
Further, the information processing apparatus further includes a change data deletion unit that deletes the change data on the master server side when receiving a process of deleting the change data extracted by the extraction unit from the replica terminal.
[0012]
Each change data is provided with a terminal ID and a data identification number, and the change data deletion means deletes the change data specified by the terminal ID and the data identification number.
[0013]
Further, a change mode determination for determining a change mode, which is a method of registering change data in the replica database, based on whether the change of the registration data in the master database is performed by a deletion process, an insertion process, or an update process. Means are further provided.
[0014]
Further, when the change mode determination means determines that the change mode of the change data is the insertion process, the change mode determination means determines whether there is data having the same key in the replica database, and determines whether the data of the same key exists. When it is determined that there is no data, the change mode is set as the insertion process, and when it is determined that there is data of the same key, the change mode is set as the update process.
[0015]
Further, when the change mode determination means determines that the change mode of the change data is an update process, the change mode determination means determines whether there is data having the same key in the replica database, and determines whether the data of the same key exists. If it is determined that the data exists, the change mode is set to the update process. If it is determined that there is no data of the same key, the change mode is set to the insert process.
[0016]
A replication system program according to the present invention includes a master server connected to a master database, and a replica terminal connected to the master server via a network and connected to a plurality of replica databases that register the same data as the registered data of the master database. And a computer for storing extraction conditions for extracting data to be registered in each replica database from the master database when registration data of the master database is changed. Extraction means for extracting change data to be registered in each replica database based on the extraction conditions stored in the held extraction condition database, and the change data in response to a request from each replica terminal. Which is a program to function as a changing data transmission means for transmitting to each replica terminal.
[0017]
BEST MODE FOR CARRYING OUT THE INVENTION
Hereinafter, a preferred embodiment in which a replication system and a program for a replication system according to the present invention are applied to an Oracle RDBMS will be described in detail with reference to the drawings.
FIG. 1 is a diagram schematically showing a hardware configuration and a data flow of a replication system according to the present invention.
[0018]
As shown in FIG. 1, the master server 11 is connected to replica terminals 12 to 14 via a network (not shown). The master server 11 includes a master DB 11A and an extraction condition DB 11B, and each of the replica terminals 12 to 14 includes replica DBs 12A to 14A.
Although the data registered in each of the replica DBs 12A to 14A is different, the same data as all the data registered in the replica DBs 12A to 14A is registered in the master DB 11A.
[0019]
Further, in the extraction condition DB 11B, extraction conditions for extracting data registered in each of the replica DBs 12A to 14A from registered data in the master DB 11A are registered in advance. This extraction condition is constituted by a combination of a plurality of search conditions (SELECT sentences) and a decision logic such as a conditional sentence.
The data contents of the replica DBs 12A to 14A are changed according to the change of the data contents of the master DB 11A based on the respective extraction conditions ("change" of the registered data of the replica DB is called "refresh").
[0020]
In the replication system of the present invention, when the data content of the master DB 11A is changed, when the registered content of the master DB 11A is changed, the master server 11 uses the database trigger function to extract each replica terminal 12 from the extraction condition DB 11B. 14 are extracted, and changed data is extracted from the master DB 11A based on the read extraction conditions to generate replica logs 11a to 11c as changed data. This extraction / generation processing is performed by the function of the master server 11 as an extraction unit.
Each of the replica logs 11a to 11c has a terminal ID which is an identifier corresponding to each of the replica terminals 12 to 14.
[0021]
The master server 11 transmits replica logs 11a to 11c in response to requests from the replica terminals 12 to 14. This is executed by the function of the master server 11 as a change data transmitting unit. The replica terminals 12 to 14 that have taken in the replica logs 11a to 11c refresh the data contents of the replica DBs 12A to 14A at preset timings, and delete the replica logs 11a to 11c of the master server 11 after the refresh.
[0022]
FIG. 2 is a diagram showing a table configuration of a replica log used in the replication system of the present invention.
As shown in FIG. 2, the replica log includes a table including a terminal ID, a change mode, a master table layout, a change time, and an SEQ (hereinafter, this table is referred to as a replica log table).
[0023]
The terminal ID is an identifier for identifying a replica terminal that refers to the changed record. The change mode refers to a change of master data (abbreviated as I) by Insert (insertion), a change of master data (abbreviated as U) by Update (update), and a change of master data (abbreviated as D) by Delete (deletion). Point to either. The change mode is determined by the function of the master server 11 as a change mode determining unit, and is determined by whether the registration data (master data) of the master DB 11A has been changed by insertion, update, or deletion.
[0024]
The master table layout is an area for storing data (changes) changed in the master DB 11A. In this area, in addition to the change data, a master primary key to be described later is also stored. The change time is the date and time when the change process was performed. The SEQ is a number (data identification number) that is incremented every time data is output to the replica log table and is numbered in chronological order so that duplicate data including past log records are not duplicated. .
[0025]
In the replica logs 11a to 11c having such a structure, the changes of the master DB 11A are stored in time series for each of the replica terminals 12 to 14. At this time, only the last state of the record changed with “terminal ID + primary key of master data” as primary is stored.
[0026]
The change processing in the replication system of the present invention will be described separately for basic change processing and exceptional change processing. This change processing is configured by processing to insert (Insert), update (Update) or delete (Delete) the data contents of the replica DBs 12A to 14A using the above-described replica logs 11a to 11c.
[0027]
The basic change process is a process of performing the following I, U, or D change process on the registered contents of the replica DBs 12A to 14A in accordance with the change mode of the tables (replica log tables) of the replica logs 11a to 11c.
I: Insert into the replica table with the value of the master table layout.
U: Update the replica table with the values of the master table layout.
D: Delete the replica table using the primary key of the master table.
[0028]
The change processing is performed in principle according to the change mode of the replica log table, but the following exceptional change processing is performed as an exception in order to maintain consistency of the data contents of the master DB 11A and the replica DBs 12A to 14A.
In the description of the exceptional change process, a process between the master server 11 and the replica terminal 12 will be described as an example, but a process between the master server 11 and the replica terminal 13 and between the master server 11 and the replica terminal 14 will be described. The same applies to the processing between the two.
[0029]
First exceptional change processing
Even if the change mode is "I", if a record with the same primary key exists in the replica DB 12A, the processing is switched to the update processing and the processing is performed.
Second exceptional change process
Even if the change mode is “U”, if there is no record of the same primary key in the replica DB 12A, the processing is switched to the insertion processing.
[0030]
The exceptional change processing as described above is not a countermeasure for a failure, but is for dealing with a problem that constantly occurs.
Specifically, it occurs in the following example shown in FIG.
FIG. 3A is a diagram illustrating the configuration of the replica log table when performing the first exceptional change process.
[0031]
In FIG. 3A, what is indicated as "key A" is a primary key.
The master server 11 always leaves only the data contents (log) of the replica log table for the latest change for the record of the same primary key. Therefore, if the master server 11 extracts the replica log table of (1) and the replica terminal 12 extracts the next replica log table of (2) before taking in the replica log 11a, the replica terminal 12 returns to (1). Before the content of the replica log table of 1) is reflected in the replica DB 12A, the data is refreshed by the data content (log) of the replica log table of 2), and the data content of the replica log table of 1) remains as a replica log. Absent.
[0032]
At this time, if the insertion processing is performed according to the replica log table of (2), the deletion processing by the replica log table of (1) is not performed in the replica DB 12A (that is, the data remains without being deleted. Since there is a record whose primary key is “key A”, the insertion process cannot be performed and an error occurs due to the unique constraint. Therefore, in such a case, the refresh change mode is switched to the update process. This is the first exceptional change process.
[0033]
FIG. 3B is a diagram illustrating the configuration of the replica log table when the second exceptional change process is performed.
As described above, the master server 11 always keeps only the data contents (log) of the replica log table for the latest change for the record of the same primary key. Therefore, if the master server 11 extracts the replica log table of (1) and the replica terminal 12 extracts the next replica log table of (2) before taking in the replica log 11a, the replica terminal 12 returns to (1). Before the content of the replica log table of 1) is reflected in the replica DB 12A, the data is refreshed by the data content (log) of the replica log table of 2), and the data content of the replica log table of 1) remains as a replica log. Absent.
[0034]
At this time, when the update process is performed in accordance with the data content (log) of the replica log table of (2), the replica DB 12A uses the replica log table of (2) without performing the insert process by the replica log table of (1). Update processing will be performed. Since there is no record having the primary key “Key A” in the replica DB 12A, the processing content is 0 update processing. Therefore, in such a case, insertion processing is performed instead of update processing. This is the second exceptional change process.
[0035]
FIG. 4 is a diagram showing the configuration of a replica log on the master DB side when performing log deletion processing in the replication system of the present invention.
The log table in the master DB 11A is deleted after the refresh on the replica terminal 12 side is completed. The deletion of the log table is performed by receiving a deletion request from the replica terminal 12 whose refresh has been completed. This is a process executed by the function of the master server 11 as a change data deleting unit.
Basically, the log table of the terminal ID of the replica terminal 12 that has been refreshed is deleted. However, instead of deleting the log table specified only by the terminal ID, using both the “terminal ID” and “SEQ” Delete the specified log table.
[0036]
As described above, the deletion process is performed using both the “terminal ID” and the “SEQ”, and the log table having the SEQ equal to or less than the SEQ of the log table finally refreshed (in FIG. 4, the log of (1)). Table), and the log table of (2) is reflected in the replica DB 12A at the next refresh, so that only the log table of (1) exists on the master server 11 side at the start of the refresh, and the middle of the refresh Alternatively, if the log table of (2) occurs between the completion of the refresh and the log table deletion processing, the log table specified by only the terminal ID is specified and the deletion processing is performed after the completion of the refresh. The log of (2) without reflecting the data content (log) of the log table of (2) in the replica DB 12A Buru is deleted, it is possible to prevent the inconsistencies in the data content of the master DB11A and Replica DB12A～14A.
[0037]
The processing procedure on the master server side and the replica terminal side in the replication system of the present invention having the above functions is as follows.
FIG. 5 is a flowchart showing a processing procedure on the master server side in the replication system of the present invention. This processing procedure is executed by a program used for the replication system of the present invention.
[0038]
As shown in FIG. 5, first, when the database trigger function of Oracle is activated, the processing in the master server 11 is started.
In step S1, a terminal ID for specifying an output destination of data (change) changed in the master DB 11A is specified. This is the terminal ID of the replica terminal (any of 12 to 14) that has requested the output of the replica log table.
In step S2, it is determined whether the change mode is a delete process (D), an insert process (I), or an update process (U). This is a determination based on the change mode of the replica logs 11a to 11c in the master DB 11A.
[0039]
If it is determined in step S2 that the process is the deletion process (D), the flow proceeds to step S3, and the data content (log) for executing the deletion process in the replica log table is determined by the terminal ID specified in step S1. It outputs to any of the represented replica terminals 12-14. This ends step S3.
[0040]
If it is determined in step S2 that the processing is the insertion processing (I), the flow proceeds to step S4, and it is determined whether or not a record of the same primary key exists in the replica DB 12A.
If it is determined in step S4 that there is a record with the same primary key in the replica DB 12A, the flow proceeds to step S5, and the data content (log) for executing the insertion process in the replica log table is stored in step S1. Output to any of the replica terminals 12 to 14 represented by the specified terminal ID. This ends step S5.
[0041]
On the other hand, if it is determined in step S4 that there is no record with the same primary key in the replica DB 12A, the flow proceeds to step S7 described below to perform an update process. Thus, the first exceptional change processing is performed.
[0042]
If it is determined in step S2 that the process is an update process (U), the flow proceeds to step S6, and determines whether or not a record with the same primary key exists in the replica DB 12A.
If it is determined in step S6 that there is a record with the same primary key in the replica DB 12A, the flow proceeds to step S7, where the data contents (log) for executing the update process in the replica log table are stored in step S1. Output to any of the replica terminals 12 to 14 represented by the specified terminal ID. This ends step S7.
[0043]
On the other hand, if it is determined in step S6 that no record with the same primary key exists in the replica DB 12A, the flow proceeds to step S5 described above, and insert processing is performed. Thus, the second exceptional change process is performed.
Thus, the processing on the master server 11 side ends.
[0044]
FIG. 6 is a flowchart showing a processing procedure on the replica terminal side in the replication system of the present invention. This processing procedure is executed by a program used for the replication system of the present invention.
[0045]
In FIG. 6, when a start command is received at a preset time or at an arbitrary time on the replica terminal side, processing on the replica terminal side starts.
First, in step S11, the master server 11 is accessed, and it is determined whether a replica log table having the terminal ID of the own terminal exists in the master DB 11A.
[0046]
If it is determined that there is a replica log table having the terminal ID of the own terminal, the flow proceeds to step S12, and the change mode included in the replica log table is the deletion process (D), the insertion process (I), or the update process. (U) is determined.
[0047]
If it is determined in step S12 that the change mode is the deletion process (D), the flow proceeds to step S13, and the record of the log table in the replica DB of the terminal is deleted. Thereby, the refresh by the deletion process is completed. When the process of deleting the records in the log table ends, the flow proceeds to step S16, accesses the master server 11, and deletes the replica log corresponding to this process.
When step S16 ends, the flow ends.
[0048]
If it is determined in step S12 that the change mode is the insertion process (I), the flow proceeds to step S14, where a record of the log table in the replica DB of the terminal is inserted.
When the process of inserting the record in the log table ends, the flow proceeds to step S16, accesses the master server 11, and deletes the replica log corresponding to this process.
When step S16 ends, the flow ends.
[0049]
If it is determined in step S12 that the change mode is the update process (U), the flow proceeds to step S15, and the record of the log table in the replica DB of the terminal is updated.
When the update processing of the record in the log table ends, the flow proceeds to step S16, accesses the master server 11, and deletes the replica log corresponding to this processing.
When step S16 ends, the flow ends.
[0050]
In the above description, a case has been described in which the replication system and the replication system program of the present invention are applied to Oracle's RBDMS. However, the replication system and the replication system program of the present invention can also be applied to RBDMS other than Oracle. Things.
[0051]
As described above, according to the replication system of the present invention and the program used therein, it is possible to set complicated replica extraction conditions for each replica terminal. For this reason, since each replica terminal only needs to take in the replica log of its own terminal, there is no need to read a log including unnecessary data as in the related art, and the refresh time can be greatly reduced.
[0052]
In addition, a change data deletion unit for deleting change data on the master server side is provided, a replica log is created for each replica terminal, and when each replica terminal refreshes, the replica log is deleted. Since there is only one terminal that does not perform the log processing, it is not necessary to hold a log having a huge capacity for a long time on the main server side, and the disk capacity on the main server side can be saved. Further, the disk capacity can be saved also by outputting only the last change content to the master server.
[0053]
Further, since the change data specified by each terminal ID and data identification number is deleted, it is possible to prevent inconsistency in the data contents between the master DB 11A and the replica DBs 12A to 14A.
[0054]
Further, a change mode determination for determining a change mode, which is a method of registering change data in the replica database, based on whether the change of the registration data in the master database is performed by a deletion process, an insertion process, or an update process. Since the apparatus further includes means, it is possible to provide a replication system in which data inconsistency between the master DB and the replica DB is suppressed.
[0055]
Further, when the change mode determination means determines that the change mode of the change data is the insertion process, the change mode determination means determines whether there is data having the same key in the replica database, and determines whether the data of the same key exists. If it is determined that there is no data, the change mode is set to the insertion processing, and if it is determined that there is data of the same key, the change mode is set to the update processing. Therefore, a replication system that suppresses data inconsistency between the master DB and the replica DB. Can be provided.
[0056]
Further, when the change mode determination means determines that the change mode of the change data is an update process, the change mode determination means determines whether there is data having the same key in the replica database, and determines whether the data of the same key exists. If it is determined that there is, the change mode is set as the update process, and if it is determined that there is no data of the same key, the change mode is set as the insert process, so even if the content of the replica log changes before the refresh is performed, It is possible to provide a replication system that suppresses data inconsistency between the master DB and the replica DB.
[0057]
The program for a replication system according to the present invention includes a master server connected to a master database, a plurality of replica databases connected to the master server via a network, and registering the same data as the registered data of the master database. A program for a replication system used in a relational database system having a replica terminal, wherein, when registration data of a master database is changed in a computer, extraction conditions for extracting data to be registered in each replica database from the master database Extracting means for extracting change data to be registered in each replica database based on the extraction conditions stored in the extraction condition database storing and holding the data, and changing the data in response to a request from each replica terminal. Since this is a program for functioning as a modified data transmission means for transmitting data to each replica terminal, complex replica extraction conditions can be set for each replica terminal, and each replica terminal can store its own replica log. Since it is only necessary to capture only the data, it is not necessary to read a log including unnecessary data unlike the related art, and it is possible to provide a replication system program that can greatly reduce the refresh time.
[0058]
【The invention's effect】
The replication system of the present invention includes a master server connected to a master database, and a replica terminal connected to the master server through a network and connected to a plurality of replica databases that register the same data as the registered data of the master database. An extraction condition database that stores and retains extraction conditions for extracting data to be registered in each replica database from the master database, and a registered data of the master database is changed. Extracting means for extracting change data to be registered in each replica database based on the extraction conditions stored and held in the extraction condition database; and transmitting the change data to each replica terminal in response to a request from each replica terminal. Because and a changing data transmission means for transmitting, it is possible to set up complex replica extraction condition for each replica terminal. For this reason, since each replica terminal only needs to take in the replica log of its own terminal, there is no need to read a log including unnecessary data as in the related art, and the refresh time can be greatly reduced.
[Brief description of the drawings]
FIG. 1 is a diagram schematically showing a hardware configuration and a data flow of a replication system according to the present invention.
FIG. 2 is a diagram showing a table configuration of a replica log used in the replication system of the present invention.
FIG. 3 is a diagram illustrating a configuration of a replica log table when performing a first exceptional change process and a second exceptional change process in the replication system of the present invention.
FIG. 4 is a diagram showing a configuration of a log table in a replica DB when performing log deletion processing in the replication system of the present invention.
FIG. 5 is a flowchart showing a processing procedure on the master server side in the replication system of the present invention.
FIG. 6 is a flowchart showing a processing procedure on the replica terminal side in the replication system of the present invention.
FIG. 7 is a diagram schematically showing a hardware configuration and a data flow of a conventional replication system.
[Explanation of symbols]
11 master server, 11A master DB, 11a, 11b, 11c replica log, 11B extraction condition DB, 12, 13, 14 replica terminal, 12A, 13A, 14A replica DB.

Claims (7)

Used in a relational database system comprising a master server to which a master database is connected, and a replica terminal connected to the master server through a network and connected to a plurality of replica databases for registering the same data as the registration data of the master database. A replication system,
An extraction condition database that stores and retains extraction conditions for extracting data to be registered in each replica database from the master database;
When registration data of the master database is changed, extraction means for extracting change data to be registered in each replica database based on the extraction conditions stored in the extraction condition database,
A replication system comprising: a change data transmitting unit that transmits the change data to each replica terminal in response to a request from each replica terminal. 2. The replication system according to claim 1, further comprising a change data deletion unit that deletes the change data on the master server side when receiving a deletion process of the change data extracted by the extraction unit from the replica terminal. 3. The replication according to claim 2, wherein each change data is provided with a terminal ID and a data identification number, and the change data deletion unit deletes the change data specified by the terminal ID and the data identification number. system. A change mode determining unit that determines a change mode, which is a method of registering change data in the replica database, based on whether the change of the registration data in the master database is performed by a deletion process, an insertion process, or an update process. The replication system according to any one of claims 1 to 3, further comprising: When the change mode determination unit determines that the change mode of the change data is the insertion process, the change mode determination unit determines whether there is data having the same key in the replica database. 5. The replication system according to claim 4, wherein when it is determined, the change mode is an insertion process, and when it is determined that there is data of the same key, the change mode is an update process. When the change mode determination unit determines that the change mode of the change data is the update process, the change mode determination unit determines whether there is data having the same key in the replica database. 5. The replication system according to claim 4, wherein when it is determined that the change mode is the update processing, and when it is determined that there is no data of the same key, the change mode is the insertion processing. Used in a relational database system comprising a master server to which a master database is connected, and a replica terminal connected to the master server through a network and connected to a plurality of replica databases for registering the same data as the registration data of the master database. A program for a replication system, wherein a computer
When the registration data in the master database is changed, the extraction conditions for extracting the data to be registered in each replica database from the master database are stored and retained. The extraction conditions are registered in each replica database based on the extraction conditions stored in the database. Extraction means for extracting change data to be provided; and
A program for a replication system for functioning as change data transmission means for transmitting the change data to each replica terminal in response to a request from each replica terminal.

Images