Ora-01555產生原因：

1、一致性讀導致的ora-01555

當一個較長的查詢執行時，查詢資料過程中資料塊發生了修改，並且被commit後事物量較大，undo segment被覆蓋，該查詢檢索到被修改資料塊時，為了一致性讀需要去undo中查詢資料塊前映象，而該undo segment已經被覆蓋所以無法找到資料，此時就會報出ora-01555錯誤。

2、延遲塊清除導致的ora-01555

當資料塊被修改後存放到buffer cache中，commit之前進行塊清除時使用了延遲塊清除（需要清除的資料塊較多，超出了buffer_cache的10%，或者在commit之前，已經有dbwr程式將buffer_cache中的資料寫入dbwr，比如commit前重新整理了buffer cache），被修改的資料塊透過dbwr寫入資料檔案，而每個事務被commit之後會有一個commit scn記錄在undo裡，select語句發出時會有一個select scn，如果發現select檢索的資料塊是被延遲塊清除的，會透過ITLl事物槽到undo裡找對應的該資料庫的commit scn，如果此時undo已經被重新整理，找不到對應的該資料庫的scn，會拿select scn與undo裡的最小的commit scn對比，一般來說select scn都會比undo 最小commit scn大，所以會把undo中最小commit scn作為被延遲塊清除的資料庫的commit scn，這樣雖然有真正的該資料庫的commit scn不一致，但資料是一直的，所以還是安全的。但是如果真出現比較極端的，select scn比undo裡最小的commit scn還要小，那就出現ora - 01555的錯誤了。

具體過程分析

為了方便演示過程，臨時修改一下undo的引數：

SQL> create undo tablespace undotbs02 datafile '/u01/app/oracle/oradata/orcl_dup/undotbs002' size 5m autoextend off;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs02 scope=spfile;

System altered.

SQL> alter system set undo_retention=3 ;

System altered.

undo_retention引數用來控制undo資料的保留時間，預設的是900s，此處改為3s只是為了方便測試，更加詳細的講解請參照http://blog.itpub.net/17203031/viewspace-774498

10203事件用來跟蹤塊的清除操作

SQL> alter system set event='10203 trace name context forever' scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 3

undo_tablespace string UNDOTBS02

SQL> conn scott/oracle

Connected.

SQL> create table t as select * from emp;

Table created.

SQL> select

2 rowid,

3 dbms_rowid.rowid_relative_fno(rowid)rel_fno,

4 dbms_rowid.rowid_block_number(rowid)blockno

5 from t

6 where rownum=1;

ROWID REL_FNO BLOCKNO

------------------ ---------- ----------

AAAOEDAAEAAAAIMAAA 4 524

/** 為了方便，scott使用者會話不要中斷，不然事務會自動回滾，以下測試中dump操作均使用新的會話 **/

一致性讀導致的ora-01555

開啟一個遊標，執行一個查詢

SQL> conn scott/oracle

Connected.

SQL> var c1 refcursor

SQL> begin

2 open :c1 for select * from t;

3 end;

4 /

PL/SQL procedure successfully completed.

T表的第一行資料塊物理位置為資料檔案4資料庫524，先把該資料塊dump出來看看資料庫的ITL狀態，為了方便些了個簡單sql組合

[oracle@node1 db_1]$ more dump_block.sql

alter system dump datafile 4 block 524;

oradebug setmypid;

oradebug tracefile_name;

SQL> @dump_block

System altered.

Statement processed.

/u01/app/oracle/admin/orcl_dup/udump/orcl_dup_ora_4786.trc

Itl狀態如下，此時是沒有鎖定資訊的

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.002cce0f

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

更改該行資料,並再次dump出塊資訊,Itl(0x02)行被鎖定

SQL> update t set sal=1234 where rownum=1;

1 row updated.

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.002d0488

0x02 0x0005.02f.000005ae 0x00800189.05d0.04 ---- 1 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

透過Xid可以找到相應的事務資訊，0005為分配的回滾段號，02f是事務槽，00000152對應wrap

資料的lb被指定為0x2

tab 0, row 0, @0x1cdf

tl: 38 fb: --H-FL-- lb: 0x2 cc: 8

col 0: [ 3] c2 0d 23

col 1: [ 4] 74 65 73 74

col 2: [ 4] 74 65 73 74

col 3: [ 3] c2 4e 5a

col 4: [ 7] 78 72 07 11 0a 37 2c

col 5: [ 4] c3 02 18 2e

col 6: *NULL*

col 7: [ 2] c1 0b

找到回滾段，轉存回滾端頭部資訊（使用上面的方式透過匯出資料塊的Xid也可以推算）

SQL> select * from v$rollname where usn=5;

USN NAME

---------- ------------------------------

5 _SYSSMU5$

或者透過v$transaction,v$session,v$rollname檢視查詢當前的回滾段也可以

SQL> alter system dump undo header "_SYSSMU5$";

System altered.

SQL> alter system dump undo header "_SYSSMU5$";

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/admin/orcl_dup/udump/orcl_dup_ora_7539.trc

2f事務槽狀態為10此時已經被使用

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

------------------------------------------------------------------------------------------------

