1，闪回数据库到之前某时间点（在安装时需要开放闪回功能）不支持表空间删除 select name from v$database; --看是否打开闪回功能 select flashback_on,name from v$database; select name from v$bgprocess where paddr<>'00' order by 1;
打开闪回功能SQL> shutdown immediatSQL> startup mountSQL> alter database flashback on;SQL> alter database open;修改闪回时间(默认是一天1440)SQL> alter system set db_flashback_retention_target=2880
RMAN> backup recovery files;--执行此程序，必须要有磁带执行数据库闪回，必须是在mount,readonly状态如果重建了控制文件，则修改控制文件以前的数据是不可以闪回的。例子；create table t2( a int);insert into t2 values(1);insert into t2 values(2);insert into t2 values(5);alter system switch logfile;t2 10:35insert into t2 values(100);alter system switch logfile;insert into t2 values(200);闪回到某时间点FLASHBACK DATABASE TO TIME = TO_DATE('2004-05-27 16:00:00','YYYY-MM-DD HH24:MI:SS'); FLASHBACK DATABASE 2 TO TIMESTAMP(SYSDATE-1/24); 查日志进程select * from v$log;恢复到某过程sequence为查询的sequence,THREAD把状态置为mount状态flashback database to sequence=58 thread=1;打开数据库alter database open resetlogs;
2，闪回drop表,数据(普通用户都可以操作)原理：是把放到数据库回收站的对象，数据拉回来凡不放回收站的对象都不能闪回.SQL>create table t1(a int);SQL>drop table t1;闪回后，把删除的表，及索引都会恢复SQL>flashback table t1 to before drop; 语法：FLASHBACK TABLE <table_name> TO BEFORE DROP [RENAME TO <new_name>]
可以用下面的命令或查询查看删除的表；show recyclebin;select * from recyclebin --where ORIGINAL_NAME='T1';DBA的回收站SELECT owner, original_name, object_name, type, ts_name, droptime, related, space FROM dba_recyclebin WHERE can_undrop = 'YES';用户回收站SELECT original_name, object_name, type, ts_name, droptime, related, space FROM user_recyclebin WHERE can_undrop = 'YES';
表实际不没有删除，只是改了一个名子，放到回收站了索引名子依然没变。a,用SYS用户删除的表不可以闪回。如conn / as sysdbaalter table cbsd.t1 move tablespace system;drop table cbsd.t1;则不可以闪回b.索引，物化视图，删除后不可以闪回
3.闪回事务查询alter table t1 enable row movement;查询所有可以撤消的操作SELECT versions_xid as xid, versions_startscn, versions_endscn, versions_operation FROM t1 VERSIONS between scn minvalue and maxvalueWHERE a = 20;
撤消数据update 3-->5 undo data数据放在undo tablespace
sql>show parameter undo;sql>select file_name from dba_data_files where tablespace_name='UNDOTBS1';闪回数据：RVWR+闪回日志+归档日志闪回现有表：undo tablespace闪回删除表:表原来物理位置
