Oracle Recovery Data

Oracle recovery deletes a table or table record

One: Recovery of the table to the wrong deleted table, as long as not using the Purge permanent delete option, then recover from the flash back area hope is quite large. General steps are: 1, from the Flash back query the deleted table select * from RecycleBin 2. Perform a table recovery flashback table TB to before drop, where the TB represents the name of the table you want to restore 。

Second: Table data recovery on the table records mistakenly deleted, as long as there is no TRUNCATE statement, you can choose to restore the transaction according to the time of submission, the general steps are:

1, the first query from the Flashback_transaction_query view, the view provides the table name for the query, transaction commit time, Undo_sql and other fields. such as: SELECT * from Flashback_transaction_query where table_name= ' TEST ';

2, the implementation of table records recovery is generally based on the time to query, the query statement mode for SELECT * from TB as of timestamp to_timestamp (timing, ' yyyy-mm-dd hh24:mi:ss '); TB refers to the name of a table, time refers to a point-of-order such as SELECT * from Scott.test as of timestamp to_timestamp (' 2009-12-11 20:53:57 ', ' Yyyy-mm-dd hh24:mi:s     S ');    If there is data, recovery is very simple, the statement is Flashback table TB to timestamp to_timestamp (time, ' yyyy-mm-dd hh24:mi:ss ');   such as flashback table Scott.test to timestamp to_timestamp (' 2009-12-11 20:47:30 ', ' yyyy-mm-dd hh24:mi:ss ');     NOTE: ALTER TABLE Testvarchar enable row movement; The purpose of this command is to allow Oracle to modify the ROWID assigned to the row. In Oracle, when a row is inserted, it is assigned a rowid, and this line will always have this rowid. The Flashback table processing completes the delete to the EMP and reinsert the rows so that the rows are assigned a new ROWID. To support flashback, Oracle must be allowed to perform this operation

Back to Top1. Table Recovery

For mistakenly deleted tables, you can basically recover from the Flashback table area as long as you do not use the Purge permanent delete option.

The data sheet and the data can be restored back, remember flashback table is provided from Oralce 10g, the general steps are:

A. Querying the deleted data table from flashback table

Desc

B. Performing a recovery of a table

Drop

Back to Top2. Table Data Recovery

As long as there is no TRUNCATE statement, we can select recovery according to the commit time of the transaction.

This feature is also provided by Oracle 10g above, and the general steps are:

A. From the Flashback_transaction_query view, the view provides the table name, transaction commit time, Undo_sql and other fields for querying.

WHERE table_name=' table name (uppercase) ' required to recover data;   

B. Query deletion point in time

'yyyy-mm-dd hh24:mi:ss' fromdual;  

Or you know probably remember to delete the point, you can also try the query, find out the point in time before the deletion

Timestamp To_timestamp (' time point 'yyyy-mm-dd hh24:mi:ss');   

C. Data recovery

The second step is to find the point in time for data loss, which is extremely simple, with the statement

Timestamp to_timestamp (' data loss before point ','yyyy-mm-dd hh24:mi:ss');  

Note: When doing this, Oracle needs to be allowed to modify the ROWID assigned to the row, when Oracle needs to assign a new physical address to the recovered data.

Table table_name enable row movement;

In fact, after finding the point in time before the data is lost, the recovered data can be inserted directly into the target table

' data loss table timestamp to_timestamp (' Point-in- time 'yyyy-mm-dd hh24:mi:sswhere ...;   

Oracle Recovery Data