Database recovery when datafile lost from physical HDD !

Prob : Datafile is lost physically but the related tablespace is not drop from database

In oracle database 10g , I created a test database name 'support' . I created a tablespace 'support_ts' here .
In my absent mind , I deleted the datafile which is associated to "support_ts" physically .Logically the tablespace information is in database but physically the datafile is deleted .

Lets , 'SUPPORT' database has no backup (export and rman ) ,also the database is in noarchivelog mode .In this moment , is it possible to recover it or can i focely remove the tablespace information from database ? so that , in next startup database don't search the "support_ts" tablespace information ?




Solution :


Recover the datafile is not possible without backup of datafile.

You can recreate the control file and startup the database.

Steps to recrete the control file.

1. Alter database backup controlfile to trace as '';
2. Open the control file and remove the support tablespace and datafile entry in noreset logs part.
3. Connect sys as sysdba
4. Shutdown immediate/ shut abort
5. startup nomount
6. paste the controlfile content of noreset logs
7. after created the controlfile mount and open the database.


startup nomount

Run the below script , without last "comma" :

CREATE CONTROLFILE REUSE DATABASE "SUPPORT" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/dbfs/oradata/support/redo01.log' SIZE 100M,
GROUP 2 '/dbfs/oradata/support/redo02.log' SIZE 100M,
GROUP 3 '/dbfs/oradata/support/redo03.log' SIZE 100M,
GROUP 4 '/orafs/oradata/support/redo04.log' SIZE 100M,
GROUP 5 '/orafs/oradata/support/redo05.log' SIZE 100M,
GROUP 6 '/orafs/oradata/support/redo06.log' SIZE 500M,
GROUP 7 '/orafs/oradata/support/redo07.log' SIZE 500M
DATAFILE
'/dbfs/oradata/support/system01.dbf',
'/dbfs/oradata/support/undotbs01.dbf',
'/dbfs/oradata/support/sysaux01.dbf',
'/dbfs/oradata/support/users01.dbf',
'/dbfs/oradata/support/ababil_ts01.dbf',
'/dbfs/oradata/support/ababil_index01.dbf',
'/dbfs/oradata/support/ababil_ts02.dbf',
'/orafs/support/datafile/ababil_ts03.dbf'
CHARACTER SET WE8ISO8859P1
;


recover databsae ;

alter database open ;