重現ORA-01555 細說Oracle Undo 數據管理

1. 概述

1.1. Undo 數據應用

undo數據是：

原始的、修改之前的數據副本

是針對更改數據的每個事務處理所捕獲的

至少保留到事務處理結束

用于支持：

回退操作

讀取一致性查詢

閃回查詢、閃回事務處理和閃回表

從失敗的事務處理中進行恢復

1.2. 事務和 undo 數據

1.3. undo管理方式與新特性

1.4. undo的狀態--EXPIRE過期

Undo段里數據三種狀態：

ACTIVE：未提交的數據
UNEXPIRE：已提交，未超過undo retention保留時間
EXPIRE：已提交，超過undo_retention保留時間

2. 設置 CDB 使用 Shared Undo Mode

2.1. 案例需求

12cR2 版本中，DBCA 建庫時默認勾選 Use Local Undo tablespace for PDBs，CDB 中每個容器使用本地的 UNDO 表空間。

查看當前 undo 模式，設置 CDB 使用 shared undo mode，即 CDB 中所有容器共享使用一個 UNDO 表空間。

2.2. 啟動數據實例

[oracle@enmoedu1 ~]$ export ORACLE_SID=cdb1 
[oracle@enmoedu1 ~]$ sqlplus / as sysdba
SYS@cdb1> startup 
SYS@cdb1> show pdbs

2.3. 啟動所有 PDB

SYS@cdb1> show pdbs 
SYS@cdb1> alter pluggable database all open; 
SYS@cdb1> show pdbs

2.4. 查看 CDB 數據庫中所有 undo 表空間

SYS@cdb1> select con_id,TABLESPACE_NAME,CONTENTS
from cdb_tablespaces 
where contents='UNDO';查看 undo 表空間對應數據文件 
SYS@cdb1> col FILE_NAME for a60 
select con_id,tablespace_name,file_name 
from cdb_data_files 
where tablespace_name='UNDOTBS1';

2.5. 查看 undo 相關參數

SYS@cdb1> col name for a20 
col value for a20 
select con_id,NAME,VALUE,ISINSTANCE_MODIFIABLE, ISPDB_MODIFIABLE
from v$parameter
where name 
like 'undo%';

2.6. 查看 CDB 當前 undo 模式

SYS@cdb1> 
col property_name for a20 
col PROPERTY_VALUE for a20 
select PROPERTY_NAME,PROPERTY_VALUE 
from database_properties 
where ROPERTY_NAME='LOCAL_UNDO_ENABLED';

當前環境使用 12cR2 版本，DBCA 創建 cdb1 時，默認勾選了 Use Local Undo tablespace for PDBs

2.7. 設置 CDB 使用 Shared Undo Mode

關閉實例，startup upgrade 啟動實例

SYS@cdb1> shutdown immediate; 
SYS@cdb1> startup upgrade;
SYS@cdb1> alter pluggable database all open; 
SYS@cdb1> show pdbs;

2.8. 查看 cdb1 當前 undo 模式

SYS@cdb1> 
col property_name for a20 
col PROPERTY_VALUE for a20 
select PROPERTY_NAME,PROPERTY_VALUE 
from database_properties 
where ROPERTY_NAME='LOCAL_UNDO_ENABLED';

當前使用 Shared Undo Mode，CDB 中所有容器共享使用一個 Undo 表空間

2.9. 刪除 pdb1、pdb2 本地 undo 表空間

共享 undo 模式下，oracle 忽略 PDB 本地的 undo 表空間。

可以刪除 PDB 本地的 undo 表空間及數據文件。

[oracle@enmoedu1 ~]$ sqlplus sys/oracle@pdb1 as sysdba
col file_name for a60
select tablespace_name,file_name from dba_data_files;
SYS@pdb1> drop tablespace undotbs1 including contents and datafiles; 
SYS@pdb1> select tablespace_name,file_name from dba_data_files; 
[oracle@enmoedu1 ~]$ sqlplus sys/oracle@pdb2 as sysdba 
SYS@pdb2> drop tablespace undotbs1 including contents and datafiles;
SYS@pdb2> 
col file_name for a60 
select tablespace_name,file_name from dba_data_files;

2.10. 查看 CDB 數據庫中所有 undo 表空間

查看所有容器中 undo 表空間

SYS@cdb1> select con_id,TABLESPACE_NAME,CONTENTS 
from cdb_tablespaces 
where contents='UNDO';

查看 undo 表空間對應數據文件

SYS@cdb1> col FILE_NAME for a60 
select con_id,tablespace_name,file_name 
from cdb_data_files 
where tablespace_name='UNDOTBS1';

2.11. 關閉 pdb2

后續實驗在根容器和 pdb1 中進行操作

SYS@cdb1> alter pluggable database pdb2 close immediate; 
show pdbs

