Skip to main content

Search

Welcome to Tibero GTS!

UNDO Shortage Error Occurs When Starting DB

Document Type | Troubleshooting

Category | Monitoring/Inspection

Applicable Product Version | 6FS07

Document Number | TMOTS004

 

Issue

An error occurs when adjusting _USGMT_ONLINE_MIN from the default 11 to 200, and the parameter value is reverted.
An UNDO shortage error occurs when starting the DB, making startup impossible.
Reference
  • Error 21004: ERROR_TX_CANT_ALLOC_EXT, No more extent available in tablespace 'UNDO'.
  • Error 21028: ERROR_TX_CANT_ONLINE_MIN_USGMT, Unable to bring minimum number of undo segments online.
[2022-12-10T03:30:05.801057] [TXT-242] [I] can't extend tablespace TS #1(UNDO)

[2022-12-10T03:30:05.878756] [FRM-242] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'UNDO'.] (csr_id:4294967295) [tx_ts.c:1337:ts_extend]

[2022-12-10T03:30:05.887286] [FRM-242] [I] THROW. ec=ERROR_TX_CANT_ONLINE_MIN_USGMT(-21028) [ Unable to bring minimum number of undo segments online.] (csr_id:4294967295) [tx_usgmt.c:2585:usgmt_create_min]

[2022-12-10T03:30:05.887306] [FRM-242] [I]

********************************************************

* Critical Warning : Server Boot failed. The reason is

*   TBR-21028 :  Unable to bring minimum number of undo segments online.

*   Process shutdown.

********************************************************

 

[2022-12-10T03:30:05.887349] [FRM-00] [I] MTHR received SHUTDOWN message! SHUTTING DOWN...

[2022-12-10T03:30:05.887367] [MT-00] [I] MTHR starts to shutdown mode=4, CUR_SVRMODE=7

[2022-12-10T03:30:05.887374] [MT-00] [I] MTHR terminiates all childs
 
 
 

Cause

When _USGMT_ONLINE_MIN was increased to 200, an error occurred due to insufficient space to create usgmt.
 
However, since there appears to be sufficient space on v$undo_free_space and enough undo datafiles, this is a situation where many expired undo blocks belonging to usgmt exist, rather than many active undo blocks.
 
The usgmts created at boot time with the _USGMT_ONLINE_MIN value cannot take space from other usgmts but only from free space on the undo datafile. Therefore, in such a situation, undo space belonging to other usgmts must be returned to the disk free space before creating new undo segments.
 

 
 

Solutions

1. Execute command

SQL> alter rollback segment all shrink;
 
 

2. Change _USGMT_ONLINE_MIN value

Since the slog shows many transactions concentrated at a specific time, it is also possible to consider increasing the _USGMT_ONLINE_MIN value by about 1000. 

_USGMT_ONLINE_MIN=1000
 
 UNDO shortage error occurs when starting and startup is impossible.
Reference
  • With the parameter setting below, expired undo space belonging to usgmt can be periodically returned to free space on disk. (in minutes)
    USGMT_AUTO_SHRINK_INTERVAL = 180
 
 
 
 
 
 
 
 
 
 
 

Related to