Trying to Recover Database and Open it with Resetlogs will lead to this error :-
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/NPDB/system01.dbf’
This Error is only apart of the problem, To solve it :-
Recover database using file name (Redolog) to get the current filename :-
SQL > Startup mount ;
SQL > select member from v$logfile lf , v$log l where l.status=’CURRENT’ and lf.group#=l.group#;
MEMBER
——————————————-
/u01/app/oracle/oradata/NPDB/redo03.log
SQL > Recover database using backup controlfile until cancel ;
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/NPDB/redo03.log ( From the Above Query ).
Log applied.
Media recovery complete.
Trying to open database :-
SQL > Alter database open Resetlogs ;
Failed !!!! with Another Datafile which is UNDO.
ORA-01110: data file 4:’/u01/app/oracle/oradata/NPDB/UNDOTBS01.DBF’
SQL > Create Pfile from Spfile ;
Modify the pfile and add the hidden parameter :-
_allow_resetlogs_corruption=True
But this Not Solved the problem either.
Again Edit Pfile But this Time :-
undo_management=manual
undo_tablespace=UNDOTBS1
SQL> select name from v$datafile;
NAME
——————————————–
/u01/app/oracle/oradata/NPDB/SYSTEM01.DBF
/u01/app/oracle/oradata/NPDB/UNDOTBS01.DBF
/u01/app/oracle/oradata/NPDB/SYSAUX01.DBF
/u01/app/oracle/oradata/NPDB/USERS01.DBF
……………………
SQL > alter database datafile ‘/u01/app/oracle/oradata/NPDB/UNDOTBS01.DBF’ offline drop;
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping tablespace
Check Segment that Needs Recovery :-
SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status=’NEEDS RECOVERY’;
SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU11$ NEEDS RECOVERY UNDOTBS1
_SYSSMU12$ NEEDS RECOVERY UNDOTBS1
_SYSSMU13$ NEEDS RECOVERY UNDOTBS1
Add the following line to pfile:
_corrupted_rollback_segments = (‘_SYSSMU11$’,’_SYSSMU12$’,’_SYSSMU13$’);
SQL > Startup mount pfile=’/u01/osama.ini’;
SQL > drop rollback segment “_SYSSMU11$” –> Drop all the above Segments ;
Drop the Undo And Recreate it again.
SQL > Startup ;
SQL> create undo tablespace UNDOTBS2
datafile ‘/u01/app/oracle/oradata/NPDB/UNDOTBS02.DBF’ size 1G;
Remove All Hidden Parameter and restore UNDO_Managment Parameter to AUTO and UNDO_TABLESPACE=UNDOTBS2 then :-
SQL> create spfile from pfile;
first Advice Enable RMAN Please
Thank you
Osama Mustafa
Reference :-
1- Doc ID 94114.1
2- Doc ID 1295294.1
3- Doc ID 28226.1