闪回基本参数
企业版才支持数据闪回
打开重置日志ALTER DATABASE OPEN RESETLOGS
必须在归档模式archive log list;
开启mount状态startup mount exclusive;
查看闪回参数show parameter db_recovery
show parameter flashback
设置闪回空间alter system set db_recovery_file_dest='/u01/app/oracle/flashback_area' scope=spfile; 设置闪回区大小alter system set db_recovery_file_dest_size=20g scope=spfile;
设置保留时间
alter system set db_flashback_retention_target=4320;
开启闪回alter database flashback on;
查看闪回开启select flashback_on from v$database;
具体闪回待添加----搭建rac之后
创建可靠还原点
sys@WZXIS>create restore point b1 guarantee flashback database;
闪回表
scott@DJJXIS>drop table emp;
Table dropped.
scott@DJJXIS>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$R+Kc8tW4kBTgVQAAAAAAAQ==$0 TABLE
BONUS TABLE
DEPT TABLE
SALGRADE TABLE
查看回收站
scott@DJJXIS>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------- EMP BIN$R+Kc8tW4kBTgVQAAAAAAAQ==$0 TABLE 2017-02-07:03:31:11
scott@DJJXIS>flashback table emp to before drop;
Flashback complete.
彻底删除表：purge选项
scott@DJJXIS>drop table T purge;
Table dropped.
scott@DJJXIS>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$R+Kc8tW4kBTgVQAAAAAAAQ==$0 TABLE
BONUS TABLE
DEPT TABLE
SALGRADE TABLE
闪回查询
这个可能用EM来做更加方便
闪回删除表
标记时间点
sys@WZXIS>select sysdate from dual;
SYSDATE
-------------------
2017-01-31 10:21:48
sys@WZXIS>shutdown immediate
sys@WZXIS>flashback database to timestamp to_timestamp('2017-01-31 09:48:48','yyyy-mm-dd hh24:mi:ss');
sys@WZXIS>alter database open read only;
sys@WZXIS>select * from scott.emp;没有发现
sys@WZXIS>startup force mount
.......一直到找到emp表
找到之后另起会话
[oracle@localhost ~]$ exp scott/tiger file=/backup/emp.dump tables=emp [oracle@localhost ~]$ imp scott/tiger file=/backup/emp.dump tables=emp
sys@WZXIS>flashback database to timestamp to_timestamp('2017-01-31 10:21:48','yyyy-mm-dd hh24:mi:ss');
sys@WZXIS>alter database open resetlogs;
可靠还原点
不受闪回区时间的限制，都能还原到这个点，一般用作测试数据库，
创建：
sys@WZXIS>create restore point b3 guarantee flashback database; Restore point created.
闪回可靠还原点：
sys@WZXIS>flashback database to restore point b3;
Flashback complete.
sys@WZXIS>alter database open resetlogs;
Database altered.
查询已建立的还原点
sys@WZXIS>select name from v$restore_point;
NAME
------------------------------
B1
删除还原点
sys@WZXIS>drop restore point b1;
Restore point dropped.。