LRM-00109: UNDO Tablespace Error

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

• Roll back transactions when a ROLLBACK statement is issued
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Flashback Query
• Recover from logical corruptions using Flashback features

Today I encountered this error when starting my Oracle database in windows server:

SQL> startup;
ORA-01078: failure in processing system parameters
LRM-00109: impossible d'ouvrir le fichier de param┐tres 'E:\APP\ORACLE\PRODUCT\1
1.2.0\DBHOME_2\DATABASE\INITHREF.ORA'

Resolution steps:

Indicate the init.ora file used by the database:

SQL> startup pfile=E:\REFCIV9\database\pfile\initHREF.ora
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
Instance ORACLE lancÚe.

Total System Global Area  455184384 bytes
Fixed Size                  2282056 bytes
Variable Size             343936440 bytes
Database Buffers          100663296 bytes
Redo Buffers                8302592 bytes
Base de donnÚes montÚe.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
ID de processus : 8324
ID de session : 9,  NumÚro de sÚrie : 3

But,

Another message was displayed when opening the instance:

ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
ID de processus : 8324
ID de session : 9,  NumÚro de sÚrie : 3

So,

Shutdown the Database and set the following parameters in parameter file

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
undo_retention=10800

undo_tablespace=UNDOTBS02


Start the Database

SQL> startup pfile=E:\REFCIV9\database\pfile\initHREF.ora
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
Instance ORACLE lancÚe.

Total System Global Area  455184384 bytes
Fixed Size                  2282056 bytes
Variable Size             343936440 bytes
Database Buffers          100663296 bytes
Redo Buffers                8302592 bytes
Base de donnÚes montÚe.

Base de donnÚes ouverte.

Some warnings still there:

ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated

Delete these two parameters from init file:

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=E:\REFSENEGALV9\database\log
core_dump_dest=E:\REFSENEGALV9\database\log
user_dump_dest=E:\REFSENEGALV9\database\log


And restart the database.

It seems OK for now :)

But when using my application I had this Oracle error

ORA-01552: cannot use system rollback segment for non-system tablespace 'string'

To fix it:

Create a new undo tablespace:

SQL> CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'E:\REFCIV9\database\sys\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED;
Tablespace crÚÚ.

Swith the default UNDO to the new one fails:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1
*
ERREUR Ó la ligne 1 :
ORA-02097: le paramÞtre ne peut pas Ûtre modifiÚ, car la valeur indiquÚe n'est
pas valide
ORA-30014: opÚration prise en charge uniquement en mode de gestion
d'annulations automatique

Modify in the init file the management mode of undo tablespace to "AUTO" and restart the database:


###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO

SQL> startup pfile=E:\REFCIV9\database\pfile\initHREF.ora
Instance ORACLE lancÚe.

Total System Global Area  455184384 bytes
Fixed Size                  2282056 bytes
Variable Size             343936440 bytes
Database Buffers          100663296 bytes
Redo Buffers                8302592 bytes
Base de donnÚes montÚe.
Base de donnÚes ouverte.
SQL>
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS02


The switch succed now:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;

SystÞme modifiÚ.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS1


Drop the old tablespace with its files:

SQL> DROP TABLESPACE UNDOTBS02 INCLUDING CONTENTS AND DATAFILES;

Tablespace supprimÚ.


Hope that helps !