A synchronization method, device and system for source-side master-slave database environment

Abstract

The present invention relates to a synchronization method, device and system for a source-side master-standby database environment. The method portion mainly includes: deploying a source-side data synchronization service in a standby database in a source-side master-standby database environment, and deploying a target-side data synchronization service in a target-side database; obtaining the SCN of the current log of the master database in the source-side master-standby database environment as a first SCN, waiting for the active transactions related to the table to be synchronized to be committed, and then obtaining the SCN of the current log of the master database in the source-side master-standby database environment as a second SCN; obtaining the SCN of the current log of the standby database in the source-side master-standby database environment as a third SCN, and the source-side data synchronization service performs a flashback query on the table to be synchronized through the third SCN to achieve initialization loading; after the initialization loading is completed, the target-side data synchronization service performs incremental synchronization for the node according to the third SCN. The present invention can effectively reduce the impact of the initialization step on the performance of the source-side master-standby database system.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a synchronization method, device and system for a source-side master-slave database environment. Background Art

[0002] In a database real-time synchronization system (supporting heterogeneous architecture) based on a log parsing architecture, the source-side data synchronization service is responsible for capturing the operation logs of the source database, parsing the logs to restore the corresponding database operations, and then sending them to the target-side data synchronization service. The target-side data synchronization service is responsible for executing these operations in the target database, thereby completing the data synchronization process.

[0003] When using a data synchronization tool for real-time database data synchronization, you first need to initialize the target database to establish a baseline for data synchronization. After completing the data initialization, you can perform real-time incremental data synchronization on this baseline data. In an environment where the source database is a primary / standby database, a data synchronization service is currently often set up on the primary database, extracting data from the primary database to initialize the target database, and then parsing the primary database's logs for incremental data synchronization. However, this setup method increases the burden on the primary database, thereby reducing the external responsiveness of the entire primary / standby database system on the source side. Therefore, in a primary / standby environment, initializing data and capturing incremental logs to the target side during data synchronization has a significant impact on the performance of the source primary / standby database system.

[0004] In view of this, how to overcome the defects of existing technologies and how to reduce the impact of initializing data and capturing incremental logs to the target side during data synchronization on the performance of the source side master and standby database system have become important technical issues that need to be urgently addressed in the industry. Summary of the Invention

[0005] In response to the defects or improvement needs in the existing technology: How to reduce the impact of initializing data and capturing incremental logs to the target side during data synchronization on the performance of the source-side master and standby database system. The present invention provides a synchronization method, device, and system for the source-side master and standby database environment. When initializing data on the target side, only the system checkpoint (System Change Number, abbreviated as: SCN) of the initialization data is extracted from the primary database of the source-side master and standby database, and the query data is executed on the standby machine, thereby effectively reducing the impact of the initialization step on the performance of the source-side master and standby database system.

[0006] The present invention adopts the following technical solutions:

[0007] In a first aspect, the present invention provides a synchronization method for a source-side master-slave database environment, comprising:

[0008] Deploy the source-side data synchronization service on the standby database in the source-side primary-standby database environment, and deploy the target-side data synchronization service on the target-side database.

[0009] Get the SCN of the current log of the primary database in the source active / standby database environment as the first SCN. After the active transactions related to the table to be synchronized are committed, get the SCN of the current log of the primary database in the source active / standby database environment as the second SCN.

[0010] The SCN of the current log of the standby database in the source-side primary / standby database environment is obtained as the third SCN. When the third SCN is greater than or equal to the second SCN, the source-side data synchronization service performs a flashback query on the table to be synchronized using the third SCN and sends the obtained data to the target-side data synchronization service for storage, thereby implementing initial loading.

[0011] After the initialization loading is completed, the source-side data synchronization service sends the captured operations to the target-side data synchronization service, and the target-side data synchronization service performs incremental synchronization for the node according to the third SCN.

[0012] In some embodiments, after waiting for the active transactions related to the table to be synchronized to be committed, obtaining the SCN of the current log of the primary database in the source primary-standby database environment as the second SCN specifically includes:

[0013] Query the table to be synchronized on the source database. If there is an active transaction related to the table to be synchronized among the current active transactions, and the start SCN of the related active transaction is less than the first SCN, wait until the related active transaction is committed, and then obtain the SCN of the current log of the primary database in the source primary and standby database environment as the second SCN.

[0014] In some embodiments, when the third SCN is greater than or equal to the second SCN, the source-side data synchronization service performs a flashback query on the table to be synchronized using the third SCN, specifically including:

