Solved

database file removed

Posted on 2010-09-09
14
774 Views
Last Modified: 2013-12-19

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
Comment
Question by:sstrange
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 168 total points
ID: 33640183
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
 
LVL 6

Expert Comment

by:anumoses
ID: 33640195
create tablespace ts_something
  logging
  datafile '/dbf1/ts_sth.dbf'
  size 32m
  autoextend on
  next 32m maxsize 2048m
  extent management local;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 33640198
For a restore,  I'm assuming the file really is a "data" file  and not a "temp" file despite the name

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:sridharv9
ID: 33640402
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
 

Author Comment

by:sstrange
ID: 33641941

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
 
LVL 7

Assisted Solution

by:sumit2906
sumit2906 earned 166 total points
ID: 33643644
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
 

Author Comment

by:sstrange
ID: 33664738

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
 
LVL 8

Assisted Solution

by:Rindbaek
Rindbaek earned 166 total points
ID: 33912049
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
 
LVL 8

Expert Comment

by:Rindbaek
ID: 34292154
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 34292247
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
 
LVL 8

Expert Comment

by:Rindbaek
ID: 34292285
fair enough ;-)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34440955
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question