30、undo_2_2（事务分析、ora-01555）
dump undo段头块分析事务表、dump表的数据块分析事务槽（整个操作在一个事务模拟中分析）
模拟一个事务（首先分析undo段头块的事务）
undo工作原理图：
使用u1用户登录：
[oracle@db11g ~]$ sqlplus u1/u1
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 16 11:58:39 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
开始一个新的事务：
SQL> delete from t1;
1 row deleted.
查询当前活动的事务：
select * from v$transaction
查询活动的事务在undo的哪个段上（在undo段的哪些段上有活动事务）：
select * from v$rollstat;
在1号undo段上有一个活动事务
将1号undo段的段头块dump出来：
查询undo段：
select * from dba_rollback_segs;
查询1号undo段里面的区（这个命令最好不要使用）：
select * from dba_extents where segment_name='_SYSSMU1_3724004606$';
将1号undo段的段头块dump出来：
SQL> alter system dump undo header '_SYSSMU1_3724004606$';
System altered.
[oracle@db11g trace]$ ll
-rw-r----- 1 oracle oinstall 1143 Mar 12 00:29 orcl_mmon_5555.trm
-rw-r----- 1 oracle oinstall 909 Dec 12 15:57 orcl_mmon_7121.trc
-rw-r----- 1 oracle oinstall 60 Dec 12 15:57 orcl_mmon_7121.trm
-rw-r----- 1 oracle oinstall 13052 Dec 13 00:22 orcl_mmon_7223.trc
-rw-r----- 1 oracle oinstall 1384 Dec 13 00:22 orcl_mmon_7223.trm
-rw-r----- 1 oracle oinstall 9066 Mar 16 14:51 orcl_ora_13853.trc -- 这个文件
-rw-r----- 1 oracle oinstall 99 Mar 16 14:51 orcl_ora_13853.trm
-rw-r----- 1 oracle oinstall 907 Mar 15 09:12 orcl_ora_1406.trc
-rw-r----- 1 oracle oinstall 81 Mar 15 09:12 orcl_ora_1406.trm
-rw-r----- 1 oracle oinstall 936 Mar 14 09:43 orcl_ora_1431.trc
-rw-r----- 1 oracle oinstall 81 Mar 14 09:43 orcl_ora_1431.trm
-rw-r----- 1 oracle oinstall 2743 Mar 15 09:12 orcl_ora_1450.trc
-rw-r----- 1 oracle oinstall 175 Mar 15 09:12 orcl_ora_1450.trm
查看dump出来的trace文件：
[oracle@db11g trace]$ cat orcl_ora_13853.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_13853.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: db11g
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 13853, image: oracle@db11g (TNS V1-V3)
*** 2017-03-16 14:51:43.266
*** SESSION ID:(912.33) 2017-03-16 14:51:43.266
*** CLIENT ID:() 2017-03-16 14:51:43.266
*** SERVICE NAME:(SYS$USERS) 2017-03-16 14:51:43.266
*** MODULE NAME:(sqlplus@db11g (TNS V1-V3)) 2017-03-16 14:51:43.266
*** ACTION NAME:() 2017-03-16 14:51:43.266
********************************************************************************
Undo Segment: _SYSSMU1_3724004606$ (1)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c0055c ext#: 2 blk#: 92 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c00081 length: 7
0x00c02050 length: 8
0x00c00500 length: 128
0x00c00300 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1489634505
Extent Number:1 Commit Time: 1489634505
Extent Number:2 Commit Time: 1489634505
Extent Number:3 Commit Time: 1489634505
TRN CTL:: seq: 0x00d5 chd: 0x0008 ctl: 0x0001 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c0055c.00d5.10 scn: 0x0000.001486b2
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.00d5.0f ext: 0x2 spc: 0x1808
uba: 0x00000000.00d5.38 ext: 0x2 spc: 0xd1e
uba: 0x00000000.00d2.0f ext: 0x2 spc: 0x15fc
uba: 0x00000000.00c2.2f ext: 0x23 spc: 0x19c
uba: 0x00000000.00bf.01 ext: 0x20 spc: 0x1ed0
TRN TBL::
覆盖次数（这里是16进制的数）
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0376 0x0013 0x0000.001487d8 0x00c0055a 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x01 9 0x00 0x0376 0xffff 0x0000.001488dc 0x00c0055c 0x0000.000.00000000 0x00000001 0x00000000 1489636856
0x02 9 0x00 0x0375 0x001f 0x0000.0014878f 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x03 9 0x00 0x0373 0x0000 0x0000.001487ce 0x00c0055a 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x04 10 0x80 0x0375 0x0002 0x0000.00148902 0x00c0055c 0x0000.000.00000000 0x00000001 0x00000000 0 -- 活动事务在这个槽位上
0x05 9 0x00 0x0376 0x0010 0x0000.001487a6 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x06 9 0x00 0x0375 0x0001 0x0000.0014888a 0x00c0055c 0x0000.000.00000000 0x00000001 0x00000000 1489636804
0x07 9 0x00 0x0374 0x0016 0x0000.0014872d 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x08 9 0x00 0x0375 0x0009 0x0000.001486c0 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x09 9 0x00 0x0375 0x000e 0x0000.001486cd 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x0a 9 0x00 0x0374 0x001c 0x0000.00148744 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x0b 9 0x00 0x0376 0x0021 0x0000.0014875e 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x0c 9 0x00 0x0375 0x0007 0x0000.00148722 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x0d 9 0x00 0x0376 0x000c 0x0000.00148718 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x0e 9 0x00 0x0374 0x0014 0x0000.001486d3 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x0f 9 0x00 0x0373 0x001b 0x0000.001486fe 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x10 9 0x00 0x0375 0x0017 0x0000.001487b1 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x11 9 0x00 0x0375 0x0006 0x0000.00148870 0x00c0055c 0x0000.000.00000000 0x00000003 0x00000000 1489636804
0x12 9 0x00 0x0374 0x0011 0x0000.0014883f 0x00c0055a 0x0000.000.00000000 0x00000001 0x00000000 1489636746
0x13 9 0x00 0x036e 0x0018 0x0000.001487e7 0x00c0055a 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x14 9 0x00 0x0375 0x0020 0x0000.001486e3 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x15 9 0x00 0x0375 0x0005 0x0000.0014879f 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x16 9 0x00 0x0375 0x000a 0x0000.0014873c 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x17 9 0x00 0x0375 0x001a 0x0000.001487bd 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x18 9 0x00 0x0375 0x0019 0x0000.001487fc 0x00c0055a 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x19 9 0x00 0x0375 0x0012 0x0000.00148810 0x00c0055a 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x1a 9 0x00 0x0375 0x0003 0x0000.001487c6 0x00c0055a 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x1b 9 0x00 0x0375 0x000d 0x0000.0014870c 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x1c 9 0x00 0x0375 0x000b 0x0000.0014874c 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x1d 9 0x00 0x036e 0x001e 0x0000.00148775 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x1e 9 0x00 0x0374 0x0002 0x0000.00148781 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x1f 9 0x00 0x0375 0x0015 0x0000.00148796 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x20 9 0x00 0x0373 0x000f 0x0000.001486f2 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
0x21 9 0x00 0x0374 0x001d 0x0000.00148769 0x00c00559 0x0000.000.00000000 0x00000001 0x00000000 1489636690
-- 事务表
EXT TRN CTL::
usn: 1
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000
0x0b 0x00000000 0x00000000 0x00000000 0x00000000
0x0c 0x00000000 0x00000000 0x00000000 0x00000000
0x0d 0x00000000 0x00000000 0x00000000 0x00000000
0x0e 0x00000000 0x00000000 0x00000000 0x00000000
0x0f 0x00000000 0x00000000 0x00000000 0x00000000
0x10 0x00000000 0x00000000 0x00000000 0x00000000
0x11 0x00000000 0x00000000 0x00000000 0x00000000
0x12 0x00000000 0x00000000 0x00000000 0x00000000
0x13 0x00000000 0x00000000 0x00000000 0x00000000
0x14 0x00000000 0x00000000 0x00000000 0x00000000
0x15 0x00000000 0x00000000 0x00000000 0x00000000
0x16 0x00000000 0x00000000 0x00000000 0x00000000
0x17 0x00000000 0x00000000 0x00000000 0x00000000
0x18 0x00000000 0x00000000 0x00000000 0x00000000
0x19 0x00000000 0x00000000 0x00000000 0x00000000
0x1a 0x00000000 0x00000000 0x00000000 0x00000000
0x1b 0x00000000 0x00000000 0x00000000 0x00000000
0x1c 0x00000000 0x00000000 0x00000000 0x00000000
0x1d 0x00000000 0x00000000 0x00000000 0x00000000
0x1e 0x00000000 0x00000000 0x00000000 0x00000000
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000
将16进制数的覆盖次数转换为10进制的数：
select to_number('0375','xxxxxxxx') from dual;
覆盖次数跟上查到的一样的（对上了）
dump表的数据块分析事务槽
查询t1表的id列的值的rowid：
SQL> select rowid,id from t1;
ROWID ID
------------------ --------------
AAAWNqAAEAAAACvAAA 2
根据rowid可以找到对应的数据块（将rowid里面的信息剥离出来）：
select dbms_rowid.rowid_object('AAAWNqAAEAAAACvAAA') object_id, dbms_rowid.rowid_relative_fno('AAAWNqAAEAAAACvAAA') file_id,dbms_rowid.rowid_block_number('AAAWNqAAEAAAACvAAA') block_id ,dbms_rowid.rowid_row_number('AAAWNqAAEAAAACvAAA') num from dual;
也就是t1这个表里面的数据在4号文件，第175个块里面
将这个数据块dump出来：
SQL> alter system dump datafile 4 block 175;
System altered.
查看dump出来的trace文件：
将数据块里面的uba地址转换为10进制的数：
select to_number('c0008b','xxxxxxxx') from dual;
根据10进制的数据块uba地址，找到uba地址指向的undo块所在的文件号和所在的块号：
select dbms_utility.data_block_address_file(12583051) from dual; -- 文件号
select dbms_utility.data_block_address_block(12583051) from dual; -- 块号
将这个undo块dump出来：
SQL> alter system dump datafile 3 block 139;
System altered.
查看dump出来的文件内容：
可以将数据 2 和 zyr ，转换为undo块里面记录的编号：
select dump(2,1016) from dual
select dump('zyr',1016) from dual
ORA-01555错误
ORA-01555错误产生的原因：
1、undo表空间过小（并且不能自动扩展）
2、retention时间过小
3、事务提交频繁
4、undo数据增长过快
5、select执行时间过长
模拟ORA-01555错误及MOS解决方案
1、 建一个undo表空间（很小，不能自动扩展）：
SQL> CREATE UNDO TABLESPACE rbs_ts DATAFILE '+DATA' SIZE 10M AUTOEXTEND OFF; -- 这个无法建立（没有磁盘）
使用EM建立一个undo表空间：
生成的SQL：
CREATE SMALLFILE UNDO TABLESPACE "RBS_TS" DATAFILE '/oradata/orcl/undofile01.dbf' SIZE 10M RETENTION NOGUARANTEE
2、设置相关参数：
查询undo相关参数：
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_retention=1 scope=spfile; -- 设置retention为1秒
System altered.
SQL> alter system set undo_management=auto scope=spfile; -- 自动管理
System altered.
SQL> alter system set undo_tablespace=rbs_ts scope=spfile; -- 设置使用的undo表空间为建立的表空间
System altered.
3、重启数据库：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 1409289376 bytes
Database Buffers 184549376 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
查询undo相关参数：
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1
undo_tablespace string RBS_TS
4、模拟一个事务，select执行时间过长：
创建一个表t5：
SQL> connect u1/u1
Connected.
SQL> delete from t3;
delete from t3
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'RBS_TS'
-- 报错，因为t3表很大，undo表空间太小，undo表空间存不了那么多要删除之前的数据
SQL> create table t5 as select a.* from dba_objects a where rownum<=100;
Table created.
然后定义一个游标（开始一个长的select，查询t5表）：
SQL> var cl refcursor
begin
open :cl for select * from u1.t5; -- 定义成功，select * from u1.t5这个select就开始执行了
end;
/
PL/SQL procedure successfully completed.
然后更新t5表：
SQL> update t5 set object_id = 111 where object_name = 'I_NTAB1';
1 rows updated.
SQL> commit;
Commit complete.
然后把undo表空间里，上一步更新的数据在undo里面的原来的数据库刷没它（频繁提交事务）：
SQL> begin
for i in 1..200000 loop
update t5 set object_id = 1 where object_id = 95;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
这时候，我们就可以认为：undo里面没有了更新之前的原来的数据了
然后select查询t5表，当查到object_id = 111的时候，oracle会去找更新之前的数据库，但是undo里面已经没有更新之前的数据了，这时候就会报ORA-01555错误
结束select的查询：
SQL> print :cl
刚才那个刷undo的数据时，可能还没有把更新之前的数据刷没，或者select读到的时候还没刷掉（事务提交挺频繁的，但是使用的undo挺小，没有刷掉）
现在重新建了一个t6表：
SQL> show user
USER is "U1"
SQL> create table t6 as select * from dba_objects where rownum<=10000;
Table created.
然后开始一个长的select（定义一个游标）：
[oracle@db11g ~]$ sqlplus u1/u1
SQL> var cl refcursor
begin
open :cl for select * from u1.t6;
end;
/
PL/SQL procedure successfully completed.
向t6表插入数据，然后删除t6表，这时候就会产生大量的undo数据，刷undo数据：
SQL> begin
for i in 1..20000 loop
insert into u1.t6 select * from dba_objects where rownum<1000;
delete from u1.t6;
commit;
end loop;
end;
/
查询会话的等待事件：
SQL> select event,seconds_in_wait from v$session where username='U1';
EVENT SECONDS_IN_WAIT
---------------------------------------------------- ---------------
SQL*Net message from client 373
SQL*Net message from client 0
log buffer space
如果归档满了，怎么办？
归档满了，归档切换不了
删除归档文件：
再次查询会话的等待事件：
SQL> select event,seconds_in_wait from v$session where username='U1';
EVENT SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
SQL*Net message from client 679
SQL*Net message from client 0
enq: CR - block range reuse ckpt 0
SQL> select event,seconds_in_wait from v$session where username='U1';
EVENT SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
SQL*Net message from client 843
SQL*Net message from client 0
log buffer space 0
SQL> /
EVENT SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
SQL*Net message from client 1000
SQL*Net message from client 0
log file switch (checkpoint incomplete) 2
结束select的查询：
SQL> print :cl
......
......
......
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
24-AUG-13 24-AUG-13 2013-08-24:11:37:46 VALID N N N 4
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYP
E------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS
OLAP_DIMENSIONALITY$ 1022 1022 TABLE
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
24-AUG-13 24-AUG-13 2013-08-24:11:37:46 VALID N N N 1
ERROR:
ORA-01555: snapshot too old: rollback segment number 20 with name
"_SYSSMU20_2550646231$" too small
报ORA-01555错误了
查看报错信息：
模拟insert、update、delete对资源的消耗
1、insert产生的redo比较多
2、delete产生的undo比较多
3、update产生的redo和undo都比较多
查询oracle产生的undo的数量：
SQL> desc v$transaction
SQL> create table t1 as select * from dba_objects where rownum <= 10;
Table created.
SQL> delete from t1;
10 rows deleted.
SQL> select UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;
UBAFIL UBABLK UBASQN UBAREC STATUS
---------- ---------- ---------- ---------- ----------------
3 8666 175 46 ACTIVE
