Change data acquisition method and device based on Oracle disaster recovery environment backup library

Abstract

The invention relates to a change data acquisition method and device based on an Oracle disaster recovery environment backup library, and the method comprises the steps: obtaining a log file list according to a target plug-in in response to a data change operation, the target plug-in being used for capturing change data of a database and sending the change data to a Kafka theme, the log file list comprises online redo logs and archiving redo logs, and the log file list is determined based on a starting SCN and a terminating SCN; for a standby library of the second host, creating a first dictionary file according to a specified format, the specified format being a format for generating the dictionary file by a built-in package of Oracle; and mining the log file list and the first dictionary file based on a readable and writable mining database to obtain change data, and storing the change data to a Kafka theme of a third host, the mining database being located in the second host or the third host. According to the method and the device, the change data is acquired based on the readable and writable mining database, so that the performance overhead of the main library based on the Oracle disaster recovery environment can be reduced.

Description

Technical Field

[0001] The present disclosure relates to the technical field of databases, and in particular to a method and device for acquiring change data based on an Oracle disaster recovery environment backup database. Background Art

[0002] With the development of network technology, databases have been widely used in various industries, such as hospitals, governments, transportation, finance, and energy, and have gradually become an important database platform for enterprise information construction. Among them, data synchronization and consistency are of particular concern to people, so how to efficiently obtain database change data is a technical problem that needs to be solved urgently. Summary of the invention

[0003] In order to overcome the problems existing in the related art, the present disclosure provides a method for obtaining change data based on an Oracle disaster recovery environment backup database.

[0004] According to a first aspect of an embodiment of the present disclosure, a method for acquiring change data based on an Oracle disaster recovery environment backup database is provided, the method comprising:

[0005] In response to the data change operation, a log file list is obtained according to the target plug-in, the target plug-in is used to capture the change data of the database and send the change data to the Kafka topic, the log file list includes online redo logs and archived redo logs, the log file list is determined based on the start SCN and the end SCN, and the start SCN is greater than or less than the current SCN;

[0006] For the standby database of the second host, create a first dictionary file according to a specified format, where the specified format is a format for generating a dictionary file by a built-in package of Oracle;

[0007] The log file list and the first dictionary file are mined based on a readable and writable mining database to obtain change data, and the change data is stored in a Kafka topic of a third host, the mining database is located on the second host or on the third host, and the third host includes the target plug-in.

[0008] Optionally, the first dictionary file includes a first part of information, a second part of information, and a third part of information, and the step of creating the first dictionary file according to the specified format includes:

[0009] Acquire a first portion of information of the first dictionary file, where the first portion of information is fixed content;

[0010] Query the relevant views and system tables of the standby database to obtain the second part of information, where the second part of information is the creation information part of the first dictionary file;

[0011] Query the data dictionary table of the standby database, and calculate the additional flag bit to obtain the third part of information, where the third part of information is the object information part of the first dictionary file;

[0012] The first part of information, the second part of information and the third part of information are format converted and spliced ​​to obtain the first dictionary file.

[0013] Optionally, the calculating of the additional flag bit includes:

[0014] Obtaining a PROPERTY value in the standby database, and expressing the PROPERTY value as a polynomial;

[0015] Determining whether there is a first power term greater than or equal to a first value in the PROPERTY value according to the polynomial representation;

[0016] If a first power term greater than or equal to the first value exists in the PROPERTY value, a first additional flag bit is obtained according to the first power term, and other power terms less than the first value are retained.

[0017] Optionally, the method further comprises:

[0018] If there is a second power term greater than or equal to a second value and less than the first value in the PROPERTY value, a second additional flag bit is obtained based on the second power term, and other power terms less than the second value are retained.

[0019] Optionally, the method further comprises:

[0020] If all power terms in the PROPERTY value are smaller than the second value, the PROPERTY value is used as the third flag bit.

[0021] Optionally, the changed data includes data change information, and the data change information is information generated by the data change;

[0022] The storing the changed data in the Kafka topic of the third host comprises:

[0023] If table structure information corresponding to the data change information exists, a data change event is acquired based on the table structure information and the data change information, and the data change event is sent to the Kafka topic.

[0024] Optionally, the method further comprises:

[0025] If the table structure information corresponding to the data change information does not exist, a table creation statement is generated, and the table creation statement is parsed to obtain the change event type, and the table structure information is obtained based on the standby database;

[0026] A table structure change event is obtained according to the event type and the table structure information, and the data change event is obtained according to the table structure information and the data change information, and the table structure change event and the data change event are sent to the Kafka topic.

[0027] Optionally, generating a table creation statement includes:

[0028] The relevant information of the table is obtained according to the built-in view of the Oracle database, and the table creation statement is generated based on the relevant information.

[0029] Optionally, acquiring relevant information of a table according to a built-in view of an Oracle database, and generating the table creation statement based on the relevant information includes:

[0030] querying the standby database to obtain a first view, and determining whether the change table is a relational table based on the first view;

[0031] When it is determined that the change table is a relational table, query the second view to obtain column information of the table;

[0032] Query the third view to obtain the constraint information of the table;

[0033] The column information and the constraint information are integrated and spliced ​​to obtain the table creation statement.

[0034] Optionally, the mining of the log file list and the first dictionary file based on a readable and writable mining database to obtain the change data includes:

[0035] If it is determined that the data definition language DDL does not exist in the log file list, the log file list and the first dictionary file are mined based on a readable and writable mining database to obtain change data.

[0036] Optionally, the method further comprises:

[0037] If it is determined that the DDL exists in the log file list, a second dictionary file is constructed, and the log file list and the second dictionary file are mined based on a readable and writable mining database to obtain the change data.

[0038] Optionally, the log file list includes database redo log files;

[0039] The mining of the log file list and the first dictionary file based on the readable and writable mining database to obtain the change data includes:

[0040] The database redo log file and the first dictionary file are analyzed and mined based on the readable and writable mining database to obtain the change data.

[0041] According to a second aspect of an embodiment of the present disclosure, a change data acquisition device based on an Oracle disaster recovery environment backup database is provided, comprising:

[0042] A log acquisition module is configured to obtain a log file list according to a target plug-in in response to a data change operation, wherein the target plug-in is used to capture the change data of the database and send the change data to a Kafka topic, wherein the log file list includes an online redo log and an archived redo log, and the log file list is determined based on a start SCN and an end SCN, wherein the start SCN is greater than or less than the current SCN;

[0043] A dictionary creation module is configured to create a first dictionary file according to a specified format for the standby database of the second host, where the specified format is a format for generating a dictionary file by a built-in package of Oracle;

[0044] A mining module is configured to mine the log file list and the first dictionary file based on a readable and writable mining database to obtain change data, and store the change data in a Kafka topic of a third host, wherein the mining database is located on the second host or on the third host, and the third host includes the target plug-in.

[0045] According to a third aspect of an embodiment of the present disclosure, a non-temporary computer-readable storage medium is provided, on which a computer program is stored. When the program is executed by a processor, the steps of the change data acquisition method based on the Oracle disaster recovery environment backup database provided in the first aspect are implemented.

