There were interesting situation happened after recreating undo. Most of the requests which have child requests and running more than hour are getting fail with ORA-1555 . When checking the undo parameters seems all are good as below.
SQL> sho parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
_undo_autotune boolean TRUE
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 100000
undo_tablespace string APPS_UNDOTS1
SQL>
But we noticed that there were a bug (Bug 31113682) which change the _undo_autotune = True after recreation of undo.
It is clear that the automatic undo tuning is wrongly calculated leading to unjustifiable ORA-01555 error
In case calculated tuned_undoretention is of small value whereas undo tablespace have sufficient free space and there are long running transactions, it is possible to be hitting this bug.
After resetting the parameter all requests are getting complete without any issues
SQL> alter system set “_undo_autotune”=FALSE scope=BOTH;
System altered.
SQL>
