CN113220788A - Snapshot selection method and device based on Oracle database full synchronization - Google Patents
Snapshot selection method and device based on Oracle database full synchronization Download PDFInfo
- Publication number
- CN113220788A CN113220788A CN202110597427.4A CN202110597427A CN113220788A CN 113220788 A CN113220788 A CN 113220788A CN 202110597427 A CN202110597427 A CN 202110597427A CN 113220788 A CN113220788 A CN 113220788A
- Authority
- CN
- China
- Prior art keywords
- scn
- snapshot
- parsing module
- log
- data recorded
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/18—File system types
- G06F16/1805—Append-only file systems, e.g. using logs or journals to store data
- G06F16/1815—Journaling file systems
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24553—Query execution of query operations
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a snapshot selecting method and a device based on full synchronization of an Oracle database, wherein the method comprises the following steps: step S1, when the synchronous table operation is performed, an SCN query request is sent to the log parsing module to obtain the SCN required by the snapshot; step S2, when receiving the SCN query request, the log parsing module scans the data recorded by the log parsing module at the current time point, searches whether there is an operation of the relevant table, and determines the SCN required for the snapshot according to the query result of whether there is an operation of the relevant table.
Description
Technical Field
The invention relates to the technical field of computer data backup, in particular to a snapshot selection method and device based on full synchronization of an Oracle database.
Background
The Oracle database is computer software widely used, and database synchronization software is often deployed in a production environment to meet requirements of disaster tolerance, read-write separation and the like, so that a source library is synchronized to a target library in real time. The data of the source library changes in real time, and the target library is updated in real time. The database synchronization comprises two synchronization stages of full volume and increment, wherein the full volume synchronization is to synchronize all data at a certain time point to a target end; the incremental synchronization is to synchronize each change to the target side based on the full synchronization.
At present, in the full synchronization process, a table locking operation is firstly performed, the table locking is to eliminate the interference of transactions, a time point of no transaction of the table is obtained, then table data is obtained by taking the time point as a snapshot, and incremental loading is also taken as a filtering condition, so that the data consistency of a source and a target is achieved without fault tolerance. However, the lock table means that there is a risk of affecting the user traffic. Although there are no other actions in the 3 steps of locking table, acquiring SCN (System Change Number) and unlocking, and there is no waiting between actions, in a busy System, the effect of the action of locking table may exceed our imagination, and trying to lock table may result in deadlock, and business software is jammed.
Disclosure of Invention
In order to overcome the defects in the prior art, the invention aims to provide a snapshot selection method and a snapshot selection device based on full synchronization of an Oracle database, so as to achieve the purpose of snapshot selection of the Oracle database without locking table full synchronization.
In order to achieve the above object, the present invention provides a snapshot selecting method based on full synchronization of Oracle database, comprising the following steps:
step S1, when the synchronous table operation is performed, an SCN query request is sent to the log parsing module to obtain the SCN required by the snapshot;
step S2, when receiving the SCN query request, the log parsing module scans the data recorded by the log parsing module at the current time point, searches whether there is an operation of the relevant table, and determines the SCN required for the snapshot according to the query result of whether there is an operation of the relevant table.
Preferably, in step S2, if there is an operation of the relevant table in the data recorded by the log parsing module, the SCN of the earliest transaction is selected as the snapshot point.
Preferably, if the relevant operation of the table is not found in the data recorded by the log parsing module, the SCN of the last committed transaction related to the table is selected as the snapshot point.
In order to achieve the above object, the present invention further provides a snapshot selecting apparatus based on full synchronization of Oracle database, including:
the query request unit is used for sending an SCN query request to the log analysis module when the synchronous table operation is carried out so as to obtain the SCN required by the snapshot;
and the log analysis module is used for scanning data recorded by the log analysis module at the current time point when receiving the SCN query request, searching whether the operation of the relevant table exists or not, and determining the SCN required by the snapshot according to the query result of whether the operation of the relevant table exists or not.
Preferably, if there is an operation of the relevant table in the data recorded by the log parsing module, the SCN of the earliest transaction therein is selected as the snapshot point.
Preferably, if the relevant operation of the table is not found in the data recorded by the log parsing module, the SCN of the last committed transaction related to the table is selected as the snapshot point.
Compared with the prior art, the snapshot selecting method and device based on the full synchronization of the Oracle database send an SCN query request to the log analysis module when the synchronization table operation is carried out, scan the data recorded by the log analysis module, find out whether the operation of the related table exists, select the SCN of the earliest operation as a snapshot point if the operation of the related table exists, and select the SCN of the last submitted transaction as the snapshot point if the operation of the related table does not exist, so that the purpose of the snapshot selecting of the Oracle database without the full synchronization of the lock table is realized.
Drawings
FIG. 1 is a flowchart illustrating the steps of a snapshot selecting method based on full synchronization of an Oracle database according to the present invention;
FIG. 2 is a diagram illustrating the SCN required for determining a snapshot according to an embodiment of the present invention;
fig. 3 is a system structure diagram of a snapshot selecting apparatus based on full synchronization of Oracle database according to the present invention.
Detailed Description
Other advantages and capabilities of the present invention will be readily apparent to those skilled in the art from the present disclosure by describing the embodiments of the present invention with specific embodiments thereof in conjunction with the accompanying drawings. The invention is capable of other and different embodiments and its several details are capable of modification in various other respects, all without departing from the spirit and scope of the present invention.
Before describing the present invention, several related concepts are introduced:
SCN: the SCN is a single and continuous sequence in the database which increases along with the change of the consistency state of the database. Just as a timestamp represents a fixed point in time, each SCN value also represents a consistent point in time for the database during operation, with large SCN values corresponding to transactions that occur later than small SCN values. It is appropriate to say the SCN as a logical timestamp of the Oracle database.
Snapshot: containing results of a query against one or more tables or views on a local or remote database. That is to say, the oracle snapshot corresponds to data at a certain time point. Such as: SELECT FROM FLASH _ TBL AS OF SCN 1257245; this statement indicates that 1257245 is the snapshot to be queried for the data of FLASH _ TBL.
REDOLOG: i.e., ORACLE redo log. REDLOG records the start information of the transaction, the operation of the transaction, and the end information of the transaction and SCN in the ORACLE database.
Fig. 1 is a flowchart illustrating steps of a snapshot selecting method based on full synchronization of an Oracle database according to the present invention. As shown in fig. 1, the snapshot selecting method based on full synchronization of Oracle database of the present invention includes the following steps:
step S1, when performing the synchronization table operation on the Oracle database, sending an SCN query request to the log parsing module to obtain the SCN required by the snapshot.
In the invention, because the log analysis module clearly records the transaction distribution condition analyzed into the log currently, and usually, the difference between the log analysis progress and the service is in the second level, the current service condition is basically reflected, therefore, in practice, when the snapshot is needed, only a closer time point is selected, and real-time is not needed.
Therefore, in the present invention, when the full synchronization module (i.e. full synchronization of the synchronization software) is performing the synchronization table operation, an SCN query request is first sent to the log resolution module to obtain the appropriate SCN.
Step S2, when receiving the SCN query request, the log parsing module scans the data recorded by the log parsing module at the current time point, searches whether there is an operation of the relevant table, and determines the SCN required for the snapshot according to the query result of whether there is an operation of the relevant table.
Specifically, if the data recorded by the log parsing module has operations of related tables, the SCN of the operation of the earliest related table is selected as the snapshot point, and if the related operation of the table is not found in the data (redolog) recorded by the log parsing module at the current time point, the SCN of the last committed transaction related to the table is selected as the snapshot point.
In the embodiment of the present invention, it is assumed that the current fully-synchronized module performs the operation of synchronizing table a, as shown in fig. 2, the transaction distribution situation related to table a is as shown in the figure, and in a busy system, a large number of parallel transactions are running.
(1) As shown in query 1 in fig. 2, if a query request is issued at this point in time, the log parsing module, assuming that the submission time point SCN3 of T3 is the snapshot point, both the modifications b and c of T2 and T3 are visible to the snapshot and will be filtered out by SCN3 at the time of incremental loading because they are smaller than SCN 3; while modification a of T1 is not visible to the snapshot and it is also filtered out by SCN3, then the data of operation a is lost and SCN3 is not appropriate;
if the log parsing module selects the point-in-time SCNa of the earliest a-operation of operation T1 (here, assume a is the earliest one), then the data of T1, T2, T3 is not visible in the snapshot and a, b, c are not filtered out in the delta, so SCNa is the appropriate point-in-time.
(2) As shown for query 2, the situation is slightly different if the query occurs at the location of query 2. There is no modification to table a in existing transactions in the log parsing site, and all of T1, T2, and T3 have acquired complete transaction data, and existing transactions do not include these 3, at this time, if the commit time SCN1 of the last transaction (T1) whose parsing is completed is selected as the snapshot point, the data of T1, T2, and T3 are all visible in the snapshot, and the a, b, and c operations will be filtered out by SCN1 in the incremental load, and the data of the target library is complete, so SCN1 is a suitable time point.
Fig. 3 is a system structure diagram of a snapshot selecting apparatus based on full synchronization of an Oracle database according to the present invention, as shown in fig. 3, and the snapshot selecting apparatus based on full synchronization of an Oracle database according to the present invention includes:
the query request unit 301 is configured to send an SCN query request to the log parsing module to obtain the SCN required by the snapshot when performing the synchronization table operation.
In the invention, because the log analysis module clearly records the transaction distribution condition analyzed into the log currently, and usually, the difference between the log analysis progress and the service is in the second level, the current service condition is basically reflected, therefore, in practice, when the snapshot is needed, only a closer time point is selected, and real-time is not needed.
Therefore, in the present invention, when a module performing full synchronization is performing a synchronization table operation, an SCN query request is first sent to the log parsing module to obtain an appropriate SCN.
The log parsing module 302 is configured to scan data recorded by the log parsing module at a current time point when receiving an SCN query request, find whether there is an operation of a relevant table, and determine an SCN required for a snapshot according to a query result of whether there is an operation of the relevant table.
Specifically, if the data recorded by the log analysis module has operations of a relevant table, the SCN of the earliest operation is selected as the snapshot point, and if the data recorded by the log analysis module does not have the operations of the relevant table, the SCN of the last committed transaction is selected as the snapshot point.
In summary, the snapshot selecting method and device based on full synchronization of the Oracle database in the present invention send an SCN query request to the log parsing module during performing table synchronization operation, scan data recorded by the log parsing module, find out whether there is an operation of a relevant table, select the SCN of the earliest operation as a snapshot point if there is an operation of a relevant table, and select the SCN of the last submitted transaction as a snapshot point if there is no operation of a relevant table, thereby achieving the purpose of selecting a snapshot of the Oracle database without table locking full synchronization.
The foregoing embodiments are merely illustrative of the principles and utilities of the present invention and are not intended to limit the invention. Modifications and variations can be made to the above-described embodiments by those skilled in the art without departing from the spirit and scope of the present invention. Therefore, the scope of the invention should be determined from the following claims.
Claims (6)
1. A snapshot selecting method based on Oracle database full synchronization comprises the following steps:
step S1, when the synchronous table operation is performed, an SCN query request is sent to the log parsing module to obtain the SCN required by the snapshot;
step S2, when receiving the SCN query request, the log parsing module scans the data recorded by the log parsing module at the current time point, searches whether there is an operation of the relevant table, and determines the SCN required for the snapshot according to the query result of whether there is an operation of the relevant table.
2. The method for snapshot selection based on Oracle database full synchronization as claimed in claim 1, wherein in step S2, if there is an operation of the related table in the data recorded by the log parsing module, the SCN of the earliest transaction is selected as the snapshot point.
3. The method of claim 1, wherein if no table-related operation is found in the data recorded by the log parsing module, selecting the SCN of the last transaction submitted in relation to the table as the snapshot point.
4. A snapshot selecting device based on Oracle database full synchronization comprises:
the query request unit is used for sending an SCN query request to the log analysis module when the synchronous table operation is carried out so as to obtain the SCN required by the snapshot;
and the log analysis module is used for scanning data recorded by the log analysis module at the current time point when receiving the SCN query request, searching whether the operation of the relevant table exists or not, and determining the SCN required by the snapshot according to the query result of whether the operation of the relevant table exists or not.
5. The apparatus of claim 4, wherein if there is a table-related operation in the data recorded by the log parsing module, the SCN of the earliest transaction is selected as the snapshot point.
6. The apparatus of claim 4, wherein if no table-related operation is found in the data recorded by the log parsing module, the SCN of the last transaction submitted in relation to the table is selected as the snapshot point.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110597427.4A CN113220788A (en) | 2021-05-31 | 2021-05-31 | Snapshot selection method and device based on Oracle database full synchronization |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110597427.4A CN113220788A (en) | 2021-05-31 | 2021-05-31 | Snapshot selection method and device based on Oracle database full synchronization |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113220788A true CN113220788A (en) | 2021-08-06 |
Family
ID=77099579
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110597427.4A Pending CN113220788A (en) | 2021-05-31 | 2021-05-31 | Snapshot selection method and device based on Oracle database full synchronization |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113220788A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116737696A (en) * | 2023-07-05 | 2023-09-12 | 深圳证券信息有限公司 | Data migration method and related equipment |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100036843A1 (en) * | 2008-08-08 | 2010-02-11 | Oracle International Corporation | Global checkpoint scn |
| US20180341691A1 (en) * | 2015-11-24 | 2018-11-29 | T2 Data Ab | Data synchronization in a distributed data storage system |
| CN110232093A (en) * | 2019-04-30 | 2019-09-13 | 武汉达梦数据库有限公司 | Initialization stowage and equipment in database synchronization based on flashback query |
| CN110413693A (en) * | 2019-07-31 | 2019-11-05 | 上海英方软件股份有限公司 | A kind of method of inspection and device of database real-time synchronization system data consistency |
| CN111198920A (en) * | 2019-12-30 | 2020-05-26 | 上海英方软件股份有限公司 | Method and device for synchronously determining comparison table snapshot based on database |
-
2021
- 2021-05-31 CN CN202110597427.4A patent/CN113220788A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100036843A1 (en) * | 2008-08-08 | 2010-02-11 | Oracle International Corporation | Global checkpoint scn |
| US20180341691A1 (en) * | 2015-11-24 | 2018-11-29 | T2 Data Ab | Data synchronization in a distributed data storage system |
| CN110232093A (en) * | 2019-04-30 | 2019-09-13 | 武汉达梦数据库有限公司 | Initialization stowage and equipment in database synchronization based on flashback query |
| CN110413693A (en) * | 2019-07-31 | 2019-11-05 | 上海英方软件股份有限公司 | A kind of method of inspection and device of database real-time synchronization system data consistency |
| CN111198920A (en) * | 2019-12-30 | 2020-05-26 | 上海英方软件股份有限公司 | Method and device for synchronously determining comparison table snapshot based on database |
Non-Patent Citations (1)
| Title |
|---|
| 黄晓微等: "基于快照日志分析的数据同步方法", 《后勤工程学院学报》 * |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116737696A (en) * | 2023-07-05 | 2023-09-12 | 深圳证券信息有限公司 | Data migration method and related equipment |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US7606838B2 (en) | Distributed conflict resolution for replicated databases | |
| CN109933632B (en) | Data migration method, device and equipment for database | |
| CN110413693B (en) | Method and device for checking data consistency of database real-time synchronization system | |
| EP2891994A1 (en) | Method for achieving automatic synchronization of multisource heterogeneous data resources | |
| US9542468B2 (en) | Database management system and method for controlling synchronization between databases | |
| US20180081956A1 (en) | Method for automatically synchronizing multi-source heterogeneous data resources | |
| WO2016180160A1 (en) | Data snapshot recovery method and apparatus | |
| US20030220935A1 (en) | Method of logical database snapshot for log-based replication | |
| WO2017063520A1 (en) | Method and apparatus for operating database | |
| EP2380090B1 (en) | Data integrity in a database environment through background synchronization | |
| US20070143286A1 (en) | File management method in file system and metadata server therefor | |
| JP2022550049A (en) | Data indexing method in storage engine, data indexing device, computer device and computer program | |
| CN114661816B (en) | Data synchronization method and device, electronic equipment and storage medium | |
| CN116775727B (en) | Data delay statistics method, device, equipment and storage medium | |
| CN113961592A (en) | Data query method, device, device and storage medium based on master-slave database | |
| CN118193632A (en) | Database synchronization optimization method, system, computer equipment and storage medium | |
| CN118193642A (en) | Database data synchronization method, equipment, medium and product | |
| CN113220788A (en) | Snapshot selection method and device based on Oracle database full synchronization | |
| CN113282643B (en) | Table comparison method and system under ORACLE database synchronization environment | |
| CN116186082A (en) | Data summarizing method based on distribution, first server and electronic equipment | |
| CN112800060B (en) | Data processing method, data processing device, computer readable storage medium and electronic equipment | |
| CN118069745A (en) | SQL SERVER increment acquisition method, storage medium and equipment based on transaction log | |
| Coelho et al. | Loom: A closed-box disaggregated database system | |
| CN113434509B (en) | Increment index updating method and device, storage medium and electronic equipment | |
| CN114371870B (en) | Code scanning and submitting method, code scanning server, client and server |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |