CN110457181B - Log optimization analysis method and device for database - Google Patents
Log optimization analysis method and device for database Download PDFInfo
- Publication number
- CN110457181B CN110457181B CN201910713092.0A CN201910713092A CN110457181B CN 110457181 B CN110457181 B CN 110457181B CN 201910713092 A CN201910713092 A CN 201910713092A CN 110457181 B CN110457181 B CN 110457181B
- Authority
- CN
- China
- Prior art keywords
- log
- database
- capturing
- optimization analysis
- archive
- 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.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
- G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3051—Monitoring arrangements for monitoring the configuration of the computing system or of the computing system component, e.g. monitoring the presence of processing resources, peripherals, I/O links, software programs
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3065—Monitoring arrangements determined by the means or processing involved in reporting the monitored data
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR 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; CALCULATING OR 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
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Abstract
The invention relates to the technical field of database log analysis, and discloses a log optimization analysis method of a database, which is used for analyzing logs of a database container containing a plurality of plug-in databases and comprises the following steps: creating a plurality of log capturing threads which are equal to the plug-in database in number and correspond to each other one by one, and creating virtual log capturing threads; capturing an archive log of the database container by the virtual log capture thread; identifying attributions of all log records in the archive log, and delivering all log records to log capturing threads to which the log records belong; each log capturing thread analyzes the corresponding log record. The method has the technical effects of avoiding redundant reading of the log, reducing the read-write pressure of the disk and accelerating the synchronous efficiency of the database.
Description
Technical Field
The invention relates to the technical field of database log analysis, in particular to a method and a device for log optimization analysis of a database.
Background
Oracle as database software has a very wide range of applications, with Oracle12c as its newer version, proposing the concepts of CDB and PDB. The CDB, which is collectively referred to as Container Database, a database container, is composed of a plurality of PDBs and is uniformly managed. The PDB is called Pluggable Database, namely a plug-in database, which is taken as a part of a database container, can enter and exit from the database container at any time, and does not influence the external service provided by the database. Due to the introduction of such different database types, conventional database log analysis methods are not well suited. In the conventional database log analysis method, an independent log analysis thread is provided for each database, if a plurality of PDBs exist in one CDB, the log analysis thread of each PDB is required to read and analyze the log corresponding to the PDB, and the archive log which is actually analyzed is the same, which causes the waste of resources and reduces the log analysis efficiency, especially if one CDB has a plurality of PDBs. Since the CDB is composed of a plurality of PDBs, and the log management system thereof only aims at the CDB, in the common database log capturing modules, one log capturing module is required to be provided for each PDB to read and analyze the CDB, and the log capturing module is deleted in the analysis process. However, such a log capturing manner has a very prominent problem that redundant reading is performed, and only after all logs are read, each capturing module can filter out a part which does not belong to itself; in such a design, the pressure of disk reading and writing is caused, which is unfavorable for the operation of the server and the database synchronization efficiency.
Disclosure of Invention
The invention aims to overcome the technical defects, and provides a method and a device for optimizing and analyzing a database log, which solve the technical problems that in the prior art, redundant reading is performed during database container log analysis, so that disk read-write pressure is caused, and the operation of a server is not facilitated.
In order to achieve the above technical objective, the technical solution of the present invention provides a method for optimizing and analyzing logs of a database container including a plurality of plug-in databases, comprising the following steps:
creating a plurality of log capturing threads which are equal to the plug-in database in number and correspond to each other one by one, and creating virtual log capturing threads;
capturing an archive log of the database container by the virtual log capture thread;
identifying attributions of all log records in the archive log, and delivering all log records to log capturing threads to which the log records belong;
each log capturing thread analyzes the corresponding log record.
The invention also provides a log optimization analysis device of the database, which comprises a processor and a memory, wherein the memory is stored with a computer program, and the log optimization analysis method of the database is realized when the computer program is executed by the processor.
The invention also provides a computer storage medium on which a computer program is stored, which when executed by a processor, implements a method for log optimization analysis of the database.
Compared with the prior art, the invention has the beneficial effects that: according to the method, a virtual log capturing thread is firstly created, an archive log is specially read, then log records are delivered to corresponding log capturing threads, and the log capturing thread of each plug-in database analyzes the log records of the plug-in database. The method has the advantages that the virtual log capturing thread is used for realizing the reading of the archive log and the distribution and delivery of the log records, reducing the redundant reading of the log, reducing the disk pressure and accelerating the database synchronization efficiency.
Drawings
FIG. 1 is a flowchart of an embodiment of a method for log optimization analysis of a database according to the present invention;
FIG. 2 is a prior art plug-in database log management schematic;
FIG. 3 is a diagram of log reading during log analysis in the prior art;
fig. 4 is a schematic diagram of log reading allocation according to an embodiment of the method for log optimization analysis of a database provided by the present invention.
Detailed Description
The present invention will be described in further detail with reference to the drawings and examples, in order to make the objects, technical solutions and advantages of the present invention more apparent. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention.
Example 1
As shown in fig. 1, embodiment 1 of the present invention provides a log optimization analysis method of a database for analyzing a log of a database container including a plurality of plug-in databases, comprising the steps of:
s1, creating a plurality of log capturing threads which are equal to the plug-in database in number and correspond to each other one by one, and creating virtual log capturing threads;
s2, capturing an archive log of the database container through the virtual log capturing thread;
s3, identifying attributions of all log records in the archive log, and delivering all log records to log capturing threads to which the log records belong;
s4, each log capturing thread analyzes the corresponding log record.
The database container is hereinafter referred to as CDB, and the plug-in database is hereinafter referred to as PDB. As shown in fig. 2, there may be multiple PDBs in the CDB, with one ROOT container pdb$root, one SEED container pdb$seed, and multiple PDBs. All PDBs share a hardware system resource. The ROOT container pdb$ ROOT is used to uniformly manage each PDB, and the SEED container pdb$ SEED exists as a template for the newly inserted PDB, i.e., NEW PDB. The PDB is used to store a database, which can be plugged in or plugged out. The PDB has individual database data files, but its archive log is managed uniformly by the CDB and is stored in the same log file.
Since the CDB is composed of multiple PDBs, and the log management system thereof only aims at the CDB, an existing database log capturing module is adopted, and one log capturing thread must be provided for each PDB to read and analyze the CDB, and the log capturing thread is deleted in the analysis process, as shown in fig. 3.
As shown in fig. 4, according to the characteristic that multiple plug-in databases in the prior art share the same database log, n log capturing threads PDB (1), PDB (2), PDB (n) and PDB (n) are created for n plug-in databases, and meanwhile, a log capturing module PDB (n+1) is virtualized and is specially used for reading archive logs; the virtual log capturing thread PDB (n+1) is responsible for reading the archive log in the disk, and delivering the log record to the corresponding log capturing thread. The log capturing thread of each PDB only receives the log records of the PDB and only analyzes the log records of the PDB. According to the method, redundant reading of the log is reduced firstly, IO pressure of the disk is reduced secondly, and data synchronization efficiency of the database is improved.
Preferably, as shown in fig. 4, the attribution of each log record in the archive log is identified, specifically:
and carrying out attribution identification according to the identification number ID of the plug-in database recorded in each log record.
The virtual log capturing module is responsible for reading the archive log in the disk, identifying the attribution of the current log record according to the identification number ID of the PDB, and delivering the archive log to the log capturing thread of the PDB which is the same as the identification number ID of the PDB of the current log record.
Preferably, a plurality of log capturing threads which are equal in number and in one-to-one correspondence with the plug-in databases are created, specifically:
reading the configuration file of the database container, acquiring configuration information of the database container, acquiring the archive file position of the database container according to the configuration information of the database container, and creating the virtual log capturing thread according to the archive file position of the database container.
Reading configuration files and acquiring configuration information of all PDBs; and acquiring the PDB archive file position according to the configuration information of the PDB.
And creating n log capturing threads according to the archive file positions of the n PDBs, wherein the n log capturing threads are in one-to-one correspondence with the n PDBs.
Preferably, a virtual log capturing thread is created, specifically:
and reading configuration files of the database containers, obtaining configuration information of each plug-in database, obtaining archive file positions of the plug-in databases according to the configuration information of the plug-in databases, and creating the log capturing thread according to the archive file positions of the plug-in databases.
Reading a configuration file to obtain configuration information of the CDB; and acquiring the CDB archive file position according to the configuration information of the CDB, and creating a virtual log capturing thread which does not correspond to any PDB.
Each log capturing thread analyzes the corresponding log record, specifically:
and restoring the structured data of the log record into executable SQL text according to the table definition at the time of initialization.
Since the log of the database container containing a plurality of plug-in databases contains only changes to database data, no SQL text is contained. Therefore, we need to restore executable SQL text based on the structured information given by the log. The log field structure analyzed contains: redo byte address, record length, system change number, date and time of change, change vector, etc.
Specifically, during data synchronization, the source database loads initial data to the destination database, stores table definitions to be synchronized, reads a log, restores structured data in a log record into executable SQL text according to the table definitions during initialization, and sends the executable SQL text to the destination database, and the destination database executes the restored SQL text to complete a data synchronization process.
Example 2
The embodiment 2 of the invention provides a log optimization analysis device of a database, which comprises a processor and a memory, wherein the memory is stored with a computer program, and when the computer program is executed by the processor, the log optimization analysis method of the database provided by any embodiment is realized.
Specifically, the log optimization analysis method of the database comprises the following steps:
creating a plurality of log capturing threads which are equal to the plug-in database in number and correspond to each other one by one, and creating virtual log capturing threads;
capturing an archive log of the database container by the virtual log capture thread;
identifying attributions of all log records in the archive log, and delivering all log records to log capturing threads to which the log records belong;
each log capturing thread analyzes the corresponding log record.
The log optimization analysis device of the database is used for realizing the log optimization analysis method of the database, so that the log optimization analysis method of the database has the technical effects, and the log optimization analysis device of the database also has the technical effects and is not described in detail herein.
Example 3
Embodiment 3 of the present invention provides a computer storage medium having stored thereon a computer program which, when executed by a processor, implements the log optimization analysis method of a database provided in any of the above embodiments.
Specifically, the log optimization analysis method of the database comprises the following steps:
creating a plurality of log capturing threads which are equal to the plug-in database in number and correspond to each other one by one, and creating virtual log capturing threads;
capturing an archive log of the database container by the virtual log capture thread;
identifying attributions of all log records in the archive log, and delivering all log records to log capturing threads to which the log records belong;
each log capturing thread analyzes the corresponding log record.
The computer storage medium provided by the invention is used for realizing the log optimization analysis method of the database, so that the technical effects of the log optimization analysis method of the database are realized, and the computer storage medium is also provided and is not described in detail herein.
The above-described embodiments of the present invention do not limit the scope of the present invention. Any other corresponding changes and modifications made in accordance with the technical idea of the present invention shall be included in the scope of the claims of the present invention.
Claims (7)
1. A method for log optimization analysis of a database for analyzing a log of a database container containing a plurality of pluggable databases, comprising the steps of:
creating a plurality of log capturing threads which are equal to the pluggable databases in number and in one-to-one correspondence, and creating virtual log capturing threads;
capturing an archive log of the database container by the virtual log capture thread;
identifying attributions of all log records in the archive log, and delivering all log records to log capturing threads to which the log records belong;
each log capturing thread analyzes the corresponding log record.
2. The method for log optimization analysis of a database according to claim 1, wherein the identifying the attribution of each log record in the archive log specifically comprises:
and carrying out attribution identification according to the identification numbers of the pluggable databases recorded in the log records.
3. The method for log optimization analysis of a database according to claim 1, wherein creating a plurality of log capturing threads which are equal to the number of pluggable databases and correspond to each other one by one is specifically:
reading the configuration file of the database container, acquiring configuration information of the database container, acquiring the archive file position of the database container according to the configuration information of the database container, and creating the virtual log capturing thread according to the archive file position of the database container.
4. The method for log optimization analysis of a database according to claim 1, wherein creating a virtual log capture thread is specifically:
and reading configuration files of the database containers, obtaining configuration information of each pluggable database, obtaining archive file positions of the pluggable databases according to the configuration information of the pluggable databases, and creating the log capturing thread according to the archive file positions of the pluggable databases.
5. The method for log optimization analysis of a database according to claim 1, wherein each log capturing thread parses a corresponding log record, specifically:
and restoring the structured data of the log record into executable SQL text according to the table definition at the time of initialization.
6. A log optimization analysis device of a database, characterized by comprising a processor and a memory, wherein the memory stores a computer program, and the computer program, when executed by the processor, implements the log optimization analysis method of the database according to any one of claims 1 to 5.
7. A computer storage medium having stored thereon a computer program which, when executed by a processor, implements a method of log optimization analysis of a database according to any of claims 1-5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910713092.0A CN110457181B (en) | 2019-08-02 | 2019-08-02 | Log optimization analysis method and device for database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910713092.0A CN110457181B (en) | 2019-08-02 | 2019-08-02 | Log optimization analysis method and device for database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110457181A CN110457181A (en) | 2019-11-15 |
| CN110457181B true CN110457181B (en) | 2023-05-16 |
Family
ID=68484788
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910713092.0A Active CN110457181B (en) | 2019-08-02 | 2019-08-02 | Log optimization analysis method and device for database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110457181B (en) |
Citations (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103678718A (en) * | 2013-12-31 | 2014-03-26 | 金蝶软件（中国）有限公司 | Database synchronization method and system |
| CN106415536A (en) * | 2014-03-10 | 2017-02-15 | 甲骨文国际公司 | Instantaneous unplug of pluggable database from one container database and plug into another container database |
| CN107357883A (en) * | 2017-06-30 | 2017-11-17 | 北京奇虎科技有限公司 | Data migration method and device |
| CN107391720A (en) * | 2017-07-31 | 2017-11-24 | 郑州云海信息技术有限公司 | A kind of data summarization method and device |
| CN107766408A (en) * | 2017-08-31 | 2018-03-06 | 西安交大捷普网络科技有限公司 | The storage method of audit log |
| CN107918621A (en) * | 2016-10-10 | 2018-04-17 | 阿里巴巴集团控股有限公司 | Daily record data processing method, device and operation system |
| CN108255623A (en) * | 2018-01-22 | 2018-07-06 | 深圳市卓讯信息技术有限公司 | Realize the data exchange monitoring method and device of thread inside and outside dual reconciliation mechanism |
| CN109241174A (en) * | 2018-06-26 | 2019-01-18 | 东软集团股份有限公司 | Method of data synchronization, device, readable storage medium storing program for executing and electronic equipment |
| CN109271448A (en) * | 2018-09-25 | 2019-01-25 | 武汉达梦数据库有限公司 | It is the data synchronous system and method for platform based on database |
| CN109656934A (en) * | 2018-11-19 | 2019-04-19 | 武汉达梦数据库有限公司 | Source oracle database DDL synchronous method and equipment based on log parsing |
| CN110019498A (en) * | 2017-08-14 | 2019-07-16 | 北京京东尚科信息技术有限公司 | Log synchronous method and device, storage medium, electronic equipment |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9239763B2 (en) * | 2012-09-28 | 2016-01-19 | Oracle International Corporation | Container database |
| EP3365808B1 (en) * | 2015-10-23 | 2021-08-25 | Oracle International Corporation | Proxy databases |
| WO2017070580A1 (en) * | 2015-10-23 | 2017-04-27 | Oracle International Corporation | Ability to group multiple container databases as a single container database cluster |
| US10742748B2 (en) * | 2016-08-12 | 2020-08-11 | Oracle International Corporation | System and method for supporting live addition of a tenant in a connection pool environment |
-
2019
- 2019-08-02 CN CN201910713092.0A patent/CN110457181B/en active Active
Patent Citations (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103678718A (en) * | 2013-12-31 | 2014-03-26 | 金蝶软件（中国）有限公司 | Database synchronization method and system |
| CN106415536A (en) * | 2014-03-10 | 2017-02-15 | 甲骨文国际公司 | Instantaneous unplug of pluggable database from one container database and plug into another container database |
| CN107918621A (en) * | 2016-10-10 | 2018-04-17 | 阿里巴巴集团控股有限公司 | Daily record data processing method, device and operation system |
| CN107357883A (en) * | 2017-06-30 | 2017-11-17 | 北京奇虎科技有限公司 | Data migration method and device |
| CN107391720A (en) * | 2017-07-31 | 2017-11-24 | 郑州云海信息技术有限公司 | A kind of data summarization method and device |
| CN110019498A (en) * | 2017-08-14 | 2019-07-16 | 北京京东尚科信息技术有限公司 | Log synchronous method and device, storage medium, electronic equipment |
| CN107766408A (en) * | 2017-08-31 | 2018-03-06 | 西安交大捷普网络科技有限公司 | The storage method of audit log |
| CN108255623A (en) * | 2018-01-22 | 2018-07-06 | 深圳市卓讯信息技术有限公司 | Realize the data exchange monitoring method and device of thread inside and outside dual reconciliation mechanism |
| CN109241174A (en) * | 2018-06-26 | 2019-01-18 | 东软集团股份有限公司 | Method of data synchronization, device, readable storage medium storing program for executing and electronic equipment |
| CN109271448A (en) * | 2018-09-25 | 2019-01-25 | 武汉达梦数据库有限公司 | It is the data synchronous system and method for platform based on database |
| CN109656934A (en) * | 2018-11-19 | 2019-04-19 | 武汉达梦数据库有限公司 | Source oracle database DDL synchronous method and equipment based on log parsing |
Non-Patent Citations (1)
| Title |
|---|
| 电网EM平台可插拔式PDB数据库资源池优化设计;杨永娇;《自动化与仪器仪表》;20190731;全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110457181A (en) | 2019-11-15 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108170768B (en) | Database synchronization method, device and readable medium | |
| US9953102B2 (en) | Creating NoSQL database index for semi-structured data | |
| CN109460349B (en) | Test case generation method and device based on log | |
| CN101719149B (en) | Data synchronization method and device | |
| EP1540527B1 (en) | Method and system for moving data objects using locks | |
| CN103019890B (en) | Block-level disk data protection system and method thereof | |
| CN110879813A (en) | Binary log analysis-based MySQL database increment synchronization implementation method | |
| WO2021051627A1 (en) | Database-based batch importing method, apparatus and device, and storage medium | |
| CN104376082A (en) | Method for importing data in data source file to database | |
| WO2021012868A1 (en) | Transaction rollback method and apparatus, database, system, and computer storage medium | |
| CN111008246A (en) | Database log synchronization method and device, computer equipment and readable storage medium | |
| CN111444192A (en) | Method, device and equipment for generating Hash of global state in block chain type account book | |
| WO2020238860A1 (en) | Distributed file batch processing method and apparatus, and readable storage medium | |
| CN110688361A (en) | Data migration method, electronic device and computer equipment | |
| EP2904520B1 (en) | Reference data segmentation from single to multiple tables | |
| CN114385760A (en) | Method and device for real-time synchronization of incremental data, computer equipment and storage medium | |
| CN111753015B (en) | Data query method and device of payment clearing system | |
| CN110457181B (en) | Log optimization analysis method and device for database | |
| CN113792038A (en) | Method and apparatus for storing data | |
| CN108959497A (en) | distributed file system log processing method, device, equipment and storage medium | |
| CN110309206B (en) | Order information acquisition method and system | |
| CN112965939A (en) | File merging method, device and equipment | |
| CN111581227A (en) | Event pushing method and device, computer equipment and storage medium | |
| CN106802922B (en) | Tracing storage system and method based on object | |
| CN112131291A (en) | JSON data-based structured analysis method, device, equipment and storage medium |
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 | ||
| CB02 | Change of applicant information |
Address after: 430000 16-19 / F, building C3, future technology building, 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan, Hubei Province Applicant after: Wuhan dream database Co.,Ltd. Address before: 430000 16-19 / F, building C3, future technology building, 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan, Hubei Province Applicant before: WUHAN DAMENG DATABASE Co.,Ltd. |
|
| CB02 | Change of applicant information | ||
| CB03 | Change of inventor or designer information |
Inventor after: Sun Feng Inventor after: Chen Yan Inventor after: Peng Qingsong Inventor after: Liu Qichun Inventor before: Sun Feng Inventor before: Fu Quan Inventor before: Chen Yan Inventor before: Peng Qingsong Inventor before: Liu Qichun |
|
| CB03 | Change of inventor or designer information | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |