11G新特性:FLASHBACK ARCHIVE – 02
在上一篇部落格(11G新特性:FLASHBACK ARCHIVE - 01)中提到了關於透過11G新特性FLASHBACK ARCHIVE恢復意外丟失表資料的恢復方法,主要是針對DML操作。在11G中,FALSHBACKARCHIVE可以支援針對DDL語句的恢復。
針對DDL語句(新增/刪除列)
建立表t03
23:08:29SQL> create table t03 as select owner,object_id,object_name,object_type fromdba_objects where owner='SCOTT';
Table created.
23:08:36 SQL> select * from t03;
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ---------- -------------------- --------------------
SCOTT 75334 PK_DEPT INDEX
SCOTT 75333 DEPT TABLE
SCOTT 75335 EMP TABLE
SCOTT 75336 PK_EMP INDEX
SCOTT 75337 BONUS TABLE
SCOTT 75338 SALGRADE TABLE
6 rows selected.
為表t03開啟flashback archive:
23:08:41SQL> alter table t03 flashback archive;
Table altered.
記錄下表定義未被修改前的時間:
23:08:50SQL> select sysdate from dual;
SYSDATE
-------------------
2011-11-1223:09:00
表t03新增列object_bytes:
23:09:14SQL> alter table t03 add (object_bytes number);
Table altered.
為新列object_bytes新增資料:
23:11:12SQL> update t03 set object_bytes = 1024;
6 rows updated.
23:11:26 SQL> commit;
Commit complete.
23:11:31 SQL> select * from t03;
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_BYTES
---------- ---------- -------------------- -------------------- ------------
SCOTT 75334 PK_DEPT INDEX 1024
SCOTT 75333 DEPT TABLE 1024
SCOTT 75335 EMP TABLE 1024
SCOTT 75336 PK_EMP INDEX 1024
SCOTT 75337 BONUS TABLE 1024
SCOTT 75338 SALGRADE TABLE 1024
6 rows selected.
至此,t03表已被新增列object_bytes,並儲存有資料,現在我們透過FLASHBACK ARCHIVE查詢t03表定義被修改前的資料狀態,注意:2011-11-1223:09:00為表定義修改前的時間點,因此,我們可以嘗試查詢該時間點時的資料狀態。
23:12:49SQL> select * from t03 as of timestamp to_timestamp('2011-11-1223:09:00','yyyy-mm-dd hh24:mi:ss');
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_BYTES
---------- ---------- -------------------- -------------------- ------------
SCOTT 75334 PK_DEPT INDEX
SCOTT 75333 DEPT TABLE
SCOTT 75335 EMP TABLE
SCOTT 75336 PK_EMP INDEX
SCOTT 75337 BONUS TABLE
SCOTT 75338 SALGRADE TABLE
6 rows selected.
可以看到,其實表定義還在(指新新增的列:object_bytes),不過該列上的資料已不存在,相信Oracle在後續的版本中繼續完善。
上面實驗的是新增列情況,假如我刪除了列object_type,那麼透過FLASHBACK ARCHIVE查詢出的結果如下:
23:34:43SQL> select * from t04 as of timestamp to_timestamp('2011-11-1223:33:47','yyyy-mm-dd hh24:mi:ss');
OWNER OBJECT_ID OBJECT_NAME D_1248039_OBJECT_TYPE
---------- ---------- -----------------------------------------------------------------------------
SCOTT 75335 EMP TABLE
SCOTT 75336 PK_EMP INDEX
SCOTT 75333 DEPT TABLE
SCOTT 75334 PK_DEPT INDEX
SCOTT 75338 SALGRADE TABLE
SCOTT 75337 BONUS TABLE
6 rows selected.
需要注意列名D_1248039_OBJECT_TYPE,該列即為我們原本的object_type列。
針對DDL語句(TRUNCATE操作)
為表t02開啟flashback archive:
SQL>alter table t02 flashback archive;
Table altered.
當前表t02的資料狀態:
SQL>select * from t02;
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ---------- -------------------- --------------------
SCOTT 75334 PK_DEPT INDEX
SCOTT 75333 DEPT TABLE
SCOTT 75335 EMP TABLE
SCOTT 75336 PK_EMP INDEX
SCOTT 75337 BONUS TABLE
SCOTT 75338 SALGRADE TABLE
6 rows selected.
記錄truncate前的時間點:
SQL>select sysdate from dual;
SYSDATE
-------------------
2011-11-1222:20:22
truncate表t02:
SQL>truncate table t02;
Table truncated.
表t02已空:
SQL>select * from t02;
no rows selected
透過FLASHBACKARCHIVE查詢:
SQL>select * from t02 as of timestamp (systimestamp - interval '5' minute);
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ---------- -------------------- --------------------
SCOTT 75336 PK_EMP INDEX
SCOTT 75335 EMP TABLE
SCOTT 75338 SALGRADE TABLE
SCOTT 75337 BONUS TABLE
SCOTT 75333 DEPT TABLE
SCOTT 75334 PK_DEPT INDEX
6 rows selected.
當然,如果有知道確切的時間,也可以通入下語句查詢:
SQL>select * from t02 as of timestamp to_timestamp('2011-11-12 22:20:22','yyyy-mm-ddhh24:mi:ss');
在10G版本中是不可能透過此方法恢復的,所謂11G新特性嘛,呵呵。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25834554/viewspace-710843/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 11G新特性:FLASHBACK ARCHIVEHive
- 11G新特性:FLASHBACK ARCHIVE - 01Hive
- 11G Flashback Data Archive新特性的研究Hive
- oracle 11g 新特性 Flashback Data Archive 說明OracleHive
- Oracle 11g新特性--閃回資料歸檔(flashback data archive)[zt]OracleHive
- oracle11g flashback archive feature新特性OracleHive
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- Oracle Flashback Data ArchiveOracleHive
- Flashback Data Archive RequirementsHiveUIREM
- Flashback Data Archive原理詳解Hive
- flashback drop/query/table/database/archiveDatabaseHive
- Flashback Data Archive (Oracle Total Recall)HiveOracle
- 11G flashback data archive 導致產生大量歸檔日誌Hive
- 【ORACLE新特性】11G 分割槽新特性Oracle
- (轉)oracle10g新特性之 flashbackOracle
- 10G新特性筆記之FLASHBACK筆記
- Oracle 11g 新特性Oracle
- 11g feature: Flashback Data Archive Guide. (Doc ID 470199.1)HiveGUIIDE
- 11g data guard 新特性
- 11g新特性--active dataguard
- oracle 11g 的新特性Oracle
- oracle flashback特性(2.1)--Flashback Table之RECYCLEBINOracle
- oracle flashback特性(3.2)--Flashback Database操作示例OracleDatabase
- How To Using Flashback Data Archive (Oracle Total Recall)HiveOracle
- 閃回資料歸檔-- Flashback Data ArchiveHive
- Oracle Flashback Archive——Oracle閃迴歸檔(上)OracleHive
- Oracle Flashback Archive——Oracle閃迴歸檔(中)OracleHive
- Oracle Flashback Archive——Oracle閃迴歸檔(下)OracleHive
- Playing with Flashback Data Archive on 11GR2Hive
- 11g新特性:Pending Statistics
- Oracle 11g 新特性簡介Oracle
- Oracle 11g 新特性之DRCPOracle
- 11g 新特性—— Active Database DuplicationDatabase
- Oracle 11g新特性:Result CacheOracle
- 11g新特性--result caching
- Oracle 11g 新特性(轉載)Oracle
- Oracle 11g新特性之SecureFilesOracle
- 11g新特性:不可視索引索引