UNDO 日志

爱学习de小馋猫 2019-09-10

239

1. undo的作用

使用undo tablespace 存放从datafiles 读出的数据块的前镜像，提供以下四种情况所需要的信息

1）回滚事务：rollback

2）读一致性：正在做DML操作的数据块，事务结束前，其他用户读undo里面的数据前镜像（记录select开始时当前scn号数据）

3）实例的恢复：instance recover(undo -->rollback) ----undo可能是redo恢复出来的

4）闪回技术：flashback query、flashback table等—从undo中查询

2. undo 的管理模式

1）manaual 手工：roll segment（淘汰）--每做一个事务都要分配一个回滚段（事务执行前要把数据放到回滚段上）

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

Undo_tablespace:自动在哪个表空间分配，默认分配10个段见V$rollstat，事务超过10个时oracle会自动增加

select usn,latch,gets,waits,optsize,status,curext,curblk from v$rollstat;
       USN      LATCH       GETS      WAITS    OPTSIZE STATUS              CUREXT     CURBLK
---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------
         0          0         68          0            ONLINE                   3          0
         1          0        484          0            ONLINE                   2         97
         2          0        475          0            ONLINE                   7          4
         3          0        383          0            ONLINE                   7          0
         4          0        437          0            ONLINE                  14          4
         5          0        452          0            ONLINE                   0          2
         6          0        459          0            ONLINE                   2         92
         7          0        253          0            ONLINE                   2        114
         8          0        529          0            ONLINE                   1          4
         9          0        481          0            ONLINE                   3         79
        10          0        315          0            ONLINE                   0          4

3. undo 表空间管理----undo是一个表空间

1）可以建立多个undo表空间，但一个时刻只有一个处于active

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

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
EXAMPLE                        ONLINE    PERMANENT
TEST_LJING                     ONLINE    PERMANENT

4. 查看当前正在使用的undo tablespace

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

查看回滚段v$rollname

SQL>select * from v$rollname;
       USN NAME
---------- ------------------------------------------------------------
         0 SYSTEM
         1 _SYSSMU1_1240252155$
         2 _SYSSMU2_111974964$
         3 _SYSSMU3_4004931649$
         4 _SYSSMU4_1126976075$
         5 _SYSSMU5_4011504098$
         6 _SYSSMU6_3654194381$
         7 _SYSSMU7_4222772309$
         8 _SYSSMU8_3612859353$
         9 _SYSSMU9_3945653786$
        10 _SYSSMU10_3271578125$

5. 切换undo

查看当前使用undo tablespace

SQL>show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

新增undo表空间

SQL>create undo tablespace undotbs2 datafile '/u01/oradata/dbf/undotbs2.dbf' size 10m reuse autoextend on;

切换undo tablespace

SQL> alter system set undo_tablespace=undotbs2;    ##  memory动态修改

查看当前undo设置详情

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

注：undo_retention 缺省保留时间15分钟

查看当前所有在线回滚段的名字

SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
        11 _SYSSMU11_1357956213$
        12 _SYSSMU12_1357956213$
        13 _SYSSMU13_1357956213$
        14 _SYSSMU14_1357956213$
        15 _SYSSMU15_1357956213$
        16 _SYSSMU16_1357956213$
        17 _SYSSMU17_1357956213$
        18 _SYSSMU18_1357956213$
        19 _SYSSMU19_1357956213$
        20 _SYSSMU20_1357956213$

6. 删除undo tablespace

SQL> drop tablespace undotbs2 including contents and datafiles;

注：如果要删除的表空间当前正在使用中，删除会报错，不允许删除

SQL>select * from v$tablespace order by 1;
       TS# NAME                 INCLUDED_IN_DATABASE_BACKUP    BIGFILE    FLASHBACK_ON         ENCRYPT_IN_BACKUP
---------- -------------------- ------------------------------ ---------- -------------------- ------------------------------
         0 SYSTEM               YES                            NO         YES
         1 SYSAUX               YES                            NO         YES
         2 UNDOTBS1             YES                            NO         YES
         3 TEMP                 NO                             NO         YES
         4 USERS                YES                            NO         YES
         5 UNDOTBS2             YES                            NO         YES
         6 EXAMPLE              YES                            NO         YES
        12 TEST_LJING           YES                            NO         YES

7. undo 数据的4种状态

1）active：表示transaction还没有commit，不可覆盖

