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

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