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

Editor to share with you how to restore the erroneous modified and deleted data in oracle. I hope you will get something after reading this article. Let's discuss it together.

No matter how careful the Oracle ERP maintenance staff is, it is inevitable that the data will be mistakenly deleted or altered in the official database and have already commit, so I need to use the FlashBack feature in-Oracle9i, which may be used by many colleagues. Let me talk about my methods and some thoughts in use:

The specific methods are as follows > >

1. We can use it.

SELECT item_cost FROM cst_item_costs

AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAL'1' hour)

WHERE organization_id = 1

AND yyyymm = 200712

AND item_number = '6384BLCCSL'

To check the data from an hour ago.

* A few points need to be explained here:

A. AS OF TIMESTAMP expr is the FLASHBACK clause in the SELECT statement

B. SYSTIMESTAMP is a time function provided by the system that can achieve microsecond accuracy and provide the current time zone

Eg. Select SYSTIMESTAMP from dual

SYSTIMESTAMP

29-DEC-07 11.16.08.112686 AM + 09:00

C. The function of INTERVAL is to specify a time interval. Here is the algorithm by which it participates in the operation.

Operand1 Operator Operand2 Result Type

Datetime-Interval Datetime

We can change the retrogression time by changing the time unit and quantity.

Eg. TIMESTAMP (SYSTIMESTAMP-INTERVAL'1' day)-query the data from a day ago

TIMESTAMP (SYSTIMESTAMP-INTERVAL '10' minute)-query data from ten minutes ago

TIMESTAMP (SYSTIMESTAMP-INTERVAL '50' second)-query data from 50 seconds ago

d. About the recoverable time in FlashBack

There are two parameters in Oracle that can determine the retrospective time > >

Undo tablespace size and undo_retention initialization parameters.

In the general ERP formal system, the undo tablespace is about 20g, and the undo_retention is 3600s.

The maximum recoverable time during peak hours is about 2-3 hours, and the non-business time is 4-5 hours. It should be noted here that the maximum recoverable time is determined by

The size of undo table space and the frequency of database data changes are determined.

If the maximum rewind time is exceeded, the system will give a famous ORA-01555 snapshot too old (Snapshot too old) error.

e. To do FlashBack, you must have the system permission of FlashBack Any Table.

two。 So, and so on, we can use FlashBack to recover data from a certain period of time.

UPDATE cst_item_costs SET item_cost =

(SELECT item_cost FROM cst_item_costs

AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAL'1' hour)

WHERE organization_id = 1

AND yyyymm = 200712

AND item_number = '6384BLCCSL)

WHERE organization_id = 1

AND yyyymm = 200712

AND item_number = '6384BLCCSL'

* A few points should also be explained here:

a. Before recovering the data, check whether the WHERE condition is unique

b. If you want to recover data in batches or full tables, check to see if there is any data written by other sessions within the specified time interval

After reading this article, I believe you have a certain understanding of "how to recover erroneous modified and deleted data in oracle". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.