重现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 表空间。

8. 12cR1 Temporary Undo

8.1. redo 与 undo

8.2. 临时表

8.3. 12cR1 Temporary Undo

文章主要内容摘抄墨天轮恩墨大讲堂《重现ORA-01555 细说Oracle Undo 数据管理》。