Oracle Database Discussions

Hi All,

My currently configuration is as follows

DB: 11.1.0.6.0
OS: Enterprise Linux 5.2

I was away most of last week and only came into the office to be greeted by the error below

ORA-00372: file 318 cannot be modified at this time

SQL> SELECT *
FROM v$recover_file;
  2
     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
       318 OFFLINE OFFLINE CANNOT OPEN FILE              0

SQL>

I dug a little dipper and discovered a datafile was deleted on the 12th.

Fri Mar 12 12:46:28 2010
ALTER DATABASE DATAFILE '/oradata13/zambia/cdrstorage131.dbf' RESIZE 10752M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '/oradata13/zambia/cdrstorage131.dbf' RESIZE 10752M ...
Fri Mar 12 12:47:24 2010
ALTER DATABASE DATAFILE '/oradata13/zambia/cdrstorage147.dbf' RESIZE 10752M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '/oradata13/zambia/cdrstorage147.dbf' RESIZE 10752M ...
Fri Mar 12 12:56:48 2010
ALTER TABLESPACE AGGREGATES ADD DATAFILE  '/oradata04/zambia/cdrstorage148.dbf' SIZE 30720M REUSE
Fri Mar 12 13:00:35 2010
Completed: ALTER TABLESPACE AGGREGATES ADD DATAFILE  '/oradata04/zambia/cdrstorage148.dbf' SIZE 30720M REUSE
Fri Mar 12 13:00:45 2010
Thread 1 advanced to log sequence 573213
  Current log# 5 seq# 573213 mem# 0: /var/zambia/ZAMBIA/onlinelog/redo5_1.log
  Current log# 5 seq# 573213 mem# 1: /oradata01/app/oracle/flash_recovery_area/ZAMBIA/onlinelog/redo5_2.log
Fri Mar 12 13:05:24 2010
ALTER TABLESPACE CDRSTORAGE ADD DATAFILE  '/oradata04/zambia/cdrstorage148.dbf' SIZE 30720M REUSE
ORA-1537 signalled during: ALTER TABLESPACE CDRSTORAGE ADD DATAFILE  '/oradata04/zambia/cdrstorage148.dbf' SIZE 30720M REUSE ...
Fri Mar 12 13:08:50 2010
ALTER DATABASE DATAFILE '/oradata04/zambia/cdrstorage148.dbf'  OFFLINE DROP
Completed: ALTER DATABASE DATAFILE '/oradata04/zambia/cdrstorage148.dbf'  OFFLINE DROP
Fri Mar 12 13:09:25 2010
ALTER TABLESPACE CDRSTORAGE ADD DATAFILE  '/oradata04/zambia/cdrstorage148.dbf' SIZE 30720M REUSE
ORA-1537 signalled during: ALTER TABLESPACE CDRSTORAGE ADD DATAFILE  '/oradata04/zambia/cdrstorage148.dbf' SIZE 30720M REUSE ...
Fri Mar 12 13:14:06 2010
ALTER TABLESPACE AGGREGATES DROP DATAFILE '/oradata04/zambia/cdrstorage148.dbf'
ORA-3264 signalled during: ALTER TABLESPACE AGGREGATES DROP DATAFILE '/oradata04/zambia/cdrstorage148.dbf' ...
Fri Mar 12 13:14:43 2010
ALTER DATABASE DATAFILE '/oradata04/zambia/cdrstorage148.dbf'  ONLINE
ORA-1113 signalled during: ALTER DATABASE DATAFILE '/oradata04/zambia/cdrstorage148.dbf'  ONLINE ...
Fri Mar 12 13:15:03 2010
ALTER TABLESPACE AGGREGATES DROP DATAFILE '/oradata04/zambia/cdrstorage148.dbf'
ORA-3264 signalled during: ALTER TABLESPACE AGGREGATES DROP DATAFILE '/oradata04/zambia/cdrstorage148.dbf' ...
Fri Mar 12 13:16:10 2010
Thread 1 advanced to log sequence 573214
  Current log# 6 seq# 573214 mem# 0: /var/zambia/ZAMBIA/onlinelog/redo6_1.log

Unfortunately, I do not have a valid backup (we only export base tables on a daily basis).

1. How do I check for objects affected by the missing datafile?
2. Most of the data that sits on the affected TABLESPACE can easily be re-computed, but how do I go
about sorting this issue out?
3. As far as the database is concerned, the datafile needs media recovery; how do I sort this issue out?


Regards,
Phiri