[0015] Comparing the third SCN with the second SCN, and when the third SCN is greater than or equal to the second SCN, sending the third SCN to the target-side data synchronization service, so that the target-side data synchronization service associates the third SCN with the table to be synchronized;

[0016] The source-side data synchronization service uses the third SCN to perform a flashback query on the table to be synchronized on the standby database in the source-side primary and standby database environment.

[0017] In some embodiments, when comparing the third SCN and the second SCN, if the third SCN is smaller than the second SCN, the SCN of the current log of the standby database in the source master-standby database environment is repeatedly obtained as the third SCN until the third SCN is greater than or equal to the second SCN.

[0018] In some embodiments, the source-side data synchronization service sends the captured operation to the target-side data synchronization service, and the target-side data synchronization service performs incremental synchronization for the node according to the third SCN, specifically including:

[0019] The source-side data synchronization service sends the operations of the table to be synchronized that are captured in the log stream and are after the first SCN to the target-side data synchronization service for synchronization;

[0020] After receiving the transaction commit operation, the target-side data synchronization service determines whether the SCN in the transaction commit operation is greater than the third SCN; if so, it synchronizes the operations on the table to be synchronized in the current transaction.

[0021] In some embodiments, if the SCN in the transaction commit operation is not greater than the third SCN, operations on the to-be-synchronized table in the current transaction are discarded.

[0022] In some embodiments, the source-side master-standby database environment includes an Oracle DataGuard master-standby database environment.

[0023] In a second aspect, the present invention further provides a synchronization device for a source-side master-slave database environment, the device comprising:

[0024] At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the processor to execute the synchronization method for the source-side master-slave database environment described in the first aspect.

[0025] In a third aspect, the present invention further provides a synchronization system for a source-side master-slave database environment, applying the synchronization method for a source-side master-slave database environment as described in the first aspect. The system includes a synchronization service deployment module, an SCN acquisition module, an initialization loading module, and an incremental synchronization module, wherein:

[0026] The synchronization service deployment module is used to deploy the source-side data synchronization service on the standby database in the source-side primary-standby database environment, and to deploy the target-side data synchronization service on the target-side database;

[0027] The SCN acquisition module is used to obtain the SCN of the current log of the primary database in the source-side primary-standby database environment as the first SCN, and after waiting for the active transaction related to the table to be synchronized to be committed, obtain the SCN of the current log of the primary database in the source-side primary-standby database environment as the second SCN;

[0028] The initialization loading module is used to obtain the SCN of the current log of the standby database in the source-side primary and standby database environment as the third SCN. When the third SCN is greater than or equal to the second SCN, the source-side data synchronization service is called to perform a flashback query on the table to be synchronized using the third SCN, and the obtained data is sent to the target-side data synchronization service for storage, thereby implementing initialization loading;

[0029] The incremental synchronization module is used to call the source-side data synchronization service to send the captured operation to the target-side data synchronization service after the initialization loading is completed, so that the target-side data synchronization service performs incremental synchronization for the node according to the third SCN.

[0030] In a fourth aspect, the present invention further provides a non-volatile computer storage medium, which stores computer-executable instructions, which are executed by one or more processors to complete the synchronization method for the source-side master-slave database environment described in the first aspect.

[0031] Compared with the prior art, the present invention provides a synchronization method, device, and system for a source-side master-slave database environment, which has the following beneficial effects:

[0032] First, deploy a data synchronization service on the source-side standby database to capture incremental logs to reduce the performance impact of data synchronization on the source-side database master and standby systems.

[0033] Secondly, since there is no active transaction information in the standby database of the source database master-slave, when confirming the log parsing position during data initialization on the target database, it is necessary to determine the log parsing start SCN (first SCN) of the table on the source master database. After confirming that all active transactions related to the table have been committed, the second SCN is obtained again on the master database to determine the minimum position for synchronization of the standby database.

[0034] Finally, the third SCN is obtained from the standby database in the source database master-slave system. Only when the third SCN is greater than or equal to the second SCN does it indicate that the data on the current standby database has been synchronized to the point after the relevant active transactions of the primary database are committed. Only then can it be ensured that the data for flashback query on the standby database meets the initialization requirements. The third SCN is sent to the target end and associated with the table. Then, the third SCN is used on the source standby database for flashback query, and the data is initialized to the target database to complete the initialization loading of the synchronized data.

