CN111400321B - ORACLE database-based method for automatically recovering high water level - Google Patents
ORACLE database-based method for automatically recovering high water level Download PDFInfo
- Publication number
- CN111400321B CN111400321B CN202010209104.9A CN202010209104A CN111400321B CN 111400321 B CN111400321 B CN 111400321B CN 202010209104 A CN202010209104 A CN 202010209104A CN 111400321 B CN111400321 B CN 111400321B
- Authority
- CN
- China
- Prior art keywords
- high water
- water level
- database
- level recovery
- recovery
- 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
- 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 high water level based on an ORACLE database, which comprises the following steps: s1: deploying the high water level recovery script to a host computer where the database is located; s2: acquiring information from a host computer where the database is located through a high water level recovery script, and confirming that the host computer is in a state allowing high water level recovery; s3: logging in a database through a high water level recovery script, and recovering the high water level of a table in the database; s4: and (3) reconstructing a table index for the table subjected to high water level recovery to finish high water level recovery of the database. Before the operation of recovering the high water level is required to be executed, the current load and the current time of the host are judged, and the operation is not executed if the execution requirement is not met, so that the influence on the performance of the database is small; only the script is deployed on the current host computer of the database, and the script is automatically maintained after the setting is completed, so that the dependence on people is reduced, and misoperation is reduced.
Description
Technical Field
The invention relates to a database high water level recovery method, in particular to a method for automatically recovering high water level based on an ORACLE database.
Background
The architecture of the ORACLE database system from early several servers to huge data centers nowadays can not meet the operation and maintenance requirements of the database system by manpower alone, so that the standardized, automated, process optimized and other automated operation and maintenance conceptual elements are more and more important. One of the core problems solved by automated operation and maintenance is the performance problem. Performance problems are large and poor, and applications that are not good perform well, often presenting symptoms that are slow, all business people or clients have to wait.
HWM (High Water Mark) is an ORACLE database segment level concept, and the presence of a high-level line affects the performance of the ORACLE database. Because the high water line will only grow and not drop when the table is only DML (e.g., delete, insert) operated. Specifically, since the delete statement used by the program does not reclaim the space used, the data is deleted but the high water line is not lowered and still at the previous water level. As data is inserted and deleted, the high-level pipeline grows, the used space in the tablespace file grows gradually, and after all the free space is used, the tablespace file increases in size at the operating system level until the maximum scalable size is reached, as shown in FIG. 1. Since the ORACLE select statement scans all blocks below the high-water line, the execution efficiency of the statement must be affected when there are too many blocks allocated without data. While lowering the high water level increases the scanning efficiency of the meter.
Lowering the high water line may reclaim blocks below the high water level that have not stored data by reclaiming the high water level operation kringspace and lowering the high water line. The method can reduce space use and improve query efficiency without influencing data in a table and indexes on the table, but can recover the space in the table space file by recovering high water level operation kringspace which is operation of table and segment level and can not reduce the size of the table space file, and meanwhile, the recovery operation is DDL operation instead of DML operation and cannot be completed by an application program and needs to be manually completed by an administrator.
The existing high water level recovery method is completed manually, the manual execution cannot be performed timely, and the time is relatively long, meanwhile, the high water level is recovered manually, the high water level needs to be reconstructed, more host resources are consumed, and the performance of a database is affected. Therefore, there is a need for improvements to existing database high water recovery methods.
Disclosure of Invention
The technical problem to be solved by the invention is to provide a method for automatically recovering high water level based on an ORACLE database, which can judge whether recovery execution requirements are met 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 high water level based on an ORACLE database, which comprises the following steps: s1: deploying the high water level recovery script to a host computer where the database is located; s2: acquiring information from a host computer where the database is located through a high water level recovery script, and confirming that the host computer is in a state allowing high water level recovery; s3: logging in a database through a high water level recovery script, and recovering the high water level of a table in the database; s4: and (3) reconstructing a table index for the table subjected to high water level recovery to finish high water level recovery of the database.
Further, the step S2 specifically includes: s21: the high water level recovery script extracts current CPU utilization rate information and current time information from the host computer, and obtains 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 larger 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: while satisfying the steps S22 and S23, the high water level reclamation operation is allowed, otherwise, the operation is interrupted and the log is outputted.
Further, the step S3 specifically includes S31: logging in a database through an sqlplus command; s32: adopting a select command, carrying out table inquiry through a view user_tables command, obtaining table information, and exporting the table information; s33: comparing the exported table information to confirm the table needing high water level recovery; s34: and carrying out table reconstruction on the table needing to be subjected to high water level recovery through a table reconstruction command, a movable table and a table collection command, releasing a table space and recovering the high water level.
Further, in the step S32, the obtained table information is filtered and then exported to the saved text; the table information derived after filtering comprises a table name, an affiliated schema, a table number, a table block number, a table initialization size and a table current occupation size.
Further, the step S33 specifically includes: comparing the exported table row number, table block number, table initialization size and table current occupation size 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, the table is indicated to need to be subjected to high water level recovery, and the method specifically comprises the following steps: the number of the table rows 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 the set ratio, and the set ratio is 5.
Further, the step S34 further includes performing table query after recovering the high water level to obtain the number of table rows after recovering the high water level, wherein the number of table rows after recovering the high water level is smaller than the original number of table rows, and confirming that the high water level recovery of the table is successful.
Further, the step S4 specifically includes: and reconstructing table indexes of the tables after high water level recovery through index rebuild commands, and outputting logs after high water level recovery of all the tables is completed.
Compared with the prior art, the invention has the following beneficial effects: according to the ORACLE database-based automatic high water level recovery method, before the high water level recovery operation is required to be executed, the current load and the current time of the host computer are judged, the execution requirement is not met, the operation is not executed, the influence on the performance of the database is small, and the performance of the database is kept at an efficient position; the script is only required to be deployed on the current host computer of the database, the script is automatically maintained after the setting is completed, the dependency on hardware and a system is small, and the corresponding output log is arranged, so that the execution dynamics can be clearly known, the dependency on people is reduced, the misoperation is reduced, and the time cost of manual operation is reduced.
Drawings
FIG. 1 is a graph of high water line versus tablespace;
FIG. 2 is a flowchart of a method for automatically recovering a high water level based on an ORACLE database in an embodiment of the invention.
Detailed Description
The invention is further described below with reference to the drawings and examples.
FIG. 2 is a flowchart of a method for automatically recovering a high water level based on an ORACLE database in an embodiment of the invention.
Referring to fig. 2, the method for automatically recovering a high water level based on an ORACLE database according to the embodiment of the invention includes the following steps:
s1: and deploying the high water level recycling script to a host computer where the database is located.
S2: and acquiring information from the host computer where the database is located through the high water level recovery script, and confirming that the host computer is in a state allowing high water level recovery.
Extracting current CPU utilization rate information and current time information from a host computer through a high water level recovery script, and obtaining 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 larger than the set idle rate and the current time is in the low service time domain, allowing high water level recovery operation, otherwise, interrupting operation and outputting a log. And automatically judging whether the CPU idle rate allows high water level recovery operation or not, and automatically judging whether the CPU idle rate allows the high water level recovery operation or not at a low peak period of the service, and performing the high water level recovery operation at a proper time, so that the risk of operation is reduced, and the time cost is reduced.
S3: and logging in the database through the high water level recovery script, and recovering the high water level of the table in the database.
Firstly, logging in a database through an sqlplus command; then, a select command is adopted, table inquiry is carried out through a view user_tables command, table information is obtained, and the obtained table information is filtered and then is exported to a stored text, so that comparison operation is facilitated; the list information derived after filtering comprises list names, schemes, list rows, list block numbers, list initialization sizes and list current occupation sizes; comparing the exported table row number, table block number, table initialization size and table current occupation size 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, the table is indicated to need to be subjected to high water level recovery, and the method specifically comprises the following steps: the number of the table rows 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 the set ratio, and the set ratio is 5.
And carrying out table reconstruction on the table needing to be subjected to high water level recovery through a table reconstruction command, a movable table and a table collection command, releasing a table space, recovering the high water level, carrying out table inquiry after recovering the high water level to obtain the number of table rows after the high water level recovery, wherein the number of table rows after the high water level recovery is smaller than the original number of table rows, and confirming that the high water level recovery of the table is successful.
S4: and (3) reconstructing a table index for the table subjected to high water level recovery to finish high water level recovery of the database.
And reconstructing table indexes of the tables after high water level recovery through index rebuild commands, and outputting logs after high water level recovery of all the tables is completed. Because the move operation causes the row identifier rowid of the table data to change, the corresponding index will also fail, and in order not to affect the database query performance, the index rebuild is performed online at a low load time, so that the influence is minimized as much as possible.
In summary, in the method for automatically recovering a high water level based on the ORACLE database according to the embodiment of the invention, before the operation of recovering the high water level is required to be executed, the current load and the current time of the host computer are judged, and if the execution requirement is not met, the operation is not executed, the influence on the performance of the database is small, and the performance of the database is kept at an efficient position; the script is only required to be deployed on the current host computer of the database, the script is automatically maintained after the setting is completed, the dependency on hardware and a system is small, and the corresponding output log is arranged, so that the execution dynamics can be clearly known, the dependency on people is reduced, the misoperation is reduced, and the time cost of manual operation is reduced.
While the invention has been described with reference to the preferred embodiments, it is not intended to limit the invention thereto, and it is to be understood that other modifications and improvements may be made by those skilled in the art without departing from the spirit and scope of the invention, which is therefore defined by the appended claims.
Claims (6)
1. The method for automatically recovering the high water level based on the ORACLE database is characterized by comprising the following steps of:
s1: deploying the high water level recovery script to a host computer where the database is located;
s2: acquiring information from a host computer where the database is located through a high water level recovery script, and confirming that the host computer is in a state allowing high water level recovery;
s3: logging in a database through a high water level recovery script, and recovering the high water level of a table in the database;
s4: rebuilding a table index for the table subjected to high water level recovery to finish high water level recovery of the database;
the step S2 specifically includes:
s21: the high water level recovery script extracts current CPU utilization rate information and current time information from the host computer, and obtains 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 larger 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: while satisfying the steps S22 and S23, the high water level reclamation operation is allowed, otherwise, the operation is interrupted and the log is outputted.
2. The method for automatically recovering high water level based on ORACLE database according to claim 1, wherein said step S3 specifically comprises:
s31: logging in a database through an sqlplus command;
s32: adopting a select command, carrying out table inquiry through a view user_tables command, obtaining table information, and exporting the table information;
s33: comparing the exported table information to confirm the table needing high water level recovery;
s34: and carrying out table reconstruction on the table needing to be subjected to high water level recovery through a table reconstruction command, a movable table and a table collection command, releasing a table space and recovering the high water level.
3. The method for automatically retrieving high water levels based on the ORACLE database according to claim 2, wherein the step S32 filters the acquired table information and then exports the filtered table information to the saved text; the table information derived after filtering comprises a table name, an affiliated schema, a table number, a table block number, a table initialization size and a table current occupation size.
4. The method for automatically recovering high water level based on ORACLE database as recited in claim 3, wherein said step S33 specifically comprises: comparing the exported table row number, table block number, table initialization size and table current occupation size 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, the table is indicated to need to be subjected to high water level recovery, and the method specifically comprises the following steps: the number of the table rows 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 the set ratio, and the set ratio is 5.
5. The method for automatically recovering high water level based on ORACLE database according to claim 2, wherein said step S34 further comprises performing table query after recovering high water level to obtain the number of table rows after high water level recovery, wherein the number of table rows after high water level recovery is smaller than the original number of table rows, and confirming that high water level recovery of the table is successful.
6. The method for automatically recovering high water level based on ORACLE database according to claim 1, wherein said step S4 specifically comprises: and reconstructing table indexes of the tables after high water level recovery through index rebuild commands, and outputting logs after high water level recovery of all the tables is completed.
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 CN111400321A (en) | 2020-07-10 |
| CN111400321B true 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) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116049146B (en) * | 2023-02-13 | 2023-09-01 | 北京优特捷信息技术有限公司 | Database fault processing method, device, equipment and storage medium |
| CN117056359B (en) * | 2023-10-09 | 2024-01-09 | 宁波银行股份有限公司 | 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高水位线的解决方法.通讯世界.2015,(19),全文. * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111400321A (en) | 2020-07-10 |
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 | |
| CN111400321B (en) | ORACLE database-based method for automatically recovering high water level | |
| CN100495400C (en) | Indexes on-line updating method of full text retrieval system | |
| CN1184561C (en) | Method, device and program for structure actuation environment and computer readable medium | |
| CN106815326B (en) | System and method for detecting consistency of data table without main key | |
| CN110515764B (en) | System and method for cloud database backup and cross-cloud recovery | |
| CN101356527A (en) | File system dump/restore by node numbering | |
| CN107545015B (en) | Processing method and processing device for query fault | |
| US20140156603A1 (en) | Method and an apparatus for splitting and recovering data in a power system | |
| US8560499B2 (en) | Data reduction for optimizing and testing | |
| CN111737227B (en) | Data modification method and system | |
| US6901418B2 (en) | Data archive recovery | |
| CN110543621A (en) | multi-format result document analysis system of aviation detection equipment and use method thereof | |
| CN112835918A (en) | MySQL database increment synchronization implementation method | |
| CN104063468A (en) | Data report automatic generating and extracting method and device | |
| CN112214453A (en) | Large-scale industrial data compression storage method, system and medium | |
| CN112084246B (en) | Financial software information acquisition method | |
| CN103678041A (en) | Incremental backup method and system | |
| CN111694853B (en) | Data increment collection method and device based on lineage, storage medium and electronic equipment | |
| CN110399142B (en) | Method and system for isolating gray scale from production environment version | |
| CN107291574B (en) | Backup data recovery primary key generation method based on interpretation system | |
| CN106126375B (en) | A kind of each version restoration methods of YAFFS2 file based on Hash | |
| CN108090095B (en) | Method and device for reconstructing database in batches | |
| CN1193307C (en) | Method and device of using database |
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 |