ORA-02097: parameter cannot be modified because specified value is invalid-ORA-30014: operation only supported in Automatic Undo Management mode

Cause:

SQL> alter system set undo_retention=100;
alter system set undo_retention=100
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30014: operation only supported in Automatic Undo Management mode

Workaround:

Undo management was set to manual due to manual database creation.

SQL> show parameter undo_manage

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL

Change the parameter value to auto for enabling automatic undo management

SQL> alter system set UNDO_MANAGEMENT=auto scope=spfile;

System altered.

Bounce the database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size            1157631712 bytes
Database Buffers          402653184 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s