[0035] The present invention captures logs on a standby database in a source database master / standby system for incremental synchronization and performs flashback query on the standby database to initialize data in a target database, thereby reducing the impact of data synchronization on the performance of the source database master / standby system. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] To more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments of the present invention. Obviously, the drawings described below are only some embodiments of the present invention. Those skilled in the art can also derive other drawings based on these drawings without inventive effort.

[0037] Figure 1 A flowchart of a synchronization method for a source-side master-slave database environment provided in Example 1 of the present invention;

[0038] Figure 2 Flowchart of SCN acquisition provided in Example 1 of the present invention;

[0039] Figure 3 Flowchart of initialization loading provided by Example 1 of the present invention;

[0040] Figure 4 This is a flowchart of incremental synchronization provided by Example 1 of the present invention;

[0041] Figure 5 A schematic diagram of a module of a synchronization system for a source-side master-slave database environment provided in Example 1 of the present invention;

[0042] Figure 6 A schematic diagram of the structure of a synchronization device for a source-side master-slave database environment provided in Example 3 of the present invention. DETAILED DESCRIPTION

[0043] The present invention is described in detail below with reference to specific embodiments. The following examples will help those skilled in the art to further understand the present invention, but do not limit the present invention in any form. It should be noted that, for those of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present invention. These all fall within the scope of protection of the present invention. It should be noted that, if there is no conflict, the various features in the embodiments of the present invention can be combined with each other and are all within the scope of protection of this application. In addition, although the functional modules may be divided in the device schematic diagram and the logical order may be shown in the flow chart, in some cases, the steps shown or described may be performed in a different order than the module division in the device or the order in the flow chart.

[0044] Unless otherwise defined, all technical and scientific terms used in this specification have the same meanings as those commonly understood by those skilled in the art to which this invention belongs. The terms used in this specification and in the description of the present invention are for the purpose of describing specific embodiments only and are not intended to limit the present invention. Furthermore, the technical features involved in the various embodiments of the present invention described below may be combined with each other as long as they do not conflict with each other.

[0045] Unless the context requires otherwise, throughout the specification and claims, the term "including" is to be interpreted as meaning open inclusion, that is, "including, but not limited to". In the description of the specification, the terms "one embodiment", "some embodiments", "exemplary embodiments", "example", "specific example" or "some examples" and the like are intended to indicate that the specific features, structures, materials or characteristics associated with the embodiment or example are included in at least one embodiment or example of the present disclosure. The schematic representation of the above terms does not necessarily refer to the same embodiment or example. In addition, the specific features, structures, materials or characteristics may be included in any one or more embodiments or examples in any appropriate manner, that is, although they may be carried in the embodiments or examples of the above terms due to reasons such as the order and position of appearance, it is not limited to that they can be carried in combination by one embodiment or example.

[0046] In order to make the purpose, technical solutions and advantages of this application more clearly understood, this application is further described in detail below with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are only intended to illustrate this application and are not intended to limit this application. The present invention will be described in detail below with reference to the accompanying drawings and examples.

[0047] Example 1:

[0048] like Figure 1 As shown, an embodiment of the present invention provides a synchronization method for a source-side master-slave database environment, which includes the following steps.

[0049] Step 101: Deploy the source-side data synchronization service on the standby database in the source-side primary-standby database environment, and deploy the target-side data synchronization service on the target-side database.

[0050] Step 102: Obtain the SCN of the current log of the primary database in the source-side primary / standby database environment as the first SCN. After waiting for the active transactions related to the table to be synchronized to be committed, obtain the SCN of the current log of the primary database in the source-side primary / standby database environment as the second SCN. Specifically, query the table to be synchronized in the source-side database. If there is an active transaction related to the table to be synchronized among the current active transactions, and the start SCN of the related active transaction is less than the first SCN, wait for the related active transaction to be committed, and then obtain the SCN of the current log of the primary database in the source-side primary / standby database environment as the second SCN.

[0051] Step 103: Obtain the SCN of the current log of the standby database in the source-side master-standby database environment as the third SCN. When the third SCN is greater than or equal to the second SCN, the source-side data synchronization service uses the third SCN to perform a flashback query on the table to be synchronized, and sends the acquired data to the target-side data synchronization service for storage to achieve initialization loading. Specifically, compare the third SCN with the second SCN. When the third SCN is greater than or equal to the second SCN, send the third SCN to the target-side data synchronization service so that the target-side data synchronization service associates the third SCN with the table to be synchronized; the source-side data synchronization service uses the third SCN to perform a flashback query on the table to be synchronized on the standby database in the source-side master-standby database environment. If the third SCN is less than the second SCN, repeatedly obtain the SCN of the current log of the standby database in the source-side master-standby database environment as the third SCN until the third SCN is greater than or equal to the second SCN.