3. Undo 數據的應用

3.1. pdb1 創建測試表

[oracle@enmoedu1 ~]$ sqlplus sys/oracle@pdb1 as sysdba 
SYS@pdb1> 
create table test1 (id number,name char(20)); 
insert into test1 values (1,'Oracle'); 
insert into test1 values (2,'DBA'); 
commit;SYS@pdb1> select current_scn from v$database;

3.2. pdb1 session 1 執行查詢

SYS@pdb1> 
variable rfc refcursor; 
execute open :rfc for select * from test1;

模擬開始查詢，記錄查詢開始時的 SCN,但未訪問具體數據行

3.3. pdb1 新建會話 session2 更新 test1 表數據

新開會話 session2 登錄連接 pdb1

[oracle@enmoedu1 ~]$ sqlplus sys/oracle@pdb1 as sysdba 
SYS@pdb1> 
select * from test1; 
update test1 set name='OCM' where id=2; 
select * from test1;

3.4. pdb1 新建會話 session3 查看

[oracle@enmoedu1 ~]$ sqlplus sys/oracle@pdb1 as sysdba 
SYS@pdb1> select * from test1;

session2 未提交，其他會話看不到 session2 做出的修改，session3 查詢顯示的是修改之前

的狀態，id=2 的 NAME 字段值“DBA”來自 undo 數據。

未提交的事務對應的 active 狀態的 undo 數據，用于支持 rollback 回滾操作、避免臟讀。

3.5. session2 commit 提交

SYS@pdb1> commit;

3.6. session 3 再次查看

session2 提交以后，session3 查看顯示是更新之后的結果。

3.7. session 1 查看查詢結果

SYS@pdb1> print :rfc

盡管 session2 事務已經提交，session1 查詢返回結果與查詢開始時狀態保持一致，id=2 的name 列值 DBA 來自于 undo 數據。

3.8. session1 閃回查詢

SYS@pdb1> select * from test1; 
SYS@pdb1> select * from test1 as of scn 1605491;

已經提交的事務對應的 undo 數據仍然保留一段時間，未超出 undo_retention 時間的 unexpired 狀態的 undo 數據用于支持長時間執行的查詢語句的一致性讀以及部分閃回操作

（閃回查詢、閃回表等）

4. ORA-01555 錯誤

4.1. 案例需求

通過設置比較小的固定大小的 undo 表空間，模擬長時間執行的 SQL 查詢語句執行過程中，其他會話更新表中數據并提交，由于大量并發事務造成 undo 數據被覆蓋，導致當前查詢語句無法正常返回一致性讀的結果，發生 ORA-01555 錯誤。

4.2. CDB 查看當前 undo 設置

SYS@cdb1> show parameter undo

SYS@cdb1> 
select con_id,tablespace_name,file_name,bytes/1024/1024 MB,AUTOEXTENSIBLE 
from cdb_data_files 
where tablespace_name='UNDOTBS1';

4.3. cdb 創建新的 undo 表空間

創建一個非常小的固定大小的 undo 表空間（數據文件不自動擴展）

SYS@cdb1> create undo tablespace smallundo datafile '/u01/app/oracle/oradata/cdb1/smallundo01.dbf' size 2m autoextend off;
SYS@cdb1> select con_id,tablespace_name,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE 
from cdb_data_files 
where tablespace_name 
like '%UNDO%';

4.4. cdb 切換 undo 表空間

SYS@cdb1> alter system set undo_tablespace='SMALLUNDO'; 
SYS@cdb1> show parameter undo

4.5. pdb1 session1 執行查詢

session1 查看當前 test1 表中數據 
SYS@pdb1> select * from test1; 
session1 查看當前 scn 
SYS@pdb1> select current_scn from v$database;
session1 模擬開始查詢 
SYS@pdb1> 
var rfc refcursor 
exec open :rfc for select * from test1

4.6. pdb1 session2 執行循環更新操作

session2 循環更新 test1 表中數據，模擬 undo 數據被覆蓋

SYS@pdb1> 
begin 
for i in 1..20000 loop 
update test1 set name='Oracle'; 
commit; 
end loop; 
end; 
/

4.7. pdb1 session3 查看查詢結果

session2 事務在循環更新過程中已經提交，session3 查看到的是提交后的結果。

4.8. pdb1 session1 查看查詢結果

SYS@pdb1> print :rfc

4.9. pdb1 session1 閃回查詢

SYS@pdb1> select * from test1 as of scn 1608613; 
session1 再次查詢 
SYS@pdb1> select * from test1;

4.10. ORA-01555

4.11. 查看 undo 表空間屬性

SYS@cdb1> select tablespace_name,RETENTION 
from dba_tablespaces 
where tablespace_name='SMALLUNDO';

