Oracle UNDO表空间

1. 作用

1.1 事物回滚

1.2 数据库恢复

    Oracle Server 自动完成

1.3 读一致性

    对更新前的数据回避 /备份，查询时显示回避信息

1.4 闪回查询

    查询过去某时刻状态

※ 事物提交后，回避信息不可回滚，不可进行数据恢复

2. UNDO参数

通过【show parameter undo】可以查看UNDO 的各参数值

show parameter undo								

\NAME                      TYPE          VALUE
temp_undo_enabled         boolean       FALSE
undo_management           string        AUTO
undo_retention   　　      integer        900
undo_tablespace      　    string        UNDOTBS1

2.1 temp_undo_enabled（临时undo）

由于信息不会写入undo日志，undo表空间的开销得以减少并且产生的undo数据会更少。

修改方法：

alter system set temp_undo_enabled=false;

2.2 undo_management

auto：自动重做管理
    Oracle 服务器自动管理重做段的创建、分配、和调整
manaul：手工管理重做
    手工管理重做段的创建、分配、调整。这是Oracle9I之前的唯一方法

Automatic Tuning of undo retention

※ 更推荐设置undo空间的自动扩展 + 限制文件最大大小的方式来解决。

2.3 undo_retention

事物提交后，数据保存期间，默认是900s

修改方法：

alter system set undo_retention= 设定值 scope=spfile;

关闭undo retention优化建议：

show parameter undo

alter system set "_undo_autotune" = true; 

undo数据相关统计信息：

动态性能表
V$UNDOSTAT //10分钟一更新

其他
V$TEMPUNDOSTAT
DBA_HIST_UNDOSTAT

GUARANTEE：強制设定数据超过保存期間才可覆盖

create undo tablespace undotbs3 datafile size 10M autoextend off retention guarantee;
Tablespace created

select tablespace_name, contents, retention from dba_tablespaces where contents='UNDO';
// alert tablespace undotbs1 retention guarantee;
UNDOTBS1                       UNDO      NOGUARANTEE
UNDOTBS2                       UNDO      NOGUARANTEE
UNDOTBS3                       UNDO      GUARANTEE

UNDO UNDOTBS1 NOGUARANTEE //テスト環境：EE（ISIDG64144）

NOGUARANTEE：UNDO空间所剩无几时，未过期的数据也会被覆盖；
GUARANTEE：不考虑UNDO剩余空间大小，只有在数据过期的情况下才可以覆盖。

2.4 undo_tablespace

autoextend on
select max(maxquerylen) From v$undostat 

3. 状态

3.1 Free：未被分配

3.2 Active：被分配，且被事物使用，但未提交

3.3 Unexpired：事物已提交，未过期（Undo_retention 判断），不可覆盖

3.4 Expired：已提交，已过期，可覆盖。（何时转化为Free 不详）

4. 缩小UNDO 文件大小方法

方针：删除旧UNDO 文件，创建新UNDO 文件

4.1 UNDO 文件状况确认

SELECT TABLESPACE_NAME, FILE_NAME,BYTES/1024/1024 MBYTES 
FROM DBA_DATA_FILES 
WHERE 
	TABLESPACE_NAME IN ( SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='UNDO');

4.2 表空间确认

SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME='undo_tablespace';

4.3 创建表空间

create undo tablespace UNDOTBS2 datafile 'D:\Oracle\oradata\chengyu0726\UNDOTBS02.DBF' size 1024M AUTOEXTEND ON;

4.4 表空间切换

alter system set undo_tablespace = 'UNDOTBS2';

4.5 删除表空间

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

注意：
未过期也可删除，Active 状态不可删除

5. 表空间状态监视工具

5.1 Tablespace_check.sql 文件

set echo off
set pagesize 60
set linesize 120

col database_name noprint new_value db_name 
spool &1 app

select name database_name from v$database;

SELECT 
to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') systime,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL((select sum(bytes) bytes from dba_free_space where tablespace_name = 'UNDOTBS1' group by tablespace_name), 0), 0)/1024/1024 used,
NVL((select sum(bytes) bytes from dba_free_space where tablespace_name = 'UNDOTBS1' group by tablespace_name) / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL((select sum(bytes) bytes from dba_free_space where tablespace_name = 'UNDOTBS1' group by tablespace_name), 0)) / ddf.bytes * 100, 0), '990.00') "Used %",
(select sum(bytes/1024/1024) from dba_undo_extents where tablespace_name='UNDOTBS1' and status = 'ACTIVE' group by status) ACTIVE,
(select sum(bytes/1024/1024) from dba_undo_extents where tablespace_name='UNDOTBS1' and status = 'UNEXPIRED' group by status) UNEXPIRED,
(select sum(bytes/1024/1024) from dba_undo_extents where tablespace_name='UNDOTBS1' and status = 'EXPIRED' group by status) EXPIRED
FROM 
sys.dba_tablespaces dts, 
(select tablespace_name, sum(bytes) bytes 
from dba_data_files group by tablespace_name) ddf
WHERE 
dts.tablespace_name = ddf.tablespace_name 
AND dts.tablespace_name = 'UNDOTBS1';

spool off;
set feedback on
set verify on
clear breaks
clear computes
clear columns
set linesize 100
set pagesize 24 
set echo on

exit

5.2 Tablespace_check.bat 文件

@echo off

REM ---------+---------+---------+---------+---------+---------+
REM Oracle 表空间使用状况查看
REM ---------+---------+---------+---------+---------+---------+

REM ---------+---------+---------+---------+---------+---------+
REM 进入Script 目录
REM ---------+---------+---------+---------+---------+---------+
cd /d %~dp0

REM ---------+---------+---------+---------+---------+---------+
REM 日期设定
REM ---------+---------+---------+---------+---------+---------+
set NOWDATE=%DATE:~-10,4%%DATE:~-5,2%%DATE:~-2%
set NOWTIME=%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set LOGNAME=%NOWDATE%%NOWTIME%_table_space.log

REM ---------+---------+---------+---------+---------+---------+
REM SQL 获取数据（chengyu0726）
REM ---------+---------+---------+---------+---------+---------+

:start
ping -n 10 127.1>NUL
sqlplus chengyu0726/chengyu0726@chengyu0726 @Tablespace_check.sql %LOGNAME%
goto start