oracle 之 Undo Retention

oracle 之 Undo Retention

    oralce database 根据undo表空间大小和系统活动状态自动调整保留时间，可以调整undo_retention 参数设置保留时间。但是准确的将分为如下两种情况。
1）在国定大小的undo表空间中undo_retention参数被忽略，oracle会根据系统活动和空间大小自动调整合适的保留时间来满足应用需求。如果决定把undo 表空间设置为固定大小，那么一定要保证该空间具有足够的size。该undo_retention设置的参数被忽略，如果 选择的空间很小那么会出现如下情况
1、dml会失败，因为没有足够的空间去分配新的事务。
2、运行很长的query可能会出现snapshot too old 错误 ，这是由于不能满足一致性读的问题 （常见为 ORA-01555)

eg：

1)创建一个很小的不是自动扩展的表空间

SQL> create undo tablespace undotbs3 datafile '/opt/app/oracle/RHYS/undotbs3.dbf' size 50M autoextend off;

Tablespace created.

SQL> select file_name,tablespace_name,autoextensible from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME                AUT
-------------------------------------------------- ------------------------------ ---
/opt/app/oracle/RHYS/system01.dbf                  SYSTEM                         YES
/opt/app/oracle/RHYS/sysaux01.dbf                  SYSAUX                         YES
/opt/app/oracle/RHYS/undotbs01.dbf                 UNDOTBS1                       YES
/opt/app/oracle/RHYS/users01.dbf                   USERS                          YES
/opt/app/oracle/RHYS/test.dbf                      TEST                           YES
/opt/app/oracle/RHYS/arch_tbs01.dbf                ARCH_TBS                       NO
/opt/app/oracle/RHYS/statspack.dbf                 STATSPACK                      NO
/opt/app/oracle/RHYS/undotbs2.dbf                  UNDOTBS2                       YES
/opt/app/oracle/RHYS/undotbs21.dbf                 UNDOTBS2                       NO
/opt/app/oracle/RHYS/undotbs3.dbf                  UNDOTBS3                       NO

10 rows selected.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change          string      11.2.0.1
undo_management                      string      AUTO
undo_retention                       integer     60
undo_tablespace                      string      UNDOTBS2
SQL> alter system set undo_tablespace=undotbs3;

System altered.

SQL> select a.name,a.usn,b.status from v$rollstat b,v$rollname a where a.usn=b.usn;

NAME                                  USN STATUS
------------------------------ ---------- ---------------
SYSTEM                                  0 ONLINE
_SYSSMU21_285059280$                   21 ONLINE
_SYSSMU22_1963502844$                  22 ONLINE
_SYSSMU23_2778616806$                  23 ONLINE
_SYSSMU24_564050484$                   24 ONLINE
_SYSSMU25_800148274$                   25 ONLINE
_SYSSMU26_1460554600$                  26 ONLINE
_SYSSMU27_931264876$                   27 ONLINE
_SYSSMU28_1920348465$                  28 ONLINE
_SYSSMU29_1181389426$                  29 ONLINE
_SYSSMU30_2665996069$                  30 ONLINE

11 rows selected.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change          string      11.2.0.1
undo_management                      string      AUTO
undo_retention                       integer     60
undo_tablespace                      string      UNDOTBS3
SQL>

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change          string      11.2.0.1
undo_management                      string      AUTO
undo_retention                       integer     60
undo_tablespace                      string      UNDOTBS3
SQL> select begin_time,end_time,undotsn,undoblks,maxquerylen,maxqueryid from v$undostat;

BEGIN_TIM END_TIME     UNDOTSN   UNDOBLKS MAXQUERYLEN MAXQUERYID
--------- --------- ---------- ---------- ----------- -------------
21-AUG-13 21-AUG-13          9         18         336 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         41         940 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8          6         338 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         60         939 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         12         337 0rc4km05kgzb9

SQL> r                       
  1* select begin_time,end_time,undotsn,undoblks,maxquerylen,maxqueryid from v$undostat

BEGIN_TIM END_TIME     UNDOTSN   UNDOBLKS MAXQUERYLEN MAXQUERYID
--------- --------- ---------- ---------- ----------- -------------
21-AUG-13 21-AUG-13          9          0         336 0rc4km05kgzb9
21-AUG-13 21-AUG-13          9         18         336 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         41         940 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8          6         338 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         60         939 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         12         337 0rc4km05kgzb9

6 rows selected.

2）看到系统自动调整的undo retention为940s

SQL> delete from t;

73083 rows deleted.

SQL> begin              
  2  for i in 1..100 loop
  3  insert into t select * from obj$;
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> r
  1  begin
  2  for i in 1..100 loop
  3  insert into t select * from obj$;
  4  commit;
  5  end loop;
  6* end;

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
  14624600

