Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle UNDO

2018-05-01

Oracle - Study UNDO

UNDO回滚段的作用

undo回滚段作用:

事务回滚
实例恢复(利用回滚段来恢复未提交的数据)
构造CR块,读一致性
数据库闪回查询
数据库闪回恢复逻辑错误

ORA-01555快照过旧的分析

测试工作:

创建另外一个不能自动扩展的UNDO表空间

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/etc/oracle/oradata/orcl/system01.dbf
/etc/oracle/oradata/orcl/sysaux01.dbf
/etc/oracle/oradata/orcl/undotbs01.dbf
/etc/oracle/oradata/orcl/users01.dbf
/etc/oracle/oradata/orcl/example01.dbf

SQL> create undo tablespace undotbs2 datafile '/etc/oracle/oradata/orcl/undotbs02.dbf' size 5m;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL> 


SQL> col tablespace_name for a20
SQL> col file_name for a60
SQL> set linesize 1000
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 MB from dba_data_files where file_name like '%undotbs02%';

FILE_ID FILE_NAME                                          TABLESPACE         MB
---------- -------------------------------------------------- ---------- ----------
		6 /etc/oracle/oradata/orcl/undotbs02.dbf             UNDOTBS2            5

SQL> 
SQL> create user lyn identified by oracle;

User created.

SQL> grant connect,resource,dba to lyn;

Grant succeeded.

SQL> set sqlp "_USER'@'_CONNECT_IDENTIFIER> "

测试过程:打开两个会话过程

+++++++++++++++++++++++++

set time on var r1 refcursor begin open :r1 for select * from t1; end; /

+++++++++++++++++++++++++

set time on begin for i in 1..10 loop update t1 set object_id=i where object_id < 10000; commit; end loop; end; / +++++++++++++++++++++++++

Session1:

set sqlp "_USER'@'SESSION01> "

LYN@SESSION01> 

Session2:

set sqlp "_USER'@'SESSION02> "
LYN@SESSION02> 

Session1: 定义一个游标获取t1测试表的全部结果集

LYN@SESSION01> create table t1 as select * from dba_objects;

Table created.

LYN@SESSION01> 

LYN@SESSION01> set time on
var r1 refcursor
begin 
open :r1 for select * from t1;
end;
/00:45:01 LYN@SESSION01> 00:45:01 LYN@SESSION01> 00:45:01   2  00:45:01   3  00:45:01   4  

PL/SQL procedure successfully completed.

00:45:02 LYN@SESSION01> 

Session 2: 大量更新操作(目的是清理掉有限表空间内容)

00:43:37 LYN@SESSION02> set time on
begin
for i in 1..10 loop
update t1 set object_id=i where object_id < 10000;
commit;
end loop;
end;
/00:45:27 LYN@SESSION02> 00:45:27   2  00:45:27   3  00:45:27   4  00:45:27   5  00:45:27   6  00:45:27   7  

PL/SQL procedure successfully completed.

00:45:30 LYN@SESSION02> 

Session 1: 打开刚才定义的游标r1,出现ORA-01555错误

00:45:02 LYN@SESSION01> print :r1 
ERROR:
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_637614006$" too small

no rows selected

00:45:40 LYN@SESSION01> 

在diag日志中也可以找到ORA-01555

Fri May 01 00:45:40 2018
ORA-01555 caused by SQL statement below (SQL ID: 0jjc60pmrntdv, Query Duration=38 sec, SCN: 0x0000.001016a1):
SELECT * FROM T1

分析:

ORA-01555错误:

[oracle@database ~]$ oerr ora 01555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments
[oracle@database ~]$ 

ORACLE一致性读,查询的结果是发起时间(SCN)那一刻的结果集。当大查询没有结束,但其中内容已被更改时,ORACLE会从UNDO里根据发起时间SCN的值找到相应的修改前的值。但如果这时UNDO里的值已经被覆盖,找到不修改前的值了,就会报ORA-01555错误。

对策一般有下面方法:

加大UNDO表空间大小:undo datafile设置成自动扩展(单个文件最大32G),增加undo datafile的个数
加大undo_retention,使undo可以保留更长时间不被覆盖
优化SQL,使用SQL可以在较短的时间完成

Have a good day! 2018/05 via LinHong


Similar Posts

Comments