数据库闪回

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;# 重启到mountSQL> 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)获取当前数据库的系统时间和SCNSQL> 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_pointselect 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;