SQL> select begin_time,end_time,undotsn,undoblks,maxquerylen,maxqueryid from v$undostat;

BEGIN_TIM END_TIME     UNDOTSN   UNDOBLKS MAXQUERYLEN MAXQUERYID
--------- --------- ---------- ---------- ----------- -------------
21-AUG-13 21-AUG-13          9       8554         637 0rc4km05kgzb9
21-AUG-13 21-AUG-13          9         18         336 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         41         940 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8          6         338 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         60         939 0rc4km05kgzb9
21-AUG-13 21-AUG-13          8         12         337 0rc4km05kgzb9

6 rows selected.

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     60
SQL> select file_name,tablespace_name,bytes/1024/1024||'M' MB,autoextensible from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME                MB                                        AUT
-------------------------------------------------- ------------------------------ ----------------------------------------- ---
/opt/app/oracle/RHYS/system01.dbf                  SYSTEM                         990M                                      YES
/opt/app/oracle/RHYS/sysaux01.dbf                  SYSAUX                         600M                                      YES
/opt/app/oracle/RHYS/undotbs01.dbf                 UNDOTBS1                       325M                                      YES
/opt/app/oracle/RHYS/users01.dbf                   USERS                          5M                                        YES
/opt/app/oracle/RHYS/test.dbf                      TEST                           1480M                                     YES
/opt/app/oracle/RHYS/arch_tbs01.dbf                ARCH_TBS                       500M                                      NO
/opt/app/oracle/RHYS/statspack.dbf                 STATSPACK                      200M                                      NO
/opt/app/oracle/RHYS/undotbs2.dbf                  UNDOTBS2                       376.9375M                                 YES
/opt/app/oracle/RHYS/undotbs21.dbf                 UNDOTBS2                       50M                                       NO
/opt/app/oracle/RHYS/undotbs3.dbf                  UNDOTBS3                       50M                                       NO

10 rows selected.

3）删除一个大表，产生数据前镜像需要使用undo

SQL> delete from t;
delete from t
            *
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS3'

可以看到已经无法分配空间了，导致了dml错误。

注意：自动调整保留时间对于lobs不适应，lobs依然使用undo_retention进行保留。
2)当undo tablespace为 自动扩展的话，那么可以 通过 设置undo_retention参数进行保留一定的undo record单位为妙，如果没有达到该值，那么会自动扩展表空间，当达到maxsize那么就会overwrite record。但是可以使用guarantee参数，强制不覆盖undo记录。但是这是 会出现error提示。另外oracle自动调整保留策略是max((undo_retention),(maxquery+300)）。

oracle该自动调整retention 受一个隐含参数控制，当该隐含参数为false，那么该自动调整功能失效，受undo_retention参数影响。

eg：

•  

  SQL> get getpar.sql
    1* select a.ksppinm,b.ksppstvl,a.ksppdesc from x$ksppcv b,x$ksppi a where a.indx=b.indx and a.ksppinm like '%&var%'
  SQL> set linesize 200
  SQL> set verify off
  SQL> r
    1* select a.ksppinm,b.ksppstvl,a.ksppdesc from x$ksppcv b,x$ksppi a where a.indx=b.indx and a.ksppinm like '%&var%'
  Enter value for var: undo_autotune

  KSPPINM                        KSPPSTVL                                 KSPPDESC
  ------------------------------ ---------------------------------------- ----------------------------------------
  _undo_autotune                 TRUE                                     enable auto tuning of undo_retention

  SQL>

• Reference：

• The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size. See "Automatic Tuning of Undo Retention" for more information.

• For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. The UNDOTBS1 tablespace that is automatically created by DBCA is auto-extending.

•  

  Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.

   

  If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. However, this retention period may be insufficient to accommodate Oracle Flashback operations. Oracle Flashback operations resulting in snapshot too old errors are the indicator that you must intervene to ensure that sufficient undo data is retained to support these operations. To better accommodate Oracle Flashback features, you can either set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle Flashback operation, or you can change the undo tablespace to fixed size.

• If the undo tablespace is fixed size, the database dynamically tunes the undo retention period for the best possible retention for that tablespace size and the current system load. This best possible retention time is typically significantly greater than the duration of the longest-running active query.

  If you decide to change the undo tablespace to fixed-size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, the following two errors could occur:

  • DML could fail because there is not enough space to accommodate undo for new transactions.

  • Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.

• note：

• Automatic tuning of undo retention is not supported for LOBs. This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace. For LOBs, the database attempts to honor the minimum undo retention period specified by UNDO_RETENTION. However, if space becomes low, unexpired LOB undo information may be overwritten.