User Managed Recovery - undo

In Oracle Database, an "undo tablespace" is used to store undo data, which is required for maintaining the integrity and consistency of the database. Undo data is used to roll back transactions, support read consistency, and enable features like Flashback Query and Flashback Transaction. If you need to create, alter, or manage an undo tablespace, here are some common tasks you might perform.


Consider that the UNDO tablespace has been corrupted

STEP 1 : When we start the database, we get an erro about the corrupted UNDO Tablespace 
  [oracle@tesdb ~]$ export ORACLE_SID=tes
  [oracle@tesdb ~]$ sqlplus / as sysdba

  SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 20 13:02:59 2023
  Version 19.3.0.0.0

  Copyright (c) 1982, 2019, Oracle.  All rights reserved.

  Connected to an idle instance.

  SQL> startup
  ORACLE instance started.

  704 bytes
  Database mounted.
  ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
  ORA-01110: data file 5: '/u01/app/oracle/oradata/TES/undotbs02.dbf'

Step 2 : Close the database connection 
  SQL> shut immediate

  ORA-01109: database not open

  Database dismounted.
  ORACLE instance shut down.

Step 3 : Start the database in mount stage 
  SQL> startup mount
  ORACLE instance started.

  Total System Global Area 1157627168 bytes
  Fixed Size		    8895776 bytes
  Variable Size		  301989888 bytes
  Database Buffers	  838860800 bytes
  Redo Buffers		    7880704 bytes
  Database mounted.
  SQL>

Step 4 : Drop the corrupted datafile in offline mode 
  SQL>  ALTER DATABASE DATAFILE
    '/u01/app/oracle/oradata/TES/undotbs02.dbf' offline drop;

  Database altered.

Step 5 : Open the database connection 
  SQL> alter database open;

  Database altered.

Step 6 : Create new UNDO tablespace 
  SQL> CREATE UNDO TABLESPACE undotbs3 datafile
  '/u01/app/oracle/oradata/TES/undotbs03.dbf' size 100m;

  Tablespace created.

Step 7 : Set the newly created Undo tablespace as default 
  SQL> ALTER SYSTEM SET UNDO_TABLESPACE = 'undotbs3' scope=both;

  System altered.

Step 8 : Drop tablespace because earlier we have dropped only the datafile 
  SQL> drop tablespace UNDOTBS2;

  Tablespace dropped.

Step 9 : Create pfile from spfile to update the changes in pfile 
  SQL> create pfile from spfile;

  File created.

Step 10 : Take the backup of control file and read it to check if the droped tablespaceis removed 
  SQL>  ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/con_bkp.sql';

  Database altered.


(User Managed Recovery - temp)