ITPub博客

首页 > 数据库 > Oracle > 动手为王——利用logminer挖掘日志恢复误操作

动手为王——利用logminer挖掘日志恢复误操作

Oracle 作者:kunlunzhiying 时间:2017-07-31 10:47:32 0 删除 编辑


挖掘日志恢复误操作

1 介绍:

LogMinerOracle数据库提供的一个工具,它用于分析重做日志和归档日志所记载的事务操作。

Logmineroracle8i开始提供的用于分析重做日志信息的工具,它包括DBMS_LOGMNRDBMS_LOGMNR_D两个package。它既能分析redo log file,也能分析归档后的archive log file。在分析日志的过程中需要使用数据字典,一般先生成数据字典文件后使用,10g版本还可以使用在线数据字典。

Logminer也可以分析其它数据库的重做日志文件,但是必须使用重做日志所在数据库的数据字典,否则会出现无法识别的乱码。另外被分析数据库的操作系统平台最好和当前Logminer所在数据库的运行平台一样,且block size相同。

 

LogMiner的功能

1)确定数据库的逻辑损坏时间

通过LogMiner可以准确定位该误操作的执行时间和SCN值,然后通过基于时间恢复或者基于SCN恢复可以完全恢复该表数据。

 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 hh24:mi:ss')) FROM dual;

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

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

                                                                 2599788

2)确定事务级要执行的精细逻辑恢复操作

通过LogMiner可以取得任何用户的DML操作及相应的UNDO操作,通过执行UNDO操作可以取消用户的错误操作。

3)执行后续审计

通过LogMiner可以跟踪Oracle数据库的所有DMLDDLDCL操作,从而取得执行这些操作的时间顺序、执行这些操作的用户等信息。

 

追加日志

  重做日志用于实现例程恢复和介质恢复,这些操作所需要的数据被自动记录在重做日志中。但是,重做应用可能还需要记载其他列信息到重做日志中,记录其他列的日志过程被称为追加日志。

  默认情况下,Oracle数据库没有开启追加日志,从而导致默认情况下LogMiner无法支持以下特征:

  1)索引簇、链行和迁移行;

  2)直接路径插入;

  3)摘取LogMiner字典到重做日志;

  4)跟踪DDL

  5)生成键列的SQL_REDOSQL_UNDO信息;

  6LONGLOB数据类型。

 

 

----如何修改追加日志数据模式:

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

----关闭追加日志

SYS@test> alter database drop supplemental log data;

Database altered.

 

SYS@test> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

2环境准备(数据误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开始数据挖掘找回已提交的删除数据

1)数据库是关闭追加日志状态:

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)生成日志挖掘队列

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.

 

----如果刚做了误操作,日志未切换,可以只添加当前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数据如下:

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数据如下:

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)最后结束挖掘:

SYS@test> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28211342/viewspace-2142828/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
IT搬砖

注册时间:2014-07-28

  • 博文量
    665
  • 访问量
    333727