[0046] According to a fourth aspect of an embodiment of the present disclosure, an electronic device is provided, the electronic device comprising:

[0047] a memory having a computer program stored thereon;

[0048] A processor is used to execute the computer program in the memory to implement the step of acquiring the change data based on the Oracle disaster recovery environment backup database provided by the first aspect.

[0049] The present disclosure can not only avoid the constraint that the standby database cannot write data by introducing a mining readable database, but also reduce the load when the main database changes data. Specifically, in response to a data change operation, a log file list is obtained according to a target plug-in, and the target plug-in is used to capture the change data of the database and send the change data to a Kafka topic, wherein the log file list includes an online redo log and an archived redo log, and the log file list is determined based on a start SCN and an end SCN, and the start SCN is greater than or less than the current SCN. On this basis, for the standby database of the second host, a first dictionary file is created according to a specified format, and the specified format is a format for generating dictionary files by Oracle's built-in package. Then, the log file list and the first dictionary file are mined based on a readable and writable mining database to obtain change data, and the change data is stored in a Kafka topic of a third host. The mining database is located on the second host or on the third host, and the third host includes a target plug-in, so that the performance overhead of the Oracle main database can be reduced to a certain extent.

[0050] Other features and advantages of the present disclosure will be described in detail in the following detailed description. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] The accompanying drawings are used to provide a further understanding of the present disclosure and constitute a part of the specification. Together with the following specific embodiments, they are used to explain the present disclosure but do not constitute a limitation of the present disclosure. In the accompanying drawings:

[0052] Figure 1 This is an example diagram of data interaction when Oracle implements CDC in the related technology.

[0053] Figure 2 The present invention is a flowchart of a method for acquiring changed data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0054] Figure 3 The present invention is an example diagram showing a method for acquiring change data based on an Oracle disaster recovery environment backup database by introducing a mining database according to an exemplary embodiment.

[0055] Figure 4 The present invention is an example diagram showing a method for obtaining change data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0056] Figure 5 The present invention is a flowchart of another method for acquiring changed data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0057] Figure 6The figure is an example diagram of error information output when operating a standby database according to a method in a related technology shown in accordance with an exemplary embodiment.

[0058] Figure 7 The present invention is a detailed flow chart of obtaining change data in another change data obtaining method based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0059] Figure 8 The diagram is an example of a running log of a program in the snapshot phase in another method for acquiring change data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0060] Fig. 9 The diagram is an example of a running log of a program in a streaming event processing phase in another method for acquiring change data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0061] Fig.10 This is an example diagram of Kafka messages in another method for acquiring changed data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0062] Fig.11 The present invention is a block diagram showing a change data acquisition device based on an Oracle disaster recovery environment backup database according to an exemplary embodiment.

[0063] Fig.12 It is a block diagram of an electronic device according to an exemplary embodiment. DETAILED DESCRIPTION

[0064] The specific implementation of the present disclosure is described in detail below in conjunction with the accompanying drawings. It should be understood that the specific implementation described herein is only used to illustrate and explain the present disclosure, and is not used to limit the present disclosure.

[0065] It should be noted that all actions of acquiring signals, information or data in the present disclosure are carried out in compliance with the relevant data protection laws and policies of the country where the device is located and with the authorization given by the owner of the corresponding device.

[0066] In the description of the present disclosure, the terms used, such as "first", "second", etc., are used to distinguish similar objects and do not have to be understood as a specific order or sequence. In addition, in the description with reference to the accompanying drawings, the same symbols in different drawings represent the same elements unless otherwise stated.

[0067] Although operations or steps are described in a specific order in the drawings in the embodiments of the present disclosure, it should not be understood that it is required to perform these operations or steps in the specific order shown or in a serial order, or to perform all the operations or steps shown to obtain the desired results. In the embodiments of the present disclosure, these operations or steps can be performed in series; these operations or steps can also be performed in parallel; or some of these operations or steps can be performed.

[0068] With the explosive growth of enterprise data and the increasing complexity of business, traditional data processing methods can no longer meet people's requirements for data real-time and consistency, especially in a multi-system and multi-platform environment, where data synchronization and consistency are of particular concern. In related technologies, data replication usually relies on regular full or micro-batch incremental data synchronization, but this method is not only inefficient, but also brings great performance pressure to the source system.

[0069] Based on this, the Change Data Capture (CDC) technology came into being. Because it can accurately and real-time capture and transmit changing data, it has gradually become a hot technology in the field of data management and integration. In related technologies, CDC is mainly implemented based on the Debezium plug-in (Debezium data integration platform). However, the Debezium plug-in can only be applied to the main database / primary database in the Oracle disaster recovery environment, but not to the standby database in a read-only state. In addition, through testing, it was found that when the Debezium plug-in obtains change data from the Oracle main database, it will increase the pressure and performance overhead of the main database to a certain extent.

[0070] The process of implementing CDC in Oracle database can be as follows: Figure 1 As shown in the figure, in the process of obtaining change data, the Debezium plug-in can connect to the main database (master library) through JDBC (Java Database Connectivity). First, in the snapshot stage, the metadata of the main database can be read to generate events and store them in the Kafka topic to establish the baseline state of the database. Then, in the streaming event processing stage, Oracle LogMiner is used to build the data dictionary dic, load the online redo log and archived redo log files to obtain the change data, and save the parsing results of the data dictionary to the Kafka topic for consumption by downstream applications. Through testing, it is found that Figure 1 The change data acquisition process shown has a large storage and performance overhead on the main database, and also has a certain impact on other production tasks of the database.

[0071] In order to solve the above problems, the disclosed embodiment proposes a change data acquisition method based on the Oracle disaster recovery environment backup database, which can obtain a log file list according to the target plug-in, and the starting SCN (startSCN) of the log file list can be greater than or less than the current SCN (System Change Number), that is, the starting SCN can be specified, so that the change data mining range can be flexibly defined. At the same time, the disclosed embodiment of the backup database for the second host can create a first dictionary library based on a specified format, and on this basis, the log file list and the first dictionary file can be mined based on a readable and writable mining database to obtain change data, which can reduce the load of the main database.

[0072] Figure 2 FIG. 1 is a flow chart of a method for acquiring change data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment. Figure 2 As shown, the method may include the following steps.

[0073] In step S110 , in response to the data change operation, a log file list is obtained according to the target plug-in.

[0074] The disclosed embodiment is a method for acquiring change data of a backup database in an Oracle disaster recovery environment. The method may involve: Figure 3 As shown, a first host 301 , a second host 302 and a third host 303 , wherein the first host 301 may include a main library, which may be a main database, and the target plug-in on the third host 303 may read and write to the main library.

[0075] Exemplarily, the target plug-in may be a Debezium plug-in, which may be a plug-in optimized based on the open source Debezium plug-in, which can reduce the performance overhead of the Oracle master database to a certain extent. The following embodiment will take the Debezium plug-in as an example to illustrate how to obtain the change data.

[0076] In the embodiment of the present disclosure, the second host 302 may include a backup library, which may be a backup database, and the target plug-in may read data in the backup library; the third host 303 may include a mining database, which may be a readable and writable mining database.

