Steps to recreate corrupt UNDO tablespace
SQL> select segment_name, status from dba_rollback_segs where tablespace_name = ‘UNDOTBS1’;
SEGMENT_NAME STATUS
———- ——-
_SYSSMU1 $ NEED RECOVERY
Solution:
Create a new Undo tablespace:
SQL> Create undo tablespace UNDOTBS datafile ‘/app/oracle/oradata/UNDOTBS.dbf’ size 512m reuse autoextend on;;
Change the undo tablespace standard seat:
SQL> Alter system set undo_tablespace = ‘UNDOTBS’ scope = spfile;
Set offline rollback segment corrupt:
SQL> Alter system set “_offline_rollback_segments” = “_ $ SYSSMU1” scope = spfile;
Restart the database and delete the corrupted segment:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Startup
startup ORACLE instance started.
Total System Global Area 353863224 bytes
Fixed Size 742968 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
Drop rollback segment corrupted
SQL> Drop rollback segment “_SYSSMU1” $;
Delete the corrupted undo:
SQL> Drop table space UNDOTBS1 including contents and datafiles;
Ready, made this the database is available again for DML.
NOARCHIVELOG
If your database is in noarchivelog mode than better to open SR with Oracle, if it is test database than you can proced with below steps
If database database oradata folder size is less or manageable to take its backup while database is shutdown.
Once you took backup of oradata, now you proced for recovery
SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status not in (‘ONLINE’, ‘OFFLINE’);
SEGMENT_NAME STATUS TABLESPACE_NAME
—————– ———– —————-
_SYSSMU1_2943671021$ NEEDS RECOVERY UNDO01
_SYSSMU2_4044142269$ NEEDS RECOVERY UNDO01
_SYSSMU3_2957612445$ NEEDS RECOVERY UNDO01
Note: Add the following lines to pfile.
*.DB_BLOCK_CHECKSUM = FALSE
*._DB_ALWAYS_CHECK_SYSTEM_TS = FALSE
*.event=”10231 trace name context forever, level 10″
*.event=”10233 trace name context forever, level 10″
*._allow_resetlogs_corruption= true
_CORRUPTED_ROLLBACK_SEGMENTS=(‘_SYSSMU1_2943671021$’)
_CORRUPTED_ROLLBACK_SEGMENTS=(‘_SYSSMU2_4044142269$’)
_CORRUPTED_ROLLBACK_SEGMENTS=(‘_SYSSMU3_2957612445$’)
_offline_rollback_segments=(_SYSSMU1_2943671021$)
_offline_rollback_segments=(_SYSSMU2_4044142269$)
_offline_rollback_segments=(_SYSSMU3_2957612445$)
Stop Database
SQL> SHUTDOWN IMMEDIATE;
Start Database with restrict session using PFILE
SQL> STARTUP RESTRICT PFILE=/oracle/app/dbs/init.ora;
Create a new undo tablespace UNDOTBS2
SQL> Create undo tablespace UNDOTBS2 datafile ‘/u01/oradata/unodtbs02.dbf’ size 1500M REUSE AUTOEXTEND ON;
Make entry in the pfile. Modify the following parameters in pfile and restart the database.
Undo_management=auto
Undo_tablespace=’UNDOTBS2′
Make entry in the spfile. Modify the following parameters in spfile and restart the database.
SQL> Alter system set undo_tablespace = ‘UNDOTBS2’ scope = spfile;
SQL> drop rollback segment “_SYSSMU1_2943671021$”;
SQL> drop rollback segment “_SYSSMU2_4044142269$”;
SQL> drop rollback segment “_SYSSMU3_2957612445$”;
Rollback segment dropped.
Rollback segment dropped.
Rollback segment dropped.
Drop Corrupted Tablespace
SQL> drop tablespace undotbs1;
Tablespace dropped.
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
UNDOTBS2
SYSAUX
USERS
EXAMPLE
TEMP
6 rows selected.
Now the corrupted UNDO tablespace is droped.
SQL> select * from v$rollname;