Database is shutdown and can’t start it, asking for Undo recovery while dropping Undo tablespace
Dropping a Undo tablespace give message
The UNDOTBS for our RMAN Catalog database was dropped. Any attempts to switch to a newly created UNDOTBS or removing the old UNDOTBS failed with the following error:
ORA-01548: active rollback segment
Or
Undo segment shows status as needs recovery
Cause
The issue could happen if the datafile on which the undo segments reside is offline and the transaction cannot be rolled backed since the file is offline
Or
This could also happen if there is any issue in the Undo segment itself
Solution
Check if the Undo segment status first
SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status not in (‘ONLINE’, ‘OFFLINE’) ; SEGMENT_NAME STATUS TABLESPACE_NAME ------------ -------------- ---------------- _SYSSMU3$ NEEDS RECOVERY UNDO01
In the above example Undo segment _SYSSMU3$ is in Needs recovery status.
This segment belongs to Undo tablespace UNDO01
Check the status of the datafile present in the tablespace UNDO01
SQL> select status, name, file# from v$datafile where ts# in (Select ts# from v$tablespace where name=’UNDO01′ ); STATUS NAME FILE# ------------ -------------- ---------------- ONLINE /u01/undo01_01.dbf 56 RECOVER /u02/undo01_03.dbf 77
So clearly one file is in Recover status
Option A
If the database is in Archive log mode and you have all the required archive log mode you can do the following :-
Find if you have all the required Archive logs on disk or If using Rman ensure they exist in the backup
SQL> Select checkpoint_change# from v$datafile_header where file_id=<file# in Recover status from previous query> ;
Now find these changes are present in which Archive log
SQL> select sequence#,thread#,name from v$archived_log where <checkpoint_change# from query 1> between first_change# and next_change# ;
Ensure you have all the archive logs starting from this sequence# till the current sequence# in your database
For example
SQL> select checkpoint_change#,file#,status from v$datafile_header where file#=77; CHECKPOINT_CHANGE# FILE# STATUS ------------ -------------- ---------------- 2103113 4 OFFLINE SQL>Select sequence#,thread#,name from v$archived_log where 2103113 between first_change# and next_change# ; SEQUENCE# THREAD# NAME ------------ -------------- ---------------- 96 1 /u01/arch/O1_MF_1_96_6OKHP.Arc
If using rman
Check if the archive log from this sequence till current sequence is available
RMAN> list backup of archivelog from sequence 96; RMAN> recover datafile 4; ; RMAN> sql ‘alter database datafile 4 online’ ;
if using sqlplus
————-
Ensure the archive logs are present on disk SQL> recover datafile 4 ; Type AUTO and hit enter Once recovery is done SQL> alter database datafile 4 online ;
If the archive logs have been restored to a different location than the Default archive log destination your database is using then specify the same using set source command in sqlplus
SQL> set logsource “/u01/arch/newlocation” ; SQL> recover datafile 4 ; Type AUTO and hit enter Once recovery is done SQL> alter database datafile 4 online ;
Option B
If your database is in noarchivelog mode than better to open SR with Oracle, if it is test database than you can proceed 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 ------------ -------------- ---------------- _SYSSMU3$ 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=(‘_SYSSMU3$‘) _offline_rollback_segments=(_SYSSMU3$)
Rename spfile.
Start database with restrict session.
SQL>Startup restrict
Create undo tablespace UNDOTBS2.
SQL> Create undo tablespace undotbs2 datafile ‘/u01/oradata/ocrl/unodtbs02.dbf’ size 1500M;
Make entry in the pfile
Modify the following parameters in pfile and restart the database.
Undo_management=auto Undo_tablespace=’UNDOTBS2’ SQL> drop rollback segment “_SYSSMU3$”; Rollback segment dropped. 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;
Now take database export backup and create new database using dbca and import the database.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp