導資料遇見ORA-01555錯誤
昨天在對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/AUTO。9i中預設是MANUAL,10g中預設是AUTO。
從9i後,回滾段就以表空間的形式管理,並且支援系統自動管理回滾段。一個回滾表空間上可以建立多個回滾段,一個資料庫可以建立多個回滾表空間。但是,一個例項(Instance)只能使用一個回滾表空間。
如果undo_management設定為MANUAL,就是手動建立回滾段:
SQL> create rollback segment undo1 tablespace UNDOTBS1;
如果設定為AUTO,Oracle就自動管理回滾段的建立,而手工建立就會失敗。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 今天學習遇見的錯誤!!!(禁忌)
- 【儲存】flashcache 使用遇見的錯誤
- ORA-01555錯誤分析
- 航空遇見大資料大資料
- ORA-01555錯誤詳解
- 資料庫人員:常見錯誤(轉)資料庫
- ORA-01653錯誤是Oracle資料庫中常見的錯誤Oracle資料庫
- 資料分析中常見的錯誤是什麼(一)
- MySQL資料庫常見錯誤及解決方案MySql資料庫
- Sql server資料庫人員:常見錯誤(轉)SQLServer資料庫
- 匯入資料時遇見ORA-00054
- Ora-01555錯誤的模擬及處理
- 【應用服務 App Service】App Service使用Git部署時,遇見500錯誤APPGit
- 資料庫升級導致ORA-918錯誤資料庫
- 當 Kotlin 遇見 RxJava 多資料來源KotlinRxJava
- MySQL 常見錯誤MySql
- oracle 常見錯誤Oracle
- 高階資料分析流程要避免的常見錯誤KG
- 遇見山河 預見空間大資料移動互聯時代!大資料
- 資料同步中的誤導
- 思邁特軟體Smartbi:資料看板搭建的常見錯誤
- Go 常見錯誤集錦 | 字串底層原理及常見錯誤Go字串
- Go常見錯誤集錦 | 字串底層原理及常見錯誤Go字串
- INTERVAL分割槽插入大量資料導致ORA-4031錯誤
- 常見的web錯誤Web
- mysql replication常見錯誤MySql
- 【資料庫資料恢復】磁碟空間不足導致sql server錯誤的資料恢復資料庫資料恢復SQLServer
- 使用錯誤的作業系統使用者exp資料導致ORA-15186錯誤作業系統
- 常見的資料分析誤區
- oracle 9i下ORA-01555錯誤的解決方法Oracle
- 遇見大資料視覺化 : 那些 WOW 的資料視覺化案例大資料視覺化
- [原]Android 初遇Http錯誤 httpClient.executeAndroidHTTPclient
- impdp 匯入資料導致ora-600,arguments: [klaprs_11]錯誤
- 資料庫增加SGA,導致ORA-27102: out of memory錯誤資料庫
- ElasticSearch 遇見(4)Elasticsearch
- MySQL 安裝常見錯誤MySql
- mysql8 常見錯誤MySql
- 常見的錯誤 SQL 用法SQL