Shulou(Shulou.com)05/31 Report--

This article will explain in detail how Orcale uses the flashback function to recover data. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Starting with 9i, Oracle provides FLASHBACK. Use the FLASHBACK TABLE statement to read the past image of the table from the undo segment (undo segment) and reconstruct the table rows using the flashback query introduced in Oracle9i. UNDO_RETENTION gives the minimum time for flashback support. In other words, FLASHBACK can at least support the time given by UNDO_RETENTION, and if the system is idle, it can flash back for a longer time. (of course, if the space allocation of the rollback table space is insufficient, it is possible to reuse the space of data that has not yet reached the UNDO_RETENTION time limit when the system is busy.) One of the prerequisites for using flashback is that the table cannot perform DDL operations. Not only can the DDL operation not be flashed back, but also the data before the DDL operation cannot be flashed back.

I. delete mistakenly

Method 1: if the table structure has not changed, flashback the entire table directly, specific steps:

-- first of all, table flashback permission is required to enable row movement.

Alter table table name enable row movement

-- perform flashback to recover table data to a certain point in time

Flashback table table name to timestamp to_timestamp ('recovery point in time', 'yyyy-mm-dd hh34:mi:ss')

-- turn off the line movement function

Alter table table name disable row movement

Method 2: query deleted data, and then execute insert

Insert into table name (select * from table name as of timestamp to_timestamp ('recovery point in time', 'yyyy-mm-dd hh34:mi:ss'))

II. Drop mistakenly deleted

Principle: because oracle does not directly empty the blocks occupied by the table when deleting the table, oracle puts the information of these deleted tables into a virtual container "Recycle Bin", but only marks the data blocks of the table that can be overwritten, so the blocks can be restored before they are reused.

1. If you remember the deleted table name, flash back directly.

Flashback table original table name to before drop

two。 If you don't remember, first find the deleted table from the Recycle Bin, and then perform the restore:

Query the deleted table. Table_name or object_name is the name of the table that is renamed in the Recycle Bin after deletion.

Select table_name,dropped from user_tables

Select object_name,original_name,type,droptime from user_recyclebin

-- perform a restore

Flashback table "Table name in Recycle Bin" to before drop rename to New Table name

three。 Flashback the entire database

Alter database flashback on

Flashback database to scn SCNNO

Flashback database to timestamp to_timestamp ('recovery point in time', 'yyyy-mm-dd hh34:mi:ss')

This is the end of the article on "how Orcale uses the flashback function to recover data". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.