SYS@cdb1> show parameter undo

4.12. undo_retention

undo_retention 參數指定了提交后的 undo 數據（unexpired）保留多少秒。

當前環境中設置了 undo_retention=900，默認 undo 表空間未設置 guarantee。

當使用固定大小的 undo 表空間時，undo_retention 值被忽略，數據庫根據 undo 表空間

大小和系統負載情況動態調整優化 retention 值。v$undostat 視圖可以查看動態調整后的

retention 值。 v$undostat 視圖每 10 分鐘生成一行記錄，當前時間對應行的

tuned_undoretention 值會隨事務繁忙程度變化而更新，事務越繁忙，優化后的 retention 值越小。

SYS@cdb1> select to_char(begin_time, 'yyyy-mm-dd hh24:mi') begin_time, 
to_char(end_time, 'yyyy-mm-dd hh24:mi') end_time, tuned_undoretention 
from v$undostat order by end_time;

上圖查詢結果中兩行數據，第一行顯示結果是切換使用固定大小的 undo 表空間后，沒有

事務操作時，tuned_undoretention 值為 1336；第二行顯示結果是 session2 進行循環更新操作

后，tuned_undoretention 值為 41。

undo 表空間太小且不支持數據文件自動擴展時，大量并發事務操作會使 unexpired 狀態

的 undo 數據被覆蓋，進而導致長時間執行的查詢語句報錯 snapshot too old(ORA-01555).

5. ORA-30036 錯誤

5.1. 設置 retention guarantee

SYS@cdb1> select tablespace_name,contents,RETENTION 
from cdb_tablespaces 
where tablespace_name='SMALLUNDO';

設置當前使用的 undo 表空間 retention guarantee 屬性

SYS@cdb1> alter tablespace smallundo retention guarantee; 
SYS@cdb1> select tablespace_name,contents,RETENTION 
from cdb_tablespaces 
where tablespace_name='SMALLUNDO';

5.2. pdb1 session1 執行查詢

SYS@pdb1> select * from test1; 
SYS@pdb1> select current_scn from v$database; 
SYS@pdb1> 
var rfc refcursor 
exec open :rfc for select * from test1

5.3. pdb1 session2 執行循環更新操作

SYS@pdb1> 
begin 
for i in 1..20000 loop 
update test1 set name='12C OCM'; 
commit; 
end loop; 
end; 
/

5.4. pdb1 session3 查看結果

SYS@pdb1> select * from test1;

5.5. pdb1 session1 查看查詢結果

SYS@pdb1> print :rfc

5.6. pdb1 session1 閃回查詢

session1 沒有報 ORA-01555 錯誤，查詢結果與查詢開始時狀態保持一致。

session1 閃回查詢 
SYS@pdb1> select * from test1 as of scn 1644658; 
session1 再次查詢 
SYS@pdb1> select * from test1;

5.7. ORA-30036

SYS@pdb1> ! oerr ora 30036

當前設置了 undo_retention=900 并且設置了 undo 表空間 guarantee 屬性。強制保證

unexpired 狀態的 undo 數據在 900s 保留期內不被覆蓋。session1 最終能夠正常返回結果滿足一致性讀。session2 并發事務操作則由于 undo 空間不足而報錯 ORA-30036。

設置 undo 表空間的 guarantee 屬性后，查看 tuned_undoretention 值

SYS@cdb1> select to_char(begin_time, 'yyyy-mm-dd hh24:mi') begin_time, to_char(end_time, 'yyyy-mm-dd hh24:mi') end_time, tuned_undoretention 
from v$undostat 
order by end_time;

5.8. 取消 guarantee 屬性

SYS@cdb1> alter tablespace smallundo retention noguarantee; 
SYS@cdb1> select tablespace_name,contents,RETENTION 
from cdb_tablespaces 
where tablespace_name='SMALLUNDO';

5.9. resize 調整 undo 表空間大小

SYS@cdb1> alter database datafile 26 resize 10m; 
SYS@cdb1> select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE 
from cdb_data_files 
where tablespace_name='SMALLUNDO';

5.10. pdb1 創建測試表模擬事務操作

SYS@pdb1> 
create table test2 as select * from dba_objects; 
insert into test2 select * from test2; 
insert into test2 select * from test2; 
insert into test2 select * from test2; 
insert into test2 select * from test2;SYS@pdb1> select count(*) from test2; 
delete from test2; 
commit;

test2 表中插入 100 多萬行數據未提交，嘗試刪除所有數據時 undo 空間不足報錯。

當前已經取消 undo 表空間 guarantee 屬性，但由于使用了固定大小的 undo 表空間，大

事務操作也可能會因undo空間不足而報錯 ORA-30036。同時由于事務越繁忙，優化后的 undo

保留期值越小，仍然有可能報 ORA-01555 錯誤。

