ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 记一次删除数据用户定位

记一次删除数据用户定位

原创 Linux操作系统 作者:realkid4 时间:2011-03-22 00:19:57 0 删除 编辑

 

对数据库而言,最重要宝贵的内容就是其中的数据。数据安全性和完整性是任何商业数据库存在的底线。审计Audit数据变化、数据对象结构变化的功能在Oracle中是一个可选组件。在日常中,我们经常会有跟踪审计数据变化的需要,这种时候Oracle Logminer组件就可以帮助我们实现这个目的。

 

 

中午临吃饭前,同事LL提交了一个问题。他的一个开发小组成员在进行开发时,准备的数据总是不知被谁删除,而且是进行反复的删除。开发成员很多,所以希望能够将删除的用户确定出来,并且将数据恢复。

 

 

这个问题应该在一些开发团队比较常见,开发环境特别是数据库环境是共享的。大家进行开发中,难免会将他人的数据删除破坏。笔者的解决方法也是两个部分,数据恢复和删除用户定位。

 

 

环境说明

 

笔者开发环境是Oracle 11gR2。删除操作设计的数据表有多个,但初步确定是同一批误删除过程,所以找一张数据表进行研究分析。

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0    Production

 

TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 Production

 

 

SQL> select * from ref_PSB;

 

PSB_CODE CREATE_USER          CREATE_DATE UPDATE_USER          UPDATE_DATE PSB_NAME

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

 

 

 

在笔者开始入手的时候,数据表ref_PSB已经全部被清除,没有留下数据痕迹。

 

 

误删除数据恢复

 

根据开发人员反映,误删除数据操作是在一两个小时内进行的。这样选择使用闪回数据的方法是最迅速方便的手段。对数据闪回,请参见《使用闪回挽救我们的数据》http://space.itpub.net/17203031/viewspace-683706。原理是利用Undo中的数据映像,对应指定时间或者SCN的时点进行查询。

 

 

首先,尝试闪回查询到两小时前的数据。

 

 

SQL> select * from ref_PSB as of timestamp to_timestamp('2011-03-21 10:00:00','yyyy-mm-dd hh24:mi:ss');

 

PSB_CODE CREATE_USER          CREATE_DATE UPDATE_USER     UPDATE_DATE PSB_NAME

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

CN       1                    2011/3/18                                    Test

AUAU     Ansel                2011/3/21                                    Test

EN       1                    2011/3/18                                    Test

 

 

 

定位到当天10:00时,数据表ref_PSB数据还是存在的,并且和开发团队确认的确是被删除的那部分数据。接下来可以尝试进行恢复。

 

 

