How to restore the FlashBack Delete table by mistake in PLSQL flash?

How to restore the FlashBack Delete table by mistake in PLSQL flash?

1. SCN Concept

After the Oracle database is updated, the SCN is automatically maintained by the DBMS to accumulate an increasing number. There are four SCN types in the Oracle database:

System checkpoint SCN: The system checkpoint SCN is located in the control file. When the checkpoint process starts (ckpt), Oracle stores the scn of the system checkpoint in the control file.

2. Some prerequisites for using the flashback operation

• Flashback permission for this table

• Permission to SELECT, INSERT, DELETE, and ALTER the table

• Row movement must be ensured

• Flashback cannot be used to restore data in a table deleted using the PURGE parameter.

3. Flashback Query

• Purpose: Query previous deleted data. It is used to have a correct scn or timestamp When deleting and restoring the data. The system does not recover the data.

• Syntax: select * from bxj_tst_recyclebin as of [scn or timestamp]

4. Flash recovery

• Purpose: Use the confirmed SCN and timestamp to truly restore the deleted data.

• Syntax: flashback table bxj_tst_recyclebin to [scn or timestamp]

5. Run the following statement before the flashback query is restored:

Alter table bxj_tst_recyclebin enable row movement

Ii. Case 1-flash recovery using timestamps

Step 1. Create eight test documents as follows:

Step2. delete four of the documents. After COMMIT, Rollback cannot be performed and the data is restored through flashback.

Step3.1 determine the timestamp to be restored through the time interval. Before restoration, use flashback to query eight pieces of data with the event timestamp.

Alter table bxj_tst_recyclebin enable row movement

Step: Restore data directly using the specific timestamp. Before restoration, use flashback to query eight pieces of data with the event timestamp.

Step 4. Restore through flashback

Flashback table bxj_tst_recyclebin to timestamp (systimestamp-interval '000000' second );
Flashback table bxj_tst_recyclebin to timestamp (to_timestamp (to_date ('2017 11:40:00 ', 'yyyymmdd HH: MI: ss ')));

Step 5. After the restoration is complete, you can query the four deleted materials without flashing back.

Iii. Case 2-flash recovery through SCN

Step1. once a transaction is committed, the system generates an SCN. Once data is deleted by mistake, the system immediately queries the current SCN.

Step2. check whether the SCN can query the four deleted data records through the flash query.

Step4. after confirming that the SCN is correct, perform the flash recovery operation.

Flashback table bxj_tst_recyclebin to scn 10132214657206

Step 5. After the restoration is complete, you can query the four deleted materials without flashing back.