Restore the original data after Oracle database truncate table
Summary: a customer of a state-owned enterprise in Beijing Oracle 11g R2 database error truncate table CM_CHECK_ITEM_HIS, table data lost, business query to the table reported an error, at this time found that the backup of the database can not be used, table data can not be queried.
Customer Oracle 11g R2 database error truncate table CM_CHECK_ITEM_HIS, table data lost, business query to the table reported an error, at this time found that the backup of the database can not be used, table data cannot be queried.
The mechanism of Truncate data is that ORACLE updates the Data Object ID of the table in the data dictionary and Segment Header, while the blocks of the actual data part are not modified. Because the DATA_OBJECT_ID of the data dictionary and segment header is not consistent with that of the subsequent data blocks, the ORACLE service process will not read the records that have been TRUNCATE but have not actually been overwritten when reading the whole table data.
Data recovery process
To protect user data, this document constructs the same faults as the customer. Scott users create table emp1, copy emp table, copy many times in succession, the total number of records is: 7340032. The truncate table emp1, and nothing has been done since. Query the table, and the records of the table in the database are 0.
Figure 1:
Note: Os:win server 2008R2. Database version: win_oracle_11.2.0.1_x64
1 analyze the system tablespace file to find the location of the original data of the truncate table
Figure 2:
2 parse the data file database where the table is located, and find the data of truncate
3 insert the database of truncate into the database
Data recovery result
By parsing the system01.dbf file, we can find the location of the truncate data and find the deleted data. Parse the data file where the table is located, and insert the data of truncate into the database.
In the database, look for the truncate, find that the data has come back, and back up the data directly.
Figure 3:
Exp Export scott user
Figure 4:
