LogMnr-誤DML後使用logmnr對資料進行恢復
誤DML後使用logmnr對資料進行恢復
SQL> conn tangyun/tangyun
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
TANGYUN_TEST
MLOG$_T_PART
T_PART
LOGMNR
LOGMNR_REDO
MV_T_PART
TANGYUN
7 rows selected.
---------建立一張測試表，這裡為了輸出結果較明顯，表記錄不要太多
SQL> create table ty_log as select * from tangyun_test where rownum<=10;
Table created.
SQL> select count(1) count# from ty_log;
COUNT#
----------
9
SQL> delete from ty_log where tid<=5;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 130 52428800 2 NO CURRENT 896827 10-7月 -11
2 1 128 52428800 1 YES INACTIVE 894997 10-7月 -11
3 1 129 52428800 1 YES INACTIVE 896123 10-7月 -11
-----切換當前日誌，如果日誌已經被歸檔，則需要分析的是歸檔日誌，不過方法一樣
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 130 52428800 2 YES ACTIVE 896827 10-7月 -11
2 1 131 52428800 1 NO CURRENT 897105 10-7月 -11
3 1 129 52428800 1 YES INACTIVE 896123 10-7月 -11
SQL> col member format a60
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------
+DGSYSTEM/tgyun/onlinelog/group_1.257.747535069
+DGSYSTEM/tgyun/onlinelog/group_2.258.747535071
+DGSYSTEM/tgyun/onlinelog/group_3.259.747535075
+DGSYSTEM/tgyun/onlinelog/group_1.268.754483583
SQL> begin
2 sys.dbms_logmnr.add_logfile(logfilename => '+DGSYSTEM/tgyun/onlinelog/group_1.257.747535069',options => sys.dbms_logmnr.new);
3 end;
4 /
PL/SQL procedure successfully completed.
--------使用線上字典
SQL> begin
2 sys.dbms_logmnr.start_logmnr(options =>sys.dbms_logmnr.dict_from_online_catalog);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 sys.dbms_logmnr.end_logmnr();
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> col sql_redo format a60
SQL> col sql_undo format a60
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='TY_LOG';
SQL_REDO SQL_UNDO
------------------ -------------------------------------
create table ty_log as select * from tangyun_test where rown
um<=10;
---------------從輸出結果沒有看到任何的DML語句
------檢視SUPPLEMENTAL LOG DATA引數
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
--------10g版本預設SUPPLEMENTAL LOG DATA引數均為關閉
--------開啟SUPPLEMENTAL LOG DATA引數
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
/*==============開啟和關閉最小日誌=========================================================================
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
SQL> alter database drop supplemental log data;
Database altered.
SQL>
SQL>
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
*/===================================================================================================
--------開啟SUPPLEMENTAL LOG DATA引數後重新做測試
SQL> drop table ty_log purge;
Table dropped.
SQL> create table tylog as select * from tangyun_test;
Table created.
SQL> select count(1) from tylog;
COUNT(1)
----------
9
SQL> delete from tylog where tid<=5;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 130 52428800 2 YES INACTIVE 896827 10-7月 -11
2 1 131 52428800 1 NO CURRENT 897105 10-7月 -11
3 1 129 52428800 1 YES INACTIVE 896123 10-7月 -11
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------
+DGSYSTEM/tgyun/onlinelog/group_1.257.747535069
+DGSYSTEM/tgyun/onlinelog/group_2.258.747535071
+DGSYSTEM/tgyun/onlinelog/group_3.259.747535075
+DGSYSTEM/tgyun/onlinelog/group_1.268.754483583
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 130 52428800 2 YES INACTIVE 896827 10-7月 -11
2 1 131 52428800 1 YES ACTIVE 897105 10-7月 -11
3 1 132 52428800 1 NO CURRENT 897498 10-7月 -11
SQL> col filename format a60
SQL> select filename from v$logmnr_logs;
no rows selected
SQL> begin
2 sys.dbms_logmnr.add_logfile(logfilename => '+DGSYSTEM/tgyun/onlinelog/group_2.258.747535071',options => sys.dbms_logmnr.new);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select filename from v$logmnr_logs;
FILENAME
------------------------------------------------------------
+DGSYSTEM/tgyun/onlinelog/group_2.258.747535071
SQL> begin
2 sys.dbms_logmnr.start_logmnr(options =>sys.dbms_logmnr.dict_from_online_catalog);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='TYLOG';
SQL_REDO SQL_UNDO
------------------------------------------------------------ ------------------------------------------------------------
create table tylog as select * from tangyun_test;
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('01','T delete from "TANGYUN"."TYLOG" where "TID" = '01' and "TNAME"
ANGYUN'); = 'TANGYUN' and ROWID = 'AAANBSAAFAAAABMAAA';
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('02','T delete from "TANGYUN"."TYLOG" where "TID" = '02' and "TNAME"
EST'); = 'TEST' and ROWID = 'AAANBSAAFAAAABMAAB';
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('03','S delete from "TANGYUN"."TYLOG" where "TID" = '03' and "TNAME"
HXJ'); = 'SHXJ' and ROWID = 'AAANBSAAFAAAABMAAC';
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('04','S delete from "TANGYUN"."TYLOG" where "TID" = '04' and "TNAME"
SQL_REDO SQL_UNDO
------------------------------------------------------------ ------------------------------------------------------------
HSNC'); = 'SHSNC' and ROWID = 'AAANBSAAFAAAABMAAD';
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('05',' delete from "TANGYUN"."TYLOG" where "TID" = '05' and "TNAME"
湯雲'); = '湯雲' and ROWID = 'AAANBSAAFAAAABMAAE';
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('06','T delete from "TANGYUN"."TYLOG" where "TID" = '06' and "TNAME"
GYUN'); = 'TGYUN' and ROWID = 'AAANBSAAFAAAABMAAF';
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('08','t delete from "TANGYUN"."TYLOG" where "TID" = '08' and "TNAME"
est_RMAN'); = 'test_RMAN' and ROWID = 'AAANBSAAFAAAABMAAG';
SQL_REDO SQL_UNDO
------------------------------------------------------------ ------------------------------------------------------------
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('10','r delete from "TANGYUN"."TYLOG" where "TID" = '10' and "TNAME"
man'); = 'rman' and ROWID = 'AAANBSAAFAAAABMAAH';
insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('13','R delete from "TANGYUN"."TYLOG" where "TID" = '13' and "TNAME"
MAN'); = 'RMAN' and ROWID = 'AAANBSAAFAAAABMAAI';
delete from "TANGYUN"."TYLOG" where "TID" = '01' and "TNAME" insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('01','T
= 'TANGYUN' and ROWID = 'AAANBSAAFAAAABMAAA'; ANGYUN');
delete from "TANGYUN"."TYLOG" where "TID" = '02' and "TNAME" insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('02','T
= 'TEST' and ROWID = 'AAANBSAAFAAAABMAAB'; EST');
SQL_REDO SQL_UNDO
------------------------------------------------------------ ------------------------------------------------------------
delete from "TANGYUN"."TYLOG" where "TID" = '03' and "TNAME" insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('03','S
= 'SHXJ' and ROWID = 'AAANBSAAFAAAABMAAC'; HXJ');
delete from "TANGYUN"."TYLOG" where "TID" = '04' and "TNAME" insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('04','S
= 'SHSNC' and ROWID = 'AAANBSAAFAAAABMAAD'; HSNC');
delete from "TANGYUN"."TYLOG" where "TID" = '05' and "TNAME" insert into "TANGYUN"."TYLOG"("TID","TNAME") values ('05','
= '湯雲' and ROWID = 'AAANBSAAFAAAABMAAE'; 湯雲');
15 rows selected.
-------這裡如果資料量很大的情況下我們可以把分析結果存放在另外一張表中進行處理
SQL> create table ty_undo as select replace(sql_undo,'"','') sql_undo from v$logmnr_contents where table_name='TYLOG' and lower(sql_undo) like 'insert into%';
Table created.
SQL> select * from ty_undo;
SQL_UNDO
--------------------------------------------------------------------------------
insert into TANGYUN.TYLOG(TID,TNAME) values ('01','TANGYUN');
insert into TANGYUN.TYLOG(TID,TNAME) values ('02','TEST');
insert into TANGYUN.TYLOG(TID,TNAME) values ('03','SHXJ');
insert into TANGYUN.TYLOG(TID,TNAME) values ('04','SHSNC');
insert into TANGYUN.TYLOG(TID,TNAME) values ('05','湯雲');
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
--------可見，在10g中預設情況下LOGMNR已經不是一個可靠的資料獲取的方式，希望透過這種方式獲取丟失資料，則需要提前設定SUPPLEMENTAL LOG DATA。
其他參考語句：
begin
sys.dbms_logmnr.add_logfile(logfilename => '+DGSYSTEM/tgyun/onlinelog/group_2.258.747535071',options => sys.dbms_logmnr.addfile);
end;
begin
sys.dbms_logmnr.add_logfile(logfilename => '+DGSYSTEM/tgyun/onlinelog/group_2.258.747535071',options => sys.dbms_logmnr.removefile);
end;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
11g以上版本預設SUPPLEMENTAL LOG DATA為開啟狀態
參考文件：
http://ctchina.blog.sohu.com/128878209.html
http://yangtingkun.itpub.net/post/468/464865
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24930246/viewspace-1058003/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用Logmnr恢復誤刪的資料
- Mysql 誤刪資料進行恢復MySql
- Flashback Query 針對DML誤操作的恢復
- 升級失敗後對資料庫進行恢復 (2)資料庫
- 升級失敗後對資料庫進行恢復 (1)資料庫
- DM7使用DMRAMN對多次故障恢復後使用不同資料庫的歸檔進行恢復資料庫
- [Logmnr]對重做日誌進行資料探勘
- 歸檔路徑更改後，如何對資料庫進行恢復(轉)資料庫
- mysql使用binlog進行資料恢復MySql資料恢復
- 使用blockrecover 對有壞塊的資料檔案進行恢復BloC
- 資料庫resetlogs後進行rman恢復7資料庫
- 資料庫resetlogs後進行rman恢復6資料庫
- 資料庫resetlogs後進行rman恢復5資料庫
- 資料庫resetlogs後進行rman恢復4資料庫
- 資料庫resetlogs後進行rman恢復3資料庫
- 資料庫resetlogs後進行rman恢復2資料庫
- 資料庫resetlogs後進行rman恢復1資料庫
- flashback query和logmnr用於恢復資料的誤刪除（轉）
- [Logmnr]對歸檔日誌進行資料探勘
- 使用netbackup進行資料庫恢復案例資料庫
- Mongodb資料庫誤刪後的恢復MongoDB資料庫
- MySQL誤操作後如何快速恢復資料MySql
- 如何確保在對資料誤操之後可以快速的恢復資料
- 使用dbms_backup_restore進行資料庫恢復REST資料庫
- oracle資料庫災難挽救應急方案之DML誤操作恢復Oracle資料庫
- 使用mysqldump對mysql進行備份和恢復MySql
- 使用xtrabackup對mysql進行備份和恢復MySql
- truncate table 誤刪除資料後的恢復
- dbms_logmnr 線上挖歸檔 恢復資料
- rman恢復時跳過資料檔案，進行恢復
- rman恢復 使用switch映像副本進行恢復
- 如何在HarmonyOS對資料庫進行備份，恢復與加密資料庫加密
- 利用undo進行資料的恢復操作
- 【NetApp資料恢復案例】針對NetApp誤刪除資料的恢復APP資料恢復
- 使用logmnr分析歸檔日誌恢復被drop掉的資料表
- 基於LOGMINER 的表DML誤操作恢復
- Oracle恢復誤刪資料Oracle
- mysql誤刪資料恢復MySql資料恢復