undo原理详解

理论
1.undo作用
undo是datafile文件里面的内容，存放datafiles读出的数据块的前镜像，提供以下四种情况所需要的信息。

回滚事务：rollback

读一致性：正在做dml操作的数据块，事务结束前，其他用户读undo表中的事务前镜像

实例恢复：instance recover（undo--->rollback）smon全程监控，前滚再回滚

闪回查询：flashback query，flashback table

undo表坏了，dml操作将不能进行。有时候内存中undo还有，磁盘虽坏，还能操作。

执行rollback操作

SQL> select * from test0805;

X
----------
2
2

SQL> rollback;

Rollback complete.

SQL> select * from test0805;

X
----------
2
1
只要没有commit，均可以rollback

2.undo管理模式
manual手工：roll segment，回滚段，现在所有的都由undo表空间统一管理

auto自动：undo tablespace（init parameter undo_management=auto)

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
3.undo表空间管理
可建立多个undo表空间，但是一个时刻只有一个undo表空间文件处于active状态

处于active状态的undo tablespace不能offline和drop

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/SINGLE03/datafile/undotbs01.dbf' size 300m autoextend on;

Tablespace created.

检查是否创建成功

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------------------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
UNDOTBS2 ONLINE UNDO

6 rows selected.
查看正在使用的undo

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
检查undo中的内容

SQL> select name from v$rollname;

NAME
------------------------------
SYSTEM
_SYSSMU1_3238025473$
_SYSSMU2_1470581978$
_SYSSMU3_2196636587$
_SYSSMU4_739013272$
_SYSSMU5_3116014445$
_SYSSMU6_370862867$
_SYSSMU7_3780960176$
_SYSSMU8_386887930$
_SYSSMU9_1508883017$
_SYSSMU10_126353523$

11 rows selected.

下面10个表为undo中的段
切换undo表空间

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
检查undo

SQL> select name from v$rollname;

NAME
------------------------------
SYSTEM
_SYSSMU11_2128764775$
_SYSSMU12_1648238980$
_SYSSMU13_2755935120$
_SYSSMU14_2771354400$
_SYSSMU15_61391895$
_SYSSMU16_3890504534$
_SYSSMU17_947698440$
_SYSSMU18_1509181424$
_SYSSMU19_1175789042$
_SYSSMU20_4162043748$

NAME
------------------------------
_SYSSMU21_1092483237$
_SYSSMU22_2036042781$

13 rows selected.

不够用，系统会自动添加
删除原先的undo表空间

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

检查删除情况

dba_tablespaces静态试图，存于数据文件

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------------------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
UNDOTBS2 ONLINE UNDO

v$tablespace动态视图，存于控制文件中。

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 1
1 SYSAUX YES NO YES 2
2 UNDOTBS1 YES NO YES 2
3 TEMP NO NO YES 1
3 TEMP NO NO YES 2
4 USERS YES NO YES 1
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3

TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
3 TEMP NO NO YES 3
4 USERS YES NO YES 3
5 UNDOTBS2 YES NO YES 1

14 rows selected.
4.undo数据的4中状态
active：表示transaction还没有commit，不可覆盖。回滚时起作用，事务还未提交，即未结束

unexpired没过期：表示已经commit，但是还在undo_retention内，不可以覆盖（非强制），加guarantee属性后强制undo_retention内不覆盖。已经提交，但是还没过期，寻找已经提交的数据，

expired：已经commit，且时间超过了undo_retention,随时可覆盖。已经提交，且已经过期。

free：分配了但未使用过。undo块还没被用过。

注：当expired空间不够用时，优先使用free里面的空间，当expired和free空间均不够用时，autoextend on参数决定可以涨新的空间。当空间不够用时，有时候也会用到unexpired空间，只有定义了guarantee参数，才可以保证保留unexpired空间 undo retention分钟

5.undo retention参数和undo autoextend on特性
undo retention单位为秒，指提交了之后保留的分钟数

undo retention参数规定了unexpired commit数据的保留期，它是保证一致性读，是大多数闪回技术成功的关键

将undo表空间设为autoentend on，这是dbca创建数据库时的缺省设置，这个参数指定undo表空间不足时优先扩展新的空间，其次覆盖unexpired commit。可去undo表空间管理目录下去找创建undo表时的参数autoentend on

