In Undo Segments there are three types of extents, they are
Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.
-----------------------------------
Check the overall status for undos.
-----------------------------------
SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
from dba_undo_extents group by tablespace_name, status;
TBS STATUS GB
UNDO_TDS EXPIRED 5.95526123046875
UNDO_TDS UNEXPIRED 0.0001220703125
--------------------------
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat;
UNDO_BLK_PER_SEC
1241.68
----------------------
Undo Blocks per Second
----------------------
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,v$tablespace b,dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,v$parameter e,v$parameter f,( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
FROM v$undostat
) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'
ACTUAL_UNDO_SIZE_MB UNDO_RETENTION_SEC OPTIMAL_UNDO_RETENTION_SEC
------------------- ------------------ --------------------------
6108 900 630
----------------------
Optimal Undo Retention
----------------------
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ACTUAL_UNDO_SIZE_MB UNDO_RETENTION_SEC<]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from
SIZE_MB FREE_MB NEEDED_UNDO_SIZE
------------------- ------------------ -----------------
6108 900 8730.5625
------------------------------------------------------
Calculate Needed UNDO Size for given Database Activity
------------------------------------------------------
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
TBS SIZE_MB FREE_MB %USED
--- ------- ------- -----
UNDO_TDS 6108 10 99
----------------------------------
EXTRACTING THE ACTUAL DDL OF UNDO:
----------------------------------
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDO_TDS') FROM DUAL;
-----------------------
ALTERING UNDO TBS SIZE:
-----------------------
ALTER DATABASE DATAFILE '/UNDO_TDS_001.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
ALTER DATABASE DATAFILE '/UNDO_TDS_002.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
------------------------------
Monitoring:
===========
Undo information can be queried using the following views:
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1
=============
Note:460481.1
=============
Troubleshooting Steps
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'
Error: ORA-30036 (ORA-30036)
Text: unable to extend segment by %s in undo tablespace '%s'
---------------------------------------------------------------------------
Cause: the specified undo tablespace has no more space available.
Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.
This Error states that we need to add more space to Undo Tablespace, which might not be case always.
Now theoretically we know when a ora-30036 error would be reported. Now to practically confirm a genuine case of ora-30036 where we need to add more space to the undo datafile, we need to look for following information
- No/ very small free space in the undo tablespace.
select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
Replace 'UNDOTBS1' with the undo tablespace name and check if there is free space available or not.
- Undo tablespace datafile is not autoextensible.
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
- No/ very less expired extents.
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
EXPIRED 3932160 60
UNEXPIRED 2293760 20
--> after sometime
Suppose that there are no expired extents and we see only Unexpired extents and Active extents then this is most likely Undo sizing issue. In this case, check if Undo Tablespace is correctly sized. Else try reducing value for UNDO_RETENTION
The following query calculates the number of bytes needed:
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
*(UR) UNDO_RETENTION in seconds
*(UPS) Number of undo data blocks generated per second
*(DBS) Overhead varies based on extent and file size (db_block_size)
Refer to below note for more explanation on this
Note 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
With Oracle database 10g, you can use the Undo Advisor feature to get Undo sizing Recommendations
In case the EXPIRED extents are still there, then it means that the EXPIRED extents are not being reused. These Expired extents should have been reused and instead we are getting ORA-30036 error.
This could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and 11g ). In case all the below conditions are satisfied, then you need to apply the patch
* System managed undo (undo_management=auto in init.ora)
*Undo tablespace consists of all non-autoextend datafiles
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
*DML fails with this error, also written to the alert log
"ORA-30036: unable to extend segment by <n> in undo tablespace '<ts>'"
This msg is repeated for the same us# in the alert log:
"Failure to extend rollback segment <us#>"
* Instance uptime longer than 1 hour
Many (eg 1000) offline undo segments:
select count(*) from dba_rollback_segs where status='OFFLINE';
22
*No free space in the undo tablespace:
select sum(bytes) from dba_free_space where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
*Plenty of expired and/or unexpired space in the undo ts
select sum(bytes) "UNEXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='UNEXPIRED';
select sum(bytes) "EXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='EXPIRED';
Final updates:
As per the oracle doc, expired extents are not been used and hence its a bug.
Referred links:
---------------
http://www.dbvisit.com/forums/showthread.php?t=241
http://www.oracle-base.com/articles/9i/automatic-undo-management.php
http://www.oracle-dba-online.com/managing_the_undo_tablespace.htm
http://www.dbas-oracle.com/2011/04/how-to-shrink-datafile-of-undo.html
https://forums.oracle.com/forums/thread.jspa?threadID=988325
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6894817116500
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1354906470493
http://blog.mydream.com.hk/howto/how-to-determine-undo-usage-in-oracle
https://forums.oracle.com/forums/thread.jspa?threadID=662211
http://www.dba-oracle.com/sf_ora_30036_unable_to_extend_segment_by_string_in_undo_tablespace_string.htm
Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.
-----------------------------------
Check the overall status for undos.
-----------------------------------
SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
from dba_undo_extents group by tablespace_name, status;
TBS STATUS GB
UNDO_TDS EXPIRED 5.95526123046875
UNDO_TDS UNEXPIRED 0.0001220703125
--------------------------
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat;
UNDO_BLK_PER_SEC
1241.68
----------------------
Undo Blocks per Second
----------------------
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,v$tablespace b,dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,v$parameter e,v$parameter f,( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
FROM v$undostat
) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'
ACTUAL_UNDO_SIZE_MB UNDO_RETENTION_SEC OPTIMAL_UNDO_RETENTION_SEC
------------------- ------------------ --------------------------
6108 900 630
----------------------
Optimal Undo Retention
----------------------
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ACTUAL_UNDO_SIZE_MB UNDO_RETENTION_SEC<]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from
SIZE_MB FREE_MB NEEDED_UNDO_SIZE
------------------- ------------------ -----------------
6108 900 8730.5625
------------------------------------------------------
Calculate Needed UNDO Size for given Database Activity
------------------------------------------------------
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
TBS SIZE_MB FREE_MB %USED
--- ------- ------- -----
UNDO_TDS 6108 10 99
----------------------------------
EXTRACTING THE ACTUAL DDL OF UNDO:
----------------------------------
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDO_TDS') FROM DUAL;
-----------------------
ALTERING UNDO TBS SIZE:
-----------------------
ALTER DATABASE DATAFILE '/UNDO_TDS_001.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
ALTER DATABASE DATAFILE '/UNDO_TDS_002.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
------------------------------
Monitoring:
===========
Undo information can be queried using the following views:
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1
=============
Note:460481.1
=============
Troubleshooting Steps
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'
Error: ORA-30036 (ORA-30036)
Text: unable to extend segment by %s in undo tablespace '%s'
---------------------------------------------------------------------------
Cause: the specified undo tablespace has no more space available.
Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.
This Error states that we need to add more space to Undo Tablespace, which might not be case always.
Now theoretically we know when a ora-30036 error would be reported. Now to practically confirm a genuine case of ora-30036 where we need to add more space to the undo datafile, we need to look for following information
- No/ very small free space in the undo tablespace.
select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
Replace 'UNDOTBS1' with the undo tablespace name and check if there is free space available or not.
- Undo tablespace datafile is not autoextensible.
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
- No/ very less expired extents.
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
EXPIRED 3932160 60
UNEXPIRED 2293760 20
--> after sometime
Suppose that there are no expired extents and we see only Unexpired extents and Active extents then this is most likely Undo sizing issue. In this case, check if Undo Tablespace is correctly sized. Else try reducing value for UNDO_RETENTION
The following query calculates the number of bytes needed:
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
*(UR) UNDO_RETENTION in seconds
*(UPS) Number of undo data blocks generated per second
*(DBS) Overhead varies based on extent and file size (db_block_size)
Refer to below note for more explanation on this
Note 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
With Oracle database 10g, you can use the Undo Advisor feature to get Undo sizing Recommendations
In case the EXPIRED extents are still there, then it means that the EXPIRED extents are not being reused. These Expired extents should have been reused and instead we are getting ORA-30036 error.
This could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and 11g ). In case all the below conditions are satisfied, then you need to apply the patch
* System managed undo (undo_management=auto in init.ora)
*Undo tablespace consists of all non-autoextend datafiles
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
*DML fails with this error, also written to the alert log
"ORA-30036: unable to extend segment by <n> in undo tablespace '<ts>'"
This msg is repeated for the same us# in the alert log:
"Failure to extend rollback segment <us#>"
* Instance uptime longer than 1 hour
Many (eg 1000) offline undo segments:
select count(*) from dba_rollback_segs where status='OFFLINE';
22
*No free space in the undo tablespace:
select sum(bytes) from dba_free_space where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
*Plenty of expired and/or unexpired space in the undo ts
select sum(bytes) "UNEXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='UNEXPIRED';
select sum(bytes) "EXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='EXPIRED';
Final updates:
As per the oracle doc, expired extents are not been used and hence its a bug.
Referred links:
---------------
http://www.dbvisit.com/forums/showthread.php?t=241
http://www.oracle-base.com/articles/9i/automatic-undo-management.php
http://www.oracle-dba-online.com/managing_the_undo_tablespace.htm
http://www.dbas-oracle.com/2011/04/how-to-shrink-datafile-of-undo.html
https://forums.oracle.com/forums/thread.jspa?threadID=988325
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6894817116500
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1354906470493
http://blog.mydream.com.hk/howto/how-to-determine-undo-usage-in-oracle
https://forums.oracle.com/forums/thread.jspa?threadID=662211
http://www.dba-oracle.com/sf_ora_30036_unable_to_extend_segment_by_string_in_undo_tablespace_string.htm
No comments:
Post a Comment