JP5243103B2 - Database system and delayed automatic recovery method for differential copy in database system - Google Patents

Description

  The present invention relates to a technique for asynchronously copying master data at a certain point in a master site to a replica site and storing it as replica data, and more particularly to a technique for monitoring a copy delay from a master site to a replica site.

  Conventionally, there is a database system that stores master data updated by a business application at a master site, and stores the master data as replica data at a slave site asynchronously with the business application. When this is realized in the ORACLE (registered trademark) database, a materialized view (hereinafter referred to as M-View) can be used.

  When this M-View is used, replica data is periodically updated (refreshed) in the database system as shown in FIG. That is, the copy processing unit 37 of the replica site 3 refers to the log tables 12a and 12b of the master site 1 to identify the difference data of the master data corresponding to the update log, and the difference data is retrieved from the master tables 11a and 11b. Copy to replica table 31a, 31b.

For example, Patent Document 1 describes a system using M-View.
JP 2006-343798 A

  At this time, the time required for the refresh may become long (delay) for various reasons. For example, there is a case where the data amount of the difference data to be refreshed is huge and the copy process is stopped for some reason. If the refresh process is delayed beyond the allowable range, if this is left as it is, the operation of the system may be hindered.

  Therefore, an object of the present invention is to appropriately detect the delay of the refresh process.

  Another object of the present invention is to analyze the cause of delay in refresh processing and take appropriate measures.

  A database system that asynchronously copies master data according to one embodiment of the present invention to generate replica data includes a master site having a master data storage area, a first data storage area, a replica data storage area, A replica site having two data storage areas, dummy data writing means for periodically writing dummy data in the first data storage area, and master data stored in the master data storage area in the replica data storage area A copy means for periodically copying the difference to the dummy data stored in the first data storage area, and a copy means for periodically copying the dummy data to the second data storage area; A first time of writing to the first data storage area, and the copying means stores the dummy data in the first data storage area; The first monitoring means for monitoring the time required for the differential copy of the master data and the dummy data from the second time written in the two data storage areas, and the monitoring by the first monitoring means As a result, there is provided recovery means for performing automatic recovery processing so that the differential copy is completed within the first delay time if the time required for the differential copy is equal to or longer than a first delay time.

  In a preferred embodiment, the dummy data includes a time when the dummy data writing unit writes the dummy data in the first data storage area, and the copy unit stores the dummy data in the second data storage area. When writing, the copy time is also written to the second data storage area, and the first monitoring unit may monitor the time required for the differential copy with reference to the second data storage area. .

  In a preferred embodiment, the dummy data writing means may be provided at the replica site.

  In a preferred embodiment, the master site further includes a master log storage area for storing an update history of the master data storage area, and means for storing range data indicating a range in which data in the master log storage area exists. The copy means is for identifying the difference master data with reference to the range of the master log storage area indicated by the range data, and copying the difference master data to the replica data storage area; The range data may be changed so that the range indicated by the range data becomes narrow.

  In a preferred embodiment, among the differential copies that have already been executed, means for storing the start time of the last executed differential copy, and from the start time of the last executed differential copy to the current time A second monitoring means for monitoring the progress of the data, wherein the recovery means is a result of monitoring by the first monitoring means, the time required for the differential copy is equal to or longer than a first delay time, and As a result of monitoring by the second monitoring means, the automatic recovery processing may be performed when the time required for the differential copy is equal to or longer than a second delay time.

  Hereinafter, a database system according to an embodiment of the present invention will be described with reference to the drawings.

  FIG. 1 is a configuration diagram of a database system according to the present embodiment.

  This system has a master site 1 and a replica site 3 as shown in FIG. Each of the master site 1 and the replica site 3 is, for example, a computer system configured by one or more general-purpose personal computers including a processor and a storage device. For example, an ORACLE (registered trademark) database is mounted on the master site 1 and the replica site 3. Individual components or functions in the master site 1 and the replica site 3 described below are realized, for example, by executing an ORACLE (registered trademark) database or a predetermined computer program.

  The master site 1 has a master database 10 that stores master data used in predetermined business processing performed by the business application program. The master database 10 includes one or more master tables 11a and 11b that store master data, log tables 12a and 12b that store update histories of the master tables 11a and 11b, a dummy master table 13, and a dummy master table. A dummy log table 14 for storing 13 update histories is stored.

  Master data is stored in the master tables 11a and 11b. The business application program reads data from the master tables 11a and 11b and executes predetermined processing, or updates the master data in the master tables 11a and 11b according to the processing result.

  In the log tables 12a and 12b, an update log of master data by the business application program is recorded. As will be described later, when the copy processing unit 37 of the replica site 3 performs differential copy based on the update log stored in the log tables 12a and 12b, the update log is deleted from the log tables 12a and 12b. That is, the log tables 12 a and 12 b store update logs corresponding to master data that is not reflected in the replica database 30.

  The dummy master table 13 stores dummy data that is not used in business processing by the business application program. The dummy data is written into the dummy master table 13 by the monitoring unit 38 of the replica site 3 described later. The dummy data includes, for example, the time when the dummy data is written to the dummy master table 13.

  FIG. 2A shows an example of the dummy master table 13. As shown in the figure, the dummy master table 13 has an update time 131 as an item. That is, in the example shown in FIG.

  The dummy log table 14 stores an update log (dummy log) of the dummy master table 13. For example, each time the monitoring unit 38 of the replica site 3 writes dummy data to the dummy master table 13, a dummy log is written to the dummy log table 14. As with the log tables 12a and 12b, when the copy processing unit 37 of the replica site 3 makes a differential copy of the dummy master table 13 based on the dummy log, the dummy log is deleted from the dummy log table 14.

  The replica site 3 monitors the replica database 30 that stores replica data that is a copy of the master data, the copy processing unit 37 that performs copying from the master database 10 to the replica database 30, and the copy processing by the copy processing unit 37. And a monitoring unit 38.

  The replica database 30 includes one or more replica tables 31a and 31b that store replica data, a dummy replica table 33 that stores dummy replica data that is a copy of dummy data, a management table 35, and a refresh history table 36. And are stored.

  Replica data (a copy of master data) is stored in the replica tables 31 a and 31 b by the copy processing unit 37. The replica tables 31a and 31b have the same data structure as the master table. Therefore, the business application can also perform processing with reference to the replica tables 31a and 31b.

  Dummy replica data (dummy master data copy) is stored in the dummy replica table 33 by the copy processing unit 37. Further, the time when the dummy replica data is written in the dummy replica table 33 is also stored in the dummy replica table 33.

  FIG. 2B shows an example of the dummy replica table 33. As shown in the figure, the dummy replica table 33 has an update time 331 and a copy time 332 as items. The update time 331 corresponds to the update time 131 of the dummy master table 13. That is, dummy data is copied at the update time 331. The copy time 332 is an item that does not exist in the dummy master table 13. When dummy data is written at the update time 331 by the copy processing unit 37, the current time is written at the copy time 332.

  The copy processing unit 37 periodically copies the master data and dummy data in the master database 10 to the replica database 30 asynchronously with the timing at which each is updated. The copy processing unit 37 differentially copies master data and dummy data (difference data) not reflected in the replica database 30 to the master database 10. The copy processing unit 37 refers to the log tables 12a and 12b and the dummy log table 14 to extract difference data. For example, when the copy processing unit 37 refers to the log tables 12a, 12b and the dummy log table 14, and the update log or dummy log exists in the log tables 12a, 12b and the dummy log table 14, the update log or dummy log indicates The updated master data and dummy data are copied to the replica database 30. The copy processing unit 37 deletes the update log and the dummy log from the log tables 12a and 12b and the dummy log table 14 after the copying is completed. Thereby, the log tables 12a and 12b and the dummy log table 14 store the update log and the dummy log related to the update of the master data and the dummy data not reflected in the replica database 30. As a result, the copy processing unit 37 can grasp the difference data that has not been copied to the replica database 30 by referring to the log tables 12 a and 12 b and the dummy log table 14.

  When the copy processing unit 37 writes dummy data to the dummy replica table 33, the copy processing unit 37 copies the update time 131 at the update time 331 and writes the current time at the copy time 332. Thereby, the time when each record of the dummy replica table 33 was written is recorded.

  In addition, the copy processing unit 37 performs differential copying of a plurality of tables as one group (refresh group: RG) and a table of the same refresh group through a series of processes. In the present embodiment, one dummy master table 13 may be included in one refresh group. The copy processing unit 37 copies a plurality of tables in one refresh group according to a predetermined order. Here, the dummy master table 13 is the last copy order of the tables in each refresh group. Therefore, the copy time 332 indicates the end time of copying of each refresh group.

  When the copy processing unit 37 performs copying for each refresh group, it may be refreshed (copied) periodically in units of refresh groups.

  Furthermore, the copy processing unit 37 writes the refresh start time and end time to the management table 35 for each refresh group and each table.

  The management table 35 stores an update log of each master table. For example, an update log including the copy start time and copy end time of each table is stored. Furthermore, HWM (High Water Mark) of each log table is stored. The HWM is one of the indexes managed by the database system, and indicates the maximum value of the data amount stored in each log table, and is data indicating the range in which the data of each table exists. For example, it is assumed that the data storage area of each table is secured in advance (start address and end address), and the order in which data is stored in the secured area is determined in advance by the address. At this time, the HWM indicates the maximum address when the largest amount of data has been stored in the past. As a result, it is guaranteed that data exists between the head address and the address indicated by the HWM. Therefore, when searching for data stored in each table, the business application may search the range.

  The management table 35 also stores the refresh start time and end time of each refresh group.

  The refresh history table 36 stores dummy replica data stored in the dummy replica table 33.

  The monitoring unit 38 monitors copy processing delay. For example, the monitoring unit 38 may measure the time required for the copy process from the update log (copy start time and copy end time) stored in the management table 35. For example, as shown in FIG. 3, the monitoring unit 38 operates normally with the copy process (refresh process) by the copy processing unit 37 because the latest copy start time is later than the previous copy start time. Make sure. In the example of FIG. 3, since the start time of the refresh process C is 10:39 and the start time of the refresh process B is 10:35, it can be seen that the refresh process is operating normally.

  Further, if the refresh process is in progress, the monitoring unit 38 determines whether the copy process is delayed depending on whether the latest copy start time or the elapsed time from the previous copy start time has exceeded a predetermined time. Determine whether. In the example of FIG. 3, in the refresh monitoring N + 1th time (10:39), the refresh process is delayed depending on the latest copy start time (10:38) or the elapsed time from the previous copy start time (10:34). It is determined whether or not.

  In addition, the monitoring unit 38 performs delay monitoring of copy processing using dummy data. For example, the monitoring unit 38 periodically writes dummy data in the dummy master table 13. When the monitoring unit 38 writes dummy data to the dummy master table 13, the log is simultaneously written to the dummy log table 14. The monitoring unit 38 monitors the time required for the differential copy by the copy processing unit 37 based on the dummy replica data stored in the dummy replica table 33. That is, the time required for copying is measured for each refresh group from the difference between the copy time 332 and the update time 331 in the same record of the dummy replica data.

  Here, the cycle in which the monitoring unit 38 writes the dummy data to the dummy master table 13 may be the same cycle as the cycle in which the copy processing unit 37 performs the copy, or may be a different cycle.

  Incidentally, when the copy processing of the copy processing unit 37 is delayed, automatic recovery may be possible depending on the cause. As a delay that can be automatically recovered, for example, the above-described HWM may be excessively increased. That is, if the HWM is excessively high, it takes time to search for data in the log tables 12a and 12b, so that the copy process is delayed. Therefore, the monitoring unit 38 analyzes the cause when the delay of the copy process is detected, and performs automatic recovery by the following process when the cause is the HWM.

  For example, when the copy process is delayed, the monitoring unit 38 refers to the management table 35 and checks the HWM of the master tables 11a and 11b targeted by the delayed copy process. Here, when the HWM is higher than a predetermined threshold, the HWM is lowered by the SHLINK process provided by the database system so that the data search range of the log tables 12a and 12b is narrowed.

  Next, the processing procedure of the database system having the above-described configuration will be described with reference to the flowchart of FIG.

  First, the monitoring unit 38 checks the time required for the refresh process (S10). For example, the monitoring unit 38 may calculate the time required for the refresh process from the dummy replica data with reference to the dummy replica table 33, or the refresh process from the log of the copy process with reference to the management table 35. The required time may be calculated. Or the monitoring part 38 may use together calculation of the required time by said two types of methods.

  Then, the monitoring unit 38 determines whether or not the refresh process is delayed, that is, whether or not the time required for the refresh process calculated in the above process is equal to or longer than a predetermined time (threshold) (S12). In this determination, the required time calculated from the dummy data and the required time calculated from the copy processing log may be delayed by the same threshold or may be determined by different thresholds. When the delay determination by the two methods described above is used in combination, the delay may be determined when the time required for one or both exceeds the threshold value. Furthermore, the determination in step S12 may be performed for each refresh group or for each table.

  When the refresh process is not delayed (S12: No), after waiting for a predetermined time, the process returns to step S10 (S14).

  On the other hand, when the refresh process is delayed (S12: Yes), the monitoring unit 38 refers to the management table 35 and checks the HWM of the log tables 12a and 12b of the master site 1 (S20). Here, if there is an HWM rising above a predetermined value (S22: Yes), the SHURLINK process is executed and the corresponding log table is maintained (S24). On the other hand, when there is no HWM equal to or greater than the predetermined value (S22: No), it is considered that the delay is caused by other causes, so the monitoring unit 38 issues a predetermined delay alert and notifies the administrator of the delay of the refresh process. (S26).

  According to the above-described embodiment, it is possible to detect the delay of the refresh process in a timely manner.

  Furthermore, when the HWM of the log tables 12a and 12b rises and the refresh process is delayed because the data search range when performing the copy process is wide, in the above-described embodiment, the above process performs the HWM. As a result, the delay of the refresh process can be eliminated automatically.

  The above-described embodiments of the present invention are examples for explaining the present invention, and are not intended to limit the scope of the present invention only to those embodiments. Those skilled in the art can implement the present invention in various other modes without departing from the gist of the present invention.

  For example, in the above-described embodiment, the dummy data is stored in a dummy table different from the master table, and the dummy replica table is stored in a dummy replica table different from the replica table. However, the dummy data and the dummy replica data may be stored in the master table and the replica table, respectively, without providing the dummy table and the dummy replica table.