0x2f 10 0x80 0x05ae 0x0002 0x0000.00000000 0x00800189 0x0000.000.00000000 0x00000001 0x00000000 0

ccott連線的會話提交update操作

SQL> commit;

Commit complete.

提交事務後再匯出檢視一下，事務槽狀態已經變為9（非啟用），分配提交的scn和cmt

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

------------------------------------------------------------------------------------------------

0x2f 9 0x00 0x05ae 0xffff 0x0000.002d0701 0x00800189 0x0000.000.00000000 0x00000001 0x00000000 1405908462

此時匯出資料塊資訊，Scn/Fsc（commit scn）變的跟undo 中的提交scn一致，並且此時資料的鎖定狀態並未清除

SQL> @dump_block

System altered.

Statement processed.

/u01/app/oracle/admin/orcl_dup/udump/orcl_dup_ora_4960.trc

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.002d0488

0x02 0x0005.02f.000005ae 0x00800189.05d0.04 --U- 1 fsc 0x0000.002d0701

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

tab 0, row 0, @0x1ce0

tl: 37 fb: --H-FL-- lb: 0x2 cc: 8

col 0: [ 3] c2 0d 23

col 1: [ 4] 74 65 73 74

col 2: [ 4] 74 65 73 74

col 3: [ 3] c2 4e 5a

col 4: [ 7] 78 72 07 11 0a 37 2c

col 5: [ 3] c2 0d 23

col 6: *NULL*

col 7: [ 2] c1 0b

此時執行一個大一點的事務，將undo裡的更新前的524的塊資訊覆蓋

SQL> begin

2 for i in 1..10000 loop

3 update emp set sal=4000;

4 rollback;

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

此時使用之前open的cursor執行查詢，將找不到undo中的前映象

SQL> print :c1

ERROR:

ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$"

too small

no rows selected

延遲塊清除導致的ora-01555

同樣的開啟一個遊標

SQL> var c1 refcursor

SQL> begin

2 open :c1 for select * from t where rownum=1;

3 end;

4 /

PL/SQL procedure successfully completed.

更新一條資料

SQL> update t set sal = 4321 where rownum=1;

1 row updated.

dump該資料塊，事務已分配

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.002dde90

0x02 0x0006.003.000005c2 0x00800060.03d8.21 ---- 1 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

dump出回滾段段頭，事務槽1e已經分配

SQL> alter system dump undo header '_SYSSMU6$';

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/admin/orcl_dup/udump/orcl_dup_ora_7675.trc

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

------------------------------------------------------------------------------------------------

0x03 10 0x80 0x05c2 0x0000 0x0000.002dde97 0x00800060 0x0000.000.00000000 0x00000001 0x00000000 0

提交事務之前，重新整理buffer_cache,使用dbwr程式將被修改的資料塊寫入資料檔案，這樣被寫入的資料塊就使用了延遲塊快取

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush buffer_cache;

System altered.

提交scott的事務

SQL> commit;

Commit complete.

此時dump出該資料塊，ITL並未清除

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.002dde90

0x02 0x0006.003.000005c2 0x00800060.03d8.21 ---- 1 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

可是此時事務槽已經標記物inactive

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

------------------------------------------------------------------------------------------------

0x03 9 0x00 0x05c2 0xffff 0x0000.002de0a1 0x00800060 0x0000.000.00000000 0x00000001 0x00000000 1405911654

此時執行一個大的事務，覆蓋undo中該塊的前映象

SQL> begin

2 for i in 1..2000 loop

3 update emp set sal=1000;

4 rollback;

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

此時如果對開啟的cursor c1執行查詢會報錯：

SQL> print :c1

ERROR:

ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"

too small

no rows selected

再次對該資料塊進行訪問後，會發現該資料塊commit scn被更新，但是該commit scn對應的不再是上面自己的事務槽的scn，而是undo中最小commit scn

SQL> select sal from t where rownum=1;

SAL

----------

4321

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.002dde90

0x02 0x0006.003.000005c2 0x00800060.03d8.21 C-U- 0 scn 0x0000.002de239

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

測試完畢，別忘了把引數改回來

SQL> alter system set undo_retention=1080 scope=both;

System altered.

SQL> alter system set undo_tablespace=undotbs scope=both;

System altered.

SQL> drop tablespace undotbs02 including contents and datafiles;

Tablespace dropped.

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 1080

undo_tablespace string UNDOTBS

如何避免Ora-01555

1、如果自己的undo是手動管理，儘量的是rollback segment設定的一樣大。

2、儘量避免SQL的執行時間過長，透過建索引，避讓全盤掃描等手段來儘量減少SQL的執行時間。

3、儘量確保網路通暢，由於網路問題帶來的ora-01555讓dba無奈了。。。

4、cursor儘量在使用時開啟，正如上面的例子，cursor長時間開啟而不操作，很容易導致ora-01555錯誤。使用後立即關閉。

5、如果正常情況下系統undo過多，可以考慮加大undo空間。

6、儘量使大的查詢在備庫上執行。

Ora-01555問題分析

Ora-01555產生原因：

1、一致性讀導致的ora-01555

2、延遲塊清除導致的ora-01555

具體過程分析

一致性讀導致的ora-01555

延遲塊清除導致的ora-01555

如何避免Ora-01555

相關文章