datafile needs media recovery

[krisvdb]

Hi,

I mistakenly added a datafile to tablespace CWMLITE. Since I'm running in archive mode, I took the datafile offline (like Oracle mentions in note: 111316.1).

ALTER DATABASE DATAFILE OFFLINE;

Now the datafile is still 1G in size (i took it immediately offline, so no data is in it). When I try to put it online again to resize I get ora-01113: needs mediarecovery (which I think is normal for every datafile you want to put online again).

How can I get the datafile back online ? I do not have the archives anymore, but would I need them anyway to get it back online ?

If I can not get it back online, would there be any problem when I startup the database again ?
The status in v$datafile is RECOVER and in dba_data_files the status is AVAILABLE, shouldn't that be OFFLINE ?

Thanks,
Kris

[davey23uk]

I think without the Archive Logs your only option is to export the data in the tablespace, drop and recreate the tablespace and re-import

[davey23uk]

also take a look at Doc iD 286691.999 on Metalink for something else to try

[truma1]

what is the result if you do an alter database recover file 'finename'

[adewri]

Originally posted by davey23uk
I think without the Archive Logs your only option is to export the data in the tablespace, drop and recreate the tablespace and re-import

He has clearly mentioned that his database is in archived log mode.

krisvdb, you just need to recover your data file with recover command. All archives will be applied automatically to bring it to a consistent state.

[davey23uk]

and he clearly stated he doesnt have his archive logs

[krisvdb]

a recovery doesn't work, oracle asks for an archive that I don't have anymore.

I think I'll just do an export, drop tablespace, import.
CMWLITE is for OLAP, and we don't use it so I think it's safe.

Thanks for the replies.
Kris

[adewri]

Originally posted by davey23uk
and he clearly stated he doesnt have his archive logs

hmm.. i think i need a coffee.

[abhaysk]

Originally posted by adewri
hmm.. i think i need a coffee.

Amar :

All these days i thought u were kinda poor at maths, but now reading also?

Jus kidding :-)