Тетрадочка DBA

Posts Tagged ‘sql: v$undostat’

Grid Control кричит «UNDO TBS full» — без паники

Posted by shane54lv на 22 Май, 2009

Опять стреманул, пошли сообщения UNDOTBS1 is 99% full. Почитал – все спокойно, это нормально для 10gR2 при AUTOEXTEND=OFF для UNDO:


The Undo Block allocation algorithm in Automatic Undo Management is the following :

1. If the current extent has more free blocks then the next free block is allocated.
2. Otherwise, if the next extent expired then wrap in the next extent and return the first block.
3. If the next extent does not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the transaction table and return the first block in the new extent.
4. If there is no free extent available then steal from an offline transaction table. Deallocate the extent from the offline transaction table and add it to the current transaction table. Return the first free block of the extent.
5. Steal from online transaction table. Deallocate the extent from the online transaction table and add it to the current transaction table. Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current transaction table then return the block.
7. Otherwise try to reuse unexpired extents from own transaction table. If all extents are currently busy(they contains uncommitted information) go to the step 8. Otherwise wrap into the next extent.
8. Steal unexpired extents from offline transaction tables. If this fails then try on online transaction tables.
9. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace ‘%s’

When the UNDO tablespace is created with NO AUTOEXTEND, following the allocation algorithm, here is the explanation for this correct behavior:

For a fixed size UNDO tablespace (NO AUTOEXTEND), starting with 10.2, we provide max retention given the fixed undo space, which is set to a value based on the UNDO tablespace size.
This means that even if the undo_retention is set to a number of seconds (900 default), the fixed UNDO tablespace supports a bigger undo_retention time interval (e.g: 36 hours), based on the tablespace size, thing that makes the undo extents to be UNEXPIRED. But this doesn’t indicate that there are no available undo extents when a transaction will be run in the database, as the UNEXPIRED undo segments will be reused.

This is a correct behavior, concerning an UNDO tablespace created with AUTOEXTEND OFF in 10gR2, so there is not need to add more space to it or be concerned by the fact that it appears to be 100% full.

Надоест – ставим всем датафайлам UNDO autoextend = on при текущем размере, чтобы расти было некуда (наебка короче) – все, Automatic Undo начинает работать по-другому и ошибок таких не будет. И пару SQL:

  FROM dba_undo_extents;

  FROM dba_undo_extents t1
 WHERE t1.status = 'UNEXPIRED';

  FROM dba_undo_extents
 WHERE status = 'EXPIRED';

  FROM dba_undo_extents
 WHERE status = 'ACTIVE';

SELECT DISTINCT t1.tablespace_name
           FROM dba_free_space t1
       ORDER BY 1 DESC;
where t1.tablespace_name = 'UNDOTBS1';

SELECT t1.tablespace_name, t1.BYTES / 1024 / 1024
  FROM dba_free_space t1
 WHERE t1.tablespace_name = 'UNDOTBS1';

SELECT creation_time, metric_value, MESSAGE_TYPE, reason, suggested_action
  FROM dba_outstanding_alerts
 WHERE object_name = 'UNDOTBS1';
SELECT creation_time, metric_value, MESSAGE_TYPE, reason, suggested_action,
  FROM dba_alert_history
 WHERE object_name = 'UNDOTBS1';
SELECT object_type, object_name, warning_value, critical_value
  FROM dba_thresholds
 WHERE object_type = 'TABLESPACE';

               FROM dba_undo_extents
              WHERE tablespace_name = 'UNDOTBS1'
                AND status IN ('ACTIVE', 'UNEXPIRED'))
          * 100
       / (SELECT SUM (BYTES)
            FROM dba_data_files
           WHERE tablespace_name = 'UNDOTBS1') "PCT_INUSE"

  FROM v$undostat;

Posted in Query'ы, URL'ы | Отмечено: , , , , , , , , | 1 Comment »