Oracle DBA2 ---- 閃回恢復

                 

                 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.

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/701141/viewspace-214942/，如需轉載，請註明出處，否則將追究法律責任。