6.undo_retention参数
只对已经commit的undo状态有效

修改retention字段为guarantee，保证unexpired空间不在undo_retention时间内被覆盖。

SQL> select tablespace_name,status,contents,retention from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS RETENTION
------------------------------ --------- --------------------- -----------
SYSTEM ONLINE PERMANENT NOT APPLY
SYSAUX ONLINE PERMANENT NOT APPLY
TEMP ONLINE TEMPORARY NOT APPLY
USERS ONLINE PERMANENT NOT APPLY
UNDOTBS2 ONLINE UNDO NOGUARANTEE

SQL> alter tablespace undotbs2 retention guarantee;

Tablespace altered.

SQL> select tablespace_name,status,contents,retention from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS RETENTION
------------------------------ --------- --------------------- -----------
SYSTEM ONLINE PERMANENT NOT APPLY
SYSAUX ONLINE PERMANENT NOT APPLY
TEMP ONLINE TEMPORARY NOT APPLY
USERS ONLINE PERMANENT NOT APPLY
UNDOTBS2 ONLINE UNDO GUARANTEE
注：rentention的值guarantee为属性，而undo_retention为参数，可通过show parameter展示出来

且retention缺省值是noguarantee



如上图所示，数据还未提交时称rollback（回滚），当数据已经提交后，称闪回。

7.system undo
system拥有单独的undo段，段名交system，存储数据字典的变更，但undo表出问题导致无法startup 数据库时，可使其他段处于不检查状态（offline)，仅仅靠system字段也可以进入数据库。

8.undo信息查询
v$session 查看用户建立的session
v$transaction 当时的事务
v$rollstat undo段的状态
v$undo段的名称
dba_rollback_segs数据字典中记录的undo状态
一个session只能对应一个事务，建立了session未必有事务，只有事务处于活动态时才被v$transaction才会被记录。换言之，如果看到了事务，那一定有个session和它对应。

执行创建表事务，再检查数据（更新：创建表不被记录，dml操作会被下面的联合表记录）

SQL> select a.sid,a.serial#,a.username,b.xidusn,xidslot,b.ubablk,b.status from v$session a,v$transaction b where a.saddr=b.ses_addr;

SID SERIAL# USERNAME XIDUSN XIDSLOT UBABLK STATUS
---------- ---------- -------------------- ---------- ---------- ---------- ----------------
280 42616 SYSTEM 13 25 161 ACTIVE

11:53:59 SQL> select b.name,a.xacts from v$rollstat a,v$rollname b where a.usn=b.usn;

NAME XACTS
------------------------------ ----------
SYSTEM 0
_SYSSMU11_2128764775$ 0
_SYSSMU12_1648238980$ 0
_SYSSMU13_2755935120$ 1
_SYSSMU14_2771354400$ 0
_SYSSMU15_61391895$ 0
_SYSSMU16_3890504534$ 0
_SYSSMU17_947698440$ 0
_SYSSMU18_1509181424$ 0
_SYSSMU19_1175789042$ 0
_SYSSMU20_4162043748$ 0

NAME XACTS
------------------------------ ----------
_SYSSMU21_1092483237$ 0
_SYSSMU22_2036042781$ 0

13 rows selected.

name _SYSSMU13_2755935120$ 的xacts值为1，说明刚刚的事务的undo前镜像存储在了_SYSSMU13_2755935120$的undo段
理论上一个段可以存不同的事务，一般情况下一个段存一个事务

system系统undo段的usn为0. 如系统表空间修改等操作。启动时发现undo坏了，于是不检查除system undo的东西，在进入数据库中后，重新创建undo表空间

实验
undo数据文件坏了

1.移除undotbs02.dbf

[oracle@single04 SINGLE04]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs02.dbf
control02.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@single04 SINGLE04]$ mv undotbs02.dbf ../
[oracle@single04 SINGLE04]$ ls
control01.ctl redo01.log redo03.log system01.dbf users01.dbf
control02.ctl redo02.log sysaux01.dbf temp01.dbf
2.切归档并更新数据表操作

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> show con_name;

