[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為空。

--//你可以發現整個回滾的整個操作。理論將如果存在這個高的回滾事務，應該看出問題在哪裡。

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

相關文章