Oracle特殊恢复原理与实战_09 Undo深入内部解析
UNDO回滚段的作用
- 事务回滚
- 实例恢复（利用回滚来恢复未提交的数据）
- 读一致性（构造CR）
- 数据库闪回查询
- 数据库闪回恢复逻辑错误
重现ORA-01555快照过旧，分析内部原因和解决办法
创建一个不能自动扩展的UNDO表空间
show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
create undo tablespace undotbs2 datafile
'/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 5m;
alter system set undo_tablespace=undotbs2;
select name,value/1024 as KB
from (select b.name,a.value from v$mystat a,v$statname b
where a.STATISTIC#=b.statistic#)
where name='redo size' or name like 'undo change%';
NAME KB
---------------------------------------------------------------- ----------
redo size 123.75
undo change vector size 31.703125
col tablespace_name for a15
col file_name for a50
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_NAME MB
---------- -------------------------------------------------- --------------- ----------
6 /u01/app/oracle/oradata/orcl/undotbs02.dbf UNDOTBS2 5
|
重现ORA-01555快照过旧
# 会话1，定义并打开一个游标
conn lyj/lyj
drop table test1 purge;
create table test1 as select * from all_objects;
set time on
var c1 refcursor
begin
open :c1 for select * from test1;
end;
/
# 会话2，做一个大量的update操作，将T表中的所有ID字段更新了100次
conn lyj/lyj
set time on
begin
for i in 1..100 loop
update test1 set object_id=i;
commit;
end loop;
end;
/
# 会话1，打开刚才定义的游标C1，会得到如下ORA-01555的错误
SQL> print :c1
ERROR:
ORA-01555: snapshot too old: rollback segment number 20 with name
'_SYSSMU20_2141891769$' too small
# alert log中也有如下报错信息：
Fri Apr 27 14:09:41 2018
ORA-01555 caused by SQL statement below (SQL ID: 0m0zj87wk6wru, Query Duration=107 sec, SCN: 0x0000.00129590):
SELECT * FROM TEST1
|
分析内部原因和解决办法
ORACLE官方解释如下：
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一致性读，查询的结果是发起时间(SCN)那一刻的结果集。当大查询没有结束，但其中内容已被更改时，ORACLE会从UNDO里根据发起时间SCN的值找到相应的修改前的值。但如果这时UNDO里的值已经被覆盖，找到不修改前的值了，就会报ORA-01555错误。
解决办法：
- 加大UNDO表空间大小：undo datafile设置成自动扩展（单个文件最大32G），增加undo datafile的个数
- 加大undo_retention，使undo可以保留更长时间不被覆盖
- 优化查询SQL，使用SQL可以在较短的时间完成
用dump分析UNDO的一致性读
构建分析测试环境
# 删除上面刚测试用的UNDO表空间
alter system set undo_tablespace=undotbs1;
startup force
drop tablespace undotbs2 including contents and datafiles;
# 构建分析测试环境（为批执行命令，有显示结果放在最后了）
conn lyj/lyj
drop table lyj purge;
create table lyj(id int,name char(2000));
insert into lyj values(1,'AAAAA');
commit;
var x refcursor;
select current_scn from v$database;
exec open :x for select * from lyj where id=1;
update lyj set name='BBBBB' where id=1;
commit;
update lyj set name='CCCCC' where id=1;
commit;
update lyj set name='DDDDD' where id=1;
commit;
update lyj set name='EEEEE' where id=1;
col name for a20
print :x;
alter system flush buffer_cache;
# 显示的结果
select current_scn from v$database;
CURRENT_SCN
-----------
1396033
print :x;
ID NAME
------ --------------------
1 AAAAA
|
根据未提交事务获取回滚段信息
# 可以通过v$transaction视图来确认事务当前使用的undo segment信息，开一个新会话
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBABLK,UBAFIL,UBAREC,START_TIME,START_SCNB,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC START_TIME START_SCNB STATUS
---------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------------
2 30 627 2162 3 19 04/28/18 14:22:24 1396039 ACTIVE
# xidusn：undo segment number
# xidslot：slot number
# xidsqn：sequence number
# ubafil：undo block address (uba) filenum
# ubablk：uba block number
# ubarec：UBA record number
# 当undo_management设置成AUTO时，使用UNDO tablespace来管理回滚段。
# 多个undo segment，并且这些segment是存放在UNDO表空间里，这样DB的性能会提高。
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1_108211372$
2 _SYSSMU2_2934779712$ # XIDUSN=2
3 _SYSSMU3_1316151929$
4 _SYSSMU4_354880685$
5 _SYSSMU5_2085108374$
6 _SYSSMU6_1379281026$
7 _SYSSMU7_2299785305$
8 _SYSSMU8_548913123$
9 _SYSSMU9_3484822342$
10 _SYSSMU10_493803725$
SQL> select header_file,header_block from dba_segments where segment_name='_SYSSMU2_2934779712$';
HEADER_FILE HEADER_BLOCK
----------- ------------
3 144
SQL> select EXTENT_ID,FILE_ID, BLOCK_ID,BYTES,BLOCKS,STATUS from dba_undo_extents where segment_name='_SYSSMU2_2934779712$';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS STATUS
---------- ---------- ---------- ---------- ---------- ---------
0 3 144 65536 8 EXPIRED
1 3 296 65536 8 EXPIRED
2 3 2048 1048576 128 ACTIVE
# 相关视图
SQL> select * from X$KTUXE where KTUXESTA='ACTIVE';
SQL> select * from v$rollstat;
|
dump undo header
alter system dump undo header '_SYSSMU2_2934779712$';
# or
alter system dump datafile 3 block 144;
# ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XID xidusn xidslot xidsqn;
# alter system dump undo block '_SYSSMU2_2934779712$' XID 2 30 627;
# 确定dump trace位置
oradebug setmypid
oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4440.trc
# 查看trace
********************************************************************************
Undo Segment: _SYSSMU2_2934779712$ (2)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c00873 ext#: 2 blk#: 115 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c00091 length: 7
0x00c00128 length: 8
0x00c00800 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1524758471
Extent Number:1 Commit Time: 1524758471
Extent Number:2 Commit Time: 1524758471
TRN CTL:: seq: 0x00f1 chd: 0x000a ctl: 0x0006 inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c00873.00f1.01 scn: 0x0000.00154902
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c00873.00f1.01 ext: 0x2 spc: 0x1f4c
uba: 0x00000000.00f1.1f ext: 0x2 spc: 0xbf0
uba: 0x00000000.00f1.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.00f1.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.00f1.01 ext: 0x2 spc: 0x1f84
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0273 0x000c 0x0000.00154c1c 0x00c00872 0x0000.000.00000000 0x00000001 0x00000000 1524896156
0x01 9 0x00 0x0271 0x0004 0x0000.00154a94 0x00c00871 0x0000.000.00000000 0x00000002 0x00000000 1524895467
0x02 9 0x00 0x0272 0x0018 0x0000.00154a6a 0x00c00870 0x0000.000.00000000 0x00000001 0x00000000 1524895467
0x03 9 0x00 0x0272 0x000b 0x0000.001549aa 0x00c00868 0x0000.000.00000000 0x00000001 0x00000000 1524895466
0x04 9 0x00 0x0270 0x0008 0x0000.00154ac7 0x00c00871 0x0000.000.00000000 0x00000001 0x00000000 1524895555
0x05 9 0x00 0x0273 0x0016 0x0000.00154bdc 0x00c00871 0x0000.000.00000000 0x00000001 0x00000000 1524896156
0x06 9 0x00 0x026f 0xffff 0x0000.00154d62 0x00c00873 0x0000. |