Use undo in Oracle to restore Data

Use undo in Oracle to restore Data

[Note] when I accidentally saw a colleague's QQ message saying "really tired", I got a call from this colleague in less than 30 minutes, as shown below: When I was just doing data deletion, I found that the deletion was wrong because the conditions were not written, and there was no way to recover it. When I received this task, I felt deeply: When people are in bad status, try to take a rest and do less work, especially when it comes to important things.

After confirming the time point of the problem with his colleagues, he comforted him, recovered from the worried heart, and began searching for data.

[Environment description]

• Database Version: 11.2.0.3

• Database flash back: Disabled

Recovery steps]

[1] determining the time points and scripts for data deletion can be of great help for data recovery. It is best to compare the time difference between the time on the user's computer and the time on the server, time Difference needs to be added during query (the time difference between the server time and the user's computer time is checked for 5 minutes)

The user reports that the time for the above operations on the client is, but the server time lags behind the user's time by 5 minutes. Therefore, the query script is as follows:

Select * from t_original_archives as of timestamp to_timestamp ('2017-11-17 17:30:00 ', 'yyyy-MM-DD HH24: MI: ss') where id = 2015

[2] export data for users to check

Create table t_original_archives_bak as select * from t_original_archives as of timestamp to_timestamp ('2017-11-17 17:30:00 ', 'yyyy-MM-DD HH24: MI: ss') where id = 2015;

[3] If no problem is found, you can insert the deleted data to the original table.

Insert into t_original_archives select * from t_original_archives_bak

After the above steps, the data disaster was saved again;

During the recovery process, the control of the accidental deletion time is the most important. Generally, the database can be found within 15 minutes. If the time exceeds 15 minutes, it depends on the day of the meal.

The following describes the principles of flashback query.

The undo tablespace of the Oracle database is used to store the images before data deletion, ensuring data read consistency. Therefore, when the data is updated or deleted, the data does not disappear immediately, but is stored in the undo tablespace.

SQL> show parameter undo;

NAME TYPE VALUE

-----------------------------------------------------------------------------

_ In_memory_undo boolean FALSE

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

Parameter description:

Undo_management = auto: Set the automatic undo management mode. The default value is auto;

Undo_retention = n (seconds). The setting determines the maximum undo storage time. The default value is 900 seconds. It is recommended that you set more values if conditions permit.

Note: This does not mean that the system will retain the first image of 900 seconds, or the former image will disappear after 900 seconds, which is related to the size of the undo tablespace and the degree of System Busy. (Generally, data within 15 minutes can be queried)

The modification method of this parameter: SQL> alter system set undo_retention = 1800;

2. How can I ensure the storage time of undo data?

Method: Use the retention grarantee clause to ensure that the database is retained according to the undo_retention time;

2.1 start-up guarantee retained

Alter database UNDOTBS01 RETENTION GUARANTEE

2.2 disable guarantee of Information Revocation

Alter database UNDOTBS01 RETENTION NOGUARANTEE

3. disadvantages of enabling RETENTION GRARANTEE

When this parameter is enabled, when the business is busy, when the undo tablespace usage is 100%, the database will be down because the undo image should not be overwritten, therefore, the execution of other DDL statements is not allowed;

4. UNDO tablespace size setting

Method 1: You can set the size of the UNDO tablespace based on the suggestions in the AWR report.

Method 2: Observe and adjust the usage of the UNDO tablespace at any time during peak hours of the business system.

Undo tablespace loss caused by rman backup and recovery

About Oracle releasing over-used undo tablespace

Oracle undo

Oracle undo image data exploration

Oracle ROLLBACK and undo)

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian