動(dòng)手為王——利用logminer挖掘日志恢復(fù)誤操作

挖掘日志恢復(fù)誤操作

1 介紹：

LogMiner是Oracle數(shù)據(jù)庫提供的一個(gè)工具，它用于分析重做日志和歸檔日志所記載的事務(wù)操作。

Logminer是oracle從8i開始提供的用于分析重做日志信息的工具，它包括DBMS_LOGMNR和DBMS_LOGMNR_D兩個(gè)package。它既能分析redo log file，也能分析歸檔后的archive log file。在分析日志的過程中需要使用數(shù)據(jù)字典，一般先生成數(shù)據(jù)字典文件后使用，10g版本還可以使用在線數(shù)據(jù)字典。

Logminer也可以分析其它數(shù)據(jù)庫的重做日志文件，但是必須使用重做日志所在數(shù)據(jù)庫的數(shù)據(jù)字典，否則會(huì)出現(xiàn)無法識(shí)別的亂碼。另外被分析數(shù)據(jù)庫的操作系統(tǒng)平臺(tái)最好和當(dāng)前Logminer所在數(shù)據(jù)庫的運(yùn)行平臺(tái)一樣，且block size相同。

LogMiner的功能

1）確定數(shù)據(jù)庫的邏輯損壞時(shí)間

通過LogMiner可以準(zhǔn)確定位該誤操作的執(zhí)行時(shí)間和SCN值，然后通過基于時(shí)間恢復(fù)或者基于SCN恢復(fù)可以完全恢復(fù)該表數(shù)據(jù)。

 SQL> select scn_to_timestamp(2599788) from dual;

scn_to_timestamp(2599788)

-------------------

2016-05-01 06:06:01

SQL>select timestamp_to_scn(to_timestamp('2016-5-1 6:06:06','yyyy-mm-dd hh34:mi:ss')) FROM dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP('2016-5-16:06:06','YYYY-MM-DDHH24:MI:SS'))

------------------------------------------------------------------------

                                                                 2599788

2）確定事務(wù)級(jí)要執(zhí)行的精細(xì)邏輯恢復(fù)操作

通過LogMiner可以取得任何用戶的DML操作及相應(yīng)的UNDO操作，通過執(zhí)行UNDO操作可以取消用戶的錯(cuò)誤操作。

3）執(zhí)行后續(xù)審計(jì)

通過LogMiner可以跟蹤Oracle數(shù)據(jù)庫的所有DML、DDL和DCL操作，從而取得執(zhí)行這些操作的時(shí)間順序、執(zhí)行這些操作的用戶等信息。

追加日志

  重做日志用于實(shí)現(xiàn)例程恢復(fù)和介質(zhì)恢復(fù)，這些操作所需要的數(shù)據(jù)被自動(dòng)記錄在重做日志中。但是，重做應(yīng)用可能還需要記載其他列信息到重做日志中，記錄其他列的日志過程被稱為追加日志。

  默認(rèn)情況下，Oracle數(shù)據(jù)庫沒有開啟追加日志，從而導(dǎo)致默認(rèn)情況下LogMiner無法支持以下特征：

  1）索引簇、鏈行和遷移行；

  2）直接路徑插入；

  3）摘取LogMiner字典到重做日志；

  4）跟蹤DDL；

  5）生成鍵列的SQL_REDO和SQL_UNDO信息；

  6）LONG和LOB數(shù)據(jù)類型。

----如何修改追加日志數(shù)據(jù)模式：

SYS@test> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

SYS@test> alter database add supplemental log data;

Database altered.

SYS@test> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

----關(guān)閉追加日志

SYS@test> alter database drop supplemental log data;

Database altered.

SYS@test> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

2環(huán)境準(zhǔn)備(數(shù)據(jù)誤delete后并commit)

SYS@test> create user shall identified by shall;

User created.

SYS@test> grant connect,resource to shall;

Grant succeeded.

SHALL@test> create table test(id number,name varchar2(20));

Table created.

SHALL@test> begin

  2  for i in 1..100000 loop

  3  insert into test values(i,'zhong');

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SHALL@test> select count(*) from test;

  COUNT(*)

----------

    100000

SHALL@test> select * from test where id = 999;

        ID NAME

---------- --------------------

       999 zhong

SHALL@test> delete test;

100000 rows deleted.

SHALL@test> commit;

Commit complete.

SHALL@test> select * from test;

no rows selected

3開始數(shù)據(jù)挖掘找回已提交的刪除數(shù)據(jù)

（1）數(shù)據(jù)庫是關(guān)閉追加日志狀態(tài)：

SYS@test> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

（2）查詢庫redo位置

SYS@test> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/redo01_1.log

/u01/app/oracle/oradata/orcl/redo02_1.log

/u01/app/oracle/oradata/orcl/redo03_1.log

（3）生成日志挖掘隊(duì)列

SYS@test> exec dbms_logmnr.add_logfile ('/u01/app/oracle/oradata/orcl/redo01_1.log');

PL/SQL procedure successfully completed.

SYS@test> exec dbms_logmnr.add_logfile ('/u01/app/oracle/oradata/orcl/redo02_1.log');

PL/SQL procedure successfully completed.

SYS@test> exec dbms_logmnr.add_logfile ('/u01/app/oracle/oradata/orcl/redo03_1.log');

PL/SQL procedure successfully completed.

----如果剛做了誤操作，日志未切換，可以只添加當(dāng)前redo

SYS@test> select group#,status from v$log;

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

SYS@test> col member for a50

SYS@test> select group#,member,type from v$logfile;

    GROUP# MEMBER                                             TYPE

---------- -------------------------------------------------- -------

         1 /u01/app/oracle/oradata/orcl/redo01_1.log          ONLINE

         2 /u01/app/oracle/oradata/orcl/redo02_1.log          ONLINE

         3 /u01/app/oracle/oradata/orcl/redo03_1.log          ONLINE

（4）開始挖掘

SYS@test> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

（5）從v$logmnr_contents查前滾SQL和反算回來的回滾SQL

SYS@test> select sql_redo from v$logmnr_contents where lower(sql_redo) like '%delete%' and seg_name='TEST';

redo數(shù)據(jù)如下：

SQL_REDO

--------------------------------------------------------------------------------

delete from "SHALL"."TEST" where "ID" = '42016' and "NAME" = 'zhong' and ROWID =

 'AAAWGEAAEAAAOSfABL';

delete from "SHALL"."TEST" where "ID" = '42017' and "NAME" = 'zhong' and ROWID =

 'AAAWGEAAEAAAOSfABM';

delete from "SHALL"."TEST" where "ID" = '42018' and "NAME" = 'zhong' and ROWID =

 'AAAWGEAAEAAAOSfABN';

delete from "SHALL"."TEST" where "ID" = '42019' and "NAME" = 'zhong' and ROWID =

undo數(shù)據(jù)如下：

SYS@test> select sql_redo from v$logmnr_contents where lower(sql_undo) like '%delete%' and seg_name='TEST';

SQL_REDO

--------------------------------------------------------------------------------

insert into "SHALL"."TEST"("ID","NAME") values ('5039','zhong');

insert into "SHALL"."TEST"("ID","NAME") values ('5040','zhong');

insert into "SHALL"."TEST"("ID","NAME") values ('5041','zhong');

insert into "SHALL"."TEST"("ID","NAME") values ('5042','zhong');

insert into "SHALL"."TEST"("ID","NAME") values ('5043','zhong');

insert into "SHALL"."TEST"("ID","NAME") values ('5044','zhong');

insert into "SHALL"."TEST"("ID","NAME") values ('5045','zhong');

insert into "SHALL"."TEST"("ID","NAME") values ('5046','zhong');

SYS@test> set linesize 200

SYS@test> set pagesize 10000

SYS@test> create table shall as select sql_redo from v$logmnr_contents where lower(sql_undo) like '%delete%' and seg_name='TEST';

SYS@test> select * from shall;

（6）最后結(jié)束挖掘：

SYS@test> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.