查詢時出現ora-01555:snapshot too old的問題分析：
開始一個大的查詢（消耗時間較長）。在查詢的過程中，執行了兩次更新。
第一次更新50000條，消耗80%的undo表空間。
第二次更新同樣的50000條資料，由於還是要消耗80%的表空間，現有的undo表空間已經不足，按照noguarantee的特性，第一次更新的50000在回滾段中的快照就會被覆蓋。假設此時查詢到第N個塊（N<50000），發現此時該塊的scn發生了改變，已經不是查詢開始時的scn，因此需要到undo表空間去取查詢開始時的該塊的快照。但是由於已經進行了兩次更新，第二次更新已經把第一次更新後保留的快照覆蓋掉。導致取不到該塊在查詢開始時的快照，從而出現了01555的錯誤。
所以遇到該問題，解決的方式或者是增加undo表空間，或者是將undo表空間的undo_retention改為guarantee。以保證資料快照保留至undo_retention的時間。但是也有可能導致undo空間的浪費。

undo表空間用於儲存對資料修改時資料的前映像，在提交前可以回滾的表空間
當系統shutdown abort時，undo表空間
相關的引數：
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
undo_management設定Oracle段的管理方式。10g預設為auto，同時也向下相容，即也存在手動（manual）方式。
undo_retention用於控制資料提交後資料前映像保留的時間，預設為900s或10800s。10g下，當該引數為０時，表示有Oracle自動控制該值的取值範圍。
undo_tablespace表示當前所使用的回滾表空間。

--檢視錶空間的guarantee情況
增加該選項的目的是為了確保資料前映像能夠保留undo_retention所設定的時間
預設情況下是noguarantee，表明一旦空間不足，其他事務便可覆蓋即使還在undo_retention限定時間內的資料前映像
select dt.tablespace_name, dt.retention
from dba_tablespaces dt
where dt.retention <> 'NOT APPLY'
如果是noguarantee，則表明其他事務需要回滾空間，而實際空間不足時，將覆蓋這些已經提交、但是仍然在undo_retention時間內的資料。
如果是guarantee，則表明在以上這種情況下，不覆蓋這些資料，即使會導致其他請求回滾空間的事務失敗也不予覆蓋。
官方文件解釋：
RETENTION Undo tablespace retention:
GUARANTEE - Tablespace is an undo tablespace with RETENTION specified as GUARANTEE
A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail.
NOGUARANTEE - Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE
NOT APPLY - Tablespace is not an undo tablespace.

如何修改guarantee引數：
alter tablespace undotbs1 retention guarantee;

建立新的undo表空間：
create undo tablespace undotbs2 datafile 'E:\oracle\oradata\lyon\undotbs2.dbf' size 10m ;
修改undo表空間為新的表空間：
alter system set undo_tablespace = undotbs1
undo表空間相關檢視：
select * from v$rollstat;--回滾空間資訊
select * from v$undostat;--
相關查詢
1、檢視undo表空間的資料檔案大小，使用情況、狀態等
select df.file_name, df.tablespace_name, df.bytes, df.user_bytes, df.online_status
from dba_data_files df
where df.tablespace_name = 'UNDOTBS2'
2、檢視回滾段使用情況，哪個使用者正在使用哪個回滾段
select s.USERNAME, u.name, r.*
from v$transaction t, v$session s, v$rollstat r, v$rollname u
where t.ADDR = s.TADDR
and t.XIDUSN = r.USN
and r.USN = u.usn

參考文章

釋放過度使用的Undo表空間

故障現象：UNDO表空間越來越大,長此下去最終資料因為磁碟空間不足而崩潰；

問題分析：產生問題的原因主要以下兩點：
1. 有較大的事務量讓Oracle Undo自動擴充套件，產生過度佔用磁碟空間的情況；
2. 有較大事務沒有收縮或者沒有提交所導制；
說明：本問題在ORACLE系統管理中屬於比較正常的一現象，日常維護多注意對磁碟空間的監控。
備份： (如果沒有線上事務，可以不做，關閉監聽)