SQL> insert into ref_PSB select * from ref_PSB as of timestamp to_timestamp('2011-03-21 10:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> commit;

SQL> select * from ref_PSB; //数据被恢复

 

PSB_CODE CREATE_USER          CREATE_DATE UPDATE_USER     UPDATE_DATE PSB_NAME

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

CN       1                    2011/3/18                                    Test

AUAU     Ansel                2011/3/21                                    Test

EN       1                    2011/3/18                                    Test

 

 

 

这样,数据被成功恢复。其他数据表于此同理。

 

在此处注意一下,Oracle中有一个参数是undo_retention,默认是900s,也就是约15分钟。有一种观点认为这个是支持Undo闪回数据的时间,实际上是不准确的。Undo闪回查询支持的范围与Undo Tablespace的设置大小,系统繁忙程度和其他一些参数设置相关。很多时候,超过15分钟的闪回也是支持的。如果闪回的时间太长,Oracle是会报错的。

 

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

 

 

这样,我们就利用闪回flashback“拯救”回我们被误删的数据。下一个问题略复杂,就是定位到是谁删除了数据?

 

 

Logminer进行审计挖掘

 

 

Oracle来说,只要进行ddl操作或者提交事务,数据的结构或者内容就发生了变化。所有的变化,都会记录在redo log日志中,以序列化方式进行保存。Oracle进行所谓的先写日志的操作,保证所有的commit操作都会被落实在数据库中,不会发生事务不完整的现象。同时,配合检查点checkpoint机制,减少每次实例恢复过程的前滚量。

 

这样一来无论是DDL操作还是DML操作,实际上都是能够在Oracle redo log中找到对应的记录。Redo Log就变成了一个审计信息宝库。如果开启了归档模式archived mode,数据库变化就可以进行连续的追踪审计跟踪。于是,Oracle提供了Logminer组件帮助我们进行挖掘分析工作。

 

 

首先,我们需要定位到进行误删除操作的redo log文件是哪个?是否已经被归档。

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME

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

         1          1        496  314572800        512          2 NO       CURRENT              103856429 2011/3/21 7 281474976710

         2          1        494  314572800        512          2 YES      INACTIVE             103612266 2011/3/19 2    103679841 2011/3/20 6

         3          1        495  314572800        512          2 YES      INACTIVE             103679841 2011/3/20 6    103856429 2011/3/21 7

 

SQL> set heading off;

SQL> select * from v$log;

         1          1        496  314572800        512          2 NO       CURRENT              103856429 2011/3/21 7:01:02 281474976710655

         2          1        494  314572800        512          2 YES      INACTIVE             103612266 2011/3/19 23:09:30    103679841 2011/3/20 6:33:02

         3          1        495  314572800        512          2 YES      INACTIVE             103679841 2011/3/20 6:33:02    103856429 2011/3/21 7:01:02

 

 

注意,当前的online log组的第一条记录(标红),状态为current,表示正在进行处理使用。该文件对应的开始时间为2011/3/21 7:01:02,结束时间为极大值,说明未确定。发生错误删除的时间是上午某个时间。所以,我们可以确定,进行误删除的语句记录一定在redo log group 1中。下面定位到文件名。

 

 

 

SQL> select * from v$logfile;

 

    GROUP# STATUS TYPE MEMBER             IS_RECOVERY_DEST_FILE

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

         1         ONLINE  /BSNdata01/oradata/BSNDEV/redo01.log    NO

         1         ONLINE  /BSNdata03/oradata/BSNDEV/redo01b.log   NO

         2         ONLINE  /BSNdata01/oradata/BSNDEV/redo02.log    NO

         2         ONLINE  /BSNdata03/oradata/BSNDEV/redo02b.log   NO

(篇幅原因,省略部分

 

 

可见,group1日志组有两个online成员member。两者内容相同,物理位置不同,互为备份使用。这样,分析对象确定。

 

接下来,使用logminer组件。首先,加载分析文件列表。

 

SQL> exec dbms_logmnr.add_logfile('/BSNdata01/oradata/BSNDEV/redo01.log',Options => dbms_logmnr.NEW);

 

第二步,因为在这个过程中,没有发生数据字典删除的情况,所以直接使用online当前字典就可以解决问题。

 

SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

 

 

其中,start_logmnr表示开始进行日志分析,options中的Dict_Form_ONLINE_CATALOG表示使用当前的数据字典进行分析,修饰生成语句。

 

对分析完的Log日志结果,可以通过查询v$logmnr_contents,进行分析。

--先看一下有多少记录

SQL> select count(*) from v$logmnr_contents;

 

  COUNT(*)

----------

61048 数目过大,可以应用一定的筛选条件,进行筛选。

 

SQL> select count(*) from v$logmnr_contents where upper(SQL_REDO) like '%REF_PSB%' and upper(SQL_REDO) like '%DELETE%' and seg_name='REF_PSB';

 

  COUNT(*)

----------

        12

 

SQL> select scn, timestamp, sql_redo, client_id from  v$logmnr_contents where upper(SQL_REDO) like '%REF_PSB%' and upper(SQL_REDO) like '%DELETE%' and seg_name='REF_PSB';

 

       SCN TIMESTAMP            SQL_REDO                                                                         CLIENT_ID

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

 104016620 2011/3/21 11:05:34   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'AUAU' and "CREATE_USER" =

 104016620 2011/3/21 11:05:34   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'CN' and "CREATE_USER" = '

 104016620 2011/3/21 11:05:34   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'EN' and "CREATE_USER" = '

 104016665 2011/3/21 11:06:10   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'AUAU' and "CREATE_USER" =

 104016665 2011/3/21 11:06:10   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'CN' and "CREATE_USER" = '

 104016665 2011/3/21 11:06:10   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'EN' and "CREATE_USER" = '

 104016676 2011/3/21 11:06:12   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'ABC' and "CREATE_USER" = 

 104017920 2011/3/21 11:17:19   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'AUAU' and "CREATE_USER" =

 104017935 2011/3/21 11:17:22   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'ABC' and "CREATE_USER" = 

 104021463 2011/3/21 11:42:44   delete from "BSN_COMMON"."REF_PSB" where ROWID = 'AAAZpTAAYAAAAMwAAB';          

 104021705 2011/3/21 11:44:41   delete from "BSN_COMMON"."REF_PSB" where ROWID = 'AAAZpTAAYAAAAMWAAA';          

 104021907 2011/3/21 11:45:39   delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'ABC' and "CREATE_USER" = 

 

12 rows selected

 

 

 

经过一系列的筛选,我们获取到一个12条记录与REF_PSB有关删除delete操作的结果集合。这里面详细记载了操作过程。

 

1、在“2011/3/21 11:05:34”有用户执行了操作,将原有的三条数据进行删除delete。反映在sql redo上的就是对应的delete操作语句,因为三条记录的时间相同(SCN相同),必然是直接的delete ref_PSB语句。

2、在之后的“2011/3/21 11:06:10”和之后一些时间点,有用户执行了全表的delete操作。并且将开发组其他数据进行删除。值得注意的是一个PSB_CODE=‘ABC’的操作,这个数据并没有出现在误删除之后,也没有出现在误删除之前。

 

到此,我们借助Logminer,发现了现场过程。唯一美中不足的是v$logmnr_contents中的client_id为空,没有办法显示出执行客户端的任何信息。通常,如果我们直接使用客户端进行连接,如sqlplus或者pl/sql developer,是会在这里留下个人信息痕迹。但是此处没有留下,只有可能误删除用户是通过前端应用程序进行删除,就不会有痕迹留下。

 

问题似乎无解了。但是我们发现了PSB_CODE=ABC这个痕迹。ABC是开发组一个同事的姓名缩写,难道是于此同事相关。结果通过沟通,发现原来是执行测试脚本时,进行了全表删除操作。单元测试数据应该做到的数据范围独立性被违背,所以通知相关人员进行修改。

 

 

至此,误删除用户确定完成。

 

 

这个问题,我们得到几个经验。

 

1、进行数据恢复的手段很多。从最简单的手段切入,往往可以达到意想不到的效果。不仅仅是对于DBA,普通开发人员也应该具备一定的数据恢复处理能力;

2、Oracle对于所有的数据库变化操作,都会记录redo log。分析redo log,善用logminer,可以帮助我们解决很多问题;

3、问题的解决往往是综合性的方案确定过程。技术是一个重要方面,但是绝不是唯一的方面。对业务知识、系统特点进行深入了解,对开发流程和测试流程进行梳理分析,才能借助一丝线索解决问题。

 

 

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7746628