Oracle10g闪回技术

闪回操作对应的恢复
--------------------------------------------------
flashback table
  数据错误更新表数据

flashback drop
  误删除表

flashback database
  误删除用户
  截断表
  删除表后purge
＊误删除表空间只能用备份CTL文件进行不完全恢复
--------------------------------------------------

闪回查询
--------------------------------------------------
时间与对应scn互查
select timestamp_to_scn(systimestamp) dual;
select scn_to_timestamp(current_scn) from v$database;

1. 闪回数据查询
SELECT sal FROM emp AS OF TIMESTAMP TO_TIMESTAMP ('2009-11-13 16:08:53','yyyy-mm-dd hh24:mi:ss') WHERE empno = 7788
SELECT sal FROM emp AS OF SCN 123456 WHERE empno = 7788;

2. 闪回版本查询
伪列
version_startscn
version_endscn
version_starttime
version_endtime
version_xid
version_operation

select sal,versions_xid,versions_startscn,versions_starttime,versions_operation from test versions between scn minvalue and maxvalue where empno = 7788;

select sal,versions_xid,versions_startscn,versions_starttime,versions_operation from test versions between timestamp minvalue and maxvalue where empno = 7788;

3. 闪回事务查询
desc flashback_transaction_query
select operation,undo_sql from flashback_transaction_query where xid = '0400110043010000';
--------------------------------------------------

闪回表数据
--------------------------------------------------
1. 行管理有效化/无效化
alter table emp enable row movement;
alter table test disable row movement;

2. 闪回表数据
主外键关系的表需要一起操作
FLASHBACK TABLE emp,dept TO [BEFORE] TIMESTAMP TO_TIMESTAMP('2009-11-13 16:45:51','YYYY-MM-DD HH24:MI:SS') [enable triggers];
FLASHBACK TABLE emp,dept TO SCN 123456 [enable triggers];
- 要恢复的列值在版本查询中处于start_scn<=scn<=end_scn-1
--------------------------------------------------

闪回表
--------------------------------------------------
1. 回收站查询/清空
select * from dba_recyclebin where owner = 'SYS';
select * from user_recyclebin;
show recyclebin;
purge recyclebin;
purge tablespace users [user scott];
purge user_recyclebin;
purge table scott.emp;
purge index scott.emp_idx;

2. 闪回删除表
flashback table emp to before drop rename to emp1;

3. 表被删除到回收站中的同时，index也被删除到回收站中，index恢复后需要手动改名
alter index "FDF$JLJJJJLJJJNMM" rename to tbl_idx;

4. 删除表后，与表相关的对象，制约等不会被删除，但会被重命名。恢复后需手动改名
alter table tbl rename constraint "ET$TBNK" to tbl_pk;

5. 不经过回收站的操作
用户的删除
drop user scott cascade;
表空间的删除
drop tablespace users including contents;
截断表
truncate table tbl;

6. 回收站开启与否
show parameter recyclebin
alter system set recyclebin=on/off scope=both;
--------------------------------------------------

闪回数据库
--------------------------------------------------
1. 确认闪回数据库功能是否开启
select log_mode,flashback_on from v$database;

2. 开启数据库闪回功能(需要归档模式支持)
startup mount;
alter database archivelog;
alter database flashback on;

3. 设置闪回数据保留时间(分钟)
alter system set DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;

4. 闪回数据库日志及信息查询
select flashback_size/1024/1024 "SIZE(M)", estimated_flashback_size/1024/1024 "EST_SIZE(M)" , oldest_flashback_scn from v$flashback_database_log;
select to_char(begin_time, 'HH24') HOUR, flashback_data/1024 "FLASHBACK_DATA(K)", redo_data/1024 "REDO_DATA(K)" from v$flashback_database_stat;

5. 闪回数据库
SQL
FLASHBACK database TO TIMESTAMP TO_TIMESTAMP('2009-11-13 16:45:50','YYYY-MM-DD HH24:MI:SS');
FLASHBACK DATABASE TO SCN 123456;
FLASHBACK DATABASE TO RESTORE POINT abc;

RMAN
FLASHBACK database TO TIME "TO_TIMESTAMP('2009-11-13 16:45:50','YYYY-MM-DD HH24:MI:SS')";
FLASHBACK DATABASE TO SCN 123456;
FLASHBACK DATABASE TO SEQUENCE 5 THREAD 1;

最后确认恢复成功，打开数据库
alter database open read only;
shutdown immediate;
startup mount;
alter database open resetlogs;

表空间开启/关闭闪回功能
startup mount
alter tablespace USERS flashback off;
alter tablespace USERS flashback on;
select name,flashback_on from v$tablespace;
--------------------------------------------------

注意
--------------------------------------------------
不能进行闪回数据库的操作
控制文件重做
删除表空间
数据文件压缩
resetlogs