[0077] In the disclosed embodiment, the standby database may be referred to as a physical standby database or an ADG (Active Data Guard) physical standby database, which may be an ADG standby database of a primary database on the first host 301 .

[0078] For example, Figure 3The online redo log is the online redo log, and the arch redo log is the archived redo log. The online redo log online and the archived redo log arch in the first host 301 can be updated to the second host 302 at a regular interval.

[0079] As can be seen from the above introduction, change data acquisition (CDC) can include two stages, the first stage is a snapshot stage, and the second stage can be a streaming event processing stage. When a data change is detected, that is, a data change operation is triggered, the embodiment of the present disclosure can execute CDC.

[0080] Specifically, the embodiment of the present disclosure can perform a snapshot operation, that is, enter the snapshot phase, at which time the Debezium plug-in can read the metadata of the standby database on the second host and store the generated events on the Kafka topic in the third host to establish the baseline state of the database. On this basis, enter the streaming event processing phase.

[0081] It should be noted that in the process of acquiring change data, the disclosed embodiment may enter the streaming event processing stage after implementing the snapshot stage, or may not perform the snapshot operation, that is, not enter the snapshot stage. In this case, it can be determined whether to enter the snapshot stage based on preset parameters. For example, if the preset parameter is no_data / schema_only, there is no need to create a snapshot, only generate a data dictionary, that is, directly enter the streaming event processing stage, which may include obtaining a log file list, building a dictionary, and obtaining change data.

[0082] As an optional method, when a data change operation is detected, the disclosed embodiment can obtain a list of log files according to the Debezium plug-in, wherein the list of log files may include online redo logs and archived redo logs. Here, the online redo logs and archived redo logs can be read via NFS (Network File System) / SSHFS (SSH Filesystem). Specifically, the disclosed embodiment can obtain a list of log files to be analyzed and processed by calling the DBMS_LOGMNR package of OracleLogMiner through the Debezium plug-in.

[0083] As an example, the Debezium plug-in in the third host can mount the online redo log and archived redo log of the standby database on the hostB host (the second host) to the hostC host (the third host) through NFS / SSHFS, and then obtain the change data of this log file through the LogMiner of the mining database on the hostC host (the third host). Here, the mining database can be located on the third host.

[0084] As another example, the Debezium plug-in in the third host can directly access the online redo log and archived redo log in the standby database on the hostB host (the second host) through a JDBC connection, and then obtain change data for the online redo log and the archived redo log through the LogMiner of the mining database on the hostC host (the third host). Here, the mining database can be located on the second host. In other words, when the mining database is located on hostB (the second host), because the mining database and the standby database are on the same host (the second host), the online redo log and the archived redo log can be accessed simultaneously, wherein the online redo log and the archived redo log can be collectively referred to as redo log files or database redo log files, that is, the disclosed embodiment does not need to perform network file mounting on the redo log files of the standby database.

[0085] In the disclosed embodiment, the Debezium plug-in can be connected to the main library of the first host, the standby library of the second host, and the mining database of the third host respectively. Therefore, before obtaining the list of log files, the disclosed embodiment can first create a first JDBC connection of the standby library and a second JDBC connection of the mining database, wherein the first JDBC connection can be a standbyConnection connection, which can be used to read the data of the standby library on the second host. The second JDBC connection can be an mnrConnection connection, which can be used to read and write data in the mining database.

[0086] That is, the Debezium plug-in can read the data in the standby database through the first JDBC connection, and can read and write the data in the mining database through the second JDBC connection. The disclosed embodiment can read the required data by connecting to the standby database, and write the data to be saved to the readable and writable mining database by connecting to the mining database, which can avoid the constraint that the standby database cannot write data. In addition, by connecting to the mining database to mine the changed data, the pressure and performance overhead of the main database can be avoided.

[0087] As an optional method, after establishing the JDBC connection between the Debezium plug-in and the standby database and the mining database, the embodiment of the present disclosure can enter the snapshot stage, which can generate events by connecting to the standby database to read metadata and then store them in the Kafka topic to establish the baseline state of the database. On this basis, it enters the streaming event processing stage, which is mainly used to obtain change data.

[0088] Specifically, the disclosed embodiment can first obtain a list of log files, that is, obtain online redo logs and archived redo logs. During this process, the Debezium plug-in can check the database and table status by connecting to the standby database. At this time, the parameter file can be read, and then based on the content in the parameter file, it is determined which tables and corresponding information need to be obtained this time. For example, there are 100 tables in the standby database, and 10 of them need to be checked and obtained this time. The information of these 10 tables can be written into the parameter file. After entering the streaming event processing stage, the database and table status can be checked based on the information of these 10 tables recorded in the parameter file. Here, the check is used to determine whether the table specified in the parameter file exists, and whether the format and status of the table are correct.

[0089] After the check is completed, the embodiment of the present disclosure may calculate the starting SCN, wherein the starting SCN may be a specified SCN, and the specified SCN may be greater than the current SCN or less than the current SCN. In other words, the starting SCN is greater than or less than the current SCN.

[0090] Related technologies mainly use the current SCN as the starting SCN for mining to mine change data, but the fixed calculation logic lacks flexibility. The disclosed embodiment optimizes the calculation method of the starting SCN to make the acquisition process of change data more flexible and controllable. Specifically, the disclosed embodiment can specify a certain SCN or timestamp as the starting point of mining according to actual needs, and then obtain the log file list based on the starting point.

[0091] The implementation process before and after the initial SCN optimization is as follows Figure 4 Based on Figure 4 It is known that the starting SCN in the embodiment of the present disclosure may be an extended specified SCN, and the extended specified SCN (AssignSCN|Timestamp, extended specified SCN) may be before the current SCN (CurrentSCN), or the extended specified SCN may be after the current SCN. Figure 4 Snapshot refers to the snapshot stage, and Streaming refers to the streaming event processing stage.

[0092] As an example, when performing a data change operation, if it is detected that the current starting SCN is in the snapshot stage, and the user's analysis requirement is not to analyze the snapshot stage, that is, there is no need to enter the snapshot stage, then the specified starting SCN can be located after the current SCN. It can be seen that the embodiment of the present disclosure has the ability to modify the starting SCN, that is, the starting node (starting SCN) of the changed data can be flexibly specified according to the needs.

[0093] In summary, in order to solve the problem that the starting SCN for mining change data by the Debezium plug-in cannot be specified, the embodiment of the present disclosure proposes a method of customizing the starting SCN, that is, changing the starting SCN by specifying the starting SCN to achieve a more flexible change data acquisition strategy.

[0094] Optionally, after obtaining the start SCN, the embodiment of the present disclosure can obtain the log file list based on the start SCN. It should be noted that while obtaining the log file list file, the embodiment of the present disclosure can calculate the end SCN (endSCN) based on the Debezium plug-in.

[0095] That is to say, the Debezium plug-in has the ability to calculate the termination SCN, through which the speed of controlling mining and the speed of generating archived redo logs can be determined, that is, the termination SCN is used to control the log file list within an acceptable time difference. Based on the termination SCN, the time gap with the archived redo log can be reduced, which can affect the step size of mining.

