How to Recover an Oracle UNDO Tablespace

This is task that all Oracle DBA should know and understand. Imagine that your database will loose access to your Undo table space or you Undo data files are lost!, how can you get back on the horse? So let's see what are the steps in recovering the Undo table space.

  • We will start by showing you the error that you receive when the Undo table space becomes unavailable.
 SQL startup;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             331352016 bytes
Database Buffers           79691776 bytes
Redo Buffers                4288512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/oracle/undotbs01.dbf'
Ok we can see that a ORA-01110 is up!!!
  • Startup your database in mount mode only.
 SQL startup mount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             331352016 bytes
Database Buffers           79691776 bytes
Redo Buffers                4288512 bytes
Database mounted.
SQL
  • Change the undo_management parameter to manual and bounce your database in mount mode again.
 SQL alter system set undo_management=manual scope=spfile;
System altered.

SQL shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL startup mount;
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             331352016 bytes
Database Buffers           79691776 bytes
Redo Buffers                4288512 bytes
Database mounted.
  • Drop the undo data file using the offline option.
 SQL  alter database datafile '/opt/oracle/oradata/oracle/undotbs01.dbf' offline drop;

Database altered.

SQL
  • Open the database now.
 SQL alter database open;
Database altered.
  • Drop the Undo Table space.
 SQL Show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS1

SQL drop tablespace UNDOTBS1;
Tablespace dropped.
  • Create a new undo table space
SQL  Create UNDO tablespace UNDOTBS1 datafile '/opt/oracle/oradata/oracle/undotbs_new1.dbf' size 25M autoextend on next 1M maxsize 1024M;

Tablespace created.

SQL
  • Change the undo_management parameter to auto:
 SQL alter system set undo_management=auto scope=spfile;
System altered.
  • Bounce(restart) your database
 SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             331352016 bytes
Database Buffers           79691776 bytes
Redo Buffers                4288512 bytes
Database mounted.
Database opened.
SQL

Copyright 2020. All Rights Reserved