No Backup !!! Think Again

While I was drinking my coffee yesterday in the office, A Customer Called me complaining that Non Production Database Is not working and Database Administrator quit his job without any Handover the situation was so creepy, This database hold Fusion middleware Repository and configuration, The development team use it before deploy anything on Production Database ( No Backup configured !!!! ),  

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