Oracle: Recover source code/table structures from “utterly wiped out” development database

disaster recoveryoracleoracle-11g

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).

Best Answer

UPDATE

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

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

*.instance_name='ORCL2'

Prevent listener registration, e.g:

*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DUMMY)))'

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.

/UPDATE

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.

Related Question