[0096] As an example, when it is determined that a large amount of change data is generated, the embodiment of the present disclosure may increase the step size (increase the end SCN) to ensure that more content is mined at one time; when it is determined that a small amount of change data is generated, the embodiment of the present disclosure may reduce the step size (reduce the end SCN) to ensure that less content is mined at one time. After obtaining the end SCN through calculation, the embodiment of the present disclosure may record it in the flush table to facilitate obtaining the SCN from the flush table as the next start SCN at the next startup. Here, the flush table may be stored in the mining database, or may be stored in a Kafka topic.

[0097] It should be noted that the first host (hostA), the second host (hostB) and the third host (hostC) in the embodiment of the present disclosure can be three independent hosts. The mining database can be an independent readable and writable database. In addition, the Debezium plug-in and the environment required for its operation can be deployed on the third host.

[0098] In addition, the implementation process of CDC for the standby database of the second host can also be applied to the primary database on the first host. For example, the Debezium plug-in can mount the online redo logs and archived redo logs of the primary database on the first host to the third host through NFS / SSHFS. Please refer to the above introduction for the specific process, which will not be repeated here.

[0099] In step S120, a first dictionary file is created according to a specified format for the standby database of the second host.

[0100] In some implementations, while obtaining the log file list, the disclosed embodiment may create a first dictionary file according to a specified format for the standby database of the second host. The specified format may be a format for generating a dictionary file by a built-in package of Oracle. Exemplarily, the specified format may be a format for a dictionary file generated by an Oracle DBMS_LOGMNR_D.BUILD() process.

[0101] Since the standby database cannot use the built-in DBMS_LOGMNR_D.BUILD of the Oracle database to obtain the data dictionary, the embodiment of the present disclosure can create the first dictionary file based on the format of generating the dictionary file with reference to the built-in package of Oracle.

[0102] In step S130, the log file list and the first dictionary file are mined based on the readable and writable mining database to obtain change data, and the change data is stored in the Kafka topic of the third host.

[0103] As an optional method, after obtaining the log file list and the first dictionary file, the embodiment of the present disclosure can mine the log file list and the first dictionary file based on a readable and writable mining database to obtain change data. On this basis, the mined change data is stored in the Kafka topic of the second host.

[0104] Exemplarily, the log file list may include online redo logs and archived redo logs. The disclosed embodiment may analyze and mine the redo logs, the archived redo logs, and the first dictionary file based on a readable and writable mining database to obtain change data, and store the change data content in the Kafka topic of the third host.

[0105] From the above introduction, we know that the mining database can be located on the second host or on the third host. When the mining database is located on the second host, because the mining database and the standby database are on the same host, the online redo logs and archived redo logs can be directly accessed, so there is no need to mount the online redo logs and archived redo logs through the network file; when the mining database is located on the third host, the online redo logs and archived redo logs of the standby database on the second host can be mounted to the third host through NFS / SSHFS.

[0106] To summarize, the disclosed embodiment can mount the online redo log and archived redo log (log file list) of the standby database on the second host to the third host, and then load the log file list and create a first dictionary file through the LogMiner of the mining database on the third host / or the second host to obtain change data, and save the parsing results of the first dictionary file to the Kafka topic for consumption by downstream applications.

[0107] The disclosed embodiment can not only avoid the constraint that the standby database cannot write data, but also reduce the load when the main database changes data by introducing a mining readable database. Specifically, in response to a data change operation, a log file list is obtained according to a target plug-in, and the target plug-in is used to capture the change data of the database and send the change data to a Kafka topic, wherein the log file list includes an online redo log and an archived redo log, and the log file list is determined based on a start SCN and an end SCN, and the start SCN is greater than or less than the current SCN. On this basis, for the standby database of the second host, a first dictionary file is created according to a specified format, and the specified format is a format for generating dictionary files by Oracle's built-in package. Then, the log file list and the first dictionary file are mined based on a readable and writable mining database to obtain change data, and the change data is stored in a Kafka topic of a third host. The mining database is located on the second host or on the third host, and the third host includes a target plug-in.

[0108] Figure 5 FIG. 1 is a flowchart of another method for acquiring change data based on an Oracle disaster recovery environment backup database according to an exemplary embodiment. Figure 5 As shown, the method may include the following steps.

[0109] In step S210, in response to the data change operation, a log file list is obtained according to the target plug-in.

[0110] The specific implementation of step S210 has been described in detail in the above embodiment, and will not be repeated here.

[0111] In step S220, a first dictionary file is created according to a specified format for the standby database of the second host.

[0112] From the above introduction, we know that since the standby database cannot use Oracle's built-in DBMS LOGMNR D.BUILD to build a data dictionary, the embodiment of the present disclosure proposes a method of building a first dictionary file to replace the execution of the DBMS_LOGMNR_D.BUILD process to generate a data dictionary. The first dictionary file may include a first part of information, a second part of information, and a third part of information.

[0113] Among them, the first part of information can be fixed content, which can be the introduction part of the first dictionary file; the second part of information can be the creation information part of the first dictionary file, which can include data dictionary creation information; the third part of information can be the object information part of the first dictionary file, which can include database object information.

[0114] Specifically, the embodiment of the present disclosure can first obtain the first part of information of the first dictionary file, and then query the relevant views and system tables of the standby database to obtain the second part of information. At the same time, query the data dictionary table of the standby database, and calculate the additional flag bit to obtain the third part of information. On this basis, the first part of information, the second part of information, and the third part of information are format converted and spliced ​​to obtain the first dictionary file.

[0115] As a specific implementation, the introduction part of the first dictionary file (the first part of information) is fixed content and does not change with other information; for the database dictionary creation information part (the second part of information), the disclosed embodiment can obtain it by querying the relevant views and system tables of the standby database; for the database object information part (the third part of information), it can be implemented in two parts. The first part can query the data dictionary table of the standby database, a total of 27 tables are queried, and the second part can be the additional flag bits in the calculation data dictionary. Finally, according to the above query and calculation results, the specified format is converted and spliced ​​to generate the first dictionary file.

[0116] In the embodiment of the present disclosure, the additional flag bit can be obtained in the following manner: obtain the PROPERTY value in the standby database, and represent the PROPERTY value in a polynomial. On this basis, determine whether there is a first power term greater than or equal to the first value in the PROPERTY value according to the polynomial representation, and if it is determined that there is a first power term greater than or equal to the first value in the PROPERTY value, obtain the first additional flag bit according to the first power term, and retain other power terms less than the first value.

[0117] Specifically, for PFLAGS2 (the second additional flag) and PFLAGS3 (the first additional flag) in the TAB$_TABLE table in the data dictionary, the disclosed embodiment can perform binary calculation based on the PROPERTY field in the TAB$ of the standby database, and recalculate the value of PROPERTY to obtain the additional flag.

[0118] For intuitive understanding, the embodiment of the present disclosure may express the value of PROPERTY as a polynomial of 2, and the specific formula is as follows:

[0119] PROPERTY=2 n1 +2 n2 +…+2 nk ;

[0120] Among them, n1>n2>...>nk.

[0121] On this basis, the embodiment of the present disclosure can determine whether there is a first power term greater than or equal to the first value in PROPERTY, and if so, obtain the first additional flag bit. For example, determine whether there is a power term greater than or equal to 64, and if so, process the power term greater than or equal to 64.

