ORA-01111 & ORA-01110 datafile unknown/missing problem

[arpoodle]

I have recently cloned a database, onto a new server with a new SID. the clone has worked succsesfully, and SQL has been run against the database without error.

However, yesterday, the application which will be using the system (Mediasurface) encountered an error, which mediasurface support can't help with, as it's a specific database problem.

the errors are as below:

------------------------------------------------------------------------------------------------------------------
java.sql.SQLException: ORA-00376: file 9 cannot be read at this time
>> ORA-01111: name for data file 9 is unknown - rename to correct file
>> ORA-01110: data file 9: '/usr/local/oracle/product/9.2.0/dbs/MISSING00009'
------------------------------------------------------------------------------------------------------------------

from my understanding of the ORA error codes, it means the datafile is missing.

I can see from V$datafile that there are 3 datafiles associated with TS#9, however, one of these is named as "MISSING00009". I can see the relevant physical file in it's correct location, but when I try and re-create the control files to include this datafile (using the same control-file create script as was used to clone the database originally) it fails to create the controlfile, returning the error that the header of the datafile contains a SID different from that of the server.

is there a way to 're-attach' or remount this single datafile into the tablespace?

tia

a

[oracli]

After you have created the clone what kind of SQLs were working ok?