2）unexpired：已经commit,但是还在undo_retention内，不可以覆盖（非强制），加GUARANTEE（表空间的属性，只对undo表空间有效其他表空间时not apply，在dba_tablespace中）属性后强制undo_retention内不覆盖。

3）expired：已经commit，且时间超过了undo_retention，随时可以覆盖。

4）free：分配了但未使用过。

注：这是数据文件的特性，不是undo表空间的特性，可以在创建表空间时设置

undo retention参数和undo autoextend on特性

1）undo retention参数规定了unexpired commit数据的保留期，它是保证一致性读，和大多数闪回技术成功的关键，可以通过show parameter undo 查看详情

2）将undo表空间设为autoextend on，这是DBCA创建数据库时的缺省设置, 这一个特性将在undo空间不足时优先扩展新的空间,其次才是覆盖unexpired commit。

说明：如果undo retention=0，则Oracle会启用undo自动调优，它使用900秒作为undo retention的参考值。然后根据收集到的数据库中最长查询及撤销生成率自动调整undo retention。

注意：如果要减少ORA_01555错误（snapshot too old)，考虑延长undo retention并使之guarantee, 同时使undo autoextend on避免undo耗尽。

8. 关于undo_retention 参数

这个参数只对已commit的undo状态有效

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
EXAMPLE                          ONLINE    PERMANENT NOT APPLY
TEST                             ONLINE    PERMANENT NOT APPLY
UNDOTBS2                         ONLINE    UNDO      NOGUARANTEE

guarantee属性随undo表空间建立, 可以修改

SQL> alter tablespace undotbs2 retention guarantee;   //保证在retention 期间不允许被覆盖

缺省配置下undo retention是 noguarantee

SQL> alter tablespace undotbs2 retention noguarantee;

9. undo 信息的查询

1） v$session 查看用户建立的session

2） v$transaction 当前的事务

3） v$rollname undo段的名称

4） v$rollstat undo段的状态

5) dba_rollback_segs 数据字典里记录的undo段状态

一般来说，一个session同时只能承载一个事务，建立了session未必有事务，只有事务处于活动状态时，v$transaction才能看到这个事务。换句话说，如果看到了事务（在 v$transaction里），那一定有个session和它对应，将两个视图连在一起，信息看得更为清楚。

SQL> select username,sid,serial# from v$session where username is not null;    //看看有几个session连上来了。
USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SYS                                    42          9
SCOTT                                  43        723

scott下执行

SQL> update emp1 set sal=1000 where empno=7788;    ## 产生一个活动事务

再回到sys用户下：

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
---------- ---------- ------------------------------ ---------- ---------- ---------- ----------------
        43        723 SCOTT                                   5         25       2779 ACTIVE

说明：两表联查，sid和serial#在v$session里，在v$transaction里 XIDUSN是undo segment 的id ，XIDSLOT 是事务槽的id，UBABLK是undo块号

对照上面看，下面语句显示出：

5 _SYSSMU5_4011504098$是一个活动段，与XIDUSN=5 吻合

说明：这个段被读进buffer了.

SQL> select a.usn,b.name,a.xacts from v$rollstat a, v$rollname b where a.usn=b.usn;
       USN NAME                           XACTS
---------- ------------------------- ----------
         0 SYSTEM                             0
         1 _SYSSMU1_1240252155$               0
         2 _SYSSMU2_111974964$                0
         3 _SYSSMU3_4004931649$               0
         4 _SYSSMU4_1126976075$               0
         5 _SYSSMU5_4011504098$               1
         6 _SYSSMU6_3654194381$               0
         7 _SYSSMU7_4222772309$               0
         8 _SYSSMU8_3612859353$               0
         9 _SYSSMU9_3945653786$               0
        10 _SYSSMU10_3271578125$              0

10. system表空间的undo

默认system表空间会有一个单独的undo segment(usn为0)。其他表空间的事务不能使用它。缺省下undo tablespace 会被分配10个undo段，理论上一个段可以有多个事务，但Oracle的策略并不情愿如此，实验表明，对应并发的事务，通常每个事务会分配一个UNDO段，也就是说如果这10个段不够，Oracle会自动再增加段。

当数据库启动时，如果碰到undo数据文件损坏或不同步，可以将其offline, 即只有一个系统表空间的undo也能进入系统，以便数据字典得以维护，但不能对其他表空间做DML操作。

