oracle表数据误删除恢复(闪回操作）

数据库开启归档及闪回功能

SQL> startup mount ;
ORACLE instance started.

Total System Global Area 4294964952 bytes
Fixed Size		    9144024 bytes
Variable Size		 1023410176 bytes
Database Buffers	 3254779904 bytes
Redo Buffers		    7630848 bytes
Database mounted.
SQL> alter database archivelog ;

Database altered.

SQL> alter database flashback on ;

Database altered.

SQL> alter database open ;

Database altered.
SQL> select log_mode,FLASHBACK_ON from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES

创建测试表并插入数据

SQL> create table employee ( empno int, ename varchar(10),  salary int );

Table created.

SQL> insert into employee values (1, 'a', 20000 );

1 row created.

SQL> insert into employee values (2, 'b', 80000 );

1 row created.

SQL> insert into employee values (3, 'c', 60000);

1 row created.

SQL> 
SQL> 
SQL> 
SQL> commit ;

Commit complete.

删除数据

SQL> delete from employee where empno=1;

1 row deleted.

SQL> commit ;

Commit complete.

查询scn的变化及当前的scn

col NAME for a60
SELECT
    NAME,
    FIRST_CHANGE# FSCN,
    NEXT_CHANGE#  NSCN,
    FIRST_TIME
FROM
    V$ARCHIVED_LOG;

NAME								                          FSCN       NSCN FIRST_TIM
------------------------------------------------------------ ---------- ---------- ---------


/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2025_0	3430758    3557724 27-MAY-25
5_29/o1_mf_1_32_n3htww1t_.arc

/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2025_0	3557724    3622455 29-MAY-25
5_29/o1_mf_1_33_n3hv2lfk_.arc

/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2025_0	3622455    3674700 29-MAY-25
5_29/o1_mf_1_34_n3hvkto1_.arc


NAME								   FSCN       NSCN FIRST_TIM
------------------------------------------------------------ ---------- ---------- ---------
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2025_0	3674700    3731424 29-MAY-25
5_29/o1_mf_1_35_n3hw3d4m_.arc

/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2025_0	3731424    3798784 29-MAY-25
5_29/o1_mf_1_36_n3j2z8w5_.arc

/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2025_0	3798784    3932544 29-MAY-25
6_12/o1_mf_1_37_n4nx74qw_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2025_0	3932544    3957895 12-JUN-25
6_12/o1_mf_1_38_n4o0gd1s_.arc


SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FSCN FROM DUAL;

      FSCN
----------
   3958892

使用删除用户统计现有表的数据行数

SQL> select count(*) from employee;

  COUNT(*)
----------
	 2

创建恢复的新表

SQL> CREATE TABLE employee_1 AS SELECT * from employee where 1=0;

Table created.

根据误操作时间及scn号查到最合适的scn 恢复

SELECT COUNT(*) FROM  employee AS OF SCN 3955600;

  COUNT(*)
----------
	 3
 SELECT COUNT(*) FROM  employee AS OF SCN 3955900;

  COUNT(*)
----------
	 2

恢复到employee_1 ， 筛选出误操作数据恢复到生产表中

SQL> INSERT INTO employee_1 SELECT * FROM employee AS OF SCN 3955600;

3 rows created.

SQL> commit ;

Commit complete.