如何管理undo

如何管理undo
一、监控undo
SQL> select file_name from dba_data_files;


FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/huangchao/users01.dbf
/u01/app/oracle/oradata/huangchao/sysaux01.dbf
/u01/app/oracle/oradata/huangchao/undotbs01.dbf
/u01/app/oracle/oradata/huangchao/system01.dbf
/u01/app/oracle/oradata/huangchao/example01.dbf
/u01/app/oracle/oradata/huangchao/test01.dbf
/u01/app/oracle/oradata/huangchao/demo01.dbf
/u01/app/oracle/oradata/huangchao/daodao.dbf
SQL> create undo tablespace undo datafile '/u01/app/oracle/oradata/huangchao/daodao_undo.dbf'  size 1m ;


Tablespace created.
SQL> alter system set undo_tablespace=undo;
SQL> create table DBA(id number);
SQL> begin
  2      for i in 1 .. 100000 loop
  3      insert into dba values(i);
  5      end loop;
  6      end;
  7  /
一次undo表空间报警的处理及相关分享：
1.undo各个状态占用状态
select tablespace_name, status, sum(bytes) / 1024 / 1024 / 1024 as space_GB
  2    from dba_undo_extents
  3   group by tablespace_name, status
  4   order by 1;
TABLESPACE_NAME                STATUS      SPACE_GB
------------------------------ --------- ----------
UNDOTBS1                       ACTIVE    0.12207031
UNDOTBS1                       EXPIRED   63.8407592
UNDOTBS1                       UNEXPIRED 93.5275268
UNDOTBS2                       EXPIRED   7.89965820


ACTIVE:
 表示使用这个undo block的 transaction还未提交，处于活动状态。
EXPIRED:
 当undo datafile无法扩展时，smon会把这部分(超出undo retention时间外的)空间可回收，给新的会话使用，作预分配使用。
UNEXPIRED:
 保留undo retention时间内的空间。
这几个优先级别如下：
active > unexpired > expired


undo的自动扩展顺序：
1.先重用undo空闲(free状态)的空间，，如没有或不够，下一步。
2.扩展undo 数据文件中free状态空间(dba_free_space.bytes),如果不够时。
datafile 有自动扩展特性，则自动扩展，但这个受限于是不是raw(固定大小，一般是autoextend off)及asm(或文件系统，64位中单个数据文件最大为32g)的大小。
smon释放undo空间中的expired空间使用。
此时还不够的话，则使用unexpired的空间。则原来放进unexpired的前数据有可能被踢出，造成快照过旧。


select tablespace_name, status, sum(bytes) / 1024 / 1024 / 1024 as space_GB
  2    from dba_undo_extents
  3   group by tablespace_name, status
  4   order by 1;
TABLESPACE_NAME                STATUS      SPACE_GB
------------------------------ --------- ----------
UNDOTBS1                       ACTIVE    0.12207031
UNDOTBS1                       EXPIRED   63.8407592
UNDOTBS1                       UNEXPIRED 93.5275268
UNDOTBS2                       EXPIRED   7.89965820
放进保留时间内有93.52G，在保留时间外有63.84G。此时，如果没有datafile空闲空间扩展使用，oracle后台进程会自动释放expired空间供使用。


检查占用undo的sql及相关信息：


create or replace view v$sqlarea_total as
select sql_id,sql_text from gv$sqlarea
 union all  select sql_id,to_char(SUBSTR(SQL_TEXT,1,4000)) from Dba_Hist_Sqltext;


CREATE OR REPLACE FUNCTION fn_getsqlbysqlid(vSql_ID varchar2) return varchar is
  Result varchar2(4000);
begin
  dbms_output.enable(1000000);
  begin
    for x in (Select substr(sql_text, 1, 4000) sql_text
                from V$sqlarea_Total t --v$sqlarea t
               where t.sql_id = vSql_ID
                 and rownum = 1) loop
      Result := Result || x.sql_text;
    End loop;
  End;
  return(substr(Result, 1, 4000));
end get_sqltext;




select begin_time,
       end_time,
       fn_getsqlbysqlid(maxqueryid),
       maxqueryid,
       t.TUNED_UNDORETENTION,
       trunc(end_time, 'mi') - trunc(begin_time, 'mi') as exec_time_min
       /*t.**/
  from v$undostat t
 order by exec_time_min desc;


上述用到的v$undostat的说明：
每一条记录分别以每10分钟一个区间作数据采集，24小时循环。
begin_time / end_time：以10分钟为间隔的结束时间
undoblks：使用的undo块总数
maxqueryid : 等于sql_id
tuned_undoretention： oracle建议的配置undo retention


kill process
select pro.spid from v$session ses,v$process pro where ses.paddr=pro.addr and ses.sid=&sid;
到操作系统层面看看进程号，并适当的进行kill掉
[oracle@oss139db2 ~]$ ps -ef |grep 26193
oracle   21132 20083  0 21:46 pts/0    00:00:00 grep --color 26193
oracle   26193     1  0 Jun06 ?        00:29:25 ora_dbw4_oss1392
[oracle@oss139db2 ~]$ 


处理后检查空间及占用情况 
--各状态空间的变化
SQL> select tablespace_name, status, sum(bytes) / 1024 / 1024 / 1024 as space_GB
  2    from dba_undo_extents
  3   group by tablespace_name, status
  4   order by 1;
TABLESPACE_NAME                STATUS      SPACE_GB
------------------------------ --------- ----------
UNDOTBS1                       ACTIVE        0.0625
UNDOTBS1                       EXPIRED    108.84375
UNDOTBS1                       UNEXPIRED 45.7003173
UNDOTBS2                       EXPIRED   7.89965820
放进保留时间内由93.52g下降至45g，在保留时间外的有63.84g上升至108.84g
SQL> select tablespace_name, status, sum(bytes) / 1024 / 1024 / 1024 as space_GB
  2    from dba_undo_extents
  3   group by tablespace_name, status
  4   order by 1;


TABLESPACE_NAME                STATUS      SPACE_GB
------------------------------ --------- ----------
UNDOTBS1                       ACTIVE    0.11523437
UNDOTBS1                       EXPIRED   74.7815551
UNDOTBS1                       UNEXPIRED 45.6531372
UNDOTBS2                       EXPIRED   7.89965820
非保留时间由108.84g下降为74.78g，其他的空间释放为datafile的空闲空间了。


undo会自己释放或扩展：
总结：
1.不只是dml才会占用undo表空间，查询也占用undo表空间
2.undo用尽时，才会报快照过旧错误。




切换undo表空间
1、建立新的表空间UNDOTBS2
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'F:/backup/undo03.dbf' size 100M reuse;
表空间已创建。
2、切换到新建的UNOD表空间上来,操作如下
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
系统已更改。
3、将原来的UNDO表空间,置为脱机:
SQL> alter tablespace UNDO offline;
表空间已更改。
4、删除原来的UNDO表空间:
SQL> drop tablespace UNDO including contents AND DATAFILES CASCADE CONSTRAINTS ;
表空间已删除。
如果只是drop tablespace UNDO ，则只会在删除控制文件里的记录，并不会物理删除文件。 
Drop undo表空间的时候必须是在未使用的情况下才能进行。如果undo表空间正在使用（例如事务失败，但是还没有恢复成功），那么drop表空间命令将失败。