Given an Oracle 11.1 database used for development, running in NOARCHIVELOG mode, where all datafiles (including undo) have been wiped out; and only SYSTEM and SYSAUX datafiles and control file remain (and even then, just as hot backup of disk taken while database was open; not as database/RMAN backup), is there any way to open a "skeletal" version of the database such that source code and table schemas can be retrieved? No concerns whatsoever for data recovery/integrity/etc; just want to retrieve whatever data dictionary contents that are possible (given these scraps of the original).



In order to start the instance without interfering with an existing running instance (e.g ORCL), before starting anything:

sqlplus / as sysdba
create pfile='/tmp/initORCL2.ora' from spfile='?/dbs/spfileORCL.ora';

Edit /tmp/initORCL2.ora, and modify (add if doesn't exist) the instance_name:


Prevent listener registration, e.g:


Create the new spfile:

sqlplus / as sysdba
create spfile='?/dbs/spfileORCL2.ora' from pfile='/tmp/initORCL2.ora';

And continue according to the original steps:

sqlplus / as sysdba

If you don't use spfile, simply copy and edit the initORCL.ora.


startup nomount

You have lost the UNDO tablespace, so switch to manual undo management:

alter system set undo_management=manual scope=spfile sid='*';

You have lost the redo logs and your remaining datafiles are in an inconsistent state, so you will not be able to open the instance, unless you allow resetlogs corruption:

alter system set "_allow_resetlogs_corruption"=true scope=spfile sid='*';

Mount the database:

startup force mount

You have lost the datafiles, so you will not be able to open the instance, unless you offline drop the datafiles:

select name from v$datafile;

alter database datafile '/database/users01.dbf' offline drop;
alter database datafile '/database/undotbs01.dbf' offline drop;

In order to perform open resetlogs, and incomplete media recovery should be performed (even if it can not do anyhting):

recover database until cancel;

At this point you should be able to open the instance:

alter database open resetlogs;

Should be, because depending on the scale of inconsistency of the remaining SYSTEM datafile, the instance may not open at all, or it may crash immediately or in best case scenario, it may open and remain stable so you can query DBA_SOURCE, SYS.SOURCE$, etc.

| improve this answer | |
  • Fantastic response! May be later this week, but I'll give this a whirl and let you know how it works out. – KevinKirkpatrick May 27 '15 at 9:34
  • [ETA] One question - how would I (or can I) integrate into this approach the need to open this database with a different SID/global name than that with which it crashed? Project team is already at work on new version of dev db under original database name (with all indications that recovery of a couple packages would still be of great help). Last thing I want to do is disrupt that further with this recovery effort. Don't really have alt. env. to attempt recovery on though. – KevinKirkpatrick May 27 '15 at 9:40
  • Updated my answer for this. – Balazs Papp May 27 '15 at 13:59
  • Tried to startup twice, got ORA-00600 both times. SCN incremented slightly between. First attempt: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [2662], [3293], [1796330011], [3293], [1798905637], [4194398], [], [] Second attempt: ORA-00600: internal error code, arguments: [2662], [3293], [1796330015], [3293], [1798905637], [4194398], [], [] – KevinKirkpatrick May 28 '15 at 18:50
  • [eta] should probably mention following SQL> recover database until cancel; ORA-00279: change 14145123635743 generated at 05/28/2015 11:57:25 needed for thread 1 ORA-00289: suggestion : /[trimmed]/2015_05_28/o1_mf_1_1_%u_.arc ORA-00280: change 14145123635743 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL 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: '[trimmed]system01.dbf' ORA-01112: media recovery not started – KevinKirkpatrick May 28 '15 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.