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
[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