ITPub博客

首页 > 数据库 > Oracle > [20181222]如何找出回滚操作.txt

[20181222]如何找出回滚操作.txt

原创 Oracle 作者:lfree 时间:2018-12-23 19:25:24 0 删除 编辑

[20181222]如何找出回滚操作.txt

—//链接问的问题, http://www.itpub.net/thread-2107324-1-1.html .
—//每秒有422.7个rollback.如果找到发生回滚的操作.
—//首先一点应该更佳关注transaction rollbacks。通过测试说明问题。

1.环境:
SCOTT @test01p > @ ver1

PORT_STRING VERSION BANNER CON_ID


IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

2.测试一:
SCOTT @test01p > @ viewsess rollback
NAME STATISTIC# VALUE SID


user rollbacks 7 0 89
transaction tables consistent read rollbacks 597 0 89
rollbacks only - consistent read gets 601 0 89
cleanouts and rollbacks - consistent read gets 602 0 89
rollback changes - undo records applied 607 0 89
transaction rollbacks 608 0 89
IMU CR rollbacks 656 0 89
IM populate undo segheader rollback 689 0 89
IM repopulate undo segheader rollback 697 0 89
IM scan CUs rollback 710 0 89
IM scan CUs no rollback 711 0 89
11 rows selected.

SCOTT @test01p > rollback;
Rollback complete.

SCOTT @test01p > @ viewsess rollback
NAME STATISTIC# VALUE SID


user rollbacks 7 1 89
transaction tables consistent read rollbacks 597 0 89
rollbacks only - consistent read gets 601 0 89
cleanouts and rollbacks - consistent read gets 602 0 89
rollback changes - undo records applied 607 0 89
transaction rollbacks 608 0 89
IMU CR rollbacks 656 0 89
IM populate undo segheader rollback 689 0 89
IM repopulate undo segheader rollback 697 0 89
IM scan CUs rollback 710 0 89
IM scan CUs no rollback 711 0 89
11 rows selected.
—//所以不能关注user rollbacks,而应该更多的关注transaction rollbacks.

3.如何找到回滚操作,测试logminer看看。
SYSTEM @test > alter database add supplemental log data;
Database altered.

—//开启附加日志,不然logminer会漏掉一些语句,如果不开启,我真不知道使用logminer是否可行。

SCOTT @test01p > create table t as select rownum id ,lpad(‘x’,100,’x’) name from dual connect by level<=2;
Table created.

—//建立脚本:
$ cat undo_t.txt
column member new_value v_member
column member noprint
set numw 12
pause run alter system archive log current or alter system switch logfile;
—//12c不允许在pluggable database执行这条命令
—//alter system archive log current;
SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS=’CURRENT’ and rownum=1;

column curr1 new_value v_curr1
select current_scn curr1 from v$database;

—//以下操作内容:
update t set name=lpad(‘y’,100,’y’) where id=1;
commit ;
update t set name=lpad(‘z’,100,’z’) where id=2;
rollback ;

column curr2 new_value v_curr2
select current_scn curr2 from v$database;

prompt exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => &&v_curr1 ,ENDSCN => &&v_curr2 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
prompt alter system dump logfile ‘&&v_member’ scn min &&v_curr1 scn max &&v_curr2;
alter system dump logfile ‘&&v_member’ scn min &&v_curr1 scn max &&v_curr2;
@pp

SCOTT @test01p > @ undo_t.txt
run alter system archive log current or alter system switch logfile


   CURR1



 5870518

1 row updated.
Commit complete.
1 row updated.
Rollback complete.


   CURR2



 5870530

exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => 5870518 ,ENDSCN => 5870530 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE)
alter system dump logfile ‘D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG’ scn min 5870518 scn max 5870530
old 1: alter system dump logfile ‘&&v_member’ scn min &&v_curr1 scn max &&v_curr2
new 1: alter system dump logfile ‘D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG’ scn min 5870518 scn max 5870530
System altered.

TRACEFILE

D:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE\test_ora_4600.trc

4.使用logminer分析:
—//登录cdb,执行如下:
SYSTEM @test > BEGIN
2 DBMS_LOGMNR.START_LOGMNR
3 (
4 STARTSCN => 5870518
5 ,ENDSCN => 5870530
6 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
7 + DBMS_LOGMNR.CONTINUOUS_MINE
8 );
9 END;
10 /

PL/SQL procedure successfully completed.

SYSTEM @test > create table x1 as select * from V$LOGMNR_CONTENTS;
Table created.

SYSTEM @test > EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.

SYSTEM @test > BEGIN
2 DBMS_LOGMNR.START_LOGMNR
3 (
4 STARTSCN => 5870518
5 ,ENDSCN => 5870530
6 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
7 + DBMS_LOGMNR.COMMITTED_DATA_ONLY
8 + DBMS_LOGMNR.CONTINUOUS_MINE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.

SYSTEM @test > create table x2 as select * from V$LOGMNR_CONTENTS;
Table created.

SYSTEM @test > EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.

SYSTEM @test > column sql_redo format a60
SYSTEM @test > select scn,rs_id,sql_redo from system.x1
2 minus
3 select scn,rs_id,sql_redo from system.x2
4 /
SCN RS_ID SQL_REDO


5870524 0x00005f.00004a11.0010 set transaction read write;
5870524 0x00005f.00004a11.0010 update “SCOTT”.”T” set “NAME” = ‘zzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzz’ where “NAME” = ‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxx’ and ROWID = ‘AAAF5JAALAAAACrAAB’;

5870525 0x00005f.00004a12.00f8
5870527 0x00005f.00004a13.0010 update “SCOTT”.”T” set “NAME” = ‘xxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxx’ where ROWID = ‘AAAF5JAALAAAACrAAB’;

5870528 0x00005f.00004a13.016c rollback;

—//注不能使用select from system.x1 minus select from system.x2,因为没有参数DBMS_LOGMNR.COMMITTED_DATA_ONLY
—//的情况下,start_scn,stop_scn在视图 V$LOGMNR_CONTENTS为空。
—//你可以发现整个回滚的整个操作。理论将如果存在这个高的回滚事务,应该看出问题在哪里。

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2625
  • 访问量
    6389632