[0052] Step 104: After the initialization load is completed, the source-side data synchronization service sends the captured operations to the target-side data synchronization service, and the target-side data synchronization service performs incremental synchronization for the node based on the third SCN. Specifically, the source-side data synchronization service sends the operations of the table to be synchronized that are captured in the log stream and are after the first SCN to the target-side data synchronization service for synchronization; after receiving the transaction commit operation, the target-side data synchronization service determines whether the SCN in the transaction commit operation is greater than the third SCN; if so, synchronizes the operations on the table to be synchronized in the current transaction. If the SCN in the transaction commit operation is not greater than the third SCN, the operations on the table to be synchronized in the current transaction are discarded.

[0053] Through the above steps, when initializing data on the target side, this embodiment only extracts the SCN of the initialization data from the primary database of the source side database, and executes the query data on the backup machine, thereby effectively reducing the impact of the initialization step on the system performance of the source side primary and backup databases.

[0054] It should be noted that the source-side master-slave database of the embodiment of the present invention is described using the Oracle database as an example. Oracle DataGuard (a high-availability database solution launched by Oracle) is Oracle's own data synchronization function. The basic principle is to transfer log files from the primary database to the standby database, and then apply these log files on the standby database, so that the standby database is synchronized with the primary database. It is a database-level high-availability solution. The embodiment of the present invention is aimed at the master-slave database environment (such as the Oracle DataGuard master-slave database environment) as the source, and uses log parsing technology to synchronize the incremental data of the source-side database to the data synchronization environment of the isomorphic or heterogeneous target-side database. The data synchronization service is deployed on the standby database of the source-side master-slave database to capture the incremental log operation and perform data initialization operations on the target database to obtain the basic point of data synchronization. When initializing data on the target side, only the SCN of the initialization data is extracted from the master database of the source-side master-slave database, and the query data is executed on the standby machine, thereby effectively reducing the impact of the initialization step on the system performance of the source-side master-slave database.

[0055] Specifically, in one embodiment, a synchronization system is deployed on the standby database and the target database in a source-side primary / standby database environment. The source-side database synchronization system reads logs from the source-side standby database, while the target-side database synchronization system stores synchronization operations sent from the source as transactions. It should be noted that deploying a synchronization system on the standby database in a source-side primary / standby database environment captures log changes from the standby database to synchronize data, helping to reduce the load on the primary / standby database systems and mitigate the impact of the data synchronization system on the service performance of the source-side primary / standby database systems.

[0056] refer to Figure 2 As shown, in one embodiment, obtaining the SCN specifically includes the following steps.

[0057] Step 201: Obtain the SCN of the current log of the primary database in the source-side primary-standby database environment as the first SCN, query the system table of the source database, and if there is an active transaction related to the table to be synchronized among the current active transactions, and the start SCN of the related active transaction is less than the first SCN, wait for the related active transaction to be committed. It should be noted that since the first SCN is the starting point for parsing the synchronization log of the incremental data of the current table to be synchronized, operations on the table before the first SCN will not be captured and parsed, and uncommitted data based on the first SCN will not be visible during flashback. Therefore, after obtaining the first SCN, it is necessary to wait for the completion of the execution of the active transaction involving the table and whose transaction start SCN is less than the first SCN before the flashback query can be performed.

[0058] Step 202: Obtain the SCN of the current log of the primary database in the source-side primary-standby database environment as a second SCN, and save the second SCN to determine the minimum position of the standby database synchronization in the source-side primary-standby database.

[0059] Step 203: Obtain the SCN of the current log of the standby database in the source-side primary-standby database environment as the third SCN. Compare the third SCN with the second SCN. If the third SCN is less than the second SCN, a flashback query cannot be performed on the standby server until the third SCN is greater than or equal to the second SCN. Repeat obtaining the SCN of the current log of the standby database as the third SCN until the third SCN is greater than or equal to the second SCN. It should be noted that in the source-side primary-standby database environment, the standby database and the primary database may be asynchronous. In this case, the standby database's SCN may be less than the primary database's SCN. Flashback queries cannot be performed on the standby server until the standby database's SCN log is replayed to the second SCN.

[0060] refer to Figure 3 As shown, in one embodiment, initialization loading specifically includes the following steps.

