对数据库而言,最重要宝贵的内容就是其中的数据。数据安全性和完整性是任何商业数据库存在的底线。审计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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-690120/