Use flashback to query and restore accidentally deleted data

Flashback query traces the pre-image data stored in the rollback segment to obtain the data before the change. This allows you to quickly restore and roll back the data before the pre-image is overwritten.

The flashback feature is affected by the initialization parameter UNDO_RETENTION,

Alter system set UNDO_RETENTION = 10800 SCOPE = BOTH; increasing this parameter may cause more UNDO tablespaces to be used.

Exercise caution when modifying such a parameter in a production environment:

1. Perform certain maintenance operations in the production environment after testing.

2. Perform certain maintenance operations only when the production environment is idle.

Run the following command to modify the UNDO_RETENTION parameter in the Oracle 10gR1 RAC environment:

Alter system set undo_retention = 18000 sid = '*';

This command directly suspends other RAC nodes. Oracle records a related Bug with Bug no. 4220405. Oracle prompts that UNDO_RETENTION with no specific SID is not supported in this version, different instances can be modified separately.

Example of deleting data using the flash query recovery code:

When the database runs in archive mode, you can first use the V $ ARCHIVED_LOG view to confirm the database SCN changes:

SQL> col fscn for 99999999999999999999999

SQL> col nscn for 9999999999999999999999

SQL> select name, FIRST_CHANGE # fscn, NEXT_CHANGE # nscn, FIRST_TIME from v $ archived_log;

Obtain the current SCN

SQL> select dbms_flashback.get_system_change_number fscn from dual;

Use the app user to try to flash back:

SQL> connect username/password

Current data:

SQL> select count (*) from hs_passport;

Create a recovery table as follows:

SQL> create table hs_passport_recov as select * from hs_passport where 1 = 0;

Based on the approximate misoperation time provided by the developer, combined with the V $ ARCHIVED_LOG view, select the appropriate SCN to execute the flashback query forward:

SQL> select count (*) from hs_passport as of scn 12929970422;

Try multiple SCN to get the best value (if you know the specific time, you can obtain the prepared data flash back );

SQL> select count (*) from hs_passport as of scn & scn:

Finally, select the restoration SCN time point.

SQL> insert into hs_passport_recov select * from hs_passport as of scn 129 .....

Return the data deleted by the error code to the current table and confirm whether the data is filled. The flash recovery is successful.