Oracle Study之案例--数据恢复神器Flashback(2)
Oracle Study之案例--數(shù)據(jù)恢復(fù)神器Flashback(2)
一、Flashback Table:
對于DML的誤操作,可以通過Undo block對表進行回退(兩種模式:基于時間和基于SCN)
案例分析:
1、基于SCN(可以通過logminer找到DML操作的時間點和SCN)
模擬測試環(huán)境: SQL>?select?current_scn?from?v$database; CURRENT_SCN -----------1264179 07:16:18?SQL>?select?*?from?test;????????????????????????????????????????????????????????????????????????????????????????????????????????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM?????DEPTNO ----------?----------?---------?----------?---------?----------?----------?----------7369?SMITH??????CLERK???????????7902?17-DEC-80????????800????????????????????207499?ALLEN??????SALESMAN????????7698?20-FEB-81???????1600????????300?????????307369?SMITH??????CLERK???????????7902?17-DEC-80????????800????????????????????207499?ALLEN??????SALESMAN????????7698?20-FEB-81???????1600????????300?????????307521?WARD???????SALESMAN????????7698?22-FEB-81???????1250????????500?????????307566?JONES??????MANAGER?????????7839?02-APR-81???????2975????????????????????207654?MARTIN?????SALESMAN????????7698?28-SEP-81???????1250???????1400?????????307698?BLAKE??????MANAGER?????????7839?01-MAY-81???????2850????????????????????307782?CLARK??????MANAGER?????????7839?09-JUN-81???????2450????????????????????107788?SCOTT??????ANALYST?????????7566?19-APR-87???????3000????????????????????207839?KING???????PRESIDENT????????????17-NOV-81???????5000????????????????????107844?TURNER?????SALESMAN????????7698?08-SEP-81???????1500??????????0?????????307876?ADAMS??????CLERK???????????7788?23-MAY-87???????1100????????????????????207900?JAMES??????CLERK???????????7698?03-DEC-81????????950????????????????????307902?FORD???????ANALYST?????????7566?03-DEC-81???????3000????????????????????207934?MILLER?????CLERK???????????7782?23-JAN-82???????1300????????????????????10 16?rows?selected.07:16:23?SQL>?delete?from?test;?????????????????????????????????????????????????????????????????????????????????????????????????????????? 16?rows?deleted. 07:16:50?SQL>?commit;??????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Commit?complete.07:16:52?SQL>?select?*?from?test;???????????????????????????????????????????????????????????????????????????????????????????????????????? no?rows?selected 07:16:57?SQL>?insert?into?test?select?*?from?emp?where?rownum=1;????????????????????????????????????????????????????????????????????????? 1?row?created. 07:17:17?SQL>?commit;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Commit?complete. 07:17:19?SQL>?select?*?from?test;????????????????????????????????????????????????????????????????????????????????????????????????????????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM?????DEPTNO ----------?----------?---------?----------?---------?----------?----------?----------7369?SMITH??????CLERK???????????7902?17-DEC-80????????800????????????????????20通過flashback?table回退:?????? 07:17:21?SQL>?flashback?table?test?to?scn?1264179;??????????????????????????????????????????????????????????????????????????????????????? flashback?table?test?to?scn?1264179* ERROR?at?line?1: ORA-08189:?cannot?flashback?the?table?because?row?movement?is?not?enabled回退table必須支持row?movement: 07:17:41?SQL>?alter?table?test?enable?row??movement;????????????????????????????????????????????????????????????????????????????????????? Table?altered.07:18:01?SQL>?flashback?table?test?to?scn?1264179;??????????????????????????????????????????????????????????????????????????????????????? Flashback?complete. 07:18:05?SQL>?select?*?from?test;????????????????????????????????????????????????????????????????????????????????????????????????????????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM?????DEPTNO ----------?----------?---------?----------?---------?----------?----------?----------7369?SMITH??????CLERK???????????7902?17-DEC-80????????800????????????????????207499?ALLEN??????SALESMAN????????7698?20-FEB-81???????1600????????300?????????307369?SMITH??????CLERK???????????7902?17-DEC-80????????800????????????????????207499?ALLEN??????SALESMAN????????7698?20-FEB-81???????1600????????300?????????307521?WARD???????SALESMAN????????7698?22-FEB-81???????1250????????500?????????307566?JONES??????MANAGER?????????7839?02-APR-81???????2975????????????????????207654?MARTIN?????SALESMAN????????7698?28-SEP-81???????1250???????1400?????????307698?BLAKE??????MANAGER?????????7839?01-MAY-81???????2850????????????????????307782?CLARK??????MANAGER?????????7839?09-JUN-81???????2450????????????????????107788?SCOTT??????ANALYST?????????7566?19-APR-87???????3000????????????????????207839?KING???????PRESIDENT????????????17-NOV-81???????5000????????????????????107844?TURNER?????SALESMAN????????7698?08-SEP-81???????1500??????????0?????????307876?ADAMS??????CLERK???????????7788?23-MAY-87???????1100????????????????????207900?JAMES??????CLERK???????????7698?03-DEC-81????????950????????????????????307902?FORD???????ANALYST?????????7566?03-DEC-81???????3000????????????????????207934?MILLER?????CLERK???????????7782?23-JAN-82???????1300????????????????????10 16?rows?selected. ---回退成功?!2、基于timestamp(可以通過logminer找到DML操作的時間點和SCN)
1.?基于undo?的表恢復(fù),需要注意DDL?操作的影響
? ? ? 第三個就是修改并提交過數(shù)據(jù)之后,對表做過DDL?操作,包括:
? ? ? drop/modify?列,?move?表,?drop?分區(qū)(如果有的話),?truncate?table/partition,這些操作會另undo?表空間中的撤銷數(shù)據(jù)失效,對于執(zhí)行過這些操作的表應(yīng)用flashback?query?會觸發(fā)ORA-01466?錯誤。另外一些表結(jié)構(gòu)修改語句雖然并不會影響到undo?表空間中的撤銷記錄,但有可能因表結(jié)構(gòu)修改導(dǎo)致undo?中重做記錄無法應(yīng)用的情況,比如對于增加了約束,而flashback?query?查詢出的undo?記錄已經(jīng)不符合新建的約束條件,這個時候直接恢復(fù)顯然不可能成功,你要么暫時disable?約束,要么通過適當邏輯,對要恢復(fù)的數(shù)據(jù)進行處理之后,再執(zhí)行恢復(fù)。
? ? ? 另外,flashback?query?對v$tables,x$tables?等動態(tài)性能視圖無效,不過對于dba_*,all_*,user_*等數(shù)據(jù)字典是有效的。同時該特性也完全支持訪問遠端數(shù)據(jù)庫,比如select?*?from?tbl@dblink?as?of?scn?360;的形式。?
2.?基于undo?的表恢復(fù),flashback?table?實際上做的也是dml?操作(會在被操作的表上加dml?鎖),因此還需要注意triggers?對其的影響
? ? ?默認情況下,flashback?table?to?scn/timestamp?在執(zhí)行時會自動disable?掉與其操作表相差的triggers,如果你希望在此期間trigger?能夠繼續(xù)發(fā)揮做用,可以在flashback?table?后附加
ENABLE?TRIGGERS?子句。
二、Flashback Drop
? ? ? ? ?在實際開發(fā)和維護中,我們有時候會遇到把數(shù)據(jù)表drop掉的情況。過去這種情況,我們只能通過之前保留的備份,進行不完全的備份。這樣的工作量很大也很麻煩。從Oracle10g起,引入了回收站的機制,將drop掉的數(shù)據(jù)表保存在回收站中。當發(fā)現(xiàn)誤刪除的時候,可以通過回收站回收數(shù)據(jù)表。
? ? ? ? ? 回收站機制類似于我們在Windows上的回收站。在windows中,當我們選擇刪除一個文件時,本質(zhì)上并沒有將文件從硬盤上刪除,只是將文件以一種形式改名,這樣就能從回收站中看到。
? ?Oracle的回收站也是采用同樣的原理。下面我們做一個簡單的實驗。
? ?首先,確定系統(tǒng)參數(shù)。在Oracle10g中,有一個參數(shù)recyclebin,控制數(shù)據(jù)表回收站機制的啟動和關(guān)閉。
?用sys帳號登錄,確定recyclebin參數(shù):
SQL>?show parameter recyclebin;
?NAME????????????????????????????????TYPE???????VALUE
------------------------------------ ----------- ------
recyclebin??????????????????????????string?????on ? ? ? ? ? ? ? ?//當取值為on的時候,表示開啟回收站功能;
案例分析:
1)06:52:29?SQL>?select?*?from?tab;????????????????????????????????????????????????????????????????????????????????????????????????????????? TNAME??????????????????????????TABTYPE??CLUSTERID ------------------------------?-------?---------- DEPT???????????????????????????TABLE EMP????????????????????????????TABLE BONUS??????????????????????????TABLE SALGRADE???????????????????????TABLE TEST???????????????????????????TABLE T01????????????????????????????TABLE T02????????????????????????????TABLE 7?rows?selected. 06:52:31?SQL>?drop?table?t01;???????????????????????????????????????????????????????????????????????????????????????????????????????????? Table?dropped.查看回收站: 06:52:38?SQL>?show?recycle;?????????????????????????????????????????????????????????????????????????????????????????????????????????????? ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- T01??????????????BIN$qrJLbL74ZgvgQKjA8Agb/A==$0?TABLE????????2011-08-17:06:52:38--------除了system?表空間,其余表空間都有一個類似windows?回收站,在drop?table,實際上把table?改名后放入recyclebin。06:52:44?SQL>?flashback?table?t01?to?before?drop;???????????????????????????????????????????????????????????????????????????????????????? Flashback?complete.06:54:05?SQL>?show?recycle;?????????????????????????????????????????????????????????????????????????????????????????????????????????????? 06:54:07?SQL>??select?*?from?tab;???????????????????????????????????????????????????????????????????????????????????????????????????????? TNAME??????????????????????????TABTYPE??CLUSTERID ------------------------------?-------?---------- DEPT???????????????????????????TABLE EMP????????????????????????????TABLE BONUS??????????????????????????TABLE SALGRADE???????????????????????TABLE TEST???????????????????????????TABLE T01????????????????????????????TABLE T02????????????????????????????TABLE 7?rows?selected.06:54:11?SQL>?drop?table?t02?purge;????//purge??會徹底的刪除table???????????????????????????????????????????????????????????????????????????????????????????????????? Table?dropped. 06:54:40?SQL>?show?recycle;?? -----------清空recyclebin06:54:43?SQL>?drop?table?t01;???????????????????????????????????????????????????????????????????????????????????????????????????????????? Table?dropped. 06:55:49?SQL>?show?recycle;?????????????????????????????????????????????????????????????????????????????????????????????????????????????? ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- T01??????????????BIN$qrJLbL75ZgvgQKjA8Agb/A==$0?TABLE????????2011-08-17:06:55:49 06:55:51?SQL>?purge?recyclebin;?????????????????????????????????????????????????????????????????????????????????????????????????????????? Recyclebin?purged.06:55:57?SQL>?show?recycle;?????????????????????????????????????????????????????????????????????????????????????????????????????????????? 06:55:59?SQL>? --------------如何恢復(fù)同一個schema?下同名的table06:56:32?SQL>?drop?table?test;??????????????????????????????????????????????????????????????????????????????????????????????????????????? Table?dropped. 06:56:42?SQL>?create?table?test?as??select?*?from?emp;??????????????????????????????????????????????????????????????????????????????????? Table?created. 06:56:46?SQL>?select?*?from?tab;????????????????????????????????????????????????????????????????????????????????????????????????????????? TNAME??????????????????????????TABTYPE??CLUSTERID ------------------------------?-------?---------- DEPT???????????????????????????TABLE EMP????????????????????????????TABLE BONUS??????????????????????????TABLE SALGRADE???????????????????????TABLE BIN$qrJLbL76ZgvgQKjA8Agb/A==$0?TABLE TEST???????????????????????????TABLE 6?rows?selected. 06:56:50?SQL>?show?recycle;?????????????????????????????????????????????????????????????????????????????????????????????????????????????? ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- TEST?????????????BIN$qrJLbL76ZgvgQKjA8Agb/A==$0?TABLE????????2011-08-17:06:56:36 06:56:58?SQL>?flashback?table?test?to?before?drop;??????????????????????????????????????????????????????????????????????????????????????? flashback?table?test?to?before?drop * ERROR?at?line?1: ORA-38312:?original?name?is?used?by?an?existing?object 06:57:09?SQL>?flashback?table?test?to?before?drop?rename?to?test_old;???????????????????????????????????????????????????????????????????? Flashback?complete. 06:57:32?SQL>?select?*?from?tab;????????????????????????????????????????????????????????????????????????????????????????????????????????? TNAME??????????????????????????TABTYPE??CLUSTERID ------------------------------?-------?---------- DEPT???????????????????????????TABLE EMP????????????????????????????TABLE BONUS??????????????????????????TABLE SALGRADE???????????????????????TABLE TEST_OLD???????????????????????TABLE TEST???????????????????????????TABLE 6?rows?selected.flashback?Drop不支持sys用戶: ----system?表空間不存在recyclebin?,表直接被刪除 06:57:36?SQL>?conn?/as?sysdba???????????????????????????????????????????????????????????????????????????????????????????????????????????? Connected. 06:58:33?SQL>? 06:58:33?SQL>?create?table?test?as?select?*?from?user_tables;???????????????????????????????????????????????????????????????????????????? Table?created. 06:58:42?SQL>?drop?table?test;?????????????????????????????????????????????????????????????????????????????????????????????????????????? Table?dropped. 06:58:46?SQL>?show?recycle;?閃回表回收站——3個視圖 使用方面,閃回特性還要關(guān)注兩個回收站視圖。user_recyclebin、all_recyclebin、dba_recyclebin。? ? ?所謂的閃回drop,就是一種對象假刪除技術(shù)。當系統(tǒng)參數(shù)recyclebin被設(shè)置為on的時候,Oracle是開啟閃回drop功能的。當對數(shù)據(jù)表使用drop的時候,Oracle并不是將對象直接刪除,而是采用了對象改名。將刪除的數(shù)據(jù)表進行改名(邏輯上),改為BIN$開頭的一個編碼。這個編碼占據(jù)了原有數(shù)據(jù)表的所有資源(包括對象信息和存儲信息),但是不能算成為原對象的等價體。也就是說,如果我們直接操作這個修改名,系統(tǒng)會報錯,因為Oracle不認為這個對象是一個數(shù)據(jù)表。數(shù)據(jù)表T的數(shù)據(jù)字典信息被刪除,而改名的對象信息沒有加入其中。
轉(zhuǎn)載于:https://blog.51cto.com/tiany/1603975
總結(jié)
以上是生活随笔為你收集整理的Oracle Study之案例--数据恢复神器Flashback(2)的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: xcode6是否导入framework
- 下一篇: centos 7 安装 google c