導資料遇見ORA-01555錯誤

edwardking888發表於2010-05-20

昨天在對CALLCENTER系統的主庫匯入CUSTOMER資料的時候,遇到了ORA-01555

錯誤。

軟體環境:兩臺DB都是oracle9.2.0.4

匯入工具使用:powercenter8.6

主庫是redhat  linux4.7

備庫是windows2003(32位)環境

在匯入過程中,報:

ORA-01555: 快照過舊: 回退段號 1 在名稱為 "_SYSSMU1$" 過小

錯誤。

1555錯誤產生的原因,就需要知道ORACLE的兩個特性:一致性讀(Consistent Get)和延遲塊清除(Delayed Block Cleanout)。此外,還要知道關於回滾段的一些配置引數:

相關引數

      先看下Oracle中關於UNDO有哪些配置引數:

SQL> show parameter undo
 
NAME                  TYPE                 VALUE
--------------------- -------------------- -----------------------
undo_management       string               MANUAL
undo_retention        integer              900
undo_suppress_errors  boolean              FALSE
undo_tablespace       string               UNDOTBS1

undo_management

      回滾段的管理方式。值可以為MANUAL/AUTO9i中預設是MANUAL10g中預設是AUTO

      9i後,回滾段就以表空間的形式管理,並且支援系統自動管理回滾段。一個回滾表空間上可以建立多個回滾段,一個資料庫可以建立多個回滾表空間。但是,一個例項(Instance)只能使用一個回滾表空間。

如果undo_management設定為MANUAL,就是手動建立回滾段

SQL> create rollback segment undo1 tablespace UNDOTBS1;

      如果設定為AUTOOracle就自動管理回滾段的建立,而手工建立就會失敗。

undo_retention

      這個引數設定回滾段中的被提交或回滾的資料強制保留時間,單位是秒。請注意,這個引數和1555錯誤有非常大的關係。但是,需要提醒的是,並不是回滾段中的資料超過這個時間以後就會被清除掉,而是等到後面事務產生的回滾資料覆蓋掉“超期”資料。所以這就是為什麼我們往往看到系統的回滾表空間佔有率始終是100%的原因了。

undo_suppress_errors

      是否報與回滾段有關的錯誤。如果為FALSE,就不會產生與回滾段有關的錯誤。但是,請注意,並不是不會發生回滾段錯誤,而只是遮蔽錯誤資訊,錯誤發生了就會存在滴。在10g中,這個引數是隱含引數。

undo_tablespace

      為每個例項制定的唯一當前使用的回滾段表空間。

面我們就模擬一下1555錯誤發生的情況:

首先建立測試環境。由於我們只是要模擬1555錯誤的發生,所以需要建立一個小的回滾表空間,並且設定undo_retention時間為1(秒),以便回滾資料儘快被覆蓋(呵呵,要防止1555錯誤發生,這就一定要避免的)。

 

CREATE UNDO TABLESPACE rbs_ts
DATAFILE 'rbs_ts2.dbf' SIZE 10M AUTOEXTEND OFF;
 
alter system set undo_retention=1 scope=spfile;
 
alter system set undo_management=auto scope=spfile;
 
alter system set undo_tablespace=rbs_ts scope=spfile;
 
startup force
 
alter tablespace rbs_ts online;
 
create table demo.t_dual as select * from dual;
 
insert into t_dual values(1);
 
commit;
 

 

一致性讀導致1555錯誤

開始讀取表。

SQL>
SQL> var cl refcursor
SQL> begin
  2  open :cl for select * from demo.t_multiver;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL>

 

更新表資料,產生回滾資訊

SQL> update demo.t_multiver set b = 111 where a = 1;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 

 

執行大批其他事務,充滿所有回滾段,以致覆蓋上面的回滾資訊回滾段可以通過dba_rollback_segs檢視。

SQL> begin
  2    for i in 1..20000 loop
  3      update demo.t_dual set dummy=1;
  4      commit;
  5    end loop;
  6  end;
  7
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> /
 
PL/SQL procedure successfully completed.
 

 

查詢到更新過的資料記錄,回滾資訊已經被覆蓋,所以報1555錯誤。

SQL> print :cl
ERROR:
ORA-01555: snapshot too old: rollback segment number 18 with name "_SYSSMU18$"
too small
 
 
 
no rows selected
 
SQL>

延遲塊清除導致的1555錯誤

開始讀取表。

SQL> var cc refcursor
SQL>
SQL> begin
  2  open :cc for select * from t_multiver;
  3  end;
  4  /
 

 

這時一個事務更新了該資料塊,但在提交前,我們手工將buffer cache中的資料做了flush,再做提交。這時的資料塊上只記錄了鎖標誌,沒有事務標誌和Commit SCN

PL/SQL procedure successfully completed.
 
SQL>
SQL> update t_multiver set b=115 where a=1;
 
1 row updated.
 
SQL>
SQL> alter system flush buffer_cache;
 
System altered.
 
SQL>
SQL> commit;
 
Commit complete.
 

 

進行非常多的事務,將回滾段中的事務資訊表中的資料全部覆蓋:

SQL>
SQL> begin
  2    -- overwrite rollback slot
  3    for i in 1..40000 loop
  4      update t_dual set dummy=1;
  5      commit;
  6    end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 

 

讀取資料塊前需要回滾段的事務資訊表中讀取Itl中沒有標記完全的事務的狀態和Commit SCN,以判斷是否需要進行一致性讀。但是事務資訊表中的資料都已經被覆蓋,所以報1555錯誤:

SQL>
SQL> print :cc
ERROR:
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20$"
too small
 
 
 
no rows selected

 

      以上兩個例子看起來是好像很類似,但是,他們的本質區別是:第一個實際上是在進行一致性讀得時候發生的1555錯誤,而第二個例子是在判斷是否需要進行一致性讀得時候發生的1555錯誤。

解決1555錯誤的方法

現在,我們已經知道了1555錯誤產生的原因。那麼,就可以總結出以下方法來解決1555錯誤問題:

1、擴大回滾段

因為回滾段是迴圈使用的,如果回滾段足夠大,那麼那些被提交的資料資訊就能儲存足夠長的時間是那些大事務完成一致性讀取。

2、增加undo_retention時間

undo_retention規定的時間內,任何其他事務都不能覆蓋這些資料。

3、優化相關查詢語句,減少一致性讀。

減少查詢語句的一致性讀,就降低讀取不到回滾段資料的風險。這一點非常重要!

4、減少不必要的事務提交

提交的事務越少,產生的回滾段資訊就越少。

5、對大事務指定回滾段

通過以下語句可以指定事務的回滾段

SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment

給大事務指定回滾段,即降低大事務回滾資訊覆蓋其他事務的回滾資訊的機率,又降低了他自身的回滾資訊被覆蓋的機率。大事務的存在,往往是1555錯誤產生的誘因。

6、使用遊標時儘量使用顯式遊標,並且只在需要的時候開啟遊標,同時將所有可以在遊標外做的操作從遊標迴圈中拿出。

當遊標開啟時,查詢就開始了,直到遊標關閉。減少遊標的開啟時間,就減少了1555錯誤發生的機率。

在本次操作中,筆者的解決方案是:

1.適當增大undo表空間的尺寸,防止回滾資訊被覆蓋。

2.增加undo_retention時間,增加到10800秒(在匯入源和匯出源都修改)。

3.刪除目標表的部分索引,加快匯入速度,在倒完資料後,再建立。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8183550/viewspace-663171/,如需轉載,請註明出處,否則將追究法律責任。

相關文章