North Asia case: How to recover Data from an Oracle database by mis-deletion

Learning the database, we are just learning attitude, consider how to use the database command statements, did not think about the work, if the wrong operation, can lead to irreparable loss. When I really met these questions at work, I started looking for answers.

Today, we mainly take Oracle database as an example to introduce the solution to data deletion in the table. (regardless of full-library backup and utilization of archive logs)

There are three ways to delete data in a table:

Delete (delete a record)

Drop or truncate delete data from a table

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/91/95/wKiom1j29uTTaIDaAAOJwzdfBVw014.jpg-wh_500x0-wm_ 3-wmp_4-s_2705127086.jpg "title=" 1.jpg "alt=" Wkiom1j29uttaidaaaojwzdfbvw014.jpg-wh_50 "/>

1.delete Solution for accidental deletion

Principle: Using the Flashback method provided by Oracle, if you have not done a lot of operations after deleting the data (as long as the block of deleted data is not overwritten), you can retrieve the deleted data directly using the flash back way.

The specific steps are:

* Determine when the data is deleted (the time before the data is deleted, but preferably the point in time when the data is deleted)

* Use the following statement to find the deleted data: SELECT * from table name as of timestamp to_timestamp (' delete point ', ' yyyy-mm-dd hh24:mi:ss ')

* Reinsert the deleted data into the original table:

Insert into table name (SELECT * from table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')); Note to ensure that the primary key is not duplicated.

If the table structure does not change, you can also restore the data directly using the way the entire table was flashed back.

The specific steps are:

Table flashback requires that the user must have flash any table permission

ALTER TABLE name enable row movement

Flashback table name to timestamp to_timestamp (delete time point ', ' yyyy-mm-dd hh24:mi:ss ')

2.drop Solution for accidental deletion

Principle: Since Oracle does not directly empty the blocks of the table when it deletes the table, Oracle places the information for these deleted tables in a virtual container "Recycle Bin", but only makes the data blocks of the table writable, so it can be resumed before the blocks are reused.

Specific steps:

* Query the Recycle Bin or query the user_table view to find the table that has been deleted:

· Select table_name,dropped from User_tables

· Select Object_name,original_name,type,droptime from User_recyclebin

In the above information, the table name has been renamed, the field table_name or object_name is deleted in the Recycle Bin in the storage table names

* If you can remember the table name, you can restore it directly using the following statement:

Flashback table name to before drop

If you do not remember, you can also directly use the Recycle Bin table name for recovery, and then rename, reference the following statement:

Flashback table name in the Recycle Bin (for example: bin$dsbdfd4rdfdfdfegdfsf==$0) to before drop rename to new table name

In addition to the basic features above, Oracle's flashback functionality can be flashed back to the entire database:

Using the database Flashback feature, you can return a database to a state in the past with the following syntax:

Sql>alter Database Flashback on

Sql>flashback database to SCN Scnno;

Sql>flashback database to timestamp to_timestamp (' 2007-2-12 12:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

Summarize:

Oracle provides the above mechanisms to ensure safe operation, but also for the generation of another problem, is the space occupancy, due to the operation of the above mechanism, using the drop of a table or delete data, the space will not be automatically reclaimed, for some to determine the unused table, delete the same time to reclaim space, There are 2 ways to do this:

1, the use of Truncate method for truncation. (But data recovery is not possible)

2. Add purge option at drop: drop table name purge

This option also has the following uses:

You can also delete the table permanently by deleting the recyclebin zone, and the original delete table is dropped in the form EMP cascade constraints

Purge table emp;

To delete the current user's Recycle Bin:

Purge RecycleBin;

Delete data for all users in the Recycle Bin:

Purge Dba_recyclebin

This article is from the "SUN" blog, be sure to keep this source http://sun510.blog.51cto.com/9640486/1917350

North Asia case: How to recover Data from an Oracle database by mis-deletion