Oracle flashback table based on the SCN number

We recommend that you change the undo_retention parameter when installing the oracle software. We recommend that you change the default value of 900 to 10800 seconds, that is, 3 hours; altersystemsetundo_retention1

We recommend that you change the undo_retention parameter when installing the oracle software. We recommend that you change the default value of 900 to 10800 seconds, that is, 3 hours; alter system set undo_retention = 1

We recommend that you change the undo_retention parameter when installing the Oracle software. We recommend that you change the default value of 900 to 10800 seconds, that is, 3 hours; alter system set undo_retention = 10800 scope = both;

A part of the data added to an employees table is deleted and submitted. The data must be restored and the database runs in archive mode.


Idea: Use the SCN number to restore the table to the original record.

First, check the SCN changes:

Conn/as sysdba

Select name, first_change # fscn, next_change # nscn, first_time

From v $ archived_log;

NAME FSCN

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

NSCN FIRST_TIME

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

5795407 2012-09-14: 22: 00: 52



/U/oracle/fast_recovery_area/ORA11G/Archy 5795407

Ivelog/2012_09_17/o1_mf_000043_85fzkcbs _.

Rc

5803747 2012-09-17: 18: 10: 38



/U/oracle/fast_recovery_area/ORA11G/Archy 5803747

Ivelog/2012_09_17/o1_mf_000044_85fzkh75 _.



NAME FSCN

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

NSCN FIRST_TIME

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

Rc

5807441 2012-09-17: 18: 40: 43



/U/oracle/fast_recovery_area/ORA11G/Archy 5807441

Ivelog/2012_09_17/o1_mf_1_45_85fzkmq7 _.

Rc

5810907 2012-09-17: 18: 40: 47



/U/oracle/fast_recovery_area/ORA11G/Archy 5810907

(Display part)


Obtain the current SCN

SQL> select dbms_flashback.get_system_change_number fscn from dual;



FSCN

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

5874910


Connect to the user (try to restore with the user ):

Conn/as sysdba

View existing data:


SQL> select count (*) from employees;



COUNT (*)

----------

107



Create a recovery table:

SQL> create table t as select * from employees where 1 = 0;

The table has been created.


Based on the provided approximate misoperation time, combined with the V $ archived_log view, select to execute the flashback query;

SQL> select count (*) from employees as of scn 5810907;


COUNT (*)

----------

107

Find the most appropriate value. For example, the most suitable value is 5810907.

Select a time point with SCN 5810907 for restoration:

SQL> insert into t select * from employees as of scn 5810907;

You have created 107 rows.

SQL> commit;

Submitted.

Complete. View data integrity!