UNDO表空间监控说明

在Oracle 10g版本中可以使用V$UNDOSTAT视图用于监控实例中当前事务使用UNDO表空间的情况。视图中的每行列出了每隔十分钟从实例中收集到的统计信息。每行都表示了在过去7*24小时里每隔十分钟UNDO表空间的使用情况,事务量和查询长度等信息的统计快照。

UNDO表空间的使用情况会因事务量变化而变化,一般我们在计算时同时参考UNDO表空间的平均使用情况和峰值使用情况。

以下SQL语句用于计算过去7*24小时中UNDO表空间的平均使用量:

select ur undo_retention,

dbs db_block_size,

((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as “M_bytes”

from (select value as ur from v$parameter where name = ‘undo_retention’),

(select (sum(undoblks) / sum(((end_time – begin_time) * 86400))) ups

from v$undostat),

(select value as dbs from v$parameter where name = ‘db_block_size’)

以下SQL语句则按峰值情况计算UNDO表空间所需空间:

select ur undo_retention,

dbs db_block_size,

((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as “M_bytes”

from (select value as ur from v$parameter where name = ‘undo_retention’),

(select (undoblks / ((end_time – begin_time) * 86400)) ups

from v$undostat

where undoblks in (select max(undoblks) from v$undostat)),

(select value as dbs from v$parameter where name = ‘db_block_size’)

需要注意因RAC情况下一般存在2个UNDO表空间,视乎实际情况分别在2个实例中执行以上查询。

一般来说为了尽可能维护日常业务的正常运行,我们建议按照峰值情况估算和分配UNDO表空间的大小,虽然这样存在存储空间上的浪费,但是可以避免UNDO表空间不足所带来的问题。

同时我们也可以使用DBA_UNDO_EXTENTS视图实时监控UNDO表空间的使用情况:

select sum(bytes / 1024 / 1024), status, tablespace_name

from dba_undo_extents

group by status, tablespace_name;

该查询将返回以STATUS分组的各状态回滚信息所使用的空间量,一般存在三种STATUS状态:EXPIRED,UNEXPIRED,ACTIVE。ACTIVE表示目前仍活跃的事务相关回滚信息,UNEXPIRED表示虽然事务已经结束但回滚信息保留的时间仍未超过实例参数UNDO_RETENTION所设定的值,EXPIRED表示回滚信息保留时间已超过UNDO_RETENTION所设定的值。

在UNDO表空间未启用guarantee选项的情况下(当前使用情况),新事务的回滚空间分配遵循以下依据:

a)         寻找不存在ACTIVE区间的回滚段,若没有则创建一个新的回滚段,若空间不允许生成新段,则返回错误。

b)        如果有一个回滚段被选中,但是其中空闲的空间并不足以存储该事务的回滚信息,那么它将尝试创建区间,如果表空间上没有空间,那么将会进入下一步。

c)         如果创建新区间失败,它将会搜索其他回滚段中的EXPIRED区间并重用。

d)        如果其他回滚段中没有EXPIRED区间可使用,那么它会继续搜索其他回滚段中UNEXPIRED区间并重用,注意事务不会重用本回滚段中的UNEXPIRED区间,故UNEXPIRED的回滚空间仅部分可以为Oracle重用;若仍得不到所需则返回错误。

当我们观察到ACTIVE回滚信息所占用空间很大时,说明系统目前运行的事务繁忙。因目前未启用UNDO表空间的guarantee选项,故EXPIRED的全部回滚空间与UNEXPIRED的部分回滚空间可以为Oracle复用,在实时监控时主要观察ACTIVE状态回滚信息使用的空间即可。

在系统相关业务不变的情况下,我们通过计算UNDO表空间的峰值使用情况即可最大程度完善UNDO表空间的配置;而当系统处于业务调整阶段,如新的业务加入或业务时段调整情况下,则需要进一步实时监控UNDO表空间使用情况,以满足动态调整需求。

