Solved

database file removed

Posted on 2010-09-09
14
766 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 73

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 73

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

705 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now