11. 测试：模拟数据库open下的undo损坏和修复。

如果数据库打开时，当前undo坏掉的话，比如现在UNDOTBS2出现了介质损坏，那么数据库就不能继续DML操作了。

如果这时UNDOTBS2表空间上还有active状态的事务（未提交），Oracle会将其下的所有段都标志为NEEDS RECOVERY，这时如有备份可以恢复这个UNDOTBS2，但如果没有备份，那你只能用新建的UNDO替代损坏的UNDO，那么损坏UNDO上的未提交事务也将不得不丢弃。

但Oracle没有提供自动解决NEEDS RECOVERY的机制，如果你想删除这个损坏的UNDO表空间，必须另做处理（需要系统择时重启），我们模拟一下这种情况：

查看当前undo表空间

SQL>show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

切换成我之前创建的undotbs2

SYS@vbox88in*SQL>alter system set undo_tablespace = undotbs2;

查看是否切换成功

SYS@vbox88in*SQL>show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

破坏undotbs2

[oracle@vbox88 ~]$ cd u01/oradata/dbf
[oracle@vbox88 dbf]$ ls
example01.dbf  sysaux01.dbf  system01.dbf  test_ljing.dbf  undotbs01.dbf  undotbs2.dbf  users01.dbf
[oracle@vbox88 dbf]$ mv undotbs2.dbf undotbf.bak
[oracle@vbox88 dbf]$ ls
example01.dbf  sysaux01.dbf  system01.dbf  test_ljing.dbf  undotbf.bak  undotbs01.dbf  users01.dbf
## 产生检查点
SQL>alter system checkpoint;

再做update 语句时会报错

SQL>update emp1 a set a.comm = 1000 where a.empno = 7934;
update emp1 a set a.comm = 1000 where a.empno = 7934
       *
第 1 行出现错误:
ORA-01116: 打开数据库文件 7 时出错 ORA-01110:
数据文件 7: '/u01/oradata/dbf/undotbs2.dbf'
ORA-27041: 无法打开文件
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

注意：不影响查询操作，查询可以正常执行的，只是不能做DML操作了

启动到mount状态下

SQL> startup force mount

查看数据文件change#信息

SQL>select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2158820
         2            2158820
         3            2158820
         4            2158820
         5            2158820
         6            2158820
         7            2158820

查看数据文件头change#信息

SQL>select file#,checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2158820
         2            2158820
         3            2158820
         4            2158820
         5            2158820
         6            2158820
         7                  0

将出错数据文件offline

SQL> alter database datafile 7 offline;

注：这里可以使用下面的sql直接删除offline文件

alter database datafile 7 offline drop;

打开数据库

SQL> alter database open;

查看回滚段信息，可以看到当前只有一个正常的回滚段，就是SYSTEM，这种情况也是可以正常启动的

SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM

这个静态视图可以列出所有（online或offline）UNDO段信息

SQL> select segment_name,status from dba_rollback_segs;    
SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU11_146342328$           NEEDS RECOVERY
_SYSSMU12_91139839$            NEEDS RECOVERY
_SYSSMU13_2085344819$          NEEDS RECOVERY
_SYSSMU14_3639291727$          NEEDS RECOVERY
_SYSSMU15_1225898865$          NEEDS RECOVERY
_SYSSMU16_1225571040$          NEEDS RECOVERY
_SYSSMU17_2617391731$          NEEDS RECOVERY
_SYSSMU18_3459718133$          NEEDS RECOVERY
_SYSSMU19_2679805740$          NEEDS RECOVERY
_SYSSMU20_3731297121$          NEEDS RECOVERY

创建undotbs02，创建新的undo表空间代替被损坏的undo

SQL>create undo tablespace undotbs02 datafile '/u01/oradata/dbf/undotbs02.dbf' size 10m autoextend on;

查看当前表空间

SQL>select * from v$tablespace order by 1;
TS  # NAME        INCLUDED_IN_DATABASE_BACKUP             BIGFILE    FLASHBACK_ON         ENCRYPT_IN_BACKUP
---- --------- ---------------------------------------- ----------  ----------------      -------------------
0 SYSTEM                    YES                            NO         YES
1 SYSAUX                    YES                            NO         YES
2 UNDOTBS1                  YES                            NO         YES
3 TEMP                      NO                             NO         YES
4 USERS                     YES                            NO         YES
5 UNDOTBS2                  YES                            NO         YES
6 EXAMPLE                   YES                            NO         YES
12 TEST_LJING                YES                            NO         YES
13 UNDOTBS02                 YES                            NO         YES

