Oracle undo表空间管理

最新推荐文章于 2024-05-29 21:20:19 发布

大家好我是大雄同学

最新推荐文章于 2024-05-29 21:20:19 发布

阅读量1k

点赞数

分类专栏： ORACLE 文章标签： oracle

本文链接：https://blog.csdn.net/gavin1731/article/details/70666666

版权

ORACLE 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1、undo作用

事务处理恢复
事务处理回退
读一致性
闪回查询

2、undo管理模式

UNDO段的两种管理方式：AUTO（自动管理，推荐）、MANUAL ( 手动管理)

3、undo参数

undo_management

设置数据库的撤销段是否使用自动管理模式，值可以为auto或manual

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
undo_management              string  AUTO

undo_tablespace:

设置数据库的默认undo表空间
undo的默认表空间

SQL> show parameter undo_tablespace

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace              string  UNDOTBS1

段的类型：
undo_tablespace 默认情况下有一个system段
与10个not system段
当你的数据库存在offline的表空间时，system表空间会生成一个类型为defered rollback的段，该段online后自动消失

查看数据库存在的回滚段：

SQL> select * from v$rollname;

       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1_3780397527$
         2 _SYSSMU2_2232571081$
         3 _SYSSMU3_2097677531$
         4 _SYSSMU4_1152005954$
         5 _SYSSMU5_1527469038$
         6 _SYSSMU6_2443381498$
         7 _SYSSMU7_3286610060$
         8 _SYSSMU8_2012382730$
         9 _SYSSMU9_1424341975$
        10 _SYSSMU10_3550978943$

11 rows selected.

SYSTEM，系统回滚段，主要用来存放SYSTEM表空间生成的镜像数据
_SYSTEMUn，非系统回滚段，主要用来存在非SYSTEM表空间生成的镜像数据,至少要存在一个非系统回滚段

查看系统中数据文件的状态：

SQL> select status,enabled from v$datafile;

STATUS  ENABLED
------- ----------
SYSTEM  READ WRITE
ONLINE  READ WRITE
ONLINE  READ WRITE
ONLINE  READ WRITE
ONLINE  READ WRITE

查看系统中是否存在defered段(因无表空间脱机，所以不存在defered段)：

SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';

no rows selected

脱机一个表空间，再查询系统中的defered段：

SQL> alter tablespace users offline;

Tablespace altered.

SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
1.86208
DEFERRED ROLLBACK


SQL>

当把表空间online后，defered段消失：


SQL> alter tablespace users online;

Tablespace altered.

SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';

no rows selected

SQL>

undo_retention:

指定保留的临时数据的长短（默认单位：秒）

SQL> show parameter undo_retention

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
undo_retention               integer     900

4、undo表空间的建立、删除、更改

建立

a、创建数据库的时候指定undo表空间

create database –>
undo tablespace <>datafile <> size <> <>;

b、直接创建undo表空间

create smallfile | bigfile undo tablespace <> datafile <> size <><>;

SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/orcl/undo.dbf' size 10m autoextend on ;

Tablespace created.

SQL> create bigfile undo tablespace undo2 datafile '/u01/app/oracle/oradata/orcl/undo2.dbf' size 20m autoextend on;

Tablespace created.

SQL>

删除

drop tablespace <> including contents and datafiles;

SQL> drop tablespace undo1 including contents and datafiles;

Tablespace dropped.

正在使用的undo表空间无法被删除

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL>

要删除正在使用的undo表空间，需要先设置其他其他undo表空间为默认表空间后，再将其删除

SQL> alter system set undo_tablespace=undo2;

System altered.

SQL> drop tablespace undotbs1 ;

Tablespace dropped.

SQL>

更改

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' reuse;

Tablespace created.
SQL> alter system set undo_tablespace=undotbs1;

System altered.

SQL>

更改undo表空间的名字
show parameter查看参数，发现undo表空间还是显示旧的记录

SQL> alter tablespace undotbs1 rename to undotbs2;

Tablespace altered.

SQL> show parameter undo_t

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

查询dba_data_files中，表空间名已更改；

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
USERS
UNDOTBS2
SYSAUX
SYSTEM
EXAMPLE

重启发现，show parameter undo_tablespace,发现已更新为新记录

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             624953376 bytes
Database Buffers          205520896 bytes
Redo Buffers                2412544 bytes
Database mounted.
Database opened.
SQL> show parameter undo_t;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2
SQL>

再次测试

更改前，查询参数文件中undo_tablespace的值
查看spfile文件路径

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/oracle/produc
                                                 t/11.2.0/dbhome_1/dbs/spfileor
                                                 cl.ora
