1、应用Flashback Query查询过去的数据
select * from t1 as of scn 44545454;
select * from t1 as of timestamp to_timestamp('2012-09-28:09:20:13','yyyy-mm-dd:hh24:mi:SS');
select * from t1 as of timestamp sysdate-5/1440;
--select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') sys_time,to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd:hh24:mi:ss') scn_to_time,timestamp_to_scn(sysdate) date_to_scn,dbms_flashback.get_system_change_number scn1,current_scn scn2 from v$database;
(1)使用Flashback Version Query 查询记录修改版本
select current_scn from v$database;
DML操作........
insert into t1 values(20,'gyj20');
commit;
update t1 set name='gyj200000' where id=20;
commit;
delete from t1 where id=20;
commit;
select current_scn from v$database;
SELECT ID,NAME,VERSIONS_STARTSCN STARTSCN,VERSIONS_ENDSCN ENDSCN,VERSIONS_OPERATION OPERATION,VERSIONS_XID XID
FROM T1 VERSIONS BETWEEN SCN 2942841 AND 2942947;
--VERSIONS_STARTSCN:该条记录操作时的SCN,如果为空,表示该行记录是在查询范围外创建的
--VERSIONS_ENDSCN:该条记录失效时的SCN,如果为空,说明记录在这段时间无操作,或者已经被删数,配合VERSIONS_OPERATION
--VERSIONS_OPERATION:I表示insert、D表示delete、U表示update
--VERSIONS_XID:该操作的事务ID
alter database add supplemental log data; //Oracle11g禁用了supplemental logging
select current_scn from v$database;
DML操作........
select current_scn from v$database;
select xid,commit_scn,commit_timestamp,operation,undo_sql
from flashback_transaction_query q where q.xid in (select
versions_xid from t1 versions between SCN 2943656 and 2943676);
SQL> DESC FLASHBACK_TRANSACTION_QUERY --能获得这个事务执行时的很多信息,包括UNDO语句
Name Null? Type
----------------- -------- ------------
XID RAW(8) --事务ID,对应Versions Query中的VERSIONS_XID
START_SCN NUMBER --事务开始时的SCN
START_TIMESTAMP DATE --事务开始时间
COMMIT_SCN NUMBER --事务提交时的SCN,该列为空的话,说明事务为活动事务
COMMIT_TIMESTAMP DATE --事务提交时间
LOGON_USER VARCHAR2(30) --操作用户
UNDO_CHANGE# NUMBER --UNDO SCN
OPERATION VARCHAR2(32) --执行操作,有几个值:Delete、Insert、Update、B、UNKNOWN
TABLE_NAME VARCHAR2(256)--DML操作对象的表名
TABLE_OWNER VARCHAR2(32) --表的属主
ROW_ID VARCHAR2(19) --DML操作记录的行地址
UNDO_SQL VARCHAR2(4000)--撤销该操作对应的SQL
1、从undo表空间恢复
select row_movement from user_tables where table_name='T1';
alter table t1 enable row movement;
select * from t1;
select current_scn from v$database;
delete from t1;
commit;
select * from t1;
select * from t as of scn 9999;
flashback table t1 to scn 9999;
flashback table t1 to timestamp sysdate-5/1440;
--create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/bxdb/temp02.dbf' size 50M;
--alter user gyj temporary tablespace temp2;
drop table t1;
select object_name,original_name,droptime from recyclebin;
flashback table t1 to before drop;
--flashback table t1 to before drop rename to t1_old;
实验:多个名为t1的表恢复会是什么顺序???
回收站清除是按什么规则的??????
恢复表时索引也同时被恢复,但索引名称是????
drop table t1 purge; --真正删除表
三、数据库闪回配置:Books-->BAK-->Backup and Recovery Advanced User's Guide-->flashback
v$flashback_database_log
1、闪回区的大小
db_recovery_file_dest
db_recovery_file_dest_size
db_flashback_retention_target
3、设闪回
startup mount
alter database flashback on;
alter database open;
一般在备库中做完测试,用闪回恢复到测度前状态
flashbackup database TO TIMESTAMP(SYSDATE-1/24);
alter database open;
create restore point 恢复点名字 [guarantee flashback database]
drop restore point 恢复点名字
flashback database to restore point re_data;
show parameter db_recover
arcive log list;
select flashback_on,force_logging from v$database;
alter database force logging;
select dbms_flashback.get_system_change_number from dual;
drop table t1 purge;
shutdown immediate;
startup mount;
flashback database to restore point re_data;
--flashback database to scn 1511225;
alter database open resetlogs;
select * from t1;
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!
答案在:http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036
转载于:https://blog.51cto.com/guoyjoe/1429057