[0061] Step 301: The third SCN obtained from the standby database is sent to the target-side data synchronization service. The target-side data synchronization service associates the third SCN with the table to be synchronized. It should be noted that after the data in the table to be synchronized is initialized, when the table is incrementally synchronized, data in the synchronization operation log with a commit SCN less than or equal to the second SCN is directly discarded. This is because these operations are visible during the flashback and have already been initialized to the target.

[0062] Step 302: Use the third SCN to perform a flashback query on the table to be loaded on the standby database in the source primary / standby database environment. The obtained data is then sent to the target data synchronization service for storage, completing the initial load. It should be noted that using the standby database's latest third SCN as the flashback SCN is more advantageous than using the second SCN obtained on the primary database. This is because the third SCN is often larger than the second SCN. Using a larger third SCN for flashback reduces the probability of receiving a "snapshot too old" error during a flashback query.

[0063] refer to Figure 4 As shown, in one embodiment, incremental synchronization specifically includes the following steps.

[0064] Step 401: After the initialization load is completed, log analysis on the source side is enabled for data synchronization, and operations captured after the first SCN in the log stream for the table are sent to the target side data synchronization service for synchronization.

[0065] Step 402: After receiving the transaction commit operation, the target-side data synchronization service needs to determine whether the SCN in the transaction commit operation is greater than the flashback SCN of the table, that is, the third SCN. If so, the operation of the table is synchronized; otherwise, the operation of the table in the current transaction is discarded. This implements the transition from loading to synchronization, ensuring that the initialization of data synchronization is completed without affecting the source-side database application. It should be noted that the principle of this step is that the flashback query can obtain all the data that has been committed before the flashback SCN of the table, while uncommitted transactions are not visible to the flashback query. Therefore, all transactions in the log stream whose commit SCN is greater than the flashback SCN need to be synchronized.

[0066] The above solution is mainly for the case where the source side is a database master-slave system. Incremental log parsing and initialization data are obtained on the slave database, thereby reducing the impact of data synchronization on the performance of the source side database master-slave system.

[0067] Based on the above method, this embodiment also provides a synchronization system for the source side master and standby database environment. Figure 5 As shown, the system includes a synchronization service deployment module, an SCN acquisition module, an initialization loading module and an incremental synchronization module, wherein: the synchronization service deployment module is used to deploy the source-side data synchronization service in the standby database in the source-side master-standby database environment, and deploy the target-side data synchronization service in the target-side database; the SCN acquisition module is used to obtain the SCN of the current log of the master database in the source-side master-standby database environment as the first SCN, and after waiting for the active transaction related to the table to be synchronized to be committed, obtain the SCN of the current log of the master database in the source-side master-standby database environment as the second SCN; the initialization loading module is used to obtain the SCN of the current log of the standby database in the source-side master-standby database environment as the third SCN, when the third SCN is greater than or equal to the second SCN, call the source-side data synchronization service to perform a flashback query on the table to be synchronized through the third SCN, and send the acquired data to the target-side data synchronization service for warehousing to achieve initialization loading; the incremental synchronization module is used to call the source-side data synchronization service after the initialization loading is completed to send the captured operation to the target-side data synchronization service, so that the target-side data synchronization service performs incremental synchronization for the node according to the third SCN. The specific implementation process of each module can refer to the above method steps, which will not be repeated here.

[0068] In summary, this embodiment provides a synchronization method and system for a source-side master / slave database environment, which has the following advantages:

[0069] First, Oracle DataGuard is Oracle's native data synchronization feature. Its basic principle is to transfer log files from the primary database to the standby database and then apply these log files on the standby database. Therefore, a data synchronization service can be deployed on the source standby database to capture incremental logs, thereby reducing the performance impact of data synchronization on the source database's primary and standby systems.

[0070] Secondly, since there is no active transaction information in the standby database of the source database master-slave, when confirming the log parsing position during data initialization on the target database, it is necessary to determine the log parsing start SCN (first SCN) of the table on the source master database. After confirming that all active transactions related to the table have been committed, the second SCN is obtained again on the master database to determine the minimum position for synchronization of the standby database.

[0071] Finally, the third SCN is obtained from the standby database in the source database master-slave system. Only when the third SCN is greater than or equal to the second SCN does it indicate that the data on the current standby database has been synchronized to the point after the relevant active transactions of the primary database are committed. Only then can it be ensured that the data for flashback query on the standby database meets the initialization requirements. The third SCN is sent to the target end and associated with the table. Then, the third SCN is used on the source standby database for flashback query, and the data is initialized to the target database to complete the initialization loading of the synchronized data.

