logminer來恢復在表DDL之前被刪除的資料
做這個測試是因為前同事(開發人員)在客戶現場做維護誤刪除了一張表的記錄，但在刪除表之後修改了表的結構(修改了欄位的精度），發現誤刪除記錄後，想通對錶執行閃回查詢來恢復被刪除的記錄發現不能閃回了，因為表結構發現了修改(ORA-01466: unable to read data - table definition has changed)。而且這個資料庫沒有備份，只有歸檔。簡單的方法就是透過logminer來挖掘歸檔日誌來進行恢復。
一.建立測試表t1，並插入兩條記錄
SQL> create table t1(t_id number,t_name varchar2(50));
Table created.
SQL> alter table t1 add t_salary number(8);
Table altered.
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
T_ID NUMBER
T_NAME VARCHAR2(50)
T_SALARY NUMBER(8)
SQL> insert into t1 values(1,'jy',10000);
1 row created.
SQL> insert into t1 values(2,'wj',8000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
T_ID T_NAME T_SALARY
---------- -------------------------------------------------- ----------
1 jy 10000
2 wj
二.刪除表中記錄
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE -------------------- 2016-05-13 11:07:02
刪除表t1中t_id=2的記錄，透過logminer來恢復的記錄就是它
SQL> delete from t1 where t_id=2; 1 row deleted. SQL> commit; Commit complete.
在表結構沒有發生改變之前，嘗試使用閃回查詢執行成功
SQL> select * from t1 as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss');
T_ID T_NAME T_SALARY
---------- -------------------------------------------------- ----------
1 jy 10000
2 wj 8000
修改表結構，這裡只是簡單的修改了欄位型別的長度
SQL> alter table t1 modify t_salary number(10); Table altered.
在表結構發生改變之後，嘗試使用閃回查詢執行報錯
SQL> select * from t1 as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss');
select * from t1 as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
三.使用logminer來挖掘歸檔重做日誌
檢視當前的歸檔日誌檔案
SQL> set long 900 SQL> set linesize 900 SQL> set pagesize 900 SQL> col name for a100 SQL> select name,sequence#,first_change# from v$archived_log ; NAME SEQUENCE# FIRST_CHANGE# ---------------------------------------------------------------------------------------------------- ---------- ------------- /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_3_bkl8b4to_.arc 3 559310 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_4_bkl92rxm_.arc 4 590316 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_5_bkl94mv3_.arc 5 622788 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_6_bkl9612c_.arc 6 645078 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_7_bkl97n1p_.arc 7 656708 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_8_bkl99bb9_.arc 8 670463 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_9_bkl9c4v2_.arc 9 688685 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_10_bkl9dvf0_.arc 10 705191 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_11_bkl9gjpd_.arc 11 715113 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_12_bkl9hpqq_.arc 12 731487 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_13_bkl9m1gk_.arc 13 737140 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_14_bkl9mlg9_.arc 14 749330 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_15_bkl9n5ky_.arc 15 757183 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_16_bkl9o2p1_.arc 16 766296 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_17_bkl9oyz0_.arc 17 773422 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_18_bkl9qcpf_.arc 18 779449 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_19_bkl9rhv4_.arc 19 790719 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_20_bkl9ssv4_.arc 20 804844 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_21_bkl9w2xt_.arc 21 814648 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_22_bkl9xjx8_.arc 22 826389 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_23_bkl9ym63_.arc 23 834321 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_24_bklb0gh1_.arc 24 846100 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_25_bklb17f6_.arc 25 863715 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_26_bklb23gw_.arc 26 874870 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_27_bklb3pmz_.arc 27 885043 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_28_bklb5fl3_.arc 28 898767 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_29_bklb6xr6_.arc 29 912954 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_30_bklb89wn_.arc 30 923000 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_31_bklb97v9_.arc 31 929385 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_32_bklbb8yk_.arc 32 936055 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_33_bklbccbm_.arc 33 946964 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_34_bklbd33q_.arc 34 952518 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_35_bklbf7v8_.arc 35 955561 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_36_bklbg9mf_.arc 36 966403 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_37_bklcon42_.arc 37 977840 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_38_bkldl4pl_.arc 38 996480 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_39_bklfrzhj_.arc 39 1018201 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_40_bklor7cf_.arc 40 1019655 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_04_13/o1_mf_1_41_cjvtwjm2_.arc 41 1036392 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc 42 1055116 40 rows selected.
強制日誌切換將當前使用的聯機重做日誌檔案時行歸檔
SQL> alter system switch logfile; System altered. SQL> select name,sequence#,first_change# from v$archived_log ; NAME SEQUENCE# FIRST_CHANGE# ---------------------------------------------------------------------------------------------------- ---------- ------------- /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_3_bkl8b4to_.arc 3 559310 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_4_bkl92rxm_.arc 4 590316 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_5_bkl94mv3_.arc 5 622788 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_6_bkl9612c_.arc 6 645078 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_7_bkl97n1p_.arc 7 656708 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_8_bkl99bb9_.arc 8 670463 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_9_bkl9c4v2_.arc 9 688685 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_10_bkl9dvf0_.arc 10 705191 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_11_bkl9gjpd_.arc 11 715113 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_12_bkl9hpqq_.arc 12 731487 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_13_bkl9m1gk_.arc 13 737140 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_14_bkl9mlg9_.arc 14 749330 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_15_bkl9n5ky_.arc 15 757183 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_16_bkl9o2p1_.arc 16 766296 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_17_bkl9oyz0_.arc 17 773422 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_18_bkl9qcpf_.arc 18 779449 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_19_bkl9rhv4_.arc 19 790719 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_20_bkl9ssv4_.arc 20 804844 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_21_bkl9w2xt_.arc 21 814648 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_22_bkl9xjx8_.arc 22 826389 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_23_bkl9ym63_.arc 23 834321 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_24_bklb0gh1_.arc 24 846100 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_25_bklb17f6_.arc 25 863715 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_26_bklb23gw_.arc 26 874870 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_27_bklb3pmz_.arc 27 885043 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_28_bklb5fl3_.arc 28 898767 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_29_bklb6xr6_.arc 29 912954 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_30_bklb89wn_.arc 30 923000 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_31_bklb97v9_.arc 31 929385 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_32_bklbb8yk_.arc 32 936055 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_33_bklbccbm_.arc 33 946964 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_34_bklbd33q_.arc 34 952518 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_35_bklbf7v8_.arc 35 955561 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_36_bklbg9mf_.arc 36 966403 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_37_bklcon42_.arc 37 977840 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_38_bkldl4pl_.arc 38 996480 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_39_bklfrzhj_.arc 39 1018201 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_40_bklor7cf_.arc 40 1019655 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_04_13/o1_mf_1_41_cjvtwjm2_.arc 41 1036392 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc 42 1055116 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_43_cmbkro1j_.arc 43 1083648 41 rows selected.
向logminer增加需要分析的歸檔重做日誌檔案
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc',options=>dbms_logmnr.NEW); PL/SQL procedure successfully completed.
繼續向logminer增加需要分析的歸檔重做日誌檔案
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_43_cmbkro1j_.arc',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed.
執行分析
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); PL/SQL procedure successfully completed.
將分析的內容儲存在臨時表logmnr_contents中
SQL> create table logmnr_contents as select * from v$logmnr_contents ; Table created.
終止分析操作
SQL> execute dbms_logmnr.end_logmnr; PL/SQL procedure successfully completed.
查詢T1所產生的日誌記錄
SQL> select username,scn,timestamp,sql_redo,sql_undo from logmnr_contents where seg_name='T1';
USERNAME SCN TIMESTAMP SQL_REDO SQL_UNDO
------------------------------ ---------- --------- ------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------
UNKNOWN 1102088 13-MAY-16 create table t1(t_id number,t_name varchar2(50));
JY 1102114 13-MAY-16 alter table t1 add t_salary number(8);
UNKNOWN 1102129 13-MAY-16 insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('1','jy','10000'); delete from "JY"."T1" where "T_ID" = '1' and "T_NAME" = 'jy' and "T_SALARY" = '10000' and ROWID = 'AAANc6AAEAAAAGEAAA';
UNKNOWN 1102822 13-MAY-16 insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000'); delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA';
UNKNOWN 1103738 13-MAY-16 delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA'; insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000');
JY 1103789 13-MAY-16 alter table t1 modify t_salary number(10);
從查詢結果來看刪除操作對應的sql_redo為:delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA',對應的sql_undo為:insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000');為了恢復刪除的t_id=2的記錄，只需要執行sql_undo為insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000')的語句就能恢復。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-2104050/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- 恢復被rm意外刪除資料檔案
- 恢復 Git 被刪除的分支Git
- Git恢復被刪除的分支Git
- MySQL資料庫表誤刪除恢復(一)MySql資料庫
- 【原創】你誤刪除了一張表，請使用備份+歸檔來將資料庫恢復到刪除表之前的狀態資料庫
- [Oracle]Oracle資料庫資料被修改或者刪除恢復資料Oracle資料庫
- 通過flashback database恢復被刪除的表空間Database
- 伺服器資料恢復—EMC儲存資料卷被誤刪除如何恢復資料？伺服器資料恢復
- 【資料庫資料恢復】HP-UX系統ORACLE資料庫被誤刪除的資料恢復資料庫資料恢復UXOracle
- 恢復被刪除的Word選單
- 【伺服器資料恢復】VMFS分割槽被刪除並格式化的資料恢復案例伺服器資料恢復
- 【伺服器資料恢復】XenServer虛擬機器被誤操作刪除的資料恢復案例伺服器資料恢復Server虛擬機
- oracle恢復誤刪除資料Oracle
- linux下 恢復被rm意外刪除資料檔案Linux
- 表資料被誤操作的恢復
- 恢復被刪除的Word選單(轉)
- 【EM】資料表誤刪除故障模擬及恢復
- 【備份恢復】不使用rman工具就能恢復被rm刪除的資料檔案案例
- PostgreSQL中刪除的資料能否恢復SQL
- 【北亞資料恢復】輸入錯誤命令導致MySQL資料庫資料被刪除的資料恢復案例資料恢復MySql資料庫
- Redo log檔案被刪除恢復
- ORACLE 11g TSPITR恢復被刪除的表空間Oracle
- Oracle閃回刪除恢復誤刪資料Oracle
- 【伺服器資料恢復】NetApp儲存中lun被誤刪除的資料恢復過程伺服器資料恢復APP
- Sybase ASE資料庫恢復，Sybase資料恢復，資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev
- 使用ODU恢復被truncate表的資料
- MySQL使用mysqldump+binlog完整恢復被刪除的資料庫（轉）MySql資料庫
- EMC NAS中虛擬機器被誤刪除的資料恢復案例虛擬機資料恢復
- 【北亞資料庫資料恢復】使用delete未加where子句刪除全表資料的Mysql資料庫資料恢復資料庫資料恢復deleteMySql
- Oracle恢復誤刪除的資料檔案Oracle
- truncate table 誤刪除資料後的恢復
- 2 Day DBA-管理方案物件-執行備份和恢復-使用Oracle閃回刪除功能恢復被刪除的表物件Oracle
- oracle logminer恢復truncate table的資料Oracle
- Linux環境利用恢復被rm意外刪除資料檔案Linux
- DB2 恢復誤刪除的表DB2
- LINUX下資料被誤刪除、LINUX下資料被誤格式化後資料恢復Linux資料恢復
- Flashback Query恢復誤刪除資料(轉）