1.闪回查询
原理：用户恢复删除的数据，利用的是undo表空间的数据，闪回查询过去的某一个时间点或SCN的数据信息。 SYS@PROD> conn scott/tiger SCOTT@PROD> create table t_fb_query1 as select * from dept; SCOTT@PROD> select * from t_fb_query1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SCOTT@PROD> set time on 14:30:49 SCOTT@PROD> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; ##也可以更改当前会话的时间格式： alter session set nls_date_format='yyyy-mm-dd hh24:miss'; select sysdate from dual; 2021-09-11 10:02:15 ##查询当前SCN 14:31:58 SYS@PROD> select current_scn from v$database; CURRENT_SCN ----------- 1070729 模拟删除10号部门的数据 14:33:25 SCOTT@PROD> delete t_fb_query1 where deptno=10; 14:33:53 SCOTT@PROD> select * from t_fb_query1; DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 语法：select * from 表名 as of timestamp/SCN; select * from t_fb_query1 as of timestamp to_timestamp('2021-09-11 10:02:15','yyyy-mm-dd hh24:mi:ss'); DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ##利用SCN查询 14:35:31 SCOTT@PROD> select * from t_fb_query1 as of SCN 1070729; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ##插入闪回的数据 insert into scott.t_fb_query1 select * from scott.t_fb_query1 as of timestamp to_timestamp('2021-09-11 10:02:15','yyyy-mm-dd hh24:mi:ss') minus select * from scott.t_fb_query1;
2.闪回表
原理：利用的是Undo表空间的undo数据，闪回表到某个时间点或某个SCN ##连接SCOTT用户创建业务表： conn scott/tiger create table fb_1 (id number); insert into fb_1 values(10); commit; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select sysdate from dual; ##查看此时的时间或者SCN: 时间： select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 2021-09-11 10:17:27 SCN:conn / as sysdba select current_scn from v$database; CURRENT_SCN ----------- 1071438 ##执行数据删除操作： delete from scott.fb_1; commit; ##闪回表到数据删除之前 开启被闪回表的行迁移： 14:42:45 SYS@PROD> alter table scott.fb_1 enable row movement; 闪回查询确认数据： select * from scott.fb_1 as of scn 1071438; 执行闪回,可以执行多次闪回操作： flashback table scott.fb_1 to scn 1071438; 或者 flashback table scott.fb_1 to timestamp to_timestamp('2021-09-11 10:17:27','yyyy-mm-dd hh24:mi:ss'); 验证数据： SYS@PROD> select * from scott.fb_1; ID ---------- 10 ##关闭行迁移 SYS@PROD> alter table scott.fb_1 disable row movement;
3.闪回数据库
##设置闪回区 conn / as sysdba show parameter recover alter system set db_recovery_file_dest_size=4G; alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; ##开启归档模式 查看归档模式 archive log list; 开归档 conn / as sysdba shutdown immediate startup mount alter database archivelog; alter database open; ##开启闪回及闪回日志保留数据的时间 目的：生成闪回日志 conn / as sysdba 验证是否开启了闪回 select flashback_on from v$database; 开启闪回 alter database flashback on; 如果为yes表示开启 闪回日志保留两天的数据，默认是一天（1440） alter system set db_flashback_retention_target=2880; SYS@PROD> show parameter retention_target ##模拟业务及误操作 连接scott用户： conn scott/tiger 创建业务表： create table fbdb_scn as select * from emp; select count(*) from fbdb_scn; COUNT(*) ---------- 14 反馈回14条记录 查询当前的SCN或时间： conn / as sysdba SCN:select current_scn from v$database; CURRENT_SCN ----------- 1065436 时间：select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2021-09-11 07:16:45 删除fbdb_scn的信息： delete from scott.fbdb_scn; commit; SYS@PROD> drop user scott cascade; 闪回数据库到业务表有14条记录的状态: 关闭数据库： shutdown immediate 启动到Mount状态： startup mount 闪回数据库： SYS@PROD> flashback database to SCN 1065436; 或者 flashback database to timestamp to_timestamp('2021-09-11 07:16:45','yyyy-mm-dd hh24:mi:ss'); 以只读方式打开数据库，可以先验证是否是我们想要闪回的状态： alter database open read only; conn scott/tiger select count(*) from fbdb_scn; 如果反馈是14条记录那么说明闪回成功， 如果不是14条记录，那么我们可以继续重复前两个步骤进行闪回， 直到是我们想要的状态。 重新关闭数据库： conn / as sysdba shutdown immediate startup mount alter database open resetlogs; 注：为不可逆操作 SYS@PROD> select GROUP#,SEQUENCE#,MEMBERS,STATUS from v$log; GROUP# SEQUENCE# MEMBERS STATUS ---------- ---------- ---------- ---------------- 1 1 1 CURRENT 2 0 1 UNUSED 3 0 1 UNUSED SYS@PROD> alter system switch logfile;