It is a block diagram of the database system which concerns on one Embodiment of this invention. An example of a dummy master table and a dummy replica table is shown. It is explanatory drawing of the delay monitoring of a refresh process. It is a flowchart which shows the process sequence of the database system which concerns on this embodiment.

Explanation of symbols

DESCRIPTION OF SYMBOLS 1 Master site 3 Replica site 10 Master database 11a, 11b Master table 12a, 12b Log table 13 Dummy master table 14 Dummy log table 30 Replica database 31a, 31b Replica table 33 Dummy replica table 35 Management table 37 Copy processing part 38 Monitoring part

Claims (4)

A database system that asynchronously copies master data to generate replica data,
A master site having a master data storage area and a first data storage area;
A replica site having a replica data storage area and a second data storage area ;
The replica site is
Dummy data writing means for periodically writing dummy data including the first time, which is the current time, into the first data storage area;
The master data stored in the master data storage area is periodically differential copied to the replica data storage area, and the dummy data stored in the first data storage area is periodically copied to the second data storage area. And a copy means for writing the second time, which is the time at that time, to the second data storage area ;
Said first time included in the dummy data copied to the second data storage area, and a second data storage written to area write Mareta the second time, the copy means the master data and the First monitoring means for monitoring time required for differential copy of dummy data;
If the time required for the differential copy is equal to or longer than the first delay time as a result of monitoring by the first monitoring means, the recovery is performed so that the differential copy is completed within the first delay time. And a database system. The master site further includes a master log storage area for storing an update history of the master data storage area, and means for storing range data indicating a range in which data in the master log storage area exists,
The copy means is for identifying difference master data with reference to the range of the master log storage area indicated by the range data, and copying the difference master data to the replica data storage area,
The database system according to claim 1, wherein the restoration unit changes the range data so that a range indicated by the range data is narrowed. The replica site is
Means for storing a start time of the grouped differential copy when the copy means performs a grouped differential copy in which a plurality of tables are grouped to perform differential copy;
Among the grouped differential copies that have already been executed, the elapsed time from the start time of the last executed grouped differential copy to the current time, or the current time from the start time of the currently executing grouped differential copy A second monitoring means for monitoring the elapsed time until
The recovery means, on the basis of the second monitoring by the monitoring means results database system according to claim 1 or 2, wherein the determining whether to perform the automatic restoration processing. In a database system comprising a master site having a master data storage area, a first data storage area, a replica data storage area, and a replica site having a second data storage area,
The computer at the replica site is
Periodically writing dummy data including a first time, which is a current time, in the first data storage area;
The master data stored in the master data storage area is periodically copied to the replica data storage area, and the dummy data stored in the first data storage area is periodically copied to the second data storage area. And writing a second time, which is the time at that time, into the second data storage area ;
Monitoring the time required for the differential copy from the first time included in the dummy data copied to the second data storage area and the second time written to the second data storage area When,
As a result of the monitoring, if the time required for the differential copy is equal to or longer than a first delay time, a database system having a step of performing automatic recovery processing so that the differential copy is completed within the first delay time Method for automatic delayed recovery of differential copy.

Images