Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

database file removed

Posted on 2010-09-09
14
Medium Priority
?
777 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
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 672 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

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.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…
Suggested Courses

885 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