Oracle log-based data collection method, device and system
By implementing transaction-based grouping and index management of Oracle log data, the problems of high system memory consumption and network congestion were solved, enabling an efficient data acquisition and submission process.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- BEIJING DIPU TECH CO LTD
- Filing Date
- 2023-06-29
- Publication Date
- 2026-05-19
AI Technical Summary
When collecting data in real time based on Oracle logs, the system memory consumption is large, and sending rollback data along with the data that actually needs to be submitted causes network congestion.
Data in the Oracle log is grouped and stored based on the transaction it belongs to. Indexes are generated in system memory, and data is written to disk files when a threshold is exceeded. Indexes generated by rollback data are cleared, and the remaining index queues are merged. Data is only queried from disk files to commit data when a transaction is committed.
It greatly saves system memory, avoids network congestion, and improves data processing speed and disk file processing efficiency.
Smart Images
Figure CN116644034B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of log data acquisition technology, and in particular to a data acquisition method, device and system based on Oracle logs. Background Technology
[0002] Oracle Database, also known as Oracle RDBMS, or simply Oracle, is a relational database management system. In current technologies, when collecting data in real-time based on Oracle logs, a problem arises: if a SQL (Structured Query Language) statement involves numerous DDL (Data Definition Language) operations, such as `DELECT FROM TABLE1` (delete operation), this SQL will clear the table `TABLE1`. However, if this table contains a large amount of data, this data needs to be stored in system memory until the transaction is committed before it can be processed. But if the data volume is too large, this can lead to significant system memory consumption.
[0003] Furthermore, within an Oracle transaction, data undergoes both commits and rollbacks. When a rollback occurs in the Oracle log, a large amount of data is generated. For example, if 1 million records are deleted and then a rollback operation is performed, 2 million records will be generated. However, these 2 million records do not actually need to be sent downstream. This rollback data not only consumes a significant amount of system memory, but also causes substantial network congestion when sent along with the actual data to be committed during the commit process. Summary of the Invention
[0004] To at least partially overcome the problems in related technologies where real-time data acquisition based on Oracle logs involves large system memory consumption and significant network congestion caused by sending rollback data along with the actual data to be submitted during data submission, this application provides a data acquisition method, device, and system based on Oracle logs.
[0005] The proposed solution is as follows:
[0006] According to a first aspect of the embodiments of this application, a data acquisition method based on Oracle logs is provided, comprising:
[0007] When collecting data in real time based on Oracle logs, the data in the Oracle logs is grouped according to the transaction to which it belongs, and the grouped data is stored in system memory;
[0008] Determine whether the data in the system memory exceeds a preset threshold;
[0009] When the amount of data in the system memory exceeds a preset threshold, the data in the system memory will be written to the disk file sequentially.
[0010] For committed data, a corresponding index is generated in the system memory, and the original data in the system memory is cleared; the index content includes at least: transaction identifier, data identifier, and data row number;
[0011] Clear the indexes generated by the rollback data;
[0012] Based on the data row numbers of the remaining indexes, merge and combine the remaining indexes into a row number queue;
[0013] Determine whether a transaction commit operation has been received;
[0014] Upon receiving a transaction commit operation, the disk file is traversed according to the row number queue, and the data corresponding to the row number queue is queried and committed.
[0015] Preferably, generating a corresponding index in the system memory for the submitted data includes:
[0016] The system filters data in memory based on data submission status, and generates corresponding indexes in system memory for data with a submission status of "submitted".
[0017] Preferably, clearing the indexes generated by the rollback data includes:
[0018] Use the data identifier of data whose commit status is rollback as the data identifier to be cleared;
[0019] Search for the indexes that contain the identifier of the data to be cleared in the existing indexes and select them as the indexes to be cleared;
[0020] Delete the last row number of the index to be cleared;
[0021] If the data identifier is empty after deleting the last row number of the index to be cleared, then the index to be cleared will be cleared.
[0022] Preferably, the method further includes:
[0023] When the data in the system memory does not exceed the preset threshold and a transaction commit operation is received, the data in the system memory is committed.
[0024] Preferably, the method further includes:
[0025] If there are multiple data row numbers in the index, the row numbers are separated by a preset character.
[0026] According to a second aspect of the embodiments of this application, a data acquisition device based on Oracle logs is provided, comprising:
[0027] Processor and memory;
[0028] The processor and memory are connected via a communication bus:
[0029] The processor is used to call and execute the program stored in the memory;
[0030] The memory is used to store a program, which is at least used to execute a data acquisition method based on Oracle logs as described in any of the above.
[0031] According to a third aspect of the embodiments of this application, a data acquisition system based on Oracle logs is provided, comprising:
[0032] The grouping module is used to group data in Oracle logs based on the transactions they belong to when collecting data in real time based on Oracle logs, and then store the grouped data in system memory.
[0033] The first judgment module is used to determine whether the data in the system memory exceeds a preset threshold.
[0034] The write module is used to write the data in the system memory to the disk file sequentially when the data in the system memory exceeds a preset threshold;
[0035] The index module is used to generate corresponding indexes for committed data in system memory and clear the original data in system memory; the index content includes at least: transaction identifier, data identifier, and data row number;
[0036] The cleanup module is used to clear indexes generated by rollback data;
[0037] The combination module is used to merge and combine the remaining indexes into a row number queue based on the data row numbers of the remaining indexes;
[0038] The second judgment module is used to determine whether a transaction commit operation has been received.
[0039] The commit module is used to, upon receiving a transaction commit operation, traverse the disk file according to the row number queue, query the data corresponding to the row number queue, and commit the data.
[0040] Preferably, the indexing module is specifically used to filter data in the system memory based on the data submission status, and generate a corresponding index in the system memory for data whose submission status is "submitted".
[0041] Preferably, the clearing module is specifically used to obtain the data identifier of data whose data submission status is rollback as the data identifier to be cleared;
[0042] Search for the indexes that contain the identifier of the data to be cleared in the existing indexes and select them as the indexes to be cleared;
[0043] Delete the last row number of the index to be cleared;
[0044] If the data identifier is empty after deleting the last row number of the index to be cleared, then the index to be cleared will be cleared.
[0045] Preferably, the system further includes:
[0046] The submission module is also used to submit the data in the system memory when the data in the system memory does not exceed a preset threshold and a transaction submission operation is received.
[0047] The technical solution provided in this application can include the following beneficial effects: The data acquisition method based on Oracle logs in this application includes: when acquiring data in real time based on Oracle logs, grouping the data in the Oracle logs according to the transaction to which they belong, and storing the grouped data in system memory; determining whether the data in system memory exceeds a preset threshold; when the data in system memory exceeds the preset threshold, writing the data in system memory to disk files sequentially, generating corresponding indexes for committed data in system memory, and clearing the original data in system memory. In this way, only the index is retained in system memory, greatly saving system memory, and scenarios with huge amounts of data in a single transaction can be quickly processed with very little system memory. Since the index includes at least: transaction identifier, data identifier, and data row number; clearing the index generated by rollback data; merging and combining the remaining indexes into a row number queue according to the data row numbers of the remaining indexes; determining whether a transaction commit operation has been received; when a transaction commit operation is received, traversing the disk files according to the row number queue, querying the data corresponding to the row number queue, and committing it. Since this application not only generates indexes corresponding to committed data, but also clears indexes generated by rollback data, rollback data will not be found in disk files when committing transactions. Only the data that actually needs to be committed will be committed, and rollback data will not be committed again, thus avoiding network congestion.
[0048] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and do not limit this application. Attached Figure Description
[0049] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.
[0050] Figure 1 This is a flowchart illustrating a data acquisition method based on Oracle logs provided in one embodiment of this application;
[0051] Figure 2 This is a schematic diagram of data involved in an example Oracle transaction provided in one embodiment of this application;
[0052] Figure 3 This is a schematic diagram of an index generated from data involved in an example Oracle transaction, provided in one embodiment of this application;
[0053] Figure 4 This is a schematic diagram illustrating the clearing of an index generated from data involved in an example Oracle transaction, provided in one embodiment of this application.
[0054] Figure 5 This is a flowchart illustrating another data acquisition method based on Oracle logs provided in one embodiment of this application;
[0055] Figure 6 This is a schematic diagram of the structure of a data acquisition device based on Oracle logs provided in one embodiment of this application;
[0056] Figure 7 This is a schematic diagram of the structure of a data acquisition system based on Oracle logs provided in one embodiment of this application.
[0057] Reference numerals: Processor-21; Memory-22; Grouping module-31; First judgment module-32; Write module-33; Index module-34; Clear module-35; Combination module-36; Second judgment module-37; Submit module-38. Detailed Implementation
[0058] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims.
[0059] Example 1
[0060] Figure 1 This is a flowchart illustrating a data acquisition method based on Oracle logs according to an embodiment of this application. (Refer to...) Figure 1 A data collection method based on Oracle logs, comprising:
[0061] S11: When collecting data in real time based on Oracle logs, the data in the Oracle logs is grouped according to the transaction to which it belongs, and the grouped data is stored in the system memory;
[0062] S12: Determine whether the data in the system memory exceeds a preset threshold;
[0063] S13: When the amount of data in the system memory exceeds a preset threshold, write the data in the system memory to the disk file in sequence;
[0064] S14: Generate a corresponding index in the system memory for the committed data and clear the original data in the system memory; the index content shall include at least: transaction identifier, data identifier and data row number;
[0065] S15: Clear the indexes generated by the rollback data;
[0066] S16: Merge the remaining indexes into a row number queue based on the data row numbers of the remaining indexes;
[0067] S17: Determine if a transaction commit operation has been received;
[0068] S18: Upon receiving a transaction commit operation, traverse the disk file according to the row number queue, query the data corresponding to the row number queue, and commit it.
[0069] It should be noted that the technical solution in this embodiment relates to the field of log data acquisition technology and can be specifically applied to real-time data acquisition based on Oracle logs.
[0070] It's important to note that a transaction, in general, refers to something that needs to be done or is done. In computer terminology, it refers to a unit of program execution that accesses and may update various data items in a database. Transactions are typically initiated by the execution of user programs written in high-level database manipulation languages or programming languages, and are delimited by statements (or function calls) of the form "begin transaction" and "end transaction". A transaction consists of all the operations performed between the start and end of the transaction.
[0071] In Oracle relational databases, a transaction can be a single SQL statement, a group of SQL statements, or an entire program.
[0072] It should be noted that the preset threshold is a manually set threshold. In practice, it can be set to the number of data entries, such as 100,000 data entries. Alternatively, it can be set based on the system's memory capacity, such as 10GB.
[0073] It's important to note that when the data in system memory exceeds a preset threshold, the data in system memory is sequentially written to disk files, a corresponding index is generated in system memory, and the original data stored in system memory is cleared. In this way, only the index is retained in system memory, greatly saving system memory and allowing for the rapid processing of scenarios with massive amounts of data in a single transaction with minimal system memory usage.
[0074] It should be noted that the index must include at least: transaction identifier, data identifier, data commit status, and data row number.
[0075] In practice, the transaction identifier is represented by "XID," which is the transaction ID of data in Oracle and is unique within the Oracle service. The data identifier is represented by "ROW_ID," which is a unique identifier for a piece of data in Oracle and remains unchanged regardless of how many times it is modified. The data commit status is represented by "ROLLBACK," where 0 indicates that the data has been committed, and 1 indicates that the data has been rolled back.
[0076] It should be noted that a corresponding index is generated in the system memory for the submitted data, including:
[0077] The system filters data in memory based on data submission status, and generates corresponding indexes in system memory for data with a submission status of "submitted".
[0078] Clear the indexes generated by the rollback data, including:
[0079] Use the data identifier of data whose commit status is rollback as the data identifier to be cleared;
[0080] Search for indexes with data identifiers to be cleared among the existing indexes and designate them as the indexes to be cleared;
[0081] Delete the last digit of the row number of the index to be cleared;
[0082] If the data identifier is empty after deleting the last row number of the index to be cleared, then the index to be cleared will be cleared.
[0083] like Figure 2 As shown, Figure 2 Consider a sample Oracle transaction involving four rows. Rows 1, 2, and 4 are committed, while row 3 is rolled back. Based on the committed status, the transaction filters the data and generates indexes in system memory corresponding to rows 1, 2, and 4. Figure 3 As shown, in practice, the index format is <transaction identifier, <data identifier, data row number>>.
[0084] Furthermore, since the data in row 3 is rollback data, the data identifier of the data in row 3 is used as the data identifier to be cleared. The index with the data identifier to be cleared is queried in the generated index. Since the data identifiers of the data in row 3 and the data in row 2 are the same, the index generated by the data in row 2 is the index to be cleared.
[0085] Furthermore, after deleting the last digit of the row number in row 2, the data identifier becomes empty. Therefore, in this Oracle transaction, only the data in rows 1 and 4 are the actual data that needs to be collected and committed. The final index generated by the data involved in this example Oracle transaction is as follows: Figure 4 As shown.
[0086] It should be noted that, referring to Figure 5 The methods also include:
[0087] When the data in the system memory does not exceed the preset threshold and a transaction commit operation is received, the data in the system memory is committed.
[0088] It should be noted that the method also includes:
[0089] If there are multiple row numbers in the index, the row numbers are separated by a preset character.
[0090] It is understood that the data acquisition method based on Oracle logs in this embodiment includes: when acquiring data in real time based on Oracle logs, grouping the data in the Oracle logs according to their respective transactions, and storing the grouped data in system memory; determining whether the data in system memory exceeds a preset threshold; when the data in system memory exceeds the preset threshold, writing the data in system memory to disk files sequentially, generating corresponding indexes for committed data in system memory, and clearing the original data in system memory. In this way, only the indexes are retained in system memory, greatly saving system memory and allowing for the rapid processing of scenarios with huge amounts of data in a single transaction with very little system memory. Since the index includes at least: transaction identifier, data identifier, and data row number; clearing the indexes generated by rollback data; merging the remaining indexes into a row number queue based on the data row numbers of the remaining indexes; determining whether a transaction commit operation has been received; and when a transaction commit operation is received, traversing the disk files according to the row number queue, querying the data corresponding to the row number queue, and committing it. Since this embodiment not only generates indexes corresponding to committed data, but also clears indexes generated by rollback data, rollback data will not be found in the disk file when committing a transaction. When committing, only the data that actually needs to be committed will be committed, and rollback data will not be committed again, thus avoiding network congestion.
[0091] It is understandable that, when the technical solution in this embodiment is implemented, the entire process only requires one write and read operation on the disk file, which greatly improves the processing speed of the disk file.
[0092] It is understandable that the technical solution in this embodiment can also enrich the application scenarios of Oracle CDC.
[0093] Example 2
[0094] A data acquisition device based on Oracle logs, comprising:
[0095] Processor and memory;
[0096] The processor and memory are connected via a communication bus:
[0097] The processor is used to call and execute programs stored in memory.
[0098] A memory is used to store a program, which is at least used to execute a data acquisition method based on Oracle logs as described in the above embodiments.
[0099] Example 3
[0100] A data acquisition system based on Oracle logs includes:
[0101] Grouping module 31 is used to group data in Oracle logs based on the transactions they belong to when collecting data in real time based on Oracle logs, and store the grouped data in system memory;
[0102] The first judgment module 32 is used to determine whether the data in the system memory exceeds a preset threshold.
[0103] The writing module 33 is used to write the data in the system memory to the disk file sequentially when the data in the system memory exceeds a preset threshold.
[0104] Index module 34 is used to generate corresponding indexes for committed data in system memory and clear the original data in system memory; the index content includes at least: transaction identifier, data identifier and data row number;
[0105] The clearing module 35 is used to clear the indexes generated by the rollback data;
[0106] The combination module 36 is used to merge and combine the remaining indexes into a row number queue based on the data row number of the remaining indexes;
[0107] The second judgment module 37 is used to determine whether a transaction commit operation has been received.
[0108] The commit module 38 is used to traverse the disk file according to the row number queue and query the data corresponding to the row number queue when a transaction commit operation is received.
[0109] It should be noted that the index module 34 is specifically used to filter the data in the system memory based on the data submission status, and to generate a corresponding index in the system memory for data whose submission status is "submitted".
[0110] It should be noted that the clearing module 35 is specifically used to obtain the data identifier of data whose data submission status is rollback as the data identifier to be cleared;
[0111] Search for indexes with data identifiers to be cleared among the existing indexes and designate them as the indexes to be cleared;
[0112] Delete the last digit of the row number of the index to be cleared;
[0113] If the data identifier is empty after deleting the last row number of the index to be cleared, then the index to be cleared will be cleared.
[0114] It should be noted that the system also includes:
[0115] The commit module is also used to commit the data in the system memory when the data in the system memory does not exceed a preset threshold and when a transaction commit operation is received.
[0116] It should be noted that the Oracle log-based data acquisition system in this embodiment includes: a grouping module 31, a first judgment module 32, a writing module 33, an indexing module 34, a clearing module 35, a combination module 36, a second judgment module 37, and a commit module 38. In implementation, when performing real-time data acquisition based on Oracle logs, the grouping module 31 groups the data in the Oracle logs according to their respective transactions and stores the grouped data in system memory. The first judgment module 32 determines whether the data in system memory exceeds a preset threshold. When the data in system memory exceeds the preset threshold, the writing module 33 writes the data in system memory to disk files sequentially. The indexing module 34 generates corresponding indexes for the committed data in system memory and clears the original data in system memory. Thus, only the indexes are retained in system memory, greatly saving system memory and enabling the rapid processing of scenarios with huge amounts of data in a single transaction using very little system memory. Since the index includes at least: transaction identifier, data identifier, and data row number; the clearing module 35 clears the index generated by rollback data, and the combining module 36 combines the remaining indexes into a row number queue based on the data row numbers of the remaining indexes; the second judgment module 37 determines whether a transaction commit operation has been received; when the commit module 38 receives a transaction commit operation, it traverses the disk file according to the row number queue, queries the data corresponding to the row number queue, and commits it. Because this embodiment not only generates the index corresponding to the committed data, but also clears the index generated by rollback data, rollback data will not be found in the disk file when a transaction is committed, and only the data that actually needs to be committed will be committed, without committing the rollback data, thus avoiding network congestion.
[0117] It is understood that the same or similar parts in the above embodiments can be referred to each other, and the contents not described in detail in some embodiments can be referred to the same or similar contents in other embodiments.
[0118] It should be noted that in the description of this application, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance. Furthermore, in the description of this application, unless otherwise stated, "a plurality of" means at least two.
[0119] Any process or method described in the flowchart or otherwise herein can be understood as representing a module, segment, or portion of code comprising one or more executable instructions for implementing a particular logical function or process, and the scope of the preferred embodiments of this application includes additional implementations in which functions may be performed not in the order shown or discussed, including substantially simultaneously or in reverse order depending on the function involved, as will be understood by those skilled in the art to which embodiments of this application pertain.
[0120] It should be understood that various parts of this application can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, it can be implemented using any one or a combination of the following techniques known in the art: discrete logic circuits having logic gates for implementing logical functions on data signals, application-specific integrated circuits (ASICs) having suitable combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), etc.
[0121] Those skilled in the art will understand that all or part of the steps of the methods in the above embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, the program includes one or a combination of the steps of the method embodiments.
[0122] Furthermore, the functional units in the various embodiments of this application can be integrated into a processing module, or each unit can exist physically separately, or two or more units can be integrated into a module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium.
[0123] The storage media mentioned above can be read-only memory, disk, or optical disk, etc.
[0124] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.
[0125] Although embodiments of this application have been shown and described above, it is understood that the above embodiments are exemplary and should not be construed as limiting this application. Those skilled in the art can make changes, modifications, substitutions and variations to the above embodiments within the scope of this application.
Claims
1. A data acquisition method based on Oracle logs, characterized in that, include: When collecting data in real time based on Oracle logs, the data in the Oracle logs is grouped according to the transaction to which it belongs, and the grouped data is stored in system memory; Determine whether the data in the system memory exceeds a preset threshold; When the amount of data in the system memory exceeds a preset threshold, the data in the system memory will be written to the disk file sequentially. Generate a corresponding index in the system memory for the submitted data, and clear the original data in the system memory; The index content includes at least: transaction identifier, data identifier, and data row number; Clear the indexes generated by the rollback data; Based on the data row numbers of the remaining indexes, merge and combine the remaining indexes into a row number queue; Determine whether a transaction commit operation has been received; Upon receiving a transaction commit operation, the disk file is traversed according to the row number queue, and the data corresponding to the row number queue is queried and committed. For submitted data, generate corresponding indexes in system memory, including: The system filters data in memory based on data submission status, and generates corresponding indexes for data with a submission status of "submitted" in the system memory. Clear the indexes generated by the rollback data, including: Use the data identifier of data whose commit status is rollback as the data identifier to be cleared; Search for the indexes that contain the identifier of the data to be cleared in the existing indexes and select them as the indexes to be cleared; Delete the last row number of the index to be cleared; If the data identifier is empty after deleting the last row number of the index to be cleared, then the index to be cleared will be cleared.
2. The method according to claim 1, characterized in that, The method further includes: When the data in the system memory does not exceed the preset threshold and a transaction commit operation is received, the data in the system memory is committed.
3. The method according to claim 1, characterized in that, The method further includes: If there are multiple data row numbers in the index, the row numbers are separated by a preset character.
4. A data acquisition device based on Oracle logs, characterized in that, include: Processor and memory; The processor and memory are connected via a communication bus: The processor is used to call and execute the program stored in the memory; The memory is used to store a program, which is at least used to execute the data acquisition method based on Oracle logs as described in any one of claims 1-3.
5. A data acquisition system based on Oracle logs, characterized in that, include: The grouping module is used to group data in Oracle logs based on the transactions they belong to when collecting data in real time based on Oracle logs, and then store the grouped data in system memory. The first judgment module is used to determine whether the data in the system memory exceeds a preset threshold. The write module is used to write the data in the system memory to the disk file sequentially when the data in the system memory exceeds a preset threshold; The indexing module is used to generate corresponding indexes for submitted data in system memory and clear the original data in system memory. The index content includes at least: transaction identifier, data identifier, and data row number; The cleanup module is used to clear indexes generated by rollback data; The combination module is used to merge and combine the remaining indexes into a row number queue based on the data row numbers of the remaining indexes; The second judgment module is used to determine whether a transaction commit operation has been received. The commit module is used to, upon receiving a transaction commit operation, traverse the disk file according to the row number queue, query the data corresponding to the row number queue, and commit it. For submitted data, generate corresponding indexes in system memory, including: The system filters data in memory based on data submission status, and generates corresponding indexes for data with a submission status of "submitted" in the system memory. Clear the indexes generated by the rollback data, including: Use the data identifier of data whose commit status is rollback as the data identifier to be cleared; Search for the indexes that contain the identifier of the data to be cleared in the existing indexes and select them as the indexes to be cleared; Delete the last row number of the index to be cleared; If the data identifier is empty after deleting the last row number of the index to be cleared, then the index to be cleared will be cleared.
6. The system according to claim 5, characterized in that, The system also includes: The submission module is also used to submit the data in the system memory when the data in the system memory does not exceed a preset threshold and a transaction submission operation is received.