CN111400321A - Method for automatically recycling high water level based on ORAC L E database - Google Patents
Method for automatically recycling high water level based on ORAC L E database Download PDFInfo
- Publication number
- CN111400321A CN111400321A CN202010209104.9A CN202010209104A CN111400321A CN 111400321 A CN111400321 A CN 111400321A CN 202010209104 A CN202010209104 A CN 202010209104A CN 111400321 A CN111400321 A CN 111400321A
- Authority
- CN
- China
- Prior art keywords
- water level
- high water
- database
- orac
- information
- 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.)
- Granted
Links
Images
Classifications
-
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2272—Management thereof
-
- 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/21—Design, administration or maintenance of databases
-
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- 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/24—Querying
- G06F16/245—Query processing
- G06F16/24569—Query processing with adaptation to specific hardware, e.g. adapted for using GPUs or SSDs
-
- 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/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
-
- 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
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)
- Software Systems (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method for automatically recovering a high water level based on an ORAC L E database, which comprises the following steps of S1, deploying a high water level recovery script to a host where the database is located, S2, obtaining information from the host where the database is located through the high water level recovery script, confirming that the host is in a state of allowing high water level recovery, S3, logging in the database through the high water level recovery script, recovering the high water level of a table in the database, and S4, conducting table rebuilding index on the table subjected to the high water level recovery, and completing the high water level recovery of the database.
Description
Technical Field
The invention relates to a method for recovering a high water level of a database, in particular to a method for automatically recovering the high water level based on an ORAC L E database.
Background
The ORAC L E database system has a structure from a plurality of initial servers to a huge data center, the operation and maintenance requirements of the database system cannot be met by people alone, so that the automation operation and maintenance concept elements such as standardization, automation, process optimization and the like are more and more emphasized by people.
HWM (high Water Mark) is a concept of ORAC L E database segment level, the presence of high Water line affects the performance of ORAC L E database because high Water line only grows and does not degrade when tables are operated with only DM L (e.g., delete, insert). specifically, because delete statements used by programs do not reclaim used space, data is deleted but high Water line does not degrade and remains at the previous level, high Water line is growing, used space in tablespace files gradually increases as data is inserted and deleted, when all free space is used, the size of tablespace file increases at the operating system level until the maximum size is reached, as shown in FIG. 1. because select statement of ORAC L E scans all blocks below high Water line, and when there are too many blocks allocated without data, the execution efficiency of the statement is necessarily affected.
The method can reduce space usage and improve query efficiency, and has no influence on data in a table and indexes on the table, but the method can release space in a table space file by recovering the high water level operation shock space, and meanwhile, the recovery operation is DD L operation instead of DM L operation, cannot be completed by an application program and needs to be manually completed by an administrator.
The existing high water level recovery methods are manually completed, manual execution cannot be timely processed, time is consumed, meanwhile, the high water level is manually recovered, the high water level needs to be rebuilt, more host resources are consumed, and the performance of a database is also affected. Therefore, there is a need for an improvement of existing database high water level recovery methods.
Disclosure of Invention
The invention aims to provide a method for automatically recovering a high water level based on an ORAC L E database, which can judge whether the high water level meets the recovery execution requirement and reduce misoperation and performance influence on the database.
The technical scheme adopted by the invention for solving the technical problems is to provide a method for automatically recovering a high water level based on an ORAC L E database, which comprises the following steps of S1, deploying a high water level recovery script to a host where the database is located, S2, confirming that the host is in a state of allowing high water level recovery by acquiring information from the host where the database is located through the high water level recovery script, S3, logging in the database through the high water level recovery script to recover the high water level of a table in the database, and S4, conducting table rebuilding index on the table subjected to high water level recovery to complete high water level recovery of the database.
Further, the step S2 specifically includes: s21: extracting current CPU utilization rate information and current time information from the host by the high-water-level recovery script, and obtaining the current CPU idle rate through the CPU utilization rate information; s22: comparing the current CPU idle rate with the set idle rate, and confirming that the current CPU idle rate is greater than the set idle rate; s23: comparing the current time with a set low service time domain, and confirming that the current time is in the low service time domain; s24: satisfying both steps S22 and S23 allows a high water level recovery operation, otherwise, interrupts the operation and outputs a log.
Further, the step S3 specifically includes step S31: logging in a database through an sqlplus command; s32: adopting a select command, carrying out table query through a view user _ tables command, acquiring table information, and exporting the table information; s33: comparing the derived table information, and confirming the table needing high water level recovery; s34: and performing table reconstruction on the table needing high water level recovery through a table reconstruction command, a table moving command and a table receiving command, releasing the table space and recovering the high water level.
Further, in step S32, the acquired table information is filtered and then exported to the saved text; the table information derived after filtering includes the table name, the belonged schema, the table row number, the table block number, the table initialization size and the table current occupation size.
Further, the step S33 specifically includes: comparing the derived number of rows of the table, the number of blocks of the table, the initialized size of the table and the current occupied size of the table to confirm whether the table needs to be subjected to high water level recovery or not; if the comparison result of the table information is one of the following situations, it indicates that the table needs to be subjected to high water level recovery, and the method specifically includes: the number of the table lines is zero, and the current occupied size of the table is larger than the initialized size of the table; the ratio of the number of the table rows to the number of the table blocks is smaller than a set ratio, and the set ratio is 5.
Further, the step S34 includes performing table lookup after the high water level is recovered to obtain the number of table rows after the high water level is recovered, where the number of table rows after the high water level is recovered is smaller than the number of original table rows, and it is determined that the high water level recovery of the table is successful.
Further, the step S4 specifically includes: and reconstructing the table index of the table after high water level recovery through an index rebuild command, and outputting the log after the high water level recovery of all the tables is completed.
Compared with the prior art, the method for automatically recovering the high water level based on the ORAC L E database has the advantages that before the operation of recovering the high water level needs to be executed, the current load and the current time of a host are judged, the operation is not executed if the execution requirement cannot be met, the performance of the database is kept at an efficient position due to small influence on the performance of the database, only a script needs to be deployed on the current host of the database, the script is automatically maintained after the setting is completed, the dependence on hardware and a system is small, and a corresponding output log is arranged, so that the execution dynamics can be clearly known, the dependence on people is reduced, the misoperation is reduced, and the time cost of manual operation is reduced.
Drawings
FIG. 1 is a diagram of a high water line versus a tablespace;
FIG. 2 is a flow chart of a method for automatically recovering high water level based on ORAC L E database according to an embodiment of the present invention.
Detailed Description
The invention is further described below with reference to the figures and examples.
FIG. 2 is a flow chart of a method for automatically recovering high water level based on ORAC L E database according to an embodiment of the present invention.
Referring to fig. 2, the method for automatically recovering high water level based on ORAC L E database according to the embodiment of the present invention includes the following steps:
s1: and deploying the high-water-level recovery script to a host where the database is located.
S2: and acquiring information from a host where the database is located through the high water level recovery script, and confirming that the host is in a state of allowing high water level recovery.
Extracting current CPU utilization rate information and current time information from a host through a high-water level recovery script, and obtaining the current CPU idle rate through the CPU utilization rate information; comparing the current CPU idle rate with the set idle rate, comparing the current time with the set low service time domain, if the current CPU idle rate is greater than the set idle rate and the current time is in the low service time domain, allowing the high water level recovery operation, otherwise, interrupting the operation and outputting the log. Whether the CPU idle rate allows high water level recovery operation is automatically judged, whether the CPU idle rate is in the low peak period of the business is automatically judged, and high water level recovery operation is performed at a proper time, so that the operation risk is reduced, and the time cost is also reduced.
S3: and logging in a database through a high water level recovery script, and performing high water level recovery on tables in the database.
Firstly, logging in a database through an sql plus command; then, a select command is adopted to carry out table query through a view user _ tables command, the acquired table information is filtered and then is led out to the stored text, and comparison operation is convenient to carry out; the table information exported after filtering comprises a table name, the belonged schema, the number of table lines, the number of table blocks, the initialized size of the table and the current occupied size of the table; comparing the derived number of rows of the table, the number of blocks of the table, the initialized size of the table and the current occupied size of the table to confirm whether the table needs to be subjected to high water level recovery or not; if the comparison result of the table information is one of the following situations, it indicates that the table needs to be subjected to high water level recovery, and the method specifically includes: the number of the table lines is zero, and the current occupied size of the table is larger than the initialized size of the table; the ratio of the number of the table rows to the number of the table blocks is smaller than a set ratio, and the set ratio is 5.
And (3) performing table reconstruction on the table needing high water level recovery through a table reconstruction command, a table moving command and a table receiving command, releasing the table space, recovering the high water level, performing table query after the high water level is recovered to obtain the number of table rows after the high water level is recovered, wherein the number of the table rows after the high water level is recovered is less than the number of the original table rows, and confirming that the high water level recovery of the table is successful.
S4: and (4) reconstructing the table index of the table subjected to high water level recovery to complete the high water level recovery of the database.
And reconstructing the table index of the table after high water level recovery through an index rebuild command, and outputting the log after the high water level recovery of all the tables is completed. Because the move operation can cause the row identifier rowid of the table data to change, the corresponding index also fails, and in order not to affect the database query performance, the influence is minimized as much as possible on the online index rebuild at a time with low load.
In summary, according to the method for automatically recovering the high water level based on the ORAC L E database of the embodiment of the present invention, before the operation for recovering the high water level needs to be performed, the current load and the current time of the host are determined, and the requirement for performing cannot be met, the operation is not performed, the influence on the performance of the database is small, so that the performance of the database is maintained at an efficient position, only the script needs to be deployed on the current host of the database, the automatic maintenance is performed after the setting is completed, the dependence on hardware and a system is small, and a corresponding output log is provided, so that the execution dynamics can be clearly understood, the dependence on people is reduced, the misoperation is reduced, and the time cost of manual operation is reduced.
Although the present invention has been described with respect to the preferred embodiments, it will be understood by those skilled in the art that various changes in form and details may be made therein without departing from the spirit and scope of the invention as defined by the appended claims.
Claims (7)
1. A method for automatically recovering high water level based on ORAC L E database is characterized by comprising the following steps:
s1: deploying the high water level recovery script to a host where the database is located;
s2: acquiring information from a host where a database is located through a high water level recovery script, and confirming that the host is in a state of allowing high water level recovery;
s3: logging in a database through a high water level recovery script, and recovering a high water level from a table in the database;
s4: and (4) reconstructing the table index of the table subjected to high water level recovery to complete the high water level recovery of the database.
2. The method for automatically recovering the high water level based on the ORAC L E database of claim 1, wherein the step S2 specifically includes:
s21: extracting current CPU utilization rate information and current time information from the host by the high-water-level recovery script, and obtaining the current CPU idle rate through the CPU utilization rate information;
s22: comparing the current CPU idle rate with the set idle rate, and confirming that the current CPU idle rate is greater than the set idle rate;
s23: comparing the current time with a set low service time domain, and confirming that the current time is in the low service time domain;
s24: satisfying both steps S22 and S23 allows a high water level recovery operation, otherwise, interrupts the operation and outputs a log.
3. The method for automatically recovering the high water level based on the ORAC L E database of claim 1, wherein the step S3 specifically includes:
s31: logging in a database through an sqlplus command;
s32: adopting a select command, carrying out table query through a view user _ tables command, acquiring table information, and exporting the table information;
s33: comparing the derived table information, and confirming the table needing high water level recovery;
s34: and performing table reconstruction on the table needing high water level recovery through a table reconstruction command, a table moving command and a table receiving command, releasing the table space and recovering the high water level.
4. The method for automatically recycling high water level based on ORAC L E database according to claim 3, wherein the obtained table information is filtered and exported to the saved text in step S32, and the table information exported after filtering includes table name, belonging schema, table row number, table block number, table initialization size and table current occupation size.
5. The method for automatically retrieving high water level based on ORAC L E database as claimed in claim 4, wherein the step S33 specifically comprises comparing the derived table row number, table block number, table initialization size and table current occupation size to determine whether the table needs to be retrieved for high water level, and if one of the following conditions exists in the comparison result of table information, the table needs to be retrieved for high water level, which specifically comprises that the table row number is zero and the table current occupation size is larger than the table initialization size, the ratio of the table row number to the table block number is smaller than the set ratio, and the set ratio is 5.
6. The method for automatically retrieving high water level based on ORAC L E database of claim 1, wherein the step S34 further comprises performing table lookup to obtain the number of table rows after high water level retrieval after retrieving high water level, and the number of table rows after high water level retrieval is smaller than the number of original table rows to confirm that the high water level retrieval of the table is successful.
7. The method for automatically retrieving high water level based on ORAC L E database according to claim 1, wherein the step S4 comprises reconstructing the table index for the table after high water level retrieval through index rebuild command, and outputting the log after high water level retrieval for all tables.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010209104.9A CN111400321B (en) | 2020-03-23 | 2020-03-23 | ORACLE database-based method for automatically recovering high water level |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010209104.9A CN111400321B (en) | 2020-03-23 | 2020-03-23 | ORACLE database-based method for automatically recovering high water level |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111400321A true CN111400321A (en) | 2020-07-10 |
| CN111400321B CN111400321B (en) | 2023-05-26 |
Family
ID=71429126
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010209104.9A Active CN111400321B (en) | 2020-03-23 | 2020-03-23 | ORACLE database-based method for automatically recovering high water level |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111400321B (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116049146A (en) * | 2023-02-13 | 2023-05-02 | 北京优特捷信息技术有限公司 | Database fault processing method, device, equipment and storage medium |
| CN117056359A (en) * | 2023-10-09 | 2023-11-14 | 宁波银行股份有限公司 | Table reconstruction method and device, electronic equipment and storage medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JPH10214211A (en) * | 1997-01-29 | 1998-08-11 | Tec Corp | Device and method for reorganizing database |
| CN101493842A (en) * | 2009-02-20 | 2009-07-29 | 中兴通讯股份有限公司 | Method for deleting data in bulk |
| CN104699807A (en) * | 2015-03-23 | 2015-06-10 | 上海新炬网络信息技术有限公司 | Automatic monitoring and expansion method for ORACLE data table space |
-
2020
- 2020-03-23 CN CN202010209104.9A patent/CN111400321B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JPH10214211A (en) * | 1997-01-29 | 1998-08-11 | Tec Corp | Device and method for reorganizing database |
| CN101493842A (en) * | 2009-02-20 | 2009-07-29 | 中兴通讯股份有限公司 | Method for deleting data in bulk |
| CN104699807A (en) * | 2015-03-23 | 2015-06-10 | 上海新炬网络信息技术有限公司 | Automatic monitoring and expansion method for ORACLE data table space |
Non-Patent Citations (1)
| Title |
|---|
| 廉文超;: "浅谈ORACLE高水位线的解决方法" * |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116049146A (en) * | 2023-02-13 | 2023-05-02 | 北京优特捷信息技术有限公司 | Database fault processing method, device, equipment and storage medium |
| CN116049146B (en) * | 2023-02-13 | 2023-09-01 | 北京优特捷信息技术有限公司 | Database fault processing method, device, equipment and storage medium |
| CN117056359A (en) * | 2023-10-09 | 2023-11-14 | 宁波银行股份有限公司 | Table reconstruction method and device, electronic equipment and storage medium |
| CN117056359B (en) * | 2023-10-09 | 2024-01-09 | 宁波银行股份有限公司 | Table reconstruction method and device, electronic equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111400321B (en) | 2023-05-26 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106445738B (en) | Database backup method and device | |
| CN110879813B (en) | Binary log analysis-based MySQL database increment synchronization implementation method | |
| EP3782044A1 (en) | Learning etl rules by example | |
| CN111400321A (en) | Method for automatically recycling high water level based on ORAC L E database | |
| CN111737227B (en) | Data modification method and system | |
| CN111737355B (en) | Heterogeneous data source synchronization method and system based on MongoDB metadata management | |
| CN107545015B (en) | Processing method and processing device for query fault | |
| CN106844092B (en) | Method for automatically recovering MariaDB Galera Cluster of power failure | |
| JP6720788B2 (en) | Log management device and log management program | |
| US8560499B2 (en) | Data reduction for optimizing and testing | |
| CN107656971A (en) | A kind of intelligent grid collection Monitoring Data storage method based on Redis | |
| CN108182198B (en) | Control device for storing advanced controller operation data and reading method | |
| CN112835918A (en) | MySQL database increment synchronization implementation method | |
| CN104063468A (en) | Data report automatic generating and extracting method and device | |
| CN116089545B (en) | Method for collecting storage medium change data into data warehouse | |
| CN1243431C (en) | Analysis of universal route platform command lines | |
| CN101183973A (en) | Method of implementing command line configuration distribution in distributed system | |
| CN107342944B (en) | A kind of distribution of ID mark and persistence method and system | |
| CN104750849A (en) | Method and system for maintaining tree structure-based directory relationship | |
| CN107291574B (en) | Backup data recovery primary key generation method based on interpretation system | |
| CN108090095B (en) | Method and device for reconstructing database in batches | |
| CN109634918A (en) | A kind of search method of abnormal document, system, device and readable storage medium storing program for executing | |
| CN104182522B (en) | Secondary indexing method and device on basis of circulation bitmap model | |
| CN110990640B (en) | Data determination method, device, equipment and computer readable storage medium | |
| CN106649444B (en) | Method and device for CAE database system to acquire CAD data |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |