本帖最后由 guoyJoe 于 2013-3-26 21:15 編輯

一、Fashback Query閃回查詢:Books-->APP-->Application Developer's Guide - Fundamentals-->Flashback

１、應用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;

２、應用Flashback Query查詢操作的事務

(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:Ｉ表示insert、D表示delete、U表示update

--VERSIONS_XID:該操作的事務ID

(2)使用Flashback Transaction Query 查詢事務信息(可以做審計)

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

二、Flashbackup Table閃回表:Books-->ADM-->Administrator's Guide-->flashback

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;

2、利用recyclebin,drop閃回

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

2、閃回日志保留時間

db_flashback_retention_target

3、設閃回

startup mount

alter database flashback on;

alter database open;

４、閃回操作要在mount下做

一般在備庫中做完測試,用閃回恢復到測度前狀態

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;

6、***場景1:

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;