SQL>

[oracle@oracle11g dbs]$ strings spfileorcl.ora |grep undo_t
*.undo_tablespace='UNDOTBS2'
[oracle@oracle11g dbs]$

更改前undo_tablespace的值为UNDOTBS2;
更改为undotbs1;

SQL> alter tablespace undotbs2 rename to undotbs1;

Tablespace altered.

SQL>

查询参数文件

[oracle@oracle11g dbs]$ strings spfileorcl.ora |grep undo_t
*.undo_tablespace='UNDOTBS1'
[oracle@oracle11g dbs]$

参数文件中已修改为undotbs1;
故，当对默认的undo_tablespace进行改名操作时，改名的同时也会将记录写入至参数文件中，下次重启生效！

如何估算undo表空间的大小

应在一天中数据库负载最繁重的时候进行计算
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
这里暂不作详细说明

select (UR*(UPS*DBS))+(DBS*24) as "bytes" from 
(select value as UR from v$parameter where name='undo_retention'),
(select (sum(undoblks)/sum(((end_time-begin_time)*86400))) as ups from v$undostat),
(select value as DBS from v$parameter where name='db_block_size');

5、undo资料查询

v$rollstat

v$rollname 当前正在用的段

dba_Segments

dba_rollback_Segs 系统中所有的undo表空间段的分布

v$transaction

查询当前状态下用户及对应事务：

SQL> select s.sid,s.serial#,s.username,r.name,t.cr_get,t.phy_io,t.used_ublk,t.noundo,s.program from v$rollname r,v$transaction t ,v$session s where t.addr = s.taddr and t.xidusn = r.usn order by t.cr_get,phy_io;

no rows selected
SQL>

更新emp表

SQL> update scott.emp set sal=sal+1;

14 rows updated.

SQL>

再次查询当前状态下用户及对应事务：

SQL> /

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
NAME                               CR_GET     PHY_IO  USED_UBLK NOU
------------------------------ ---------- ---------- ---------- ---
PROGRAM
------------------------------------------------
        46       2198 SYS
_SYSSMU10_2206301615$                   7          0          1 NO
sqlplus@oracle11g (TNS V1-V3)


SQL>

查询undo表空间的段是否有占用现象：

SQL> select a.name,b.gets,b.waits,(b.waits/b.gets)*100
  2   ration from v$rollname a ,v$rollstat b where a.usn = b.usn;

NAME                                 GETS      WAITS     RATION
------------------------------ ---------- ---------- ----------
SYSTEM                               1779          0          0
_SYSSMU1_3107704755$                14160          2 .014124294
_SYSSMU2_2994798748$                14650          0          0
_SYSSMU3_4012527125$                16098          5 .031059759
_SYSSMU4_2704691265$                15311          2 .013062504
_SYSSMU5_2590987221$                15639          0          0
_SYSSMU6_3288197293$                15107          1 .006619448
_SYSSMU7_2980131053$                15359          1 .006510841
_SYSSMU8_2950236811$                14704          1 .006800871
_SYSSMU9_3689319874$                13775          1 .007259528
_SYSSMU10_2206301615$               14229          0          0

11 rows selected.

SQL>

当ration大于2的时候，表明存在占用现象（你的undo表空间需要扩容），仅针对手动管理模式，自动管理模式下无需理会；

6、undo表空间的应用

事务开始—>是在一个会话下产生的
事务结束—>是在当前会话下碰到了自动或手动结束事务的操作（commit,DDL）就结束你的事务了。

事务回退

rollback
在进行DML语句，可以使用rollback命令进行事务的回退，当进行commit或者执行DDL语句时，无法进行回退

demo:
执行DML操作并设置保存点

SQL> update scott.emp set sal=sal+1;

14 rows updated.

SQL> savepoint s1;

Savepoint created.

SQL> delete scott.emp where deptno = 10;

3 rows deleted.

SQL> savepoint s2;

Savepoint created.

SQL> delete scott.emp where deptno = 20;

5 rows deleted.

SQL> savepoint s3;

Savepoint created.

SQL> delete scott.emp where deptno = 30;

6 rows deleted.

SQL>

查询当前emp表的数据：

SQL> select * from scott.emp;

no rows selected

SQL>

使用rollback命令回退到S3保存点，并查询emp表数据；

SQL> rollback to s3;

Rollback complete.
SQL> select distinct deptno from scott.emp;

    DEPTNO
----------
        30

同理回退到S2，S1,并查询emp表数据；

SQL> rollback to s2;

Rollback complete.
SQL> select distinct deptno from scott.emp;

    DEPTNO
----------
        30
        20