[0072] The embodiment of the present invention captures logs on a standby database in a source database master / slave system for incremental synchronization and performs flashback query on the standby database to initialize data in a target database, thereby reducing the impact of data synchronization on the performance of the source database master / slave system.

[0073] Example 2:

[0074] Based on the synchronization method for the source-side master-slave database environment provided in the above-mentioned embodiment 1, embodiment 2 of the present invention provides a specific example for illustration.

[0075] For example, table T exists in the source database. The table creation statement is as follows: CREATE TABLE T(ID INT).

[0076] 1. Use Oracle DataGuard as the source, a master-slave database environment, and a heterogeneous database as the target. Deploy synchronization service systems on the standby database and the target database in the source master-slave database environment. The source database synchronization service system reads logs from the source standby database, while the target database synchronization service system is responsible for storing synchronization operations sent from the source as transactions.

[0077] 2. Execute the insert statement on the source primary database without committing it:

[0078] INSERT INTO T(ID)VALUES(1);

[0079] Assume that the SCN of the source primary database is 100 at this time.

[0080] 3. Continue to execute the insert statement on the source primary database without committing it:

[0081] INSERT INTO T(ID)VALUES(2);

[0082] Assume that the SCN of the source primary database is 200 at this time;

[0083] The data synchronization service starts adding synchronization for table T. First, obtain the current first SCN 200 on the source master database using SQL:

[0084] SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL.

[0085] 4. By querying the dynamic views V$TRANSACTION and V$LOCKED_OBJECT of the source master database, determine whether there are any transactions in the current active transactions involving the table whose starting SCN is smaller than the first SCN (200). If so, wait until these transactions are committed before continuing.

[0086] It should be noted that the main purpose of the above steps is to ensure that the transaction logs involving the T table in the current database system can be read from this moment on. Because after log synchronization is turned on, the log file location where the current first SCN value is located will be set as the starting point for synchronous reading and analysis. If this is not done, a transaction involving the T table starts before the current SCN. In this case, its modification operation will not be analyzed, and the modification flashback function of the transaction will not be available, resulting in data loss.

[0087] In this example, the start SCN of the active transaction involving table T is 100, which is less than the first SCN (200), so we need to wait for the active transaction to commit. If we do not wait for the active transaction to commit, the INSERT INTO T(ID) VALUES(1) operation with SCN 100 will be lost.

[0088] Due to the characteristics of the source-side ORACLE primary and standby databases, the dynamic views V$TRANSACTION and V$LOCKED_OBJECT are not maintained on the standby database. Therefore, this step of waiting for active transactions to commit must be performed on the primary database.

[0089] 5. Execute the commit statement on the source database:

[0090] COMMIT;

[0091] Assume that the SCN of the source primary database is 300.

[0092] The data synchronization service obtains the second SCN 300 on the source primary database.

[0093] 6. Execute the insert statement on the source primary database and commit the statement:

[0094] INSERT INTO T(ID)VALUES(3);

[0095] Assume that the SCN of the source primary database is 400.

[0096] 7. Execute the insert statement on the source primary database and commit the statement:

[0097] INSERT INTO T(ID)VALUES(4);

[0098] Assume that the SCN of the source primary database is 500.

[0099] 8. The data synchronization service connects to the source standby database and obtains the current third SCN. Assume that the source database master and standby are in an asynchronous state, resulting in the latest SCN obtained on the standby database being 200.

[0100] At this point, if the standby database in the source database master-standby system uses the second SCN 300 obtained on the primary to perform a flashback query, executing the SQL statement: SELECT * FROM T AS OF SCN(300) , this SQL statement will not retrieve any data from the T table on the standby server because the standby database log has not yet been replayed to the SCN 300. After T table synchronization is enabled, transactions with a commit SCN less than the flashback SCN(300) will be discarded, resulting in the loss of the first and second rows of data in the T table because the commit SCNs of these two operations are less than or equal to 300.

[0101] 9. The data synchronization service needs to continuously obtain the latest SCN after the current standby machine is replayed on the source standby database as the third SCN.

[0102] If the third SCN currently obtained on the standby database is 400, which satisfies the condition of being greater than or equal to the second SCN (300), you can start flashback query on the standby machine.

[0103] Here, using the latest third SCN of the standby database as the flashback SCN is more advantageous than the second SCN obtained on the master, because the third SCN is often larger than the second SCN. Using a larger third SCN for flashback can reduce the probability of "snapshot too old" errors in flashback queries.