$>exp vas/vas file=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmp log=/opt/oracle/date.log wner=vas rows=y indexes=y compress=n buffer=65536 feedback=100000 volsize=0 filesize=1000M

解決步驟：
1. 啟動SQLPLUS，並用sys登陸到。

#su - oracle
$>sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Nov 8 13:45:10 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/qq994238@ddptest as sysdba;
Connected.

2. 查詢資料庫的UNDO表空間名

#cat $ORACLE_HOME/dbs/initddptest.ora
……
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS2’
……

3. 確認UNDO表空間；

SQL> select name from v$tablespace;

NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
USERS
XDB
TEMP
TESTLIB
UNDOTBS2

4. 檢查資料庫UNDO表空間佔用空間情況以及資料檔案存放位置；

SQL>select file_name,bytes/1024/1024 from dba_data_files
2 where tablespace_name like 'UNDOTBS2';

5. 檢視回滾段的使用情況，哪個使用者正在使用回滾段的資源，如果有使用者最好更換時間（特別是生產環境）。

SQL> select s.username, u.name from v$transaction t,v$rollstat r,
2 v$rollname u,v$session s where s.taddr=t.addr and
3 t.xidusn=r.usn and r.usn=u.usn order by s.username;

6. 檢查UNDO Segment狀態；

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;

7. 建立新的UNDO表空間，並設定自動擴充套件引數；

SQL> create undo tablespace undotbs1 datafile '/oradata/oradata/ddptest/UNDOTBS1.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited;

Tablespace created.

8. 動態更改spfile配置檔案；

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

System altered.

9. 等待原UNDO表空間所有UNDO SEGMENT OFFLINE；

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;

10. 再執行看UNDO表空間所有UNDO SEGMENT ONLINE；

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;

11. 刪除原有的UNDO表空間；

SQL> drop tablespace undotbs2 including contents;

Tablespace dropped.

12. 確認刪除是否成功；

SQL> select name from v$tablespace;

NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
USERS
XDB
TEMP
TESTLIB
UNDOTBS1

12 rows selected.

13. 在做此步驟前，請到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下內容是否發生變更：

#cat $ORACLE_HOME/dbs/initddptest.ora
……
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS2’
……

如果沒有發生變更請執行如下語句：

SQL> create pfile from spfile;

File created.

14. 冊除原UNDO表空間的資料檔案，其檔名為步驟中執行的結果。

#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf

附：UNDO表空間介紹

UNDO表空間用於存放UNDO資料,當執行DML操作(INSERT,UPDATE和DELETE)時,oracle會將這些操作的舊資料寫入到 UNDO段,在oracle9i之前,管理UNDO資料時使用(Rollback Segment)完成的.從oracle9i開始,管理UNDO資料不僅可以使用回滾段,還可以使用UNDO表空間.因為規劃和管理回滾段比較複雜,所有 oracle database 10g已經完全丟棄用回滾段.並且使用UNDO表空間來管理UNDO資料.

UNDO資料也稱為回滾(ROLLBACK)資料,它用於確保資料的一致性.當執行DML操作時,事務操作前的資料被稱為UNDO記錄.UNDO段用於儲存事務所修改資料的舊值,其中儲存著被修改資料塊的位置以及修改前資料,

UNDO資料的作用.

1,回退事務

當執行DML操作修改資料時,UNDO資料被存放到UNDO段,而新資料則被存放到資料段中,如果事務操作存在問題,舊需要回退事務,以取消事務變化.假定使用者A執行了語句UPDATE emp SET sal=1000 WHERE empno=7788後發現,應該修改僱員7963的工資,而不是僱員7788的工資,那麼透過執行ROLLBACK語句可以取消事務變化.當執行 ROLLBACK命令時,oracle會將UNDO段的UNDO資料800寫回的資料段中.

2,讀一致性

