CN109241061A - Guard method for oracle database Truncate operation - Google Patents
Guard method for oracle database Truncate operation Download PDFInfo
- Publication number
- CN109241061A CN109241061A CN201811073254.0A CN201811073254A CN109241061A CN 109241061 A CN109241061 A CN 109241061A CN 201811073254 A CN201811073254 A CN 201811073254A CN 109241061 A CN109241061 A CN 109241061A
- Authority
- CN
- China
- Prior art keywords
- truncate
- data
- block
- oracle database
- bitmap
- 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
- 238000000034 method Methods 0.000 title claims abstract description 17
- 230000004224 protection Effects 0.000 claims abstract description 16
- 238000012986 modification Methods 0.000 claims abstract description 12
- 230000004048 modification Effects 0.000 claims abstract description 12
- 238000011017 operating method Methods 0.000 claims description 3
- 238000004321 preservation Methods 0.000 claims description 3
- 238000011084 recovery Methods 0.000 abstract description 7
- 238000012217 deletion Methods 0.000 description 4
- 230000037430 deletion Effects 0.000 description 4
- 238000004064 recycling Methods 0.000 description 3
- 230000000694 effects Effects 0.000 description 2
- 238000012423 maintenance Methods 0.000 description 2
- 238000012790 confirmation Methods 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 238000003672 processing method Methods 0.000 description 1
- XLYOFNOQVPJJNP-UHFFFAOYSA-N water Substances O XLYOFNOQVPJJNP-UHFFFAOYSA-N 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a kind of guard methods for oracle database Truncate operation; include the following steps: S1) when Truncate operation occurs for oracle database; the table name that recording trigger is deleted by Truncate is set, and is saved in beforetruncate table；S2) timing scan beforetruncate table, if there is the table newly deleted, then the paragraph header block of the table is found in inquiry；S3 the relevant id of the table) is modified as the pervious value of Truncate, while generating the reverse script that modification is gone back；S4) after reaching default protection threshold value, the case where reverse script again reverts to mark after Truncate is run, is cleared up by the data that oracle database recycles needs.The invention enables the recoveries of Truncate to become simple and quick, and will not influence normal data and delete.
Description
Technical field
The present invention relates to a kind of oracle database processing methods, more particularly to one kind to be used for oracle database
The guard method of Truncate operation.
Background technique
During Database Systems operation, it frequently occur that
1, data volume increases suddenly, and client is without memory space, it is now desired to clear up memory space, send out by linking up
As soon as the data of existing table can delete completely, then carried out Truncate delete mode (in Oracle using delete compared with
Slowly, a large amount of filing can be generated).After having deleted, discovery data still have effect, need the case where doing back again.
2, operation maintenance personnel accidentally performs Truncate order, has carried out maloperation, data is caused to be erased entirely.
The typically no method of industry can only carry out a prompt police by establishing trigger to avoid such situation at present
It accuses, operation maintenance personnel is allowed to think thrice before acting.But such method not can solve problem, it is extensive after there is Truncate deletion
Complex data is very troublesome:
1, RMAN backup completely+whole filing just can be carried out incomplete recovery of the different machine based on time point,
Data before Truncate are found out, and are then exported, and are imported into pervious database again.If data volume is very
Greatly, this recovery process may need last from days, so that the business that can seriously affect some clients is normally carried out.
2, it does not back up completely and does not also open filing, it, can only be the bottom of by if this kind of data are wanted to give for change by Truncate
Layer recovery tool: such as the tool of DUL, ODU, AUL etc.This restores to be also very troublesome.It operates extremely complex.
Therefore currently without the effective protection to Truncate, once Truncate operation has occurred, data have can
It can thoroughly lose, extremely be taken time and effort can be carried out and restore.
Summary of the invention
Technical problem to be solved by the invention is to provide a kind of protections for oracle database Truncate operation
Method so that the recovery of Truncate becomes simple and quick, and will not influence normal data and delete.
The present invention is to solve above-mentioned technical problem and the technical solution adopted is that provide a kind of for oracle database
The guard method of Truncate operation, includes the following steps: S1) when Truncate operation occurs for oracle database, setting
Trigger；The table name that the recording trigger is deleted by Truncate, and by the information preservation of record to beforetruncate
In table；S2) timing scan beforetruncate table then executes inquiry dba_segments, looks for if there is the table newly deleted
To the paragraph header block of the table；S3) the relevant data_object_id of the table is modified as the pervious value of Truncate by perform script, together
Shi Shengcheng modifies the reverse script to go back；S4) after reaching preset protection threshold value, reverse script in operating procedure S3,
The case where mark is reverted to again after Truncate is cleared up by the data that oracle database recycles needs.
The above-mentioned guard method for oracle database Truncate operation, wherein the step S3 includes: S31)
Data_object_id in all bitmap blocks of the table and data block is modified as the pervious value of Truncate by perform script, simultaneously
Generate the reverse script that modification is gone back；S32 data dictionary OBJ $ table) is modified, the value of data_object_id is modified as
The pervious value of Truncate, while generating the reverse script that modification is gone back.
The above-mentioned guard method for oracle database Truncate operation, wherein the table of the new deletion is by one
Level segment, second level bitmap block and three-level bitmap block are constituted；The gauge outfit block of three-level bitmap block has recorded several L2 bitmap blocks, the
One L2 bitmap block is Segment Header, and each L2 bitmap block has recorded several L1 bitmap blocks again, and L1 bitmap block is then remembered
Real data block is recorded；The S31 is by the data_ in second level bitmap block, three-level bitmap block and first level-one bitmap block
Object_id is modified as the pervious value of Truncate.
The present invention comparison prior art has following the utility model has the advantages that provided by the present invention for oracle database
The guard method of Truncate operation, the table name deleted by recording trigger by Truncate, and by the relevant data_ of the table
Object_id is modified as the pervious value of Truncate, so that the recovery of Truncate becomes simple and quick；When confirmation is deleted
Data do not need, then can execute reverse script, revert to appearance when deletion, such database can automatically to data into
Row recycling will not influence normal data and delete.
Detailed description of the invention
Fig. 1 is the Truncate operation protection flow chart of oracle database of the invention；
Fig. 2 is the three-level bitmap structure of table of the present invention；
Fig. 3 is the Truncate operation protection flow diagram that oracle database is used in the embodiment of the present invention.
Specific embodiment
The invention will be further described with reference to the accompanying drawings and examples.
Fig. 1 is the Truncate operation protection flow chart of oracle database of the invention.
Referring to Figure 1, the guard method provided by the present invention for oracle database Truncate operation includes as follows
Step:
S1) when Truncate operation occurs for oracle database, trigger is set；The recording trigger quilt
The table name that Truncate is deleted, and by the information preservation of record into beforetruncate table；
S2) timing scan beforetruncate table then executes inquiry dba_ if there is the table newly deleted
Segments finds the paragraph header block of the table；
S3) the relevant data_object_id of the table is modified as the pervious value of Truncate by perform script, is generated simultaneously
Modify the reverse script to go back；
S4) after reaching preset protection threshold value, reverse script in operating procedure S3 is again reverted to mark
The case where after Truncate, is cleared up by the data that oracle database recycles needs.
Truncate why very block, that be because he there is no do not go immediately really to delete the number in data block in fact
According to.When having deleted data then and discovery data are critically important, guard method of the invention can be lived data protection, only
A few place positions are modified, data retrieving can be come when needed.When having deleted data, it is found that the data deleted really do not need
, so that it may reverse script is executed, appearance when deletion is reverted to, such database can automatically recycle data.
Fig. 2 is the three-level bitmap structure of table of the present invention, and continuing with referring to fig. 2, table of the invention is by level-one bitmap, second level
Bitmap, what three-level bitmap was constituted.And three-level bitmap block is generally exactly gauge outfit.Gauge outfit block has recorded several L2 bitmap blocks, and every
A L2 bitmap block has recorded several L1 bitmap blocks again, and L1 bitmap block then has recorded real data block.As seen from Figure 2,
Want to know that this table occupies those L3, L2, L1, data block actually.It requires to know first L3 bitmap block, because first
A L3 bitmap block has recorded the position of other L3 and L2.And it is Segment Header that first L2 bitmap block, which is also referred to as,.Such as
Noted earlier, present invention recording trigger table name, the in this way present invention can pass through dba_segments view according to table name
Inquire the information of Header_file and Header_block；Namely Segment Header is in which data file and which
Above a data block.
By above- mentioned information, the present invention can search for by following the clues, and the used L1 of this table, L2, L3 data block is all dug
Out.The tectonic information of a table is grasped, the present invention can be controlled very easily by script setting switch later
Whether delete.After database initiates Truncate, the mark of data block can change, and present invention understands that whole table
Tectonic information, the present invention can change back again mark, and generate reversed script for invertible operation.In this way the present invention just
It can play the role of a protection.After reaching the threshold value of protection, the present invention directly runs reversed script, can be mark again
The case where being changed to after Truncate.Database will clean up this partial data in this way, then real Resource recovery.
Analyze the concrete operations of Truncate again below:
1, after Truncate occurring, the object_id of data block does not change, and data_object_id can become
Change.
2, the data_object_id above paragraph header and L2 changes, and the data_object_id of first L1 also can
Changed.
3, the information for modifying first L1 high water level does not allow inquiry this is done to shield the data only deposited
Out.
Why will so Oracle do so? because after Truncate, data will be recovered and use, but table
Object there is also because table exist, as soon as then this table just needs a structure, the basic structure of one table needs L3, L2
With L1 block.So it just falls the Data_object_id of the data block of basic structure needs to modification.It is remaining not modify
Block L1 and its specified data block can all clear up.Data block can be when needing use space, this part
It is cleared up, then recycling uses.
Continuing with referring to Fig. 3, it is known that after above-mentioned principle, the present invention can write a script and carry out Truncate
Protection, this script is R1.Sh.Its effect is as follows:
1. scan round beforetruncate table per minute, if it find that immediately executing inquiry dba_ with the presence of table
Segments finds the paragraph header block of this table.
2. having found paragraph header block, whole L2 blocks can be rapidly found.
3. having found L2 block, whole L1 blocks can rapidly be found by being selected in.
Since not equal to will being recycled for Data_object_id.So perform script is modified all whole
L3, L2 block, there are also first L1 blocks, data_object_id are changed to pervious value, that is, be modified as object_id, together
The reverse script of Shi Shengcheng (script that modification is gone back).
Data dictionary OBJ $ table is modified, the value of data_object_id is modified as object_id, with object_id mono-
Sample；And generate reverse script (script that modification is gone back).
This protection script helps the present invention that data protection is lived, if whithin a period of time, data of the table really
It is not needing, the present invention can execute reverse script, and by L3, L2 block, there are also the Data_object_id of first L1 block again
Modification is gone back, while the data_object_id of data dictionary OBJ $ table modification being fallen.Database just still can be to needs in this way
The data of recycling are cleared up.
Although the present invention is disclosed as above with preferred embodiment, however, it is not to limit the invention, any this field skill
Art personnel, without departing from the spirit and scope of the present invention, when can make a little modification and perfect therefore of the invention protection model
It encloses to work as and subject to the definition of the claims.
Claims (3)
1. a kind of guard method for oracle database Truncate operation, which comprises the steps of:
S1) when Truncate operation occurs for oracle database, trigger is set；The recording trigger is deleted by Truncate
The table name removed, and by the information preservation of record into beforetruncate table；
S2) timing scan beforetruncate table then executes inquiry dba_segments, looks for if there is the table newly deleted
To the paragraph header block of the table；
S3) the relevant data_object_id of the table is modified as the pervious value of Truncate by perform script, while generating modification
The reverse script to go back；
S4) after reaching preset protection threshold value, mark is reverted to Truncate by reverse script in operating procedure S3 again
Later the case where, is cleared up by the data that oracle database recycles needs.
2. the guard method for oracle database Truncate operation as described in claim 1, which is characterized in that described
Step S3 includes:
S31) data_object_id in all bitmap blocks of the table and data block is modified as before Truncate by perform script
Value, while generating the reverse script that goes back of modification；
S32 data dictionary OBJ $ table) is modified, the value of data_object_id is modified as the pervious value of Truncate, it is raw simultaneously
The reverse script to go back at modification.
3. the guard method for oracle database Truncate operation as claimed in claim 2, which is characterized in that described
The table newly deleted is made of level-one bitmap block, second level bitmap block and three-level bitmap block；If the gauge outfit block of three-level bitmap block has recorded
Dry L2 bitmap block, first L2 bitmap block is Segment Header, and each L2 bitmap block has recorded several L1 bitmap blocks again,
And L1 bitmap block then has recorded real data block；The S31 is by second level bitmap block, three-level bitmap block and first level
Data_object_id in segment is modified as the pervious value of Truncate.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811073254.0A CN109241061B (en) | 2018-09-14 | 2018-09-14 | Protection method for Oracle database trunte operation |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811073254.0A CN109241061B (en) | 2018-09-14 | 2018-09-14 | Protection method for Oracle database trunte operation |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109241061A true CN109241061A (en) | 2019-01-18 |
| CN109241061B CN109241061B (en) | 2022-02-11 |
Family
ID=65058103
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811073254.0A Active CN109241061B (en) | 2018-09-14 | 2018-09-14 | Protection method for Oracle database trunte operation |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109241061B (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110333970A (en) * | 2019-07-10 | 2019-10-15 | 中国民航信息网络股份有限公司 | A kind of data reconstruction method and device |
| CN112860686A (en) * | 2019-11-28 | 2021-05-28 | 中兴通讯股份有限公司 | Data processing method, data processing device, computer equipment and computer readable medium |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6092085A (en) * | 1998-03-24 | 2000-07-18 | International Business Machines Corporation | Method and system for improved database disaster recovery |
| CN103617277A (en) * | 2013-12-09 | 2014-03-05 | 山东瀚高基础软件股份有限公司 | Method for restoring data table content deleted mistakenly |
| US20150278023A1 (en) * | 2014-03-28 | 2015-10-01 | Korea University Research And Business Foundation | Apparatus and method for recovering data in oracle database |
| CN106547801A (en) * | 2015-09-23 | 2017-03-29 | 北京奇虎科技有限公司 | Database data flash back method and apparatus |
| CN107169108A (en) * | 2017-05-19 | 2017-09-15 | 郑州云海信息技术有限公司 | A kind of method and apparatus that the fast quick-recovery of MySQL data is realized based on Oracle |
| CN107609011A (en) * | 2017-07-28 | 2018-01-19 | 北京百分点信息科技有限公司 | The maintaining method and device of a kind of data-base recording |
| CN108351821A (en) * | 2016-02-01 | 2018-07-31 | 华为技术有限公司 | Data recovery method and storage device |
-
2018
- 2018-09-14 CN CN201811073254.0A patent/CN109241061B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6092085A (en) * | 1998-03-24 | 2000-07-18 | International Business Machines Corporation | Method and system for improved database disaster recovery |
| CN103617277A (en) * | 2013-12-09 | 2014-03-05 | 山东瀚高基础软件股份有限公司 | Method for restoring data table content deleted mistakenly |
| US20150278023A1 (en) * | 2014-03-28 | 2015-10-01 | Korea University Research And Business Foundation | Apparatus and method for recovering data in oracle database |
| CN106547801A (en) * | 2015-09-23 | 2017-03-29 | 北京奇虎科技有限公司 | Database data flash back method and apparatus |
| CN108351821A (en) * | 2016-02-01 | 2018-07-31 | 华为技术有限公司 | Data recovery method and storage device |
| CN107169108A (en) * | 2017-05-19 | 2017-09-15 | 郑州云海信息技术有限公司 | A kind of method and apparatus that the fast quick-recovery of MySQL data is realized based on Oracle |
| CN107609011A (en) * | 2017-07-28 | 2018-01-19 | 北京百分点信息科技有限公司 | The maintaining method and device of a kind of data-base recording |
Non-Patent Citations (2)
| Title |
|---|
| 冯东晖 等: ""一种基于细粒度扫描技术的文件恢复方法"", 《微处理机》 * |
| 刘春 等: ""闪回删除技术的分析与实现"", 《硅谷》 * |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110333970A (en) * | 2019-07-10 | 2019-10-15 | 中国民航信息网络股份有限公司 | A kind of data reconstruction method and device |
| CN110333970B (en) * | 2019-07-10 | 2021-09-07 | 中国民航信息网络股份有限公司 | Data recovery method and device |
| CN112860686A (en) * | 2019-11-28 | 2021-05-28 | 中兴通讯股份有限公司 | Data processing method, data processing device, computer equipment and computer readable medium |
| CN112860686B (en) * | 2019-11-28 | 2023-03-10 | 金篆信科有限责任公司 | Data processing method, data processing device, computer equipment and computer readable medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109241061B (en) | 2022-02-11 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108572996B (en) | Synchronization method and device of database table structure, electronic equipment and storage medium | |
| US20190205293A1 (en) | Systems and methods for concurrent summarization of indexed data | |
| CN104301360B (en) | A kind of method of logdata record, log server and system | |
| CN110287192B (en) | Search application data processing method and device, computer equipment and storage medium | |
| US20140310245A1 (en) | Partition level backup and restore of a massively parallel processing database | |
| CN101923568A (en) | Method for increasing and canceling elements of Bloom filter and Bloom filter | |
| CN106649412B (en) | Data processing method and equipment | |
| US10509767B2 (en) | Systems and methods for managing snapshots of a file system volume | |
| CN111506253A (en) | Distributed storage system and storage method thereof | |
| CN109241061A (en) | Guard method for oracle database Truncate operation | |
| CN104598517A (en) | Storage and inquiry technology for tree structure table based on ordinary database | |
| EP3107005A1 (en) | Method and device for recovering checkpoint in copy on write based file system | |
| CN113625952B (en) | Object storage method, device, equipment and storage medium | |
| CN107220287A (en) | For the index managing method of log query, device, storage medium and equipment | |
| CN109683825A (en) | A kind of storage system online data compression method, device and equipment | |
| CN107229414A (en) | Memory space recovery method and device | |
| CN107463555B (en) | Method, system and device for deleting intermediate layer data | |
| JP2022512464A (en) | Generating a blockchain with blocks containing an adjustable number of transaction blocks and multiple intermediary blocks | |
| CN106294769A (en) | Method, system and device for synchronizing engineering data | |
| WO2017067397A1 (en) | Data recovery method and device | |
| CN103729301A (en) | Data processing method and device | |
| CN108153805A (en) | A kind of method, the system of efficient cleaning Hbase time series datas | |
| CN110659295A (en) | Method, apparatus and medium for recording valid data based on HAWQ | |
| CN115357384B (en) | Space reclamation method and device for repeated data deleting storage system | |
| CN109144774A (en) | Method and apparatus for copy control in a storage system |
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 |