[0122] For example, the calculation of the first additional flag bit may be to divide the power term greater than or equal to 64 in PROPERTY by 2. 64 And rounded, that is, the first additional flag PFLAGS3 can be calculated by the following formula:

[0123]

[0124] Among them, PFLAGS3 is the first additional flag, PROPERTY is the power item of PROPERTY value that is greater than or equal to the first value, Indicates a rounding operation.

[0125] At the same time, the embodiment of the present disclosure can retain the power terms less than 64, and the detailed formula is as follows:

[0126] PROPERTY=PROPERTYmod 2 64 .

[0127] Optionally, if it is determined that there is a second power term in PROPERTY that is greater than or equal to the second value and less than the first value, a second additional flag bit can be obtained based on the second power term, and other power terms less than the second value are retained. For example, the first value can be 64, and the second value can be 32.

[0128] For example, the calculation of the second additional flag bit may be to divide the power term in PROPERTY that is greater than or equal to 32 and less than 64 by 2. 32 And rounded, the second additional flag PFLAGS2 can be calculated by the following formula:

[0129]

[0130] Among them, PFLAGS2 is the second additional flag bit, and PROPERTY is the second power item of the PROPERTY value that is greater than or equal to the second value and less than the first data.

[0131] At the same time, the embodiment of the present disclosure can retain the power term less than 32, and the detailed formula is as follows:

[0132] PROPERTY=PROPERTYmod 2 32 .

[0133] During this process, the embodiment of the present disclosure can determine whether PFLAGS3 is a null value. If it is a null value, it can be assigned a value of 0.

[0134] Optionally, if it is determined that each power term in the PROPERTY value is smaller than the second value, the embodiment of the present disclosure may use the PROPERTY value as the third flag bit.

[0135] For example, the disclosed embodiment may process the powers of all items in PROPERTY that are less than 32, and the processing formula is as follows:

[0136] PROPERTY1 = PROPERTY;

[0137] During this process, the embodiment of the present disclosure can determine whether the second additional flag bit PFLAGS2 and the first additional flag bit PFLAGS3 are null values, and if they are null values, assign them a value of 0.

[0138] As a specific example, if we express the PROPERTY value as a polynomial, we get:

[0139] PROPERTY=2 0 +2 2 +2 4 +2 8 +2 16 +2 32 +2 64 +2 128 ;

[0140] Among them, the 64th power of 2 and the 128th power of 2 are greater than or equal to the upper 64th power, then these two powers can be divided by the upper 64th power of 2 respectively, and the first additional flag PFLAGS3 is obtained; between 32 and 64 powers can be divided by 32nd power of 2 to obtain the second additional flag PFLAGS2, and the remaining powers can remain unchanged, that is, the remaining powers less than 32 can be used as new PROPERTY values.

[0141] It should be noted that the calculation process of PFLAGS2 in the COL$_TABLE table in the data dictionary is similar to the calculation process of the second additional flag PFLAGS2 mentioned above, and will not be repeated here.

[0142] In related technologies, the Debezium plug-in directly performs CDC on the standby database, and when entering the snapshot phase, it will report the following Figure 6 The ORA-16000 and ORA-06512 errors shown in the figure are because the standby database is in a read-only state, and write operations and the use of some built-in packages will directly report errors and interrupt the execution process. The embodiment of the present disclosure can avoid such errors by introducing a readable and writable mining database for CDC. Figure 6 The output of the error message shown in the figure shows that the CDC process can be executed normally.

[0143] In step S230, the log file list and the first dictionary file are mined based on the readable and writable mining database to obtain the change data.

[0144] As an optional method, before obtaining the change data, the embodiment of the present disclosure can determine whether there is no data definition language (DDL) in the log file list. If it is determined that there is no DDL in the log file list, the embodiment of the present disclosure can mine the log file list and the first dictionary file based on a readable and writable mining database to obtain the change data.

[0145] In the process of determining whether DDL exists in the log file list, the embodiment of the present disclosure can first parse the log file list and the first dictionary file by mining the database to obtain the parsing result, and on this basis, determine whether DDL exists in the parsing result. If DDL exists in the parsing result, it is determined that DDL exists in the log file list.

[0146] On the contrary, if it is determined that there is DDL in the log file list, a second dictionary file is constructed, and the log file list and the second dictionary file are mined based on a readable and writable mining database to obtain change data.

[0147] When it is determined that there is DDL in the log file list, unnecessary archive redo log file switching operations can be avoided by constructing a second dictionary file, thereby reducing the number of files. In other words, in order to solve the problem of generating a large number of files when the Debezium plug-in calls Oracle to switch archive redo log files, the disclosed embodiment can reduce the number of files by introducing archive redo log file generation conditions, and has the function of calling log switching as needed. Since there is no need to write the dictionary file into the log file list, there is no need to switch the archive redo log when DDL exists, which can reduce the number of files generated.

[0148] If the Debezium plug-in uses the CATALOG IN REDO mode for mining during the mining process, a dictionary file will be generated in each recursion. These dictionary files can be at the 100MB level, which will cause an explosive growth of archive files. By analyzing the reasons, it is found that the dictionary file needs to be built only when DDL occurs. Therefore, in order to reduce the number of archive files generated, the embodiment of the present disclosure can detect whether there is DDL in the log file list.

[0149] Exemplarily, the embodiment of the present disclosure may set an overall flag, and a judgment operation may be performed when generating a dictionary file, and the dictionary file may be generated when the overall flag is true. The timing when the flag is set to true includes the first timing, the second timing, and the third timing.

[0150] Here, the first time may be when the mining task starts, and the flag is set to true to ensure that there is a dictionary file for the first mining; the second time may be after receiving the DDL change, and the flag is set to true. When the mining thread recurses next time, it will check that the flag is true and generate a dictionary file; the third time may be when the mining program reports an ORA-01371 exception, and the flag is set to true. When the mining thread recurses next time, it will check that the flag is true and generate a dictionary file.

[0151] That is, before generating the dictionary file, the embodiment of the present disclosure may determine whether the current timing satisfies any one of the first timing, the second timing, or the third timing, and if so, generate the dictionary file.

[0152] On the contrary, if any of the above timings is not met, the dictionary file will not be generated. Here, the timing can be a false timing. Each time a dictionary file is generated, the flag will be reset to false. In the following recursion, the dictionary file will not be generated again as long as no DDL is generated during the period. This can avoid the generation of a large number of archive files.

[0153] It should be noted that after extending the dictionary file generation cycle, the archived redo log scanned according to the starting SCN will not contain the dictionary file. Therefore, before scanning the archived redo log for change data mining each time, the embodiment of the present disclosure can verify whether the latest dictionary file is included. If the latest dictionary file is not included, the latest dictionary file can be loaded before subsequent mining.