使用者檢索資料庫資料時,oracle總是使用使用者只能看到被提交過的資料(讀取提交)或特定時間點的資料(SELECT語句時間點).這樣可以確保資料的一致性.例如,當使用者A執行語句UPDATE emp SET sal=1000 WHERE empno=7788時,UNDO記錄會被存放到回滾段中,而新資料則會存放到EMP段中;假定此時該資料尚未提交,並且使用者B執行SELECT sal FROM emp WHERE empno=7788,此時使用者B將取得UNDO資料800,而該資料正是在UNDO記錄中取得的.

3,事務恢復

事務恢復是例程恢復的一部分,它是由oracle server自動完成的.如果在資料庫執行過程中出現例程失敗(如斷電,記憶體故障,後臺程式故障等),那麼當重啟oracle server時,後臺程式SMON會自動執行例程恢復,執行例程恢復時,oracl會重新做所有未應用的記錄.回退未提交事務.

4,倒敘查詢(FlashBack Query)

倒敘查詢用於取得特定時間點的資料庫資料,它是9i新增加的特性,假定當前時間為上午11:00,某使用者在上午10:00執行UPDATE emp SET sal=3500 WHERE empno=7788語句,修改並提交了事務(僱員原工資為3000),為了取得10:00之前的僱員工資,使用者可以使用倒敘查詢特徵.

使用UNDO引數

1,UNDO_MANAGEMENT

該初始化引數用於指定UNDO資料的管理方式.如果要使用自動管理模式,必須設定該引數為AUTO,如果使用手工管理模式,必須設定該引數為 MANUAL,使用自動管理模式時,oracle會使用undo表空間管理undo管理,使用手工管理模式時,oracle會使用回滾段管理undo數據,

需要注意,使用自動管理模式時,如果沒有配置初始化引數UNDO_TABLESPACE,oracle會自動選擇第一個可用的UNDO表空間存放 UNDO資料,如果沒有可用的UNDO表空間,oracle會使用SYSTEM回滾段存放UNDO記錄,並在ALTER檔案中記載警告.

2,UNDO_TABLESPACE

該初始化引數用於指定例程所要使用的UNDO表空間,使用自動UNDO管理模式時,透過配置該引數可以指定例程所要使用的UNDO表空間.

在RAC(Real Application Cluster)結構中,因為一個UNDO表空間不能由多個例程同時使用,所有必須為每個例程配置一個獨立的UNDO表空間.

3,UNDO_RETENTION

該初始化引數用於控制UNDO資料的最大保留時間,其預設值為900秒,從9i開始,透過配置該初始化引數,可以指定undo資料的保留時間,從而確定倒敘查詢特徵(Flashback Query)可以檢視到的最早時間點.

建立UNDO表空間,

UNDO表空間專門用於存放UNDO資料,並且在UNDO表空間尚不能建立任何資料物件(表,索引,簇)

1,使用CREATE DATABASE命令建立UNDO表空間.

當使用CREATE DATABASE命令建立資料庫時,透過指定UNDO TABLESPACE選項,可以建立UNDO表空間.示例如下:

CREATE DATABASE db01

…

UNDO TABLESPACE undotbs_01

DATAFILE ‘/u01/oracle/rbdb1/undo0101.dbf’ SIZE 30M;

注意:UNDO TABLESPACE 子句不是必須的,如果使用自動UNDO管理模式,並且沒有指定該子句,那麼建立資料庫時會自動生成名為SYS_UNDOTBS的UNDO表空間.

2,使用CREATE UNDO TABLESPACE命令建立UNDO表空間.

CREATE UNDO TABLESPACE undotbs3

DATAFILE ‘D:demoundotbs3.dbf’ SIZE 10M;

修改UNDO表空間,

使用ALTER TABLESPACE命令修改UNDO表空間.

當事務用盡了UNDO表空間後,使用ALTER TABLESPACE … ADD DATAFILE增加資料檔案

當UNDO表空間所在的磁碟填滿是,使用ALTER TABLESPACE … RENAME DATAFIEL 命令移動資料檔案到磁碟上.

使用ALTER DATABASE … OFFLINE/ONLINE使表空間離線/聯機.

當資料庫處於ARCHIVELOG模式時,使用ALTER TABLESPACE …BEGIN BACKUP/END BACKUP命令備份UNDO表空間.