CON_NAME
------------------------------
single04
SQL> update test0805 set x=2;
update test0805 set x=2
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/SINGLE04/undotbs02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
发现因为找不到undo表空间，更新操作已经不能执行。

3.关闭数据库

SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/SINGLE04/undotbs02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
4.打开数据库只能进入到mount状态，因为缺少undo数据表，所以只能mount，不能open

SQL> startup;
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 771751936 bytes
Database Buffers 469762048 bytes
Redo Buffers 7880704 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/SINGLE04/undotbs02.dbf'
5.检查控制文件和数据文件中的表空间

SQL> select file#,checkpoint_change#,name from v$datafile;

FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
1 2151763 /u01/app/oracle/oradata/SINGLE04/system01.dbf
3 2151763 /u01/app/oracle/oradata/SINGLE04/sysaux01.dbf
5 2051579 /u01/app/oracle/oradata/SINGLE04/undotbs02.dbf
7 2151763 /u01/app/oracle/oradata/SINGLE04/users01.dbf

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2051579
3 2051579
5 0
7 2051579
可见5号文件undotbs02的状态

6.将undotbs文件offline，使数据库不检查该undo表空间的一致性

SQL> alter database datafile 5 offline;

Database altered.

SQL> alter database open;

Database altered.
7.此时只有system的undo表空间

SQL> select * from v$rollname;

USN NAME CON_ID
---------- -------------------------------------------------- ----------
0 SYSTEM 0
8.创建undo表空间，并激活新的undo表空间

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/SINGLE04/undotbs01.dbf' size 300m autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs1;

System altered.
9.检查原undo表空间是否有需要恢复的undo段

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1_2487999089$ ONLINE
_SYSSMU2_3423437920$ ONLINE
_SYSSMU3_3425423887$ ONLINE
_SYSSMU4_1512346732$ ONLINE
_SYSSMU5_3747058130$ ONLINE
_SYSSMU6_376886025$ ONLINE
_SYSSMU7_700914424$ ONLINE
_SYSSMU8_1973569216$ ONLINE
_SYSSMU9_4133479381$ ONLINE
_SYSSMU10_100869635$ ONLINE

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU23_187953927$ ONLINE
_SYSSMU24_1133271135$ ONLINE
_SYSSMU11_2261036069$ NEEDS RECOVERY
_SYSSMU12_1814340786$ NEEDS RECOVERY
_SYSSMU13_3799478061$ NEEDS RECOVERY
_SYSSMU14_1496660807$ NEEDS RECOVERY
_SYSSMU15_143313127$ NEEDS RECOVERY
_SYSSMU16_2288119159$ NEEDS RECOVERY
_SYSSMU17_3895543627$ NEEDS RECOVERY
_SYSSMU18_159612696$ NEEDS RECOVERY
_SYSSMU19_274538794$ NEEDS RECOVERY
10.删除need recovery的undo字段

SQL> drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_2261036069$' found, terminate
dropping tablespace

如上图所示，不能删除undotbs2，于是需要一个个的删除undo2的undo段

或者有备份的话可以恢复这个备份

11.创建pfile文件，修改参数，启动数据库，并删除需要恢复的undo段

SQL> create pfile from spfile;

File created.
上面生成的pfile文件存于$ORACLE_HOME/dbs里面

具体修改的参数如下所示：

_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU11_2261036069$,_SYSSMU12_1814340786$,_SYSSMU13_3799478061$,_SYSSMU14_1496660807$,_SYSSMU15_143313127$,_SYSSMU16_2288119159$,_SYSSMU17_3895543627$,_SYSSMU18_159612696$,_SYSSMU19_274538794$,_SYSSMU20_3628600363$,_SYSSMU21_3285657719$,_SYSSMU22_1383429345$)
利用新的pfile文件启动数据库

SQL> startup pfile='/u01/app/oracle/product/19.3.0/db_1/dbs/initsingle04.ora' ;
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 788529152 bytes
Database Buffers 452984832 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
删除需要恢复的rollback

SQL> drop rollback segment "_SYSSMU12_1814340786$";

Rollback segment dropped.
注：此处需要用双引号

12.删除undotbs2表空间

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.