Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

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
0
sstrange
Asked:
sstrange
  • 3
  • 3
  • 2
  • +4
3 Solutions
 
sdstuberCommented:
you have combined two different ideas and syntaxes.

if the datafile temp02.dbf was deleted, whatever tablespace was associated with it is now corrupt
try these queries...

select * from dba_data_files where file_name like '%temp02.dbf%'
select * from dba_temp_files where file_name like '%temp02.dbf%'

that tablespace doesn't have to be called TEMP02,  and based on your error, it isn't.

then you can decide if you want to drop that tablespace, or if you want to try to restore the file.
If the latter, you will need to have a backup to restore from.
0
 
anumosesCommented:
create tablespace ts_something
  logging
  datafile '/dbf1/ts_sth.dbf'
  size 32m
  autoextend on
  next 32m maxsize 2048m
  extent management local;
0
 
sdstuberCommented:
For a restore,  I'm assuming the file really is a "data" file  and not a "temp" file despite the name

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sridharv9Commented:
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;
0
 
sstrangeAuthor Commented:

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/temp02.dbf'

I do not have a backup to restore this from.

Ideas?
0
 
sumit2906Commented:
If it is only a temp file, you can try this:
ALTER DATABASE TEMPFILE '/export/oradata/ITDEV/temp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/export/oradata/ITDEV/temp02.dbf' DROP;

ALTER TABLESPACE <tablespace_name> ADD TEMPFILE '<path_and_file_name>' SIZE 500M;
0
 
sstrangeAuthor Commented:

I am able to take the file offline, but when I go to drop the datafile the database locks up and nothing happens.

Ideas?
0
 
RindbaekCommented:
if its a temp tablespace  you have dropped the file from, i suggestc that you create a new temp tablespace and alter the default temp tablespace for the system and users.
it goes something like this
CREATE TEMPORARY TABLESPACE temp01
      TEMPFILE '/oradata/temp01_01.dbf' SIZE 256M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16 M autoextend on next 128M max size 4G;

--Change default temporary tablespace (im not sure if you had this in oracle 8):
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;


--If you dont have to many users you can run this and copy paste the outcome of the select below  to change the temp tablespace of the users, you may need to modify the where clause....

set pagesize 999;
set linesize 200;
select 'ALTER USER ' || USERNAME || ' TEMPORARY TABLESPACE temp01;' from dba_users where TEMPORARY_TABLESPACE='TEMP';

When thats done and everything works drop the old temp tablespace
 
0
 
RindbaekCommented:
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

0
 
sdstuberCommented:
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
0
 
RindbaekCommented:
fair enough ;-)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now