[0154] In order to better illustrate the process of obtaining the change data, the present disclosure embodiment provides the following Figure 7 The flowchart shown is based on Figure 7 It is known that when performing CDC, JDBC connections standbyConnection and mnrConnection of the standby database and the mining database can be created first; in the snapshot stage, the embodiment of the present disclosure can connect to the standby database to read the metadata generation event and store it in the Kafka topic to establish the baseline state of the database. The program running log of the snapshot stage can be as follows Figure 8In the streaming event processing stage, the embodiment of the present disclosure can connect to the standby database to check the database and table status, and then calculate the starting SCN; connect to the standby database to build a data dictionary and save it to a file, connect to the mining database to load the log file list, and connect to the standby database to calculate the ending SCN.

[0155] On this basis, query the current SCN and write it into the mining database, determine whether the mining session is ended (whether there is DDL), if so, reconnect to the standby database to build the data dictionary (build the second dictionary file), then connect to the mining database to load the log file list, start the change data mining, obtain the change data, and calculate the new start SCN. If the session is not ended, execute the start mining, obtain the change data operation, and calculate the new start SCN. The embodiment of the present disclosure can cyclically execute the process of calculating the end SCN to calculating the new start SCN. The program running log of the streaming event processing stage can be as follows: Fig. 9 shown.

[0156] In addition, as can be seen from the above introduction, after obtaining the change data, the embodiment of the present disclosure can store the change data in the Kafka topic. Among them, the change event information received by Kafka can be as follows Fig.10 Based on Figures 8 to 10 It can be seen that the disclosed embodiment can smoothly execute and correctly capture and parse change event messages, and can successfully implement CDC based on the standby database in the Oracle disaster recovery environment, thereby avoiding pressure and performance overhead on the Oracle main database.

[0157] In step S240, if there is table structure information corresponding to the data change information, a data change event is obtained based on the table structure information and the data change information, and the data change event is sent to the Kafka topic.

[0158] In the embodiment of the present disclosure, the changed data may include data change information, which may be information generated by the data change. When the data change information is detected, the embodiment of the present disclosure may determine whether there is table structure information corresponding to the data change information. If there is table structure information corresponding to the data change information, a data change event is obtained based on the table structure information and the data change information, and on this basis, the data change event is sent to the Kafka topic.

[0159] Optionally, if there is no table structure information corresponding to the data change information, the embodiment of the present disclosure can generate a table creation statement, parse the table creation statement, obtain the change event type, and obtain the table structure information based on the standby database. On this basis, the table structure change event is obtained according to the event type and the table structure information, and the data change event is obtained according to the table structure information and the data change information, and the table structure change event and the data change event are sent to the Kafka topic.

[0160] Since the standby database cannot use the built-in DBMS_METADATA of the Oracle database to obtain the table creation script of the database table, the embodiment of the present disclosure proposes a method for generating a table creation statement. In this process, the embodiment of the present disclosure can obtain the relevant information of the table according to the built-in view of the Oracle database, and generate a table creation statement based on the relevant information.

[0161] Specifically, the embodiment of the present disclosure may query the standby database to obtain the first view, and determine whether the change table is a relational table based on the first view, wherein the first view may include the ALL_ALL_TABLES view. If it is determined that the change table is a relational table, the embodiment of the present disclosure may query the second view to obtain the column information of the table, wherein the second view may include the ALL_TAB_COLUMNS view.

[0162] At the same time, the embodiment of the present disclosure can query the third view to obtain the constraint information of the change table, wherein the third view can include the ALL_CONS_COLUMNS view and the ALL_CONSTRAINTS view. On this basis, the column information and constraint information obtained by the query are integrated and spliced ​​to obtain the table creation statement.

[0163] As a specific implementation, the disclosed embodiment can query the ALL_ALL_TABLES view (first view) to determine whether the change table is a relational table. If it is a relational table, the ALL_TAB_COLUMNS view (second view) is queried to obtain information such as the table's column name, data type, data length, and precision. This information can be the column information of the change table. At the same time, the disclosed embodiment can jointly query the ALL_CONS_COLUMNS view and the ALL_CONSTRAINTS view (third view) to obtain the table's primary key, foreign key, and other related constraint information. This information can be the constraint information of the change table. Finally, the relevant information of the table is integrated and spliced ​​to generate a database table creation script, which can be used as a table creation statement. Optionally, if it is determined that the change table is not a relational table, a NonRelationalTableException exception is output.

[0164] In order to better illustrate the effects of the embodiments of the present disclosure, a comparison table of the embodiments of the present disclosure and related technologies is given below.

[0165] Table 1

[0166] plan One hour one day One month (30 days) One year Related technologies 12GB 288GB 8.4TB 100TB The Disclosure 100MB 2.4G 72GB 864GB

[0167] The comparison conditions in Table 1 are that the number of mining threads is 1; the mining cycle of each process is 30 seconds, that is, mining is performed twice per minute, triggering two dictionary generation actions; each dictionary file is estimated to be 100MB (related to the number of tables); a DDL operation occurs once an hour, that is, a dictionary file is generated once an hour after optimization.

[0168] Based on Table 1, it can be seen that the related technology performs mining twice per minute, triggering two dictionary generation actions, and the files generated in one hour are 100MB*2*60=12GB. The amount of files generated in one day, one month, and one year can be calculated by analogy. However, the embodiment of the present disclosure only generates a dictionary file once when a DDL operation occurs, that is, the file generated in one hour is 100MB. It can be seen that the frequency of dictionary file generation is greatly reduced after optimization, which is 120 times lower than before optimization, that is, the embodiment of the present disclosure can greatly reduce the number of log files generated.

[0169] In summary, in order to address the problem that the standby database cannot write data and the Debezium plug-in switching to redo archive logs will increase database pressure and performance overhead, the disclosed embodiment proposes a multi-data source connection method, that is, the data to be written can be written into a readable and writable mining database, and the change data can be obtained through the mining database, which can reduce the standby database pressure and performance overhead.

[0170] The disclosed embodiments can support multiple data sources, implement table creation statements and data dictionary generation methods, and add methods such as customizing the starting system change number (SCN) and optimizing the Debezium plug-in call to switch the Oracle archive redo log mechanism. These methods enable the acquisition of change data for a backup database in a disaster recovery environment implemented using Oracle ADG technology, thereby avoiding pressure and performance overhead on the Oracle main database, and to a certain extent solving the problem of quasi-real-time change data acquisition in the Oracle database.

[0171] Fig.11 FIG. 3 is a block diagram of a change data acquisition device 300 based on an Oracle disaster recovery environment backup database according to an exemplary embodiment. Fig.11 The change data acquisition device 300 based on the Oracle disaster recovery environment backup database includes a log acquisition module 310, a dictionary creation module 320 and a mining module 330.

[0172] The log acquisition module 310 is configured to obtain a log file list according to a target plug-in in response to a data change operation, the target plug-in is used to capture the change data of the database and send the change data to a Kafka topic, the log file list includes an online redo log and an archived redo log, the log file list is determined based on a start SCN and an end SCN, the start SCN is greater than or less than the current SCN;

[0173] The dictionary creation module 320 is configured to create a first dictionary file according to a specified format, wherein the specified format is a format for generating a dictionary file by a built-in package of the Oracle;

[0174] The mining module 330 is configured to mine the log file list and the first dictionary file based on a readable and writable mining database to obtain change data, and store the change data in a Kafka topic of a third host, wherein the mining database is located on the second host or on the third host, and the third host includes the target plug-in.