Comments

  1. FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)
    Applies to:
    Oracle Server – Enterprise Edition – Version: 9.0.1.4 to 11.1.0.6
    Information in this document applies to any platform.
    Oracle RDBMS Server
    Purpose
    This article is intended for Database Administrators with  knowledge on Automatic Undo Management (AUM).
    And we have covered major questions related to Automatic Undo Management (AUM) which will be helpful during creation (or) monitoring the undo tablespaces.
    Questions and Answers
    What is Undo?
    Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following: 

    –  Rollback an active transaction 

    –  Recover a terminated transaction 

    –  Provide read consistency 

    –  Recovery from logical corruptions

    What is AUM / SMU?
    Automatic Undo Management(AUM) is introduced in Oracle 9i, which replaces the rollback segments.
    This is also called System Managed Undo(SMU) as the undo is managed by oracle.

    Automatic undo management is undo-tablespace based. You allocate space in the form of an undo tablespace, instead of allocating many rollback segments in different sizes.

    Oracle strongly recommends their customers to use Automatic Undo Management (AUM).
    Which are the major initialization parameters that controls AUM?

    UNDO_MANAGEMENT Initialization Parameter

    UNDO_MANAGEMENT specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.

    By default, this parameter is set to MANUAL. Set this parameter to AUTO to enable automatic undo management mode.

    This is a static parameter and cannot be modified dynamically using alter system command.
    So if you wish to switch between Rollback Segments and AUM, then you need to restart the instance.

    In RAC, multiple instances must have the same value.

    UNDO_TABLESPACE Initialization Parameter
    When an instance starts up in automatic undo management mode, it attempts to select an undo tablespace for storage of undo data.

    UNDO_RETENTION Initialization Parameter
    This parameter specifies (in seconds) the low threshold value of undo retention.

    The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a “snapshot too old” message.

    The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

    How many Undo tablespaces can we have for a database?

    We can have many undo tablespaces in a database, but only one can be Active per instance.

    In Oracle Real Application Clusters (RAC) enviornment, we need to have one Active undo tablespace per instance.  The UNDO_TABLESPACE parameter will be used for assigning a particular undo tablespace to an instance.

    How to switch to a new undo tablespace?
    You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

    The following statement switches to a new undo tablespace:
    ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02; 
    Assuming undotbs_01 is the current undo tablespace, after this command successfully executes, the instance uses undotbs_02 in place of undotbs_01 as its undo tablespace.

    If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:

    The tablespace does not exist

    The tablespace is not an undo tablespace

    The tablespace is already being used by another instance (in a RAC environment only)

    The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed. When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.

    The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.

    An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).

    If the parameter value for UNDO TABLESPACE is set to ” (two single quotes), then the current undo tablespace is switched out and the next available undo tablespace is switched in. Use this statement with care because there may be no undo tablespace available.

    The following example unassigns the current undo tablespace:
    ALTER SYSTEM SET UNDO_TABLESPACE = ”; 

     NOTE:  There is not a way to shrink an existing UNDO tablespace.  You can change UNDO tablespaces instead.  See Note 268870.1 How to Shrink the datafile of Undo Tablespace
    What is UNDO Retention?
    Undo Retention refers to duration of retaining the undo data after a transaction.
    After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

    Automatic undo management eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being overwritten.

    You can set the UNDO_RETENTION parameter to a low threshold value so that the system retains the undo for at least the time specified in the parameter.

    What is Automatic UNDO Retention (10g New Feature)? Explain.
    There is no parameter for this,  Automatic UNDO Retention is enabled by default in 10g.

    Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information.  Which means the undo information for committed transactions can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Provided that automatic undo management is enabled, the database automatically tunes the undo retention period as follows:

    The current value for tuned undo retention can be viewed by following query.
    SELECT TO_CHAR(BEGIN_TIME, ‘MM/DD/YYYY HH24:MI:SS’) BEGIN_TIME,
        
    TUNED_UNDORETENTION FROM V$UNDOSTAT;

    For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries.
    This could lead to excessive undo generation, to honor undo retention

    For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

    Automatic tuning of undo retention is not supported for LOBs. Because we don’t store any undo information in undo tablespace for transactions on LOBs.
     
    What is Guaranteed UNDO Retention? Explain.
     Oracle Database 10g lets you guarantee undo retention. In Oracle 10g Release 2, you can enable and disable undo retention.
    When you enable this option, the database never overwrites unexpired undo data. That is undo data whose age is less than the undo retention period.

    This option is disabled by default, which means that the database can overwrite the unexpired undo data to avoid failure of DML operations if there is not enough free space left in the undo tablespace.

    By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations. Therefore, use caution when enabling this feature.

    To enable do the following against the undo tablespace.
    ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE; 

    A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data.

    Explain V$UNDOSTAT, and usage?
    This view is a replacement / enhancement for V$ROLLSTAT.

    This view contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.

    The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for undo space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.

    Each row in the view contains statistics collected in the instance for a ten-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval.

    SELECT TO_CHAR(BEGIN_TIME, ‘MM/DD/YYYY HH24:MI:SS’) BEGIN_TIME, 
     
    TO_CHAR(END_TIME, ‘MM/DD/YYYY HH24:MI:SS’) END_TIME, 
     
    UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS  “MAXCON”, 
     
    MAXQUERYLEN, TUNED_UNDORETENTION 
     
    FROM v$UNDOSTAT; 

     The following table explains other useful columns of V$UNDOSTAT view

    UNXPSTEALCNT
    The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests

    UNXPBLKRELCNT
    The number of unexpired blocks removed from undo segments to be used by other transactions

    UNXPBLKREUCNT
    The number of unexpired undo blocks reused by transactions

    EXPSTEALCNT
    The number of attempts when expired extents were stolen from other undo segments to satisfy a space requests

    EXPBLKRELCNT
    The number of expired extents stolen from other undo segments to satisfy a space request

    EXPBLKREUCNT
    The number of expired undo blocks reused within the same undo segments

    SSOLDERRCNT
    The number of ORA-1555 errors that occurred during the interval

    NOSPACEERRCNT
    The number of Out-of-Space errors

    When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.
    If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.

    If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

    In 10g DBA_HIST_UNDOSTAT view contains statistical snapshots of V$UNDOSTAT information.

    Explain the DBA_UNDO_EXTENTS View, and usage?
    DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database.  This view shows the status and size of each extent in the undo tablespace.

    What are the various statuses for Undo Extents? Explain.

    Transaction Status of the undo in the extent can be any of the following:
    SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)  
       
    FROM DBA_UNDO_EXTENTS GROUP BY STATUS;  

    ACTIVE – Undo Extent is Active, Used by a transaction.

    EXPIRED – Undo Extent is expired (Exceeded the Undo Retention).

    UNEXPIRED – Undo Extent will be required to honor UNDO_RETENTION.

    Explain V$TRANSACTION, and usage?
     V$TRANSACTION lists the active transactions in the system.

    (a)  The following columns together points to a transaction.  (ie) The combination of the following should give unique transaction id for that database.

    XIDUSN – Undo segment number
    XIDSLOT – NUMBER Slot number
    XIDSQN – NUMBER Sequence number

    (b)  The following columns explains the number of undo blocks / undo records used per transaction.
    USED_UBLK – Number of undo blocks used
    USED_UREC – Number of undo records used

    In the case of transaction rollback, the above columns will give estimation about the number of undo blocks that needs to be rolled back.

    The number of undo records and undo blocks (USED_UREC and USED_UBLK) decrease while the transaction is rolling back. When they reach 0, the transaction disappears from v$transaction.

    The following query can be used to monitor the transaction rollback.

    SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK  
     
    FROM V$SESSION A, V$TRANSACTION B  
     
    WHERE A.SADDR=B.SES_ADDR; 

    (c) The STATUS following column explains the status of a transaction.

    ACTIVE – Explains the transaction is active.

    Before performing a normal/transactional shutdown, we can check this view to understand if we have any ACTIVE transactions.  
    SELECT XIDUSN, XIDSLT, XIDSEQ , SES_ADDR, STATUS FROM V$TRANSACTION;
     
    Explain DBA_ROLLBACK_SEGS, and usage?
    This view explains the various status of Undo Segments.
    In RAC, we can also see the Instance number, and its associated tablespaces.
    SELECT INSTANCE_NUM,TABLESPACE_NAME,SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

    In AUM DBA’s don’t have privileges to offline/online undo segments. And this is controlled by SMON process.   So this will be useful only in few scenarios, where we have internal errors with undo segments.

    Do we have scripts to monitor the undo growth/usage of the database?
    To understand the free space with undo tablespace.

    SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME=’&UNDOTBS’; 

    To understand state of the extents, space-used in the current undo tablespace.

    SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) 
       
    FROM DBA_UNDO_EXTENTS GROUP BY STATUS; 

    To understand the no of active transactions and its undo usage.
    SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK FROM V$TRANSACTION WHERE STATUS=’ACTIVE’ ;

    What are the possible causes for excessive undo growth?
     There could be various causes for excessive undo growth.  To start the diagnosis we need to understand the following.

    Transactions with huge undo
    It is obvious to see high undo usage when there are huge transactions.
    If that is going to be the case this growth should be expected behavior. 

    UNDO RETENTION
    Higher undo retention will cause higher undo growth.  Because we wont mark the undo extents as EXPIRED till the duration of undo retention.

    Disabling autoextend on datafiles of  active undo tablespace will reuse the UNEXPIRED extents when it has space crunch. It is a trade-off between undo retention and undo space.
    If you wish to satisfy Undo Retention, switch on autoextend in undo tablespace datafiles.
    SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE
      
    TABLESPACE_NAME=’&UNDOTBS’;

    To make those datafile auto extensible, run the following command.
    ALTER DATABASE DATAFILE ‘&FILE_ID’ AUTOEXTEND ON;
    If you wish to switch off auto extend and to reuse the UNEXPIRED space, do the following

    ALTER DATABASE DATAFILE ‘&FILE_ID’ AUTOEXTEND OFF;

     
    State of undo extents
    The status of the undo extents needs to be closely monitored.
    There are few bugs with different releases where EXPIRED extents are not being reused.

    (a) If good number of extents in UNEXPIRED status, it could be due to high undo_retention.
    SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)  
       
    FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

    (b) There are few bugs associated with undo usage,

    The unpublished bug 5442919 affects 10.2.0.3, 10.1.0.5 , 9.2.0.8 and its lesser patch levels, and the issue is fixed in 10.2.0.4.

    Bug 5442919 EXPIRED EXTENTS NOT BEING REUSED
    The above bug is unpublished, and the details can be reviewed through Note:5442919.8
    And we also have Patch:5442919 for most of the latest versions. Kindly check metalink for patch availability.
     
    How to resize the undo datafile?
    It is possible to increase an undo datafile. For example, to increase the undo datafile size from 2000 MB to 3000MB we can do the following
    ALTER DATABASE DATAFILE 39 RESIZE 3000M;
    But it may not be possible to resize to lesser value, when a undo datafile got auto extended to higher value. Even after the transactions are completed those undo extents will remain in EXPIRED status.
    As the blocks are being used by undo extents, oracle will not allow you to resize, It will result in errors similar to following. In the following case the datafile size was 3500MB and a resize to 3000MB results in following errors. 
    SQL> alter database datafile 39 resize 3000m;
    alter database datafile 39 resize 3000m
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value

     

  2. Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
    Applies to:
    Oracle Server – Enterprise Edition
    Information in this document applies to any platform.
    Purpose
    This bulletin explains how to use the new Oracle9i feature Automatic Undo Management (AUM) versus the Manual Undo Management related to conventional rollback segments.
    Scope and Application
    Oracle 9i/10g
    Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
    Checked for relevance on 13-Jan-2010

    Managing Rollback/Undo Segments in Automatic Undo Management:
    =============================================================
    This new feature simplifies and automates the management of undo segments.
    DBAs have the choice to manage rollback segments as they used to do under
    versions Oracle7, Oracle8, and Oracle8i, or to let the RDBMS do it.
    There are now two modes of rollback segments management and usage:
    * AUTOMATIC or
    * MANUAL
    To distinguish between the two types of segments, ROLLBACK segments are called
    UNDO segments when AUM is enabled.
    In both cases, rollback/undo segments are still the only way for transactions
    to execute and complete. This means that with either method, rollback/undo
    segments are present in the database and use disk space.

    *******************
    INIT.ORA parameters
    *******************
    1. UNDO_MANAGEMENT can be set to AUTO if you want the RDBMS to manage undo
    segments automatically:
    – RDBMS creates them when you create a new UNDO tablespace
    – RDBMS alters them ONLINE/OFFLINE when you choose a specific UNDO
    tablespace
    – RDBMS drops them when you drop an UNDO tablespace
    In this case, DBAs cannot manage undo segments at all, though they still do
    exist as "rollback" segments.
    Note: Though you can create rollback segments in UNDO tablespaces, it is
    strongly recommended not to do it.
    UNDO_MANAGEMENT can be set to MANUAL if you want to keep the control on
    rollback segments.
    2. If you decide to use AUM, you have to create at least one UNDO tablespace to
    store the undo segments automatically created.
    Even if AUM uses only one UNDO tablespace at the instance level, you can
    create several UNDO tablespaces. In this case, specify which UNDO tablespace
    is to be used:
    UNDO_TABLESPACE=rbs
    SQL> select name,value from v$parameter
    where name in (‘undo_management’,’undo_tablespace’);
    NAME VALUE
    ———————————— ——————————
    undo_management AUTO
    undo_tablespace RBS
    Having several UNDO tablespaces available in the database provides the
    possibility to switch and use a different tablespace with smaller or
    larger global size for different purposes of usage, such as OLTP, BATCH.

    *************************
    UNDO Tablespaces Creation
    *************************
    1. You create the UNDO tablespace at database creation. (Refer to
    <135053.1> How to create a database with Automatic Undo Management).
    2. Or after database creation:
    SQL> create undo tablespace UNDO_RBS1
    datafile ‘undorbs1.dbf’ size 100m;
    Tablespace created.

    ********************************
    UNDO Tablespaces Characteristics
    ********************************
    1. They are locally-managed with system extent allocation:
    SQL> select TABLESPACE_NAME, CONTENTS,
    EXTENT_MANAGEMENT, ALLOCATION_TYPE,
    SEGMENT_SPACE_MANAGEMENT
    from dba_tablespaces where contents=’UNDO’;
    TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
    —————————— ——— ———- ——— ——
    RBS UNDO LOCAL SYSTEM MANUAL
    UNDO_RBS1 UNDO LOCAL SYSTEM MANUAL

    2. You cannot use UNDO tablespaces for other purposes than UNDO SEGMENTS and
    cannot do any operation on system generated undo segments:

    SQL> create table T (c number) tablespace undo_rbs1;
    create table T (c number) tablespace undo_rbs1
    *
    ERROR at line 1:
    ORA-30022: Cannot create segments in undo tablespace
    SQL> create rollback segment undo_rs1 tablespace undo_rbs1;
    create rollback segment undo_rs1 tablespace undo_rbs1
    *
    ERROR at line 1:
    ORA-30019: RBU Rollback Segment operation not supported in SMU mode
    Note: You can create rollback segments on an UNDO tablespace while the
    database runs in manual mode, but it is useless since these rollback
    segments cannot be set online when running in AUM mode.
    3. Only one UNDO tablespace can be used at the instance level:
    => use UNDO_TABLESPACE=rbs in init.ora parameter file to set it before
    instance startup
    => or use the SQL command to change the UNDO tablespace during instance
    life:
    SQL> alter system set undo_tablespace=undo_rbs1;
    System altered.

    **************************************
    Rollback Segments versus UNDO Segments
    **************************************
    1. When creating an UNDO tablespace, these are automatically created:
    * n undo segments (based on SESSIONS parameter value)
    * named as _SYSSMUn$
    * owned by PUBLIC (usable for OPS configuration)
    * not manually manageable

    SQL> select owner,segment_name,tablespace_name
    from dba_rollback_segs order by 3;
    OWNER SEGMENT_NAME TABLESPACE_NAME
    —— —————————— ——————————
    PUBLIC _SYSSMU1$ RBS
    PUBLIC _SYSSMU2$ RBS
    PUBLIC _SYSSMU3$ RBS
    PUBLIC _SYSSMU5$ RBS
    PUBLIC _SYSSMU7$ RBS
    PUBLIC _SYSSMU9$ RBS
    PUBLIC _SYSSMU10$ RBS
    PUBLIC _SYSSMU8$ RBS
    PUBLIC _SYSSMU6$ RBS
    PUBLIC _SYSSMU4$ RBS
    SYS SYSTEM SYSTEM
    PUBLIC _SYSSMU11$ UNDO_RBS1
    PUBLIC _SYSSMU12$ UNDO_RBS1
    PUBLIC _SYSSMU13$ UNDO_RBS1
    PUBLIC _SYSSMU14$ UNDO_RBS1
    PUBLIC _SYSSMU15$ UNDO_RBS1
    PUBLIC _SYSSMU16$ UNDO_RBS1
    PUBLIC _SYSSMU17$ UNDO_RBS1
    PUBLIC _SYSSMU18$ UNDO_RBS1
    PUBLIC _SYSSMU19$ UNDO_RBS1
    PUBLIC _SYSSMU20$ UNDO_RBS1

    2. If you choose to use AUM, you have no chance to manage any undo or rollback,
    even on an non UNDO tablespace.
    SQL> create public rollback segment rs1 tablespace system;
    create public rollback segment rs1 tablespace system
    *
    ERROR at line 1:
    ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

    3. Only undo segments of the active UNDO tablespace and the SYSTEM rollback
    segment are kept ONLINE. All other rollback segments and undo segments of
    other UNDO tablespaces are OFFLINE.
    Nevertheless, not all undo segments of the active UNDO tablespace are ONLINE
    at startup: this depends on the SESSIONS parameter. For example, if 10 undo
    segments exist and you startup the instance with a lower SESSIONS parameter
    value, the existing UNDO segments are kept but only a few of them are
    onlined. The OFFLINE undo segments of the active UNDO tablespace are onlined
    when more transactions require the use of offlined undo segments.

    *******************************************************
    Automatic Undo Management and Real Application CLusters
    *******************************************************
    The undo space management feature is also useful in Real Application Clusters
    environments.
    1. All instances within Real Application Cluster environments must run in the
    same undo mode.
    2. Set the global parameter UNDO_MANAGEMENT to AUTO in your server parameter
    file.

    If you use client-side parameter files, the setting for UNDO_MANAGEMENT must
    be identical in all the files.
    3. Set the UNDO_TABLESPACE parameter to assign the appropriate undo tablespace
    to each respective instance. Each instance requires its own undo tablespace.
    If you do not set the UNDO_TABLESPACE parameter, each instance uses the
    first available undo tablespace.

    Remarks
    ——-
    1. There is another undo_ init.ora parameter: UNDO_SUPPRESS_ERRORS.
    Use it very carefully: set to TRUE, it suppresses any error message issued
    when attempting manual operations while in AUTO mode.
    SQL> alter rollback segment "_SYSSMU1$" online;
    Rollback segment altered.
    SQL> alter rollback segment "_SYSSMU13$" offline;
    Rollback segment altered.
    SQL> alter rollback segment rs1 online;
    Rollback segment altered.
    All these statements seem to have executed the operation, but in reality did
    not do anything.
    2. Like rollback segments dropped MANUALLY, queries that need to access the
    transaction undo information residing in a dropped UNDO tablespace may
    result in ORA-01555 "snapshot too old (rollback segment too small)" error,
    if the snapshot is older than the DROP-SCN of the UNDO tablespace.

  3. admin says

    How to Shrink the datafile of Undo Tablespace
    Applies to:
    Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.2
    Oracle Server – Standard Edition – Version: 9.2.0.7 to 11.2.0.2
    Information in this document applies to any platform.
    Goal
    Your production database has bi-annual or annual purging programs which generate huge redo. Due to this requirement, your undo tablespace grows rapidly and occupies most of the space on file system.
    The purging process is run only few times a year. So would not like to keep the huge undo datafile in your database throughout the year. You don’t want to buy additional disks unnecessarily.

    You have created an undo tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA 1651 : unable to extend save undo segment by <num> in tablespace <name>.

    You have tried “alter database datafile .. resize” which always fails with Error: ORA 3297 : file contains <num> blocks of data beyond requested RESIZE value.

    You want to shrink the datafile to utilize the disk space for other tablespaces or other purposes.
    Solution
    — Create new undo tablespace with smaller size.
    SQL> create undo tablespace UNDO_RBS1 datafile ‘undorbs1.dbf’ size 100m;

    — Set new tablespace as undo_tablespace
    SQL> alter system set undo_tablespace=undo_rbs1;

    — Drop the old tablespace.
    SQL> drop tablespace undo_rbs0 including contents.

    NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace ‘%s’ is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.    Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain “allocated” from the OS perspective until the database restart.

    Points to Consider:
    –  The value for undo_retention also has a role in growth of undo tablespace. If there is no way to get the undo space for a new transaction, then the undo space (retention) will be reused. But, if the datafiles for undo tablespace are set to auto extensible, it will not reuse the space. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.
    –  Is big really bad?  Overhead on larger file/tablespaces can theoretically impact the database and the OS.  With a small file, the OS would have to do minimal I/O.  Oracle would be able to cache the whole file and there would be less segments to manage.  With AUM you get bitmapped files and all its (space management) performance benefits– (number of) undo segments are automatically managed and are not related to the size of the tablespace.  With the bigger file/tablespace you will have other overhead–e.g. backup will take longer–but as far as the undo management there should be no performance impact just because the file/tbs is bigger.  That said, it is important to monitor systems (e.g. with statspack) and watch for environment-specific issues.
     

  4. Troubleshooting ORA-30036 – Unable To Extend Undo Tablespace [ID 460481.1]

    Applies to:
    Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.1.0.7
    Enterprise Manager Grid Control – Version: 10.2.0.4 to 11.1.0.7
    Information in this document applies to any platform.

    Purpose
    Purpose of this document is to have a checklist for troubleshooting ORA-30036 errors i.e unable to extend segment by %s in undo tablespace ‘%s’ when using Automatic Undo Management (AUM). This note will also discuss some known issues on this subject.

    Last Review Date
    September 27, 2007
    Instructions for the Reader
    A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
    Troubleshooting Details
    When transactions hit a database and they need undo space the allocation happens in the following sequence:

    1. Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments.

    2. If no undo segment found then oracle tries to online an off-line undo segment and use it.

    3. If no undo segments to online, then we create a new undo segment and use it.

    4. If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments.

    For a running transaction associated with undo segment/ extent, if it needs more undo space then:

    1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.

    2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.

    3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.

    4. If there is no free extent available then steal from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.

    5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.

    NOTE: Bug 2900863 indicates this steps is broken in some cases. This is fixed in Server patchset version 9.2.0.5 and above, and in Server release 10g.

    6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment then return the block.

    7. Otherwise try to reuse unexpired extents from own undo segment. If all extents are currently busy(they contains uncommitted information) go to the step 8. Otherwise wrap into the next extent.

    8. Randomly steal unexpired extents from offline undo segments. If this fails then try to online undo segments for reuse.

    9. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace ‘%s’

maclean进行回复 取消回复

*

沪ICP备14014813号-2

沪公网安备 31010802001379号