切换到undotbs02

SQL> alter system set undo_tablespace=UNDOTBS02;

注意：此时原有活动事务的信息（未提交)可能仍然保存在UNDOTBS2, 已经没有恢复的必要了。

SQL> update emp1 set sal=1000 where empno=7902;    //emp1表被锁住，不能做DML操作
update emp1 set sal=1000 where empno=7902
*
第 1 行出现错误:
ORA-00376: 此时无法读取文件 7
ORA-01110: 数据文件 7: '/u01/oradata/dbf/undotbs02.dbf'

删除undotbs2表空间

SQL>drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU11_146342328$', 终止删除表空间

这个UNDOTBS2删不掉是因为Oracle把其下的所有段都标志成 NEEDS RECOVERY了，再查看一下相关的数据字典：

SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU11_146342328$           NEEDS RECOVERY
_SYSSMU12_91139839$            NEEDS RECOVERY
_SYSSMU13_2085344819$          NEEDS RECOVERY
_SYSSMU14_3639291727$          NEEDS RECOVERY
_SYSSMU15_1225898865$          NEEDS RECOVERY
_SYSSMU16_1225571040$          NEEDS RECOVERY
_SYSSMU17_2617391731$          NEEDS RECOVERY
_SYSSMU18_3459718133$          NEEDS RECOVERY
_SYSSMU19_2679805740$          NEEDS RECOVERY
_SYSSMU20_3731297121$          NEEDS RECOVERY
_SYSSMU21_243957597$           ONLINE
_SYSSMU22_905184800$           ONLINE
_SYSSMU23_3760924324$          ONLINE
_SYSSMU24_841618551$           ONLINE
_SYSSMU25_2790068211$          ONLINE
_SYSSMU26_2289204256$          ONLINE
_SYSSMU27_4280371216$          ONLINE
_SYSSMU28_2050682228$          ONLINE
_SYSSMU29_3715777446$          ONLINE
_SYSSMU30_1558982866$          ONLINE

解决有两个办法：

1）使用备份恢复

2）使用oracle提供的隐含参数_CORRUPTED_ROLLBACK_SEGMENTS

我们假设没有备份，就使用第2种方法

SQL>create pfile from spfile;      //建立静态参数文件
SQL>shutdown abort
[oracle@vbox88 ~]$ cd u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@vbox88 ~]$ vi initvbox88in.ora
//在静态参数文件里第一行插入以下内容，注意这里根据你的 NEEDS RECOVERY 填写
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU11_146342328$ ,_SYSSMU12_91139839$,_SYSSMU13_2085344819$ ,_SYSSMU14_3639291727$,_SYSSMU15_1225898865$,_SYSSMU16_1225571040$,_SYSSMU17_2617391731$,_SYSSMU18_3459718133$,_SYSSMU19_2679805740$,_SYSSMU20_3731297121$)

然后存盘,再使静态参数文件启动数据库

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initvbox88in.ora'
SQL>drop rollback segment "_SYSSMU11_146342328$";
## 回退段已删除。
## ..........依次删除所有回滚段...............
SQL> drop rollback segment "_SYSSMU20_3731297121$";

查看当前回滚段情况

SQL>select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU21_243957597$           ONLINE
_SYSSMU22_905184800$           ONLINE
_SYSSMU23_3760924324$          ONLINE
_SYSSMU24_841618551$           ONLINE
_SYSSMU25_2790068211$          ONLINE
_SYSSMU26_2289204256$          ONLINE
_SYSSMU27_4280371216$          ONLINE
_SYSSMU28_2050682228$          ONLINE
_SYSSMU29_3715777446$          ONLINE
_SYSSMU30_1558982866$          ONLINE

删除ubdotbs2

SQL> drop tablespace undotbs2 including contents and datafiles;

如果仍然删除失败（比较少见），则尝试修改字典

select * from v$tablespace;  
## 记下要删除的undo对应的ts#号，比如是=2，则执行下行语句：
update seg$ set type# = 3 where ts#=2;

然后再删除该表空间即可。

数据库

文章转载自爱学习de小馋猫，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

UNDO 日志

评论