sstrange
asked on
database file removed
the database file temp02.dbf was deleted.
How can you recreate the file? I have tried
DROP TABLESPACE temp02 INCLUDING CONTENTS and receive the error:
ORA-00959: tablespace 'TEMP02' does not exist
Thanks for any suggestions
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For a restore, I'm assuming the file really is a "data" file and not a "temp" file despite the name
If you are still looking for solution, post results from following commands.
select file_name, tablespace_name, online_status from dba_data_files;
select file_name, tablespace_name, status from dba_temp_files;
select file_name, tablespace_name, online_status from dba_data_files;
select file_name, tablespace_name, status from dba_temp_files;
ASKER
So I ran the following:
SQL> select * from dba_temp_files where file_name like '%temp02.dbf%';
select * from dba_temp_files where file_name like '%temp02.dbf%'
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/export/oradata/ITDEV/tem
I do not have a backup to restore this from.
Ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am able to take the file offline, but when I go to drop the datafile the database locks up and nothing happens.
Ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi angellll
since no one else wants to comments on this i will do it rsiking my neck.
sdstuber assist with 33640183 to determine the nature of the datafile.
Summit had a go but unfortunately it didn't work. If sstrange returns with news that it was solved based on 33643644 credits shoud go there else i belive i have provided a vaild solution in 33912049.
/Rindbaek
since no one else wants to comments on this i will do it rsiking my neck.
sdstuber assist with 33640183 to determine the nature of the datafile.
Summit had a go but unfortunately it didn't work. If sstrange returns with news that it was solved based on 33643644 credits shoud go there else i belive i have provided a vaild solution in 33912049.
/Rindbaek
I concur, first determine if file is datafile or tempfile, then if datafile then restore, if tempfile then recreate
so split with
http:#33640183
http:#33643644
http:#33912049
so split with
http:#33640183
http:#33643644
http:#33912049
fair enough ;-)
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
logging
datafile '/dbf1/ts_sth.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;