[0175] In some implementations, the first dictionary file includes a first portion of information, a second portion of information, and a third portion of information, and the dictionary creation module 320 may include:

[0176] A fixed information acquisition submodule is configured to acquire a first portion of information of the first dictionary file, where the first portion of information is fixed content;

[0177] A first query submodule is configured to query relevant views and system tables of the standby database to obtain the second part of information, where the second part of information is a creation information part of the first dictionary file;

[0178] A second query submodule is configured to query the data dictionary table of the standby database and calculate the additional flag to obtain the third part of information, where the third part of information is the object information part of the first dictionary file;

[0179] The splicing submodule is configured to perform format conversion and splicing on the first part of information, the second part of information and the third part of information to obtain the data dictionary.

[0180] In some embodiments, the second query submodule is configured to obtain the PROPERTY value in the backup database and represent the PROPERTY value as a polynomial; determine whether there is a first power term greater than or equal to a first numerical value in the PROPERTY value based on the polynomial representation; if there is a first power term greater than or equal to the first numerical value in the PROPERTY value, obtain a first additional flag bit based on the first power term, and retain other power terms less than the first numerical value.

[0181] In some embodiments, the second construction submodule is further configured to obtain a second additional flag bit based on the second power term if there is a second power term greater than or equal to a second numerical value and less than the first numerical value in the PROPERTY value, and retain other power terms less than the second numerical value.

[0182] In some implementations, the second building submodule is further configured to use the PROPERTY value as a third flag bit if all power terms in the PROPERTY value are smaller than the second value.

[0183] In some embodiments, the change data includes data change information, which is information generated by the data change. The mining module 330 can be configured to obtain a data change event based on the table structure information and the data change information if there is table structure information corresponding to the data change, and send the data change event to the Kafka topic.

[0184] In some implementations, the change data acquisition device 300 based on the Oracle disaster recovery environment backup database may also include:

[0185] a table creation statement generation module, configured to generate a table creation statement if table structure information corresponding to the data change information does not exist, parse the table creation statement to obtain a change event type, and obtain the table structure information based on the standby database;

[0186] The event acquisition module is configured to obtain a table structure change event according to the event type and the table structure information, and to obtain the data change event according to the table structure information and the data change information, and to send the table structure change event and the data change event to the Kafka topic.

[0187] In some implementations, the table creation statement generation module is configured to obtain table related information according to a built-in view of the Oracle database, and generate the table creation statement based on the related information.

[0188] In some implementations, the table creation statement generation module is further configured to query the standby database to obtain a first view, and determine whether the change table is a relational table based on the first view; when it is determined that the change table is a relational table, query the second view to obtain the column information of the table; query the third view to obtain the constraint information of the table; integrate and splice the column information and the constraint information to obtain the table creation statement.

[0189] In some implementations, the mining module 330 may be configured to mine the log file list and the first dictionary file based on a readable and writable mining database to obtain change data if it is determined that no data definition language DDL exists in the log file list.

[0190] In some embodiments, the mining module 330 can also be configured to construct a third dictionary file and a second dictionary file if it is determined that the DDL exists in the log file list, and mine the log file list and the third dictionary file and the second dictionary file based on a readable and writable mining database to obtain the change data.

[0191] In some implementations, the log file list includes database redo log files; the mining module 330 is configured to analyze and mine the database redo log files and the first dictionary file based on the readable and writable mining database to obtain the change data.

[0192] The disclosed embodiment can not only avoid the constraint that the standby database cannot write data by introducing a mining readable database, but also reduce the load when the main database changes data. Specifically, in response to a data change operation, a log file list is obtained according to a target plug-in, and the target plug-in is used to capture the change data of the database and send the change data to a Kafka topic, wherein the log file list includes an online redo log and an archived redo log, and the log file list is determined based on a start SCN and an end SCN, and the start SCN is greater than or less than the current SCN. On this basis, for the standby database of the second host, a first dictionary file is created according to a specified format, and the specified format is a format for generating dictionary files by Oracle's built-in package. Then, the log file list and the first dictionary file are mined based on a readable and writable mining database to obtain change data, and the change data is stored in a Kafka topic of a third host. The mining database is located on the second host or on the third host, and the third host includes a target plug-in, so that the performance overhead of the Oracle main database can be reduced to a certain extent.

[0193] Regarding the device in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.

[0194] Fig.12 FIG. 7 is a block diagram of an electronic device 700 according to an exemplary embodiment. Fig.12 As shown, the electronic device 700 may include: a processor 701 and a memory 702. The electronic device 700 may also include one or more of a multimedia component 703, an input / output (I / O) interface 704, and a communication component 705.

[0195] The processor 701 is used to control the overall operation of the electronic device 700 to complete all or part of the steps in the above-mentioned change data acquisition method based on the Oracle disaster recovery environment backup database. The memory 702 is used to store various types of data to support the operation of the electronic device 700, and these data may include instructions for any application or method used to operate on the electronic device 700, and application-related data, such as contact data, sent and received messages, pictures, audio, video, etc. The memory 702 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (Static Random Access Memory, referred to as SRAM), electrically erasable programmable read-only memory (Electrically Erasable Programmable Read-Only Memory, referred to as EEPROM), erasable programmable read-only memory (Erasable Programmable Read-Only Memory, referred to as EPROM), programmable read-only memory (Programmable Read-Only Memory, referred to as PROM), read-only memory (Read-Only Memory, referred to as ROM), magnetic memory, flash memory, disk or optical disk. The multimedia component 703 may include a screen and an audio component. The screen may be, for example, a touch screen, and the audio component is used to output and / or input audio signals. For example, the audio component may include a microphone for receiving external audio signals. The received audio signal may be further stored in the memory 702 or sent through the communication component 705. The audio component also includes at least one speaker for outputting audio signals. The I / O interface 704 provides an interface between the processor 701 and other interface modules, and the other interface modules may be keyboards, mice, buttons, etc. These buttons may be virtual buttons or physical buttons. The communication component 705 is used for wired or wireless communication between the electronic device 700 and other devices. Wireless communication, such as Wi-Fi, Bluetooth, Near Field Communication (NFC), 2G, 3G, 4G, NB-IOT, eMTC, or other 5G, etc., or a combination of one or more of them, is not limited here. Therefore, the corresponding communication component 705 may include: Wi-Fi module, Bluetooth module, NFC module, etc.

[0196] In an exemplary embodiment, the electronic device 700 can be implemented by one or more application specific integrated circuits (ASIC), digital signal processors (DSP), digital signal processing devices (DSPD), programmable logic devices (PLD), field programmable gate arrays (FPGA), controllers, microcontrollers, microprocessors or other electronic components to execute the above-mentioned change data acquisition method based on the Oracle disaster recovery environment backup database.

[0197] In another exemplary embodiment, a computer-readable storage medium including program instructions is also provided, and when the program instructions are executed by a processor, the steps of the above-mentioned method for acquiring change data based on the Oracle disaster recovery environment backup database are implemented. For example, the computer-readable storage medium can be the above-mentioned memory 702 including program instructions, and the above-mentioned program instructions can be executed by the processor 701 of the electronic device 700 to complete the above-mentioned method for acquiring change data based on the Oracle disaster recovery environment backup database.