切換UNDO表空間.

啟動例程並開啟資料庫後,同一時刻特定例程只能使用一個UNDO表空間,切換UNDO表空間是指停止例程當前使用的UNDO表空間,並啟動其他UNDO表空間,下面以啟用undotbs2表空間為例,說明切換UNDO表空間的方法.

ALTER SYSTEM SET undo_tablespace=undotbs02;

在RAC(Real Application Cluster)機構中,不同例程必須使用獨立的UNDO表空間,而不能共用同一個UNDO表空間.

刪除UNDO表空間.

當前例程正在使用的UNDO表空間是不能被刪除的,如果確定要刪除當前例程正在使用的UNDO表空間,應首先切換UNDO表空間.然後刪除相應的UNDO表空間.

DROP TABLESPACE undotbs3;

1,確定當前例程正在使用的UNDO表空間.

Show parameter undo_tablespace

2,顯示資料庫的所有UNDO表空間.

SELECT tablespace_name FROMdba_tablespaces WHERE contents=’UNDO’;

3,顯示UNDO表空間統計資訊.

使用自動UNDO管理模式時,需要合理地設定UNDO表空間的尺寸,為例合理規劃UNDO表空間尺寸,應在資料庫執行的高峰階段蒐集UNDO表空間的統計資訊.最終根據該統計資訊確定UNDO表空間的尺寸.透過查詢動態效能檢視V%UNDOSTAT,可以蒐集UNDO統計資訊.

SELECT TO_CHAR(BEGIN_TIME,’HH24:MI:SS’) BEGIN_TIME,

TO_CHAR(END_TIME,’HH24:MI:SS’) END_TIME,

UNDOBLKS

FROM V$UNDOSTAT;

BEGIN_TIME用於標識起始統計時間,END_TIME用於標識結束統計時間,UNDOBLKS用於標識UNDO資料所佔用的資料塊個數.oracle每隔10分鐘生成一行統計資訊.

4,顯示UNDO段統計資訊.

使用自動UNDO管理模式時,oracle會在UNDO表空間上自動建立10個UNDO段,透過查詢動態資訊檢視V$ROLLNAME,可以顯示所有聯機UNDO段的名稱,透過查詢動態效能檢視V$ROLLLISTAT,可以顯示UNDO段的統計資訊.透過在V$ROLLNAME和V$ ROLLLISTAT之間執行連線查詢,可以監視特定UNDO段的特定資訊.

SELECT a.name, b.xacts, b.writes, b.extents

FROM v$rollname a, v$rollstat b

WHERE a.usn=b.usn;

Name用於標識UNDO段的名稱,xacts用於標識UNDO段所包含的活動事務個數,

Writes用於標識在undo段上所寫入的位元組數,extents用於標識UNDO段的區個數.

5,顯示活動事務資訊.

當執行DML操作時,oracle會將這些操作的舊資料放到UNDO段中,動態效能檢視v$session用於顯示會話的詳細資訊,動態效能檢視v $transaction用於顯示事務的詳細資訊,動態效能檢視v$rollname用於顯示聯機UNDO段的名稱.透過在這3個動態效能檢視之間執行連接查詢,可以確定正在執行事務操作的會話,事務所使用的UNDO段,以及事務所佔用的UNDO塊個數.

Col username format a10

Col name format a10

SELECT a.username, b.name, c.used_ublk

FROM v$session a, v$rollname b, v$transaction c

WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn

AND a.username=’SCOTT’;

6,顯示UNDO區資訊

資料字典檢視dba_undo_extents用於顯示UNDO表空間所有區的詳細資訊.包括UNDO區尺寸和狀態等資訊.

SELECT extend_id, bytes, status FROM dba_undo_extents

WHERE segment_name’_SYSSMU5$’;

其中,extent_id用於標識區編號,bytes用於標識區尺寸,status用於標識區狀態(ACTIVE:表示該區處於活動狀態,EXPIRED:標識該區未用).

undo表空間總結

相關文章