ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 日志挖掘,发现大量delete .... where ROWID = 'AAAnQyABBAAAAAOAAA'

日志挖掘,发现大量delete .... where ROWID = 'AAAnQyABBAAAAAOAAA'

原创 Linux操作系统 作者:monica_yaotf 时间:2011-05-03 17:25:22 0 删除 编辑
最近有个系统经常出现客户端被hung住,没有反应的情况,因为是Oracle10g,
首先使用了hist视图,查找到大量的TX锁,
select b.username,
       a.SQL_ID,
       a.session_id,
       a.EVENT,
       a.WAIT_CLASS,
       a.WAIT_TIME,
       a.PROGRAM,
       a.client_id
  from dba_hist_active_sess_history a, dba_users b
 where a.user_id = b.user_id
  and a.SAMPLE_TIME between to_date('2011-05-03 02:24:00','yyyy-mm-dd hh24:mi:ss')
  and to_date('2011-05-203 05:03:00','yyyy-mm-dd hh24:mi:ss');

进而使用日志挖掘,发现有大量的
delete .... where  ROWID = 'AAAnQyABBAAAAAOAAA'
语句;

怀疑是主键重复导致的,所以自己做了个实验:
1.创建t1表
-- Create table
create table T1
(
  A NUMBER not null,
  B VARCHAR2(20)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T1
  add constraint PK_T1 primary key (A);

1.在一个session内对t1表执行插入操作
insert into t1 values(3,'monica2');
insert into t1 values(4,'monica2');
insert into t1 values(5,'monica2');
insert into t1 values(6,'monica2');

2.分别在其他4个session内执行上面4条语句

均不提交

3.查看系统锁的情况
select s.sql_id,w.EVENT,a.SQL_TEXT,s.USERNAME from v$session s,v$sqlarea a,v$session_wait w
where s.sql_id=a.SQL_ID
  and s.SID=w.SID
  and s.EVENT='enq: TX - row lock contention'
1    f7fr7w2jrjvz1    enq: TX - row lock contention    insert into t1 values(6,'monica2')     TEST
2    apq3p5ztr0zta    enq: TX - row lock contention    insert into t1 values(3,'monica2')     TEST
3    8ntkd93mb77sj    enq: TX - row lock contention    insert into t1 values(5,'monica2')     TEST
4    8ys4qq60d47f9    enq: TX - row lock contention    insert into t1 values(4,'monica2')     TEST

4.对session执行commit;
其他四个session均报主键冲突的错误
ORA-00001:unique constraint(TEST.PK_T1)violated

5.再次查看此时锁的情况,锁已经全部不见了

6.切换日志
7.进行日志挖掘
SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('redo03.log', SYS.DBMS_LOGMNR.NEW)

PL/SQL 过程已成功完成。
/dev/recomredo2m
SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 过程已成功完成。

SQL> SELECT TO_CHAR(TIMESTAMP ,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER ='TEST' AND TABLE_NAME = 'T1';

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('3','monica2');

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('4','monica2');

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('5','monica2');

2011-05-03 17:33:36
insert into "TEST"."T1"("A","B") values ('6','monica2');

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:33:39
insert into "TEST"."T1"("A","B") values ('3','monica2');

2011-05-03 17:33:39
insert into "TEST"."T1"("A","B") values ('4','monica2');

2011-05-03 17:33:42
insert into "TEST"."T1"("A","B") values ('5','monica2');

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:33:45
insert into "TEST"."T1"("A","B") values ('6','monica2');

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAOAAA';

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAMAAA';

TIMESTAMP
-------------------
SQL_REDO
--------------------------------------------------------------------------------

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAQAAE';

2011-05-03 17:34:06
delete from "TEST"."T1" where ROWID = 'AAAnQyABBAAAAAPAAA';


到此,验证结束。

说明:Oracle在做插入的时候,均把数据插入到了表中,在提交后,插入失败的数据数据库将自动删除。
所有操作均在日志中记录。

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

请登录后发表评论 登录
全部评论

注册时间:2008-03-27

  • 博文量
    73
  • 访问量
    298208