欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

flashback query闪回数据

程序员文章站 2022-04-11 08:49:06
误删除了部分重要数据,已提交,需要恢复。首先尝试flashback query闪回数据。数据库运行在归档模式,首先确认数据库的SCN的变化:SQL> col fscn for 999999999999999999999SQL> col nscn for 999999999999999999999SQ ......

误删除了部分重要数据,已提交,需要恢复。
首先尝试flashback query闪回数据。

数据库运行在归档模式,首先确认数据库的SCN的变化:

SQL> col fscn for 999999999999999999999
SQL> col nscn for 999999999999999999999
SQL> select name,first_change# fscn,next_change# nscn, first_time from v$archived_log;

NAME                                             FSCN     NSCN FIRST_TIME
----------------------------------------------------------------------------- ----------- ----------- ------------------
/u01/app/oracle/fast_recovery_area/CDB/CDB/archivelog/2018_05_28/o1_mf_1_3_fj      1547949     1550763 2018:05:2816:37:41
qj6z9t_.arc

/u01/app/oracle/fast_recovery_area/CDB/CDB/archivelog/2018_05_28/o1_mf_1_4_fj      1550763     1551171 2018:05:2816:42:07
qjsm3t_.arc

/u01/app/oracle/fast_recovery_area/CDB/CDB/archivelog/2018_05_28/o1_mf_1_5_fj      1551171     1551255 2018:05:2816:52:03
qjwkog_.arc

/u01/app/oracle/fast_recovery_area/CDB/CDB/archivelog/2018_05_28/o1_mf_1_6_fj      1551255     1551654 2018:05:2816:53:37
qjxycd_.arc


获取当前的SCN:
SQL> select dbms_flashback.get_system_change_number  fscn from dual;

          FSCN
----------------------
           1551702
           

使用应用用户尝试闪回:
SQL> conn c##scott/tiger
Connected.

           
现有数据:
SQL> select count(*) from emp1;

  COUNT(*)
----------
     0

创建恢复表:
SQL> create table emp1_recov as select * from emp1 where 1=0;

Table created.

根据业务提供的大致误操作时间,结合V$ARCHIVED_LOG视图,选择适当SCN向前执行闪回查询:
 
SQL> select count(*) from emp1 as of scn 1551171;
select count(*) from emp1 as of scn 1551171
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

尝试多个SCN,获取最佳值(能知道具体时间,那么可以获取准确的数据恢复):

SQL> select count(*) from emp1 as of scn 1551255;

  COUNT(*)
----------
    14

SQL> select count(*) from emp1 as of scn 1551200;
select count(*) from emp1 as of scn 1551200
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL> select count(*) from emp1 as of scn 1551233;
select count(*) from emp1 as of scn 1551233
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL> select count(*) from emp1 as of scn 1551244;

  COUNT(*)
----------
    14


最后选择恢复到SCN为1551244的时间点,代码如下:
SQL> insert into emp1_recov select * from emp1 as of scn 1551244;

14 rows created.

SQL> commit;

Commit complete.

由业务人员通过emp1_recov表确认,向当前表补回误删除的数据,至此闪回恢复成功。没有闪回特性的话,需要通过物理备份执行不完全恢复,或者找出足够及时的逻辑备份来进行恢复,其过程都可能是极其复杂的。