SQL> rollback to s1;

Rollback complete.

SQL> select distinct deptno from scott.emp;

    DEPTNO
----------
        30
        20
        10

SQL>

查询emp表中现阶段员工的薪资情况：

SQL> select sal from scott.emp;

       SAL
----------
       801
      1601
      1251
      2976
      1251
      2851
      2451
      3001
      5001
      1501
      1101

       SAL
----------
       951
      3001
      1301

14 rows selected.

SQL>

直接使用rollback，会回退至事务开始前

SQL> rollback
  2  ;

Rollback complete.

SQL> select sal from scott.emp;

       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100

       SAL
----------
       950
      3000
      1300

14 rows selected.

SQL>

注：rollback不可逆，当你回退到S1保存点后，无法再回退到S2，S3保存点；

事务提交

a、人为的commit，适用于DML语句
b、DDL语句，自动结束一个事务
当你进行insert update delete —->
无进行commit语句，之后进行DDL语句，也会提交，结束事务

闪回数据

演示：
删除emp表的所有数据,并提交：

SQL> delete scott.emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL>

查询当前emp表的数据，与5分钟前emp表的数据:

SQL> select * from scott.emp;

no rows selected

SQL> select * from scott.emp as of timestamp sysdate - 5/1440;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

使用查询到的数据恢复emp表,并查看当前emp表的数据：

SQL> insert into scott.emp select * from scott.emp as of timestamp sysdate-5/1440;

14 rows created.

SQL> commit;

Commit complete.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

SQL>

undo表空间的备份

a、热备
b、RMAN备
参考system表空间的备份管理部分
http://blog.csdn.net/gavin1731/article/details/70592891

undo表空间损坏的处理

a、有备份的直接使用备份恢复

参考system表空间的恢复部分
http://blog.csdn.net/gavin1731/article/details/70592891

b、无备份恢复思路

1、设置参数undo_management = manual
undo_tablespace = 其他的可用undo
“_offline_rollback_segments”=true

2、启动至mount
3、将损坏undo数据文件脱机
4、alter database open;
5、看dba_rollback_segs 中的status —损坏的表空间的段是否有recover字样，如果没有，则可以删除损坏的undo，如果有则使用_corrupted_rollback_segments隐藏参数

6、删除损坏的undo表空间，建立刚删除的undo表空间
7、还原1步骤中修改的所有参数undo_management=auto,undo_tablespace=原undo表空间,取消隐藏参数设置
8、再次启动数据库
undo表空间管理注意：要备份，要有备用的undo表空间（恢复时可进行表空间切换）

再次强调，备份很重要，备份很重要，备份很重要

demo:
启动数据库报undo表空间文件丢失

SQL> startup;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             683673632 bytes
Database Buffers          146800640 bytes
Redo Buffers                2412544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'


SQL> alter system set undo_tablespace=undotbs scope=spfile;

System altered.

SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> alter system set "_offline_rollback_segments" =true scope=spfile;

System altered.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU10_2206301615$          OFFLINE
_SYSSMU9_3689319874$           OFFLINE
_SYSSMU8_2950236811$           OFFLINE
_SYSSMU7_2980131053$           OFFLINE
_SYSSMU6_3288197293$           OFFLINE
_SYSSMU5_2590987221$           OFFLINE
_SYSSMU4_2704691265$           OFFLINE
_SYSSMU3_4012527125$           OFFLINE
_SYSSMU2_2994798748$           OFFLINE
_SYSSMU1_3107704755$           OFFLINE

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU20_1255684503$          OFFLINE
_SYSSMU19_696553002$           OFFLINE
_SYSSMU18_2602531068$          OFFLINE
_SYSSMU17_3371774879$          OFFLINE
_SYSSMU16_2665224403$          OFFLINE
_SYSSMU15_77113936$            OFFLINE
_SYSSMU14_631266162$           OFFLINE
_SYSSMU13_1664273118$          OFFLINE
_SYSSMU12_4181843025$          OFFLINE
_SYSSMU11_3685496409$          OFFLINE

21 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             683673632 bytes
Database Buffers          146800640 bytes
Redo Buffers                2412544 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1;

Tablespace dropped.

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

Tablespace created.

SQL> alter system set undo_tablespace=undotbs1 scope=spfile;

System altered.


SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> show parameter offline

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_offline_rollback_segments           string      TRUE
SQL> alter system reset "_offline_rollback_segments" scope=spfile sid='*';

System altered.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> startup;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             683673632 bytes
Database Buffers          146800640 bytes
Redo Buffers                2412544 bytes
Database mounted.
Database opened.
SQL> show parameter undo_

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