Oracle DBA课程系列笔记（11）

原创

客居天涯 2012-02-28 15:05:39 博主文章分类：数据库技术 ©著作权

文章标签 oracle 职场休闲 Oracle study 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，请联系作者获取转载授权，否则将追究法律责任

第十一章：管理UNDO

   1、undo tablespace 功能？
        存放从datafiles 读出的数据块旧的镜像

       1）回滚事务：rollback
       2）读一致性：正在做DML操作的数据块，在没有提交前，其他用户不能读，其他用户读undo里面的数据块信息
       3）事务的恢复：instance recover(undo -------->rollback)
       4）倒叙查询：flashback query、flashback table
   2、undo 的管理模式：
       1）manaual 手工：roll segment
       2）auto 自动：undo tablespace （init parameter ：undo_management = auto)


   3、undo 表空间管理
     1）建立新的undo表空间（处于active状态的undo tablespace 不能offline 和 drop）,可以建立多个undo表空间，但一个时刻只有一个处于active
     09:47:08 SQL> create undo tablespace
09:47:17   2    undo_tbs datafile '/u01/app/oracle/oradata/prod/undo_tbs01.dbf' size 100m
09:47:20   3    autoextend on next 10m maxsize 500m;
09:47:55 SQL> select tablespace_name,status ,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
TEST                           ONLINE    PERMANENT
UNDO_TBS                       ONLINE    UNDO

8 rows selected.
2）查看当前正在使用的undo tablespace
09:48:00 SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
09:48:56 SQL>
   3）切换undo
09:50:10 SQL> alter system set undo_tablespace=undo_tbs;

System altered.

09:50:28 SQL> show parameter undo

4）删除undo tablespace
09:51:36 SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

    5） undo_retention 参数

    19:25:14 SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      RTBS

SQL>Alter tablespace undotbs1 retention guarantee;

如果想禁止undo表空间retention guarantee，
例如：
SQL> Alter tablespace undotbs1 retention noguarantee;

       当事务提交后，undo 数据块在undo tablespace 空间未使用完之前的的保留时间（不被覆盖）
   SQL> Alter tablespace undotbs1 retention guarantee;（保证在retention 期间不允许被覆盖）
    6）undo 表空间的大小

         1） undo_retention
         2) undo block /per second
         3) UNDO BLOCK SIZE

4、undo 信息的查询

       1） v$session 查看用户建立的session
       2） v$transaction 当前的事务
       3） v$rollname 回滚段的名称
       4） v$rollstat 回滚段的状态

       10:03:28 SQL> select username ,sid,serial# from v$session
10:03:39   2    where username is not null;

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SCOTT                                 144          7
SYS                                   147         15
SCOTT                                 159         15

10:07:59 SQL> r
1 select a.sid,a.serial#,a.username,b.xidusn,xidslot,b.ubablk,b.status,b.name from v$session a,v$transaction b
2* where a.saddr=b.ses_addr

10:07:59 SQL> col name for a10
10:08:05 SQL> /

SID SERIAL# USERNAME XIDUSN XIDSLOT UBABLK STATUS NAME
---------- ---------- -------------------- ---------- ---------- ---------- ---------------- ----------
144 7 SCOTT 15 26 76 ACTIVE

------------xidusn ：undo segment 的id ，xidslot 事务槽的id

10:09:14 SQL> select * from v$rollname;

       USN NAME
---------- ----------
         0 SYSTEM
        11 _SYSSMU11$
        12 _SYSSMU12$
        13 _SYSSMU13$
        14 _SYSSMU14$
        15 _SYSSMU15$
        16 _SYSSMU16$
        17 _SYSSMU17$
        18 _SYSSMU18$
        19 _SYSSMU19$
        20 _SYSSMU20$

11 rows selected.

----默认system有一个undo segment，undo tablespace 会被分配10个undo segment

10:11:13 SQL> select usn,EXTENTS,WRITES ,XACTS ,STATUS from v$rollstat;

       USN    EXTENTS     WRITES      XACTS STATUS
---------- ---------- ---------- ---------- ---------------
         0          6      27264          0 ONLINE
        11          2      35170          0 ONLINE
        12          2      27172          0 ONLINE
        13          2      42362          0 ONLINE
        14          2      30654          0 ONLINE
        15          2      13568          1 ONLINE
        16         21    5753978          0 ONLINE
        17          2      34292          0 ONLINE
        18          4     119852          1 ONLINE
        19          2      42584          0 ONLINE
        20          2      32172          0 ONLINE

