Oracle DBA ----閃回恢復
可以在scott中的emp表，進行一系列的更新和刪除操作后，可再用行級閃回功能找回這些歷史數據。
使用時間查詢
column VERSIONS_STARTTIME for a20
column VERSIONS_ENDTIME for a20
select versions_starttime,versions_endtime,versions_xid,versions_operation,ename from emp versions between timestamp minvalue and maxvalue where empno in('6000','6001') order by versions_starttime
查出對該行所作的所有更改，
同時，可以使用具有管理員身份登陸，查詢flashback_transaction_query如：
SYS AS SYSDBA on 21-MAR-08 at ORCL>select undo_sql from flashback_transaction_query where xid=
'04000F00F2050000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SCOTT"."EMP" set "ENAME" = 'Mikie01' where ROWID = 'AAAL+ZAAEAAAAAeAAB';
使用scn查詢
select versions_startscn,versions_endscn,versions_xid,versions_operation,ename
from emp versions between timestamp minvalue
and maxvalue order by versions_starttime
只有在undo_retention設置的時間内，才可以查詢到表的記錄，而且，也只有初始化undo_management設置為auto后才能使用閃囘查詢。
SYS AS SYSDBA on 21-MAR-08 at ORCL>show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS AS SYSDBA on 21-MAR-08 at ORCL>show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SYS AS SYSDBA on 21-MAR-08 at ORCL>show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
閃回表，如果，在該表上定義了索引和觸發器，索引或觸發器也將重新命名。
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
EMP TABLE
SCOTT on 2008-03-21 14:51:14 at ORCL>drop table emp;
Table dropped.
SCOTT on 2008-03-21 14:51:42 at ORCL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
BIN$YaYNfEMeQCG+9et9jItpTA==$0 TABLE
SCOTT on 2008-03-21 14:51:50 at ORCL>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$YaYNfEMeQCG+9et9jItpTA==$0 TABLE 2008-03-21:14:51:41
SCOTT on 2008-03-21 14:54:10 at ORCL>
SCOTT on 2008-03-21 14:54:10 at ORCL>flashback table emp to before drop;
Flashback complete.
SCOTT on 2008-03-21 14:55:32 at ORCL>select *from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
EMP TABLE
管理回收站：
SCOTT on 2008-03-21 14:56:04 at ORCL>purge recyclebin;
Recyclebin purged.
Purge table emp
Purge table BIN$YaYNfEMeQCG+9et9jItpTA==$0 TABLE
Purge index ind_1
Purge tablespace users ;
Purge tablespace users user scott;
Purge recyclebin
Purge dba_recyclebin
使用閃回，必須要滿足：
１：數據庫必須處於archivelog模式
２：必須配置數據庫閃回功能
３：必須配置初始化參數db_flashback_retention_target
SYS AS SYSDBA on 21-MAR-08 at ORCL>show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
查詢動態視圖：
SYS AS SYSDBA on 21-MAR-08 at ORCL>select name,log_mode,open_mode,flashback_on from v$database
;
NAME LOG_MODE OPEN_MODE FLA
--------- ------------ ---------- ---
ORCL ARCHIVELOG READ WRITE NO
SYS AS SYSDBA on 21-MAR-08 at ORCL>select to_char(oldest_flashback_time,'yyyy-mm-dd hh24:mi:ss
') time,oldest_flashback_scn scn from v$flashback_database_log;
TIME SCN
------------------- ----------
2008-03-21 15:11:48 13152078
SYS AS SYSDBA on 21-MAR-08 at ORCL>flashback database to timestamp to_date('2008-03-21 15:11:4
8','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SYS AS SYSDBA on 21-MAR-08 at ORCL>flashback database to scn 13152084
2 ;
Flashback complete.
SYS AS SYSDBA on 21-MAR-08 at ORCL>flashback database to scn 13152084
2 ;
Flashback complete.
SYS AS SYSDBA on 21-MAR-08 at ORCL>alter database open resetlogs;
Database altered.
