Oracle数据库闪回详解

数据库闪回

1. 闪回开启

1、闪回开启
a.开启归档
# mount状态：
alter database archivelog;

b.设置闪回区
db_recovery_file_dest：指定闪回恢复区的位置
db_recovery_file_dest_size：指定闪回恢复区的可用空间大小
db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟(1天),实际取决于闪回区大小

SQL> alter system set db_recovery_file_dest_size=60G scope=both;

SQL> alter system set db_recovery_file_dest='/oracle/data/fast_recovery_area' scope=both;

SQL> alter system set db_flashback_retention_target=4320 scope=both;

c.开启flashback (10g在mount开启)
SQL> alter database flashback on;

(4)确定闪回开启
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES    

(5)关闭闪回
SQL> alter database flashback off;

2. 闪回drop

# 开启回收站
ALTER SESSION SET recyclebin = ON; //当前会话开启
 
ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE; //永久开启

SELECT * FROM RECYCLEBIN WHERE TYPE='TABLE'; //查看回收站内容


# 注意 
1.对system表空间存储的数据无效
2.删除时使用purge 则无效
3.索引(drop index)无效 

# 具体步骤：
(1)、查询这个“回收站”或者查询user_table视图来查找已被删除的表:

show recyclebin;

select table_name,dropped from user_tables;

select object_name,original_name,type,droptime from user_recyclebin;

以上表名都是被重命名过的，字段table_name或者object_name就是删除后在回收站中的存放表名。

#  a、如果还能记住表名，则可以用下面语句直接恢复：

  flashback table 原表名 to before drop;

#  b、如果记不住了，也可以直接使用回收站的表名进行恢复，然后再重命名，参照以下语句：

  flashback table "Bin$DSbdfd4rdfdfdfegdfsf==$0" to before drop rename to 新表名;


# 3、闪回整个数据库

使用数据库闪回功能，可以使数据库回到过去某一状态, 语法如下：

alter database flashback on;
flashback database to scn tmp_db1;
flashback database to timestamp to_timestamp('2017-6-28 11:30','yyyy-mm-dd hh24:mi:ss');



# 示例
SQL> create table student("id" char(6),"name" varchar2(10),"sex" char(1),"age" integer,"phone" varchar2(12)) tablespace test_db;

SQL> drop table student;
SQL> drop table student1 purge;
SQL> purge table TR02;

# 恢复操作
create table test tablespace users as select * from "BIN$59YTYxa8dv3gU45YHazhyQ==$0";
SQL> flashback table student to before drop;
SQL> flashback table "BIN$59YTYxbCdv3gU45YHazhyQ==$0" to before drop;

3. 闪回delete

# 注意：删除操作需要commit才可以成功。
# 具体实例及步骤：

(1) 确定删除数据的时间（在删除数据之前的时间就行，最好是删除数据的时间点）

# 查看oracle时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

# 找到删除的记录：

select * from test as of timestamp to_timestamp('2017-06-28 11:04','yyyy-mm-dd hh24:mi:ss');

# 把删除的数据重新插入原表：

insert into test ( select * from test as of timestamp to_timestamp('2017-06-28 11:04','yyyy-mm-dd hh24:mi:ss'));

commit;


(2) 如果表结构没有发生改变，还可以直接使用闪回整个表的方式来恢复数据，表闪回要求用户必须要有flash any table权限。

# 具体步骤：

# 开启行移动功能

alter table test enable row movement;

# 恢复表数据
flashback table test to timestamp to_timestamp('2022-09-23 17:17:28','YYYY-MM-DD HH24:MI:SS');

# 关闭行移动功能(此操作千万要记住)
alter table test disable row movement;

4. 闪回truncate

# 准备工作
# 查看oracle时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

SQL> truncate table scott.EMP;

# 重启到mount
SQL> shutdown immediate;

SQL> startup mount;

# 闪回数据库
SQL> flashback database to timestamp to_timestamp('2017-12-14 14:12:46','yyyy-mm-dd HH24:MI:SS');

# 开启数据库，重建日志逻辑
SQL> alter database open resetlogs;


SQL> select * from scott.emp;

5. 常用查询语句

1、相关数据字典
V$FLASHBACK_DATABASE_LOG ##查看数据库可闪回的时间点/SCN等信息
V$flashback_database_stat ##查看闪回日志空间记录信息

2、常用查询语句
(1)查看数据库状态

SQL> select NAME,OPEN_MODE ,DATABASE_ROLE,CURRENT_SCN,FLASHBACK_ON from v$database;

NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FLASHBACK_ON
------------- -------------------- ---------------- ----------- ------------------
TESTDB READ WRITE PRIMARY 16812246 YES

(2)获取当前数据库的系统时间和SCN
SQL> select to_char(systimestamp,'yyyy-mm-dd HH24:MI:SS') as sysdt, dbms_flashback.get_system_change_number scn from dual;

(3)查看数据库可恢复的时间点

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
16801523 2017-12-14 11:35:05 4320 104857600 244113408

(4)查看闪回日志空间情况

SQL> select * from V$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2017-12-14 14:34:53 2017-12-14 14:56:43 1703936 9977856 1487872 0    

(5)SCN和timestamp装换关系查询

select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time;

(6)查看闪回restore_point

select scn, STORAGE_SIZE ,to_char(time,'yyyy-mm-dd hh24:mi:ss') time,NAME from v$restore_point;

(7)闪回语句

a.闪回数据库
　　FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd HH24:MI:SS');;
　　flashback database to scn 16813234;
b.闪回DROP
其中table_name可以是删除表名称，也可以是别名
　　flashback table table_name to before drop;
　　flashback table table_name to before drop rename to table_name_new;
c.闪回表
　　flashback table table_name to scn scn_number;
　　flashback table table_name to timestamp to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd hh24:mi:ss');
d.闪回查询
　　select * from table_name as of timestamp to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd hh24:mi:ss');
　　select * from scott.dept as of scn 16801523;