11 rows selected.

10:15:32 SQL> col usn for a10
10:15:48 SQL> r
1 select a.sid,a.serial#,a.username,b.xidusn,xidslot,b.ubablk,b.status,c.usn,c.name,d.extents,d.writes,d.xacts
2    from v$session a,v$transaction b,v$rollname c ,v$rollstat d
3*     where a.saddr=b.ses_addr and b.xidusn=c.usn and c.usn=d.usn；



10:15:54 SQL> /

       SID    SERIAL# USERNAME       XIDUSN    XIDSLOT     UBABLK STATUS            USN NAME          EXTENTS     WRITES
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     XACTS
----------
       144          7 SCOTT              15         26         76 ACTIVE             15 _SYSSMU15$          2      13568
         1

143 41 SYS 18 34 171 ACTIVE 18 _SYSSMU18$ 4 119852
1

查看undo segment 数据块状态（dba_undo_EXTENTS)

10:03:10 SQL> insert into t01 values (6) ;

1 row created.

10:05:09 SQL> insert into t01 values (7) ;

1 row created.

10:05:10 SQL

10:12:32 SQL> create table emp1 as select * from scott.emp;

Table created.

10:12:52 SQL> insert into emp1 select * from emp1;

14 rows created.

10:18:10 SQL> select segment_name,tablespace_name,extent_id,file_id ,bytes/1024 ,status from dba_undo_extents
10:18:45 2 where status like '%ACTIVE%';

SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID BYTES/1024 STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
_SYSSMU18$                     UNDO_TBS                                2          7         64 ACTIVE
_SYSSMU15$                     UNDO_TBS                                0          7         64 ACTIVE

10:12:59 SQL> COMMIT;

Commit complete.

10:19:01 SQL> /

10:20:01 SQL>
查看undo tablespace 统计信息

10:23:20 SQL> select BEGIN_TIME,end_time,undotsn,undoblks,ACTIVEBLKS,EXPIREDBLKS,MAXQUERYID from v$undostat;

BEGIN_TIME          END_TIME               UNDOTSN   UNDOBLKS ACTIVEBLKS EXPIREDBLKS MAXQUERYID
------------------- ------------------- ---------- ---------- ---------- ----------- -------------
2011-08-08 10:22:35 2011-08-08 10:23:20          8          0        160         760
2011-08-08 10:12:35 2011-08-08 10:22:35          8          6        160         760
2011-08-08 10:02:35 2011-08-08 10:12:35          8        589        160           8
2011-08-08 09:52:35 2011-08-08 10:02:35          8        179        160           8
2011-08-08 09:42:35 2011-08-08 09:52:35          8         19        160           0
2011-08-08 09:32:35 2011-08-08 09:42:35          1          7        160        2840
2011-08-08 09:22:35 2011-08-08 09:32:35          1          2        160        2848
2011-08-08 09:12:35 2011-08-08 09:22:35          1          0          0           0
2011-08-08 09:02:35 2011-08-08 09:12:35          1          3          0           0

9 rows selected.

04:27:39 SQL> select segment_name,tablespace_name ,segment_id,file_id ,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                SEGMENT_ID    FILE_ID STATUS
------------------------------ ------------------------------ ---------- ---------- ----------------
SYSTEM                         SYSTEM                                  0          1 ONLINE
_SYSSMU1$                      RTBS                                    1          2 ONLINE
_SYSSMU2$                      RTBS                                    2          2 ONLINE
_SYSSMU3$                      RTBS                                    3          2 ONLINE
_SYSSMU4$                      RTBS                                    4          2 ONLINE
_SYSSMU5$                      RTBS                                    5          2 ONLINE
_SYSSMU6$                      RTBS                                    6          2 ONLINE
_SYSSMU7$                      RTBS                                    7          2 ONLINE
_SYSSMU8$                      RTBS                                    8          2 ONLINE
_SYSSMU9$                      RTBS                                    9          2 ONLINE
_SYSSMU10$                     RTBS                                   10          2 ONLINE

11 rows selected.