[0104] 10. Send the third SCN (400) to the target end data synchronization and associate it with the initialization data of the T table.

[0105] 11. The standby database of the source database master-standby system uses the third SCN 400 obtained above to perform a flashback query and send it to the target data synchronization service for storage.

[0106] Execute SQL: SELECT*FROM T AS OF SCN(400);

[0107] The above flashback query can find three rows of data with T table IDs 1, 2, and 3 and initialize the T table in the target database.

[0108] 12. The source-side data synchronization service captures incremental log operations of the T table starting from the first SCN 100 in the standby database log stream and sends them to the target side. The target side discards transaction operations with an SCN less than 400 and only synchronizes log operations with an SCN greater than 400, completing the connection between initialization data and incremental log synchronization.

[0109] In summary, in a master-standby database environment built using Oracle DataGuard, the master database not only needs to provide external services but also needs to synchronize data to the standby database, which places a significant load on the master database. In such a heterogeneous synchronization environment, if log capture and initialization and loading flashback queries are performed on the master database, the master database will experience a greater load, thus affecting the timeliness of services. In an embodiment of the present invention, log capture and initialization and loading flashback queries are performed on the standby database, which has a lower load. Only the flashback SCN is determined by obtaining SCN and active transaction information and then executed on the master database. This solution can effectively reduce the impact of data synchronization on the performance of the master database.

[0110] Example 3:

[0111] Based on the synchronization method for the source-side master-slave database environment provided in the above embodiment 1, the present invention also provides a synchronization device for the source-side master-slave database environment that can be used to implement the above method and system, such as Figure 6 , which is a schematic diagram of the device architecture of an embodiment of the present invention. The synchronization device for the source master-slave database environment of this embodiment includes one or more processors 21 and a memory 22. Figure 6 A processor 21 is taken as an example.

[0112] The processor 21 and the memory 22 may be connected via a bus or other means. Figure 6 The bus connection is taken as an example.

[0113] Memory 22, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs, and modules, such as the synchronization method for the source-side primary and standby database environment in Example 1. Processor 21 executes the non-volatile software programs, instructions, and modules stored in memory 22 to execute various functional applications and data processing of the synchronization device for the source-side primary and standby database environment, thereby implementing the synchronization method for the source-side primary and standby database environment in Example 1.

[0114] The memory 22 may include high-speed random access memory and non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state memory device. In some embodiments, the memory 22 may optionally include a memory remotely located relative to the processor 21, and such remote memory may be connected to the processor 21 via a network. Examples of such networks include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0115] The program instructions / modules are stored in the memory 22. When executed by one or more processors 21, the synchronization method for the source master-slave database environment in the above embodiment 1 is executed. For example, the above described Figure 1-Figure 4 The steps shown.

[0116] The above-mentioned product can execute the method provided in the embodiment of this application, and has the functional modules and beneficial effects corresponding to the execution method. For technical details not fully described in this embodiment, please refer to the method provided in the embodiment of this application.

[0117] It should be noted that the device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules may be selected based on actual needs to achieve the objectives of this embodiment.

[0118] Through the description of the above embodiments, it can be clearly understood by those skilled in the art that each embodiment can be implemented by means of software plus a general hardware platform, or of course by hardware. It can be understood by those skilled in the art that all or part of the processes in the above embodiment methods can be implemented by instructing the relevant hardware through a computer program, and the program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above methods. The storage medium can be a magnetic disk, an optical disk, a read-only memory (ROM) or a random access memory (RAM).

[0119] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them. Under the concept of the present invention, the technical features in the above embodiments or different embodiments can also be combined, the steps can be implemented in any order, and there are many other changes in different aspects of the present invention as described above. For the sake of simplicity, they are not provided in detail. Although the present invention has been described in detail with reference to the above embodiments, ordinary technicians in this field should understand that they can still modify the technical solutions described in the above embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A synchronization method for a source-side master-slave database environment, characterized in that: include: Deploy the source-side data synchronization service on the standby database in the source-side primary-standby database environment, and deploy the target-side data synchronization service on the target-side database. Get the SCN of the current log of the primary database in the source active / standby database environment as the first SCN. After the active transactions related to the table to be synchronized are committed, get the SCN of the current log of the primary database in the source active / standby database environment as the second SCN. The SCN of the current log of the standby database in the source-side primary / standby database environment is obtained as the third SCN. When the third SCN is greater than or equal to the second SCN, the source-side data synchronization service performs a flashback query on the table to be synchronized using the third SCN and sends the obtained data to the target-side data synchronization service for storage, thereby implementing initial loading. After the initialization loading is completed, the source-side data synchronization service sends the captured operations to the target-side data synchronization service, and the target-side data synchronization service performs incremental synchronization for the node according to the third SCN.

