如何管理undo

来源：互联网发布：saas软件销售模式编辑：程序博客网时间：2024/04/30 00:26

如何管理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表空间命令将失败。

0 0