[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为空。 --//你可以发现整个回滚的整个操作。理论将如果存在这个高的回滚事务，应该看出问题在哪里。