How to bring Oracle Database Back from the Dead

How to bring Oracle Database Back from the Dead or how to open an Oracle database with missing (or deleted, or lost) archive logs.

Here is the "typical" scenario you need to open/recover oracle database but you do not have the archive log files - that pretty much renders your Oracle database useless except you do know that that the data is still there and there MUST be a way to open the database without the archive logs and “reset” logfiles…

Here is how to open Oracle Database without archive logs:

1. Shutdown your database.
2. Set the following parameter in your init.ora files (you might need to create pfile from spfile)

_allow_resetlogs_corruption=true
3. Mount your database and issues “alter database open resetlogs”
4. The database will attempt to open but will crash.
5. Edit your init.ora file and and perform the following:

Remove _allow_resetlogs_corruption=true entry
Add undo_management=manual
6. Mount your database
7. Recover database using “recover database command”
8. Open your database – “miracle” your database will open. “alter database open” do not user “alter database open resetlogs”

BUT (It’s a big BUT) the database is not ready yet

Your UNDO tablespace is still in manual mode and the original UNDO tablespace is still corrupted.

Here is how to fix that:

1. Create new undo tablespace i.e UNDOTBS2
2. Set UNDOTBS2 as default undo tablespace
3. Remove undo_management=manual from init.ora
4. Bounce your database


Follow below step to drop your “old” tablespace:

1 – Identify the bad segment -

select
segment_name,
status
from
dba_rollback_segs
where
tablespace_name='undotbs_corrupt'
and
status = ‘NEEDS RECOVERY’;


SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU22$ NEEDS RECOVERY

2. Bounce the instance with the hidden parameter “_offline_rollback_segments”, specifying the bad segment name:

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$


3. Bounce database, nuke the corrupt segment and tablespace:
SQL> drop rollback segment "_SYSSMU22$";
Rollback segment dropped.

SQL > drop tablespace undotbs including contents and datafiles;
Tablespace dropped.
Now you are done …