[0198] In another exemplary embodiment, a computer program product is also provided, which includes a computer program that can be executed by a programmable device, and the computer program has a code portion for executing the above-mentioned change data acquisition method based on the Oracle disaster recovery environment backup database when executed by the programmable device.

[0199] The preferred embodiments of the present disclosure are described in detail above in conjunction with the accompanying drawings; however, the present disclosure is not limited to the specific details in the above embodiments. Within the technical concept of the present disclosure, a variety of simple modifications can be made to the technical solution of the present disclosure, and these simple modifications all fall within the protection scope of the present disclosure.

[0200] It should also be noted that the various specific technical features described in the above specific embodiments can be combined in any suitable manner without contradiction. In order to avoid unnecessary repetition, the present disclosure will not further describe various possible combinations.

[0201] In addition, various embodiments of the present disclosure may be arbitrarily combined, and as long as they do not violate the concept of the present disclosure, they should also be regarded as the contents disclosed by the present disclosure.

Claims

1. A method for acquiring change data based on an Oracle disaster recovery environment backup database, characterized in that: The method comprises: In response to the data change operation, a log file list is obtained according to the target plug-in, the target plug-in is used to capture the change data of the database and send the change data to the Kafka topic, the log file list includes online redo logs and archived redo logs, the log file list is determined based on the start SCN and the end SCN, and the start SCN is greater than or less than the current SCN; For the standby database of the second host, create a first dictionary file according to a specified format, where the specified format is a format for generating a dictionary file by a built-in package of Oracle; The log file list and the first dictionary file are mined based on a readable and writable mining database to obtain change data, and the change data is stored in a Kafka topic of a third host, the mining database is located on the second host or on the third host, and the third host includes the target plug-in.

2. The change data acquisition method based on the Oracle disaster recovery environment backup database according to claim 1 is characterized in that: The first dictionary file includes a first part of information, a second part of information, and a third part of information. The step of creating the first dictionary file according to the specified format includes: Acquire a first portion of information of the first dictionary file, where the first portion of information is fixed content; Query the relevant views and system tables of the standby database to obtain the second part of information, where the second part of information is the creation information part of the first dictionary file; Query the data dictionary table of the standby database, and calculate the additional flag bit to obtain the third part of information, where the third part of information is the object information part of the first dictionary file; The first part of information, the second part of information and the third part of information are format converted and spliced ​​to obtain the first dictionary file.

3. The change data acquisition method based on Oracle disaster recovery environment backup database according to claim 2 is characterized in that: The calculation of the additional flag bit includes: Obtaining a PROPERTY value in the standby database, and expressing the PROPERTY value as a polynomial; Determining whether there is a first power term greater than or equal to a first value in the PROPERTY value according to the polynomial representation; If a first power term greater than or equal to the first value exists in the PROPERTY value, a first additional flag bit is obtained according to the first power term, and other power terms less than the first value are retained.

4. The method for acquiring change data based on Oracle disaster recovery environment backup database according to claim 3, characterized in that: The method further comprises: If there is a second power term greater than or equal to a second value and less than the first value in the PROPERTY value, a second additional flag bit is obtained based on the second power term, and other power terms less than the second value are retained.

5. The method for acquiring changed data based on Oracle disaster recovery environment backup database according to claim 4, characterized in that: The method further comprises: If all power terms in the PROPERTY value are smaller than the second value, the PROPERTY value is used as the third flag bit.

6. The change data acquisition method based on Oracle disaster recovery environment backup database according to claim 1 is characterized in that: The changed data includes data change information, and the data change information is information generated by the data change; The storing the changed data in the Kafka topic of the third host comprises: If table structure information corresponding to the data change information exists, a data change event is acquired based on the table structure information and the data change information, and the data change event is sent to the Kafka topic.

7. The method for acquiring changed data based on the Oracle disaster recovery environment backup database according to claim 6, characterized in that: The method further comprises: If the table structure information corresponding to the data change information does not exist, a table creation statement is generated, and the table creation statement is parsed to obtain the change event type, and the table structure information is obtained based on the standby database; A table structure change event is obtained according to the event type and the table structure information, and the data change event is obtained according to the table structure information and the data change information, and the table structure change event and the data change event are sent to the Kafka topic.

8. The method for acquiring changed data based on Oracle disaster recovery environment backup database according to claim 7, characterized in that: The generating of the table creation statement includes: The relevant information of the table is obtained according to the built-in view of the Oracle database, and the table creation statement is generated based on the relevant information.

9. The method for acquiring changed data based on the Oracle disaster recovery environment backup database according to claim 8, characterized in that: The step of acquiring relevant information of a table according to a built-in view of an Oracle database and generating the table creation statement based on the relevant information includes: querying the standby database to obtain a first view, and determining whether the change table is a relational table based on the first view; When it is determined that the change table is a relational table, query the second view to obtain column information of the table; Query the third view to obtain the constraint information of the table; The column information and the constraint information are integrated and spliced ​​to obtain the table creation statement.

10. The method for acquiring changed data based on Oracle disaster recovery environment backup database according to claim 1, characterized in that: The mining of the log file list and the first dictionary file based on the readable and writable mining database to obtain the change data includes: If it is determined that the data definition language DDL does not exist in the log file list, the log file list and the first dictionary file are mined based on a readable and writable mining database to obtain change data.

11. The method for acquiring change data based on Oracle disaster recovery environment backup database according to claim 10, characterized in that: The method further comprises: If it is determined that the DDL exists in the log file list, a second dictionary file is constructed, and the log file list and the second dictionary file are mined based on a readable and writable mining database to obtain the change data.

12. The method for acquiring change data based on an Oracle disaster recovery environment backup database according to any one of claims 1 to 11, characterized in that: The log file list includes database redo log files; The mining of the log file list and the first dictionary file based on the readable and writable mining database to obtain the change data includes: The database redo log file and the first dictionary file are analyzed and mined based on the readable and writable mining database to obtain the change data.

13. A change data acquisition device based on Oracle disaster recovery environment backup database, characterized in that: The device comprises: A log acquisition module is configured to obtain a log file list according to a target plug-in in response to a data change operation, wherein the target plug-in is used to capture the change data of the database and send the change data to a Kafka topic, wherein the log file list includes an online redo log and an archived redo log, and the log file list is determined based on a start SCN and an end SCN, wherein the start SCN is greater than or less than the current SCN; A dictionary creation module is configured to create a first dictionary file according to a specified format for the standby database of the second host, where the specified format is a format for generating a dictionary file by a built-in package of Oracle; A mining module is configured to mine the log file list and the first dictionary file based on a readable and writable mining database to obtain change data, and store the change data in a Kafka topic of a third host, wherein the mining database is located on the second host or on the third host, and the third host includes the target plug-in.

14. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the steps of the method described in any one of claims 1 to 12 are implemented.

15. An electronic device, characterized in that: include: a memory having a computer program stored thereon; A processor, configured to execute the computer program in the memory to implement the steps of the method according to any one of claims 1 to 12.

Images