利用flashback閃回表和資料

xypincle發表於2017-04-19

  1. 1.將oracle關閉
  2. shutdown immediate;

  4. 2.啟動到mount狀態
  5. startup mount;

  7. 3.檢查oracle是否開啟了archivelog模式，如果沒開，則可能會報錯
  8. 3.1 開啟歸檔
  9. alter database archivelog;
  10. 3.2 開啟閃回特性
  11. alter database flashback on;
  12. 注意：
  13. 如果開啟了歸檔，則可以順利開啟閃回特性，否則會報下列錯誤：
  14. ERROR at line 1:
  15. ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
  16. ORA-38707: Media recovery is not enabled.

  18. 4.啟動oracle到open狀態
  19. alter database open;

  21. 5.檢視flashback的閃回時間
  22. show parameter undo_retention ;
  23. 預設是900秒，也就是15分鐘。

  25. 以下為具體的操作：
  26. flashback：即回閃，是從oracle9i就開始提出的一種操作恢復的功能，在oracle10g中進行了增強和修改，透過回閃，使用者可以完成許多不可能恢復的工作，目前oracle10g的回閃包括以下特性；
  27. 1〉Oracle falshback Database. 特性允許oracle透過Flashback database語句，將資料庫會滾到前一個時間點或者scn上，而不需要作時間點的恢復工作！
  28. 2〉oracle falshback table. 特性允許oracle透過flashback table語句，將表會滾到前一個時間點或者scn上。
  29. 3〉oracle falshback drop. 特性允許oracle把恢復drop掉的table或者索引。
  30. 4〉oracle falshback version query. 特性可以得到特定的表在某一個時間段內的任何修改記錄！
  31. 5〉oracle falshback transaction query 特性可以限制使用者在某一個事務級別上檢查資料庫的修改操作，適用於診斷問題、分析效能、審計事務。
  32. 一：oracle falshback table
  33. oracle flashback table即回閃表
  34. 可以進行的操作：操作1>flashback table tablename to befor drop；
  35. 對於誤drop的table此可以使用本操作，還原drop的table！
  36. 此操作需要使用者有以下的許可權：
  37. A：falshback any table的系統許可權或者是某一個表的flashback許可權。
  38. B：有table的select、insert、delect、alter許可權。
  39. C：有table的row Movement許可權
  40. SQL> set serveroutput on
  41. SQL> set time on
  42. 11:24:37 SQL> set feadback off;
  43. 11:24:59 SQL> select * from tab;
  44. TNAME TABTYPE CLUSTERID
  45. ------------------------------ ------- ----------
  46. B TABLE 
  47. STEST TABLE 
  48. TA TABLE 
  49. GROUPINGTEST TABLE 
  50. COLTABLE TABLE 
  51. ROWTABLE TABLE 
  52. COLTABLE1 TABLE 
  53. ROWTABLE1 TABLE 
  54. A1 TABLE 
  55. A TABLE 
  56. ----------------------
  57. 可以看到本使用者下有table A，現在drop它；
  58. -----------------------
  59. 11:25:01 SQL> drop table a;
  60. 11:26:18 SQL> select * from tab;
  61. TNAME TABTYPE CLUSTERID
  62. ------------------------------ ------- ----------
  63. B TABLE 
  64. STEST TABLE 
  65. TA TABLE 
  66. GROUPINGTEST TABLE 
  67. COLTABLE TABLE 
  68. ROWTABLE TABLE 
  69. COLTABLE1 TABLE 
  70. ROWTABLE1 TABLE 
  71. BIN$dFFlS6jJQLShRwRQ+kLJMg==$0 TABLE 
  72. A1 TABLE 
  73. ----------------------
  74. table已經刪除，可以看到table並不是真正意義上的刪除掉了，而是多了一個"BIN$dFFlS6jJQLShRwRQ+kLJMg==$0"的表，這個表是原來的A表，這是oracle的recyclebin，一個類似垃圾回收站的機制。當使用者刪除表以後把表放到recyclebin中，而不是刪除掉！
  75. ----------------------
  76. 現在使用命令恢復表A
  77. 11:26:29 SQL> flashback table a to before drop;
  78. 11:30:45 SQL> select * from tab;
  79. TNAME TABTYPE CLUSTERID
  80. ------------------------------ ------- ----------
  81. B TABLE 
  82. STEST TABLE 
  83. TA TABLE 
  84. GROUPINGTEST TABLE 
  85. COLTABLE TABLE 
  86. ROWTABLE TABLE 
  87. COLTABLE1 TABLE 
  88. ROWTABLE1 TABLE 
  89. A1 TABLE 
  90. A TABLE 
  91. 可以看到表A已經恢復！
  92. 可以進行的操作：操作2>flashback table tablename to scn/timestamp(a timestamp);
  93. 此操作可以利用scn（在後面介紹）或者timestamp將table恢復到某一個時間點！
  94. -----給table row movement許可權
  95. 11:41:49 SQL> alter table a enable row movement;
  96. ----插入一條記錄
  97. 11:44:16 SQL> insert into a values('www',11);
  98. 11:45:15 SQL> commit;
  99. ----得到該記錄的scn：1576479
  100. 11:45:20 SQL> select a.*,ora_rowscn from a;
  101. NAME AGE ORA_ROWSCN
  102. ---------- ---------- ----------
  103. 500 65 1576479
  104. 600 65 1576479
  105. 111 10 1576479
  106. 222 100 1576479
  107. 333 30 1576479
  108. 444 40 1576479
  109. 555 50 1576479
  110. www 11 1576479
  111. 888 34 1573035
  112. ---刪除一條記錄
  113. 11:45:55 SQL> delete from a where name ='www';
  114. 11:46:19 SQL> commit;
  115. ---利用scn命令恢復刪掉的紀錄！
  116. 11:46:20 SQL> flashback table a to scn 1576479;
  117. 11:46:49 SQL> select * from a;
  118. NAME AGE
  119. ---------- ----------
  120. 500 65
  121. 600 65
  122. 111 10
  123. 222 100
  124. 333 30
  125. 444 40
  126. 555 50
  127. www 11
  128. 888 34
  129. ---利用timestamp命令恢復刪掉的紀錄！
  130. 11:46:54 SQL> flashback table a to timestamp to_timestamp('2007-04-27 11:45:20','yyyy-mm-dd hh24:mi:ss');
  131. 11:56:28 SQL> select * from a;
  132. NAME AGE
  133. ---------- ----------
  134. 500 65
  135. 600 65
  136. 111 10
  137. 222 100
  138. 333 30
  139. 444 40
  140. 555 50
  141. 888 34
  142. 附：--關於scn的介紹：
  143. SCN：system change numbers，oracle10g的每一個表都有ora_rowscn列！但是desc是看不到改列的。
  144. 可以透過select ora_rowscn from table 得到每一條結果集當前的SCN，timestamp_to_scn()將scn轉換到stmestamp；scn_to_timestamp()將timestamp轉換到scn。
  145. 二：flashback drop
  146. 利用flashback drop oracle10g可以對DDL操作進行恢復,oracla提供類似回收站的recyclebin來收集被刪除的物件，其實物件在刪除的時候oracle把物件寫到一個資料字典表中，當使用者不需要該物件的時候，可以利用purge命令來從回收站進行清除。
  147. --察看使用者下所有表
  148. SQL> select * from tab;
  149. TNAME TABTYPE CLUSTERID
  150. ------------------------------ ------- ----------
  151. B TABLE 
  152. STEST TABLE 
  153. TA TABLE 
  154. GROUPINGTEST TABLE 
  155. COLTABLE TABLE 
  156. ROWTABLE TABLE 
  157. COLTABLE1 TABLE 
  158. ROWTABLE1 TABLE 
  159. SYS_TEMP_FBT TABLE 
  160. A1 TABLE 
  161. A TABLE
  162. Executed in 0.078 seconds
  163. --drop 表A1
  164. SQL> drop table a1;
  165. Executed in 0.047 seconds
  166. --再察看此時使用者所擁有的所有表；
  167. SQL> select * from tab;
  168. TNAME TABTYPE CLUSTERID
  169. ------------------------------ ------- ----------
  170. B TABLE 
  171. STEST TABLE 
  172. TA TABLE 
  173. GROUPINGTEST TABLE 
  174. COLTABLE TABLE 
  175. ROWTABLE TABLE 
  176. COLTABLE1 TABLE 
  177. ROWTABLE1 TABLE 
  178. SYS_TEMP_FBT TABLE 
  179. A TABLE 
  180. BIN$fPHKLxsBTBq6+bjBLPEJaw==$0 TABLE
  181. Executed in 0.078 seconds
  182. --如上可以看到A1表drop的同時，當前使用者下又產生了一個以BIN開頭的新表BIN$fPHKLxsBTBq6+bjBLPEJaw==$0，這就是原來的A1表
  183. SQL> set timing off
  184. SQL> set time on
  185. 14:25:40 SQL> select t.object_name,t.original_name from recyclebin t;
  186. OBJECT_NAME ORIGINAL_NAME
  187. ------------------------------ --------------------------------
  188. BIN$fPHKLxsBTBq6+bjBLPEJaw==$0 A1
  189. ---採用flashback table命令恢復剛才刪除的表A1
  190. 14:29:45 SQL> flashback table a1 to before drop;
  191. 14:29:49 SQL> select * from tab;
  192. TNAME TABTYPE CLUSTERID
  193. ------------------------------ ------- ----------
  194. B TABLE 
  195. STEST TABLE 
  196. TA TABLE 
  197. GROUPINGTEST TABLE 
  198. COLTABLE TABLE 
  199. ROWTABLE TABLE 
  200. COLTABLE1 TABLE 
  201. ROWTABLE1 TABLE 
  202. SYS_TEMP_FBT TABLE 
  203. A1 TABLE 
  204. A TABLE 
  205. --當使用者確實不需要該物件時候，可以採用pruge來徹底刪除改物件
  206. 14:29:55 SQL> drop table a1;
  207. 14:31:13 SQL> select * from tab;
  208. TNAME TABTYPE CLUSTERID
  209. ------------------------------ ------- ----------
  210. B TABLE 
  211. STEST TABLE 
  212. TA TABLE 
  213. GROUPINGTEST TABLE 
  214. COLTABLE TABLE 
  215. ROWTABLE TABLE 
  216. COLTABLE1 TABLE 
  217. ROWTABLE1 TABLE 
  218. SYS_TEMP_FBT TABLE 
  219. A TABLE 
  220. BIN$N+i42FTvSSemvMrH6frCQg==$0 TABLE
  221. 14:31:32 SQL> purge table "BIN$N+i42FTvSSemvMrH6frCQg==$0";
  222. 14:31:49 SQL> select * from tab;
  223. TNAME TABTYPE CLUSTERID
  224. ------------------------------ ------- ----------
  225. B TABLE 
  226. STEST TABLE 
  227. TA TABLE 
  228. GROUPINGTEST TABLE 
  229. COLTABLE TABLE 
  230. ROWTABLE TABLE 
  231. COLTABLE1 TABLE 
  232. ROWTABLE1 TABLE 
  233. SYS_TEMP_FBT TABLE 
  234. A TABLE 
  235. --用purge命令收站想清空回收站裡的別的表
  236. 14:31:56 SQL> purge recyclebin;
  237. 14:34:02 SQL> select object_name from recyclebin;
  238. OBJECT_NAME
  239. ------------------------------
  240. 三：oracle falshback version query--閃回版本查詢
  241. oracle10g falshback 能將所有做了提交的行進行記錄，就類似於審計的功能，透過falshback可以查詢什麼時候執行了什麼操作，非常方便，包括閃回版本的查詢和審計等。
  242. 1〉閃回版本的查詢
  243. 14:41:22 SQL> insert into a values('zhao',24);
  244. 14:42:01 SQL> commit;
  245. --查詢當前版本
  246. 14:34:45 SQL> select a.*,ora_rowscn from a;
  247. NAME AGE ORA_ROWSCN
  248. ---------- ---------- ----------
  249. 500 65 1577059
  250. 600 65 1577059
  251. 111 10 1577059
  252. 222 100 1577059
  253. 333 30 1577059
  254. 444 40 1577059
  255. 555 50 1577059
  256. 888 34 1573035
  257. 14:41:22 SQL> insert into a values('zhao',24);
  258. 14:42:01 SQL> commit;
  259. 14:42:03 SQL> select a.*,ora_rowscn from a;
  260. NAME AGE ORA_ROWSCN
  261. ---------- ---------- ----------
  262. 500 65 1577059
  263. 600 65 1577059
  264. 111 10 1577059
  265. 222 100 1577059
  266. 333 30 1577059
  267. 444 40 1577059
  268. 555 50 1577059
  269. 888 34 1573035
  270. zhao 24 1585133
  271. --A:利用scn查詢以前版本的記錄
  272. 14:42:18 SQL> select * from a as of scn 1577059;
  273. NAME AGE
  274. ---------- ----------
  275. 500 65
  276. 600 65
  277. 111 10
  278. 222 100
  279. 333 30
  280. 444 40
  281. 555 50
  282. 888 34
  283. --B:利用timestamp查詢以前版本的記錄
  284. 14:42:51 SQL> select * from a as of timestamp scn_to_timestamp(1577059);
  285. NAME AGE
  286. ---------- ----------
  287. 500 65
  288. 600 65
  289. 111 10
  290. 222 100
  291. 333 30
  292. 444 40
  293. 555 50
  294. www 11
  295. 888 34
  296. 2〉審計的功能：
  297. 14:50:06 SQL> select a.*,versions_operation,versions_xid,versions_starttime from a
  298. 2 versions between timestamp minvalue and maxvalue;
  299. NAME AGE VERSIONS_OPERATION VERSIONS_XID VERSIONS_STARTTIME
  300. ---------- ---------- ------------------ ---------------- --------------------
  301. 600 65 
  302. 111 10 
  303. 222 100 
  304. 333 30 
  305. 444 40 
  306. 555 50 
  307. 888 34 
  308. zhao 24 I 0500110006030000 27-4月 -07 02.42.00 下午
  309. --可以看出 在27-4月 -07 02.42.00 下午 插入（I）了一條記錄
  310. 14:50:53 SQL> a order byversions_starttime;
  311. 14:51:23 SQL> run;
  312. NAME AGE VERSIONS_OPERATION VERSIONS_XID VERSIONS_STARTTIME
  313. ---------- ---------- ------------------ ---------------- ----------------------
  314. 500 65 
  315. 600 65 
  316. 111 10 
  317. 222 100 
  318. 333 30 
  319. 444 40 
  320. 555 50 
  321. 888 34 
  322. zhao 24 I 0500110006030000 27-4月 -07 02.42.00 下午
  323. 四：oracle falshback transaction query回閃事務查詢
  324. oracle10回閃事務功能提供對過去某段時間內所完成的事務的查詢和撤銷！
  325. 14:59:56 SQL> select undo_sql,table_name from flashback_transaction_query x where x.table_owner=upper('zxt') and x.xid ='0500110006030000';
  326. UNDO_SQL TABLE_NAME
  327. -------------------------------------------------------------------------------- --------------
  328. delete from "ZXT"."A" where ROWID = 'AAANOsAAGAAAAMWAAA'; A
  329. --其中UNDO_SQL 就是當時對錶A的DML操作;
  330. 五：oracle falshback Database：閃回資料庫
  331. oracle10g提供資料庫級別的閃回，可以使整個資料庫的資料恢復到某一個時間點或者SCN上！由於牽扯到資料庫的批次操作，這個方面的知識暫時無法研究了！

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

相關文章