Application Team encountered an unable to extend Undo in one of their Prod Batch Job but there we no error in alert log on mentioned time
Mar 31 04:26:00 ORA-30036: unable to extend segment by 4 in undo tablespace ‘UNDOTBS1’
Alert Log entry
Sat Mar 31 03:08:08 2018
Archived Log entry 112180 added for thread 1 sequence 112180 ID 0xa8b77b1e dest 1:
Sat Mar 31 04:26:19 2018
Thread 1 advanced to log sequence 112182 (LGWR switch)
Current log# 4 seq# 112182 mem# 0:
Current log# 4 seq# 112182 mem# 1:
Sat Mar 31 04:26:27 2018
Archived Log entry 112181 added for thread 1 sequence 112181 ID 0xa8b77b1e dest 1:
Sat Mar 31 05:00:04 2018
ALTER SYSTEM ARCHIVE LOG
Sat Mar 31 05:00:04 2018
Thread 1 advanced to log sequence 112183 (LGWR switch)
Current log# 5 seq# 112183 mem# 0:
Current log# 5 seq# 112183 mem# 1:
How to Check this from database end ?
Method 1 :
Check using AWR :
In AWR Check in Undo Statistics
You can see the OOS -1 (Out of Space Count) . You can confirm here that the Undo run out of space
You Can see the error occurred between 4:25-4:35 ,
The Undo Segment stats happens every ten minutes .
The Last column tells us about the stolen blocks from the expired and unexpired blocks.
How does the Undotablespace utilize expired and unexpired block in case the undo tablespace is not having guaranteed retention
select retention from dba_tablespaces where tablespace_name=’UNDO1BS1′;
Below is how the algorithm works
When we execute an operations that needs to allocate undo space:
- Allocate an extent in an undo segment which has no active transaction. Why in other segment? Because Oracle tries to distribute transactions over all undo segments.
- If no undo segment was found then oracle tries to online an off-line undo segment and use it to assign the new extent..
- If no undo segments was possible to online, then Oracle creates a new undo segment and use it.
- If the free space doesn’t permit creation of undo segment, then Oracle tries to reuse an expired extent from the current undo segments.
- If failed, Oracle tries to reuse an expired extent from another undo segment.
- If failed, Oracle tries to autoextend a datafile (if autoextensible=yes)
- If failed, Oracle tries to reuse an unexpired extent from the current undo segment.
- If failed, Oracle tries to reuse an unexpired extent from another undo segment.
- If failed, then the operation will fail.
For a running transaction associated with undo segment/ extent and it needs more undo space:
- If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
- 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.
- 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.
- If there is no free extent available then reuse from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment.
- Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment.
From AWR it is very evident that the Undo tablespace was very busy and is trying to utilize the expired and unexpired blocks ,
Lets get more info from the DBA Views
SQL> sho parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 86400
At 4:25 We hit 1 in NOSPACEERRCNT (No Space Error Count)
Also you can see the the expired blocks coming down from 17625 to 6224 in matter of 3 hours ,The first and column lets you know how many block were taken from the Unexpired and expired blocks
The Query used to fetch this data :
select to_char(begin_time,’dd:mon:yyyy hh24:mi:ss’) “Begin_Time”,
from DBA_HIST_UNDOSTAT where SNAP_ID between 78728 and 78790 order by 1;
Undo Retention and nature of the batch jobs is what the determines the read consistency and the storage planning for UNDO Tablespaces ,Here we have 24 hours for Undo_retention