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'; exit
/tmp/initORCL2.ora, and modify (add if doesn't exist) the
Prevent listener registration, e.g:
Create the new spfile:
sqlplus / as sysdba create spfile='?/dbs/spfileORCL2.ora' from pfile='/tmp/initORCL2.ora'; exit
And continue according to the original steps:
export ORACLE_SID=ORCL2 sqlplus / as sysdba
If you don't use spfile, simply copy and edit the initORCL.ora.
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