SYS@cdb1> select to_char(begin_time, 'yyyy-mm-dd hh24:mi') begin_time, to_char(end_time, 'yyyy-mm-dd hh24:mi') end_time, tuned_undoretention 
from v$undostat 
order by end_time;

6. Undo 表空間數據文件自動擴展

6.1. 開啟 undo 表空間自動擴展

SYS@cdb1> select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE from cdb_data_files where tablespace_name='SMALLUNDO';開啟 undo 表空間數據文件自動擴展 
SYS@cdb1> alter database datafile 26 autoextend on; 
SYS@cdb1> select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE from cdb_data_files where tablespace_name='SMALLUNDO';

6.2. pdb1 session1 執行查詢

SYS@pdb1> select * from test1; 
SYS@pdb1> select current_scn from v$database;
SYS@pdb1> 
var rfc refcursor 
exec open :rfc for select * from test1

6.3. pdb1 session2 執行循環更新操作

SYS@pdb1> 
begin 
for i in 1..20000 loop 
update test1 set name='Oracle DBA'; 
commit; 
end loop; 
end; 
/

更新成功，沒有報 ORA-30036 錯誤。

6.4. pdb1 session3 查看結果

SYS@pdb1> select * from test1;

6.5. pdb1 session1 查看查詢結果

SYS@pdb1> print :rfc

查詢沒有報 ORA-01555 錯誤，session1 查詢結果與開始查詢時狀態保持一致。

6.6. pdb1 session1 閃回查詢

session1 閃回查詢 
SYS@pdb1> select * from test1 as of scn 1650833; 
session1 再次查看 
SYS@pdb1> select * from test1;

6.7. 查看當前 undo 表空間數據文件大小

SYS@cdb1> select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE 
from cdb_data_files 
here tablespace_name='SMALLUNDO';

當前設置了 undo_retention=900, undo 表空間使用默認的 noguarantee 屬性。

undo 表空間數據文件設置為 autoextend on 時，數據庫以當前設置的 undo_retention 參

數值作為下限值，并會動態調整 retention 值來滿足長時間執行查詢語句的一致性讀；當 undo

空間不足時，數據庫會擴展 undo 表空間對應數據文件。

SYS@cdb1> select to_char(begin_time, 'yyyy-mm-dd hh24:mi') begin_time, to_char(end_time, 'yyyy-mm-dd hh24:mi') end_time, tuned_undoretention 
from v$undostat 
order by end_time;

如果 undo 表空間數據文件自動擴展達到 maxsize 最大值,數據庫仍然會開始覆蓋unexpired 狀態的 undo 數據。

7. 設置 CDB 使用 Local Undo Mode

7.1. 案例需求

設置當前 CDB 使用 12.2 版本默認的 Local Undo Mode，即 CDB 中每個容器使用本地

的 undo 表空間。

7.2. 查看所有容器中 undo 表空間

SYS@cdb1> select con_id,TABLESPACE_NAME,CONTENTS from cdb_tablespaces where contents='UNDO';

7.3. 查看 CDB 當前 undo 模式

SYS@cdb1>

col property_name for a20

col PROPERTY_VALUE for a20

select PROPERTY_NAME,PROPERTY_VALUE from database_properties where ROPERTY_NAME='LOCAL_UNDO_ENABLED';

7.4. 設置 Local Undo Mode

7.4.1.1. 關閉數據庫實例

SYS@cdb1> shutdown immediate;

7.4.1. startup upgrade 啟動實例

SYS@cdb1> startup upgrade;

7.4.2. 設置 undo 模式

SYS@cdb1> ALTER DATABASE LOCAL UNDO ON;

7.4.3. 重啟實例

SYS@cdb1> shutdown immediate; 
SYS@cdb1> startup

7.5. 查看 CDB 當前 undo 模式

SYS@cdb1> 
col property_name for a20 
col PROPERTY_VALUE for a20 
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where ROPERTY_NAME='LOCAL_UNDO_ENABLED';

7.6. 啟動 PDB

SYS@cdb1> alter pluggable database all open; 
SYS@cdb1> show pdbs

7.7. 查看所有容器中 undo 參數

SYS@cdb1> show parameter undo

SYS@pdb1> show parameter undo

7.8. 查看 CDB 數據庫中所有 undo 表空間

查看所有容器中 undo 表空間

SYS@cdb1> select con_id,TABLESPACE_NAME,CONTENTS from cdb_tablespaces where contents='UNDO';

查看 undo 表空間對應數據文件

SYS@cdb1> 
col FILE_NAME for a60 
select con_id,tablespace_name,file_name from cdb_data_files where tablespace_name like '%UNDO%';
設置 CDB 使用 Local Undo Mode 后，數據庫在每個 PDB 中創建了本地的 undo 表空間。