2. The synchronization method for the source-side master-slave database environment according to claim 1, characterized in that: After the active transactions related to the table to be synchronized are committed, obtaining the SCN of the current log of the primary database in the source-side primary-standby database environment as the second SCN specifically includes: Query the table to be synchronized on the source database. If there is an active transaction related to the table to be synchronized among the current active transactions, and the start SCN of the related active transaction is less than the first SCN, wait until the related active transaction is committed, and then obtain the SCN of the current log of the primary database in the source primary and standby database environment as the second SCN.

3. The synchronization method for the source-side master-slave database environment according to claim 1, characterized in that: When the third SCN is greater than or equal to the second SCN, the source-side data synchronization service performs a flashback query on the table to be synchronized using the third SCN, specifically including: Comparing the third SCN with the second SCN, and when the third SCN is greater than or equal to the second SCN, sending the third SCN to the target-side data synchronization service, so that the target-side data synchronization service associates the third SCN with the table to be synchronized; The source-side data synchronization service uses the third SCN to perform a flashback query on the table to be synchronized on the standby database in the source-side primary-standby database environment.

4. The synchronization method for the source-side master-slave database environment according to claim 3, characterized in that: When comparing the third SCN with the second SCN, if the third SCN is smaller than the second SCN, repeatedly obtaining the SCN of the current log of the standby database in the source-side primary-standby database environment as the third SCN until the third SCN is greater than or equal to the second SCN.

5. The synchronization method for the source-side master-slave database environment according to claim 1, characterized in that: The source-side data synchronization service sends the captured operation to the target-side data synchronization service, and the target-side data synchronization service performs incremental synchronization for the node according to the third SCN, specifically including: The source-side data synchronization service sends the operations of the table to be synchronized that are captured in the log stream and are after the first SCN to the target-side data synchronization service for synchronization; After receiving the transaction commit operation, the target-side data synchronization service determines whether the SCN in the transaction commit operation is greater than the third SCN; if so, it synchronizes the operations on the table to be synchronized in the current transaction.

6. The synchronization method for the source-side master-slave database environment according to claim 5, characterized in that: If the SCN in the transaction commit operation is not greater than the third SCN, the operation on the table to be synchronized in the current transaction is discarded.

7. The synchronization method for a source-side master-slave database environment according to any one of claims 1 to 6, characterized in that: The source-side master-slave database environment includes an Oracle DataGuard master-slave database environment.

8. A synchronization device for a source-side master-slave database environment, characterized in that: The device includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the processor to execute the synchronization method for the source-side master-slave database environment described in any one of claims 1-7.

9. A synchronization system for a source-side master-slave database environment, applying the synchronization method for a source-side master-slave database environment according to any one of claims 1 to 7, characterized in that: The system includes a synchronization service deployment module, an SCN acquisition module, an initialization loading module, and an incremental synchronization module, wherein: The synchronization service deployment module is used to deploy the source-side data synchronization service on the standby database in the source-side primary-standby database environment, and to deploy the target-side data synchronization service on the target-side database; The SCN acquisition module is used to obtain the SCN of the current log of the primary database in the source-side primary-standby database environment as the first SCN, and after waiting for the active transaction related to the table to be synchronized to be committed, obtain the SCN of the current log of the primary database in the source-side primary-standby database environment as the second SCN; The initialization loading module is used to obtain the SCN of the current log of the standby database in the source-side primary and standby database environment as the third SCN. When the third SCN is greater than or equal to the second SCN, the source-side data synchronization service is called to perform a flashback query on the table to be synchronized using the third SCN, and the obtained data is sent to the target-side data synchronization service for storage, thereby implementing initialization loading; The incremental synchronization module is used to call the source-side data synchronization service to send the captured operation to the target-side data synchronization service after the initialization loading is completed, so that the target-side data synchronization service performs incremental synchronization for the node according to the third SCN.

10. A non-volatile computer storage medium, characterized in that The computer storage medium stores computer-executable instructions, which are executed by one or more processors to complete the synchronization method for the source-side master-slave database environment according to any one of claims 1 to 7.

Images