Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data File Missing

Posted on 1999-09-17
10
Medium Priority
?
2,019 Views
Last Modified: 2010-05-18
Hi,

When I tried to make a table space off line and delete it later which no longer is used, I found that some one deleted the datafile assigned to this tablespace. We don't have any backups. Right now database is up and normal. But what happens when we shut it down and restart? And how to drop this table space now?

Thanks,

sreeram
0
Comment
Question by:sreeramreddy
[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
  • 5
  • 4
10 Comments
 
LVL 1

Accepted Solution

by:
jrguay_ earned 80 total points
ID: 2055069
First of all.  DON'T PANIC !!!!!
(just kidding ...)

Don't worry.  Now that the tablespace is offline you can drop it and recreate it, the datafile then will be recreated too.

If you shutdown the DB and startup, no problem also, since the tablespace is offline, the startup process wont try to open the datafile.

ADVICE: drop the tablespace now.
0
 

Author Comment

by:sreeramreddy
ID: 2055155
I am not able to make this table space off line. I am getton ORA 1116 error.

Sreeram
0
 
LVL 1

Expert Comment

by:jrguay_
ID: 2055169
Try to drop the tablespace ....
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:sreeramreddy
ID: 2055209
Even when trying to drop the tablespace I am getting ORA 1116, ora 1110 and ORA 27041 errors.

Sreeram
0
 
LVL 1

Expert Comment

by:jrguay_
ID: 2055295
Ok.  here are the steps to solve the problem:

1. go to server manager
2. connect internal
3. shutdown immediate
3. startup mount
4. alter database create datafile 'name of the missing datafile with the complete path'
5. recover datafile 'datafile'
6. alter database open
7. drop tablespace TABLESPACE

That'll do it.  And don't worry, I have tested this procedure in my test DB.

Regards.
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 2059917
Make sure that the filesystem the datafile is on is mounted and accessible from the OS prompt. What OS are you using?
0
 

Author Comment

by:sreeramreddy
ID: 2059974
This is on Sun SParc Solaris 2.6 That file system is there and when I check the space it is showing the size of the datafile as being used but actually there is no file there.
0
 
LVL 1

Expert Comment

by:jrguay_
ID: 2059991
Try rebooting the machine.
0
 

Author Comment

by:sreeramreddy
ID: 2060052
Some reporting processes are running on the server, so I am not even trying to shutdown the database now. As and when there reports are finished I will that.

Thanks,

Sreeram.
0
 

Author Comment

by:sreeramreddy
ID: 2060798
Hi,

First of all thank you very much. I did face a problem because this database is not in Archive mode. So recovery is not possible. So what Oracle support suggested is to do

alter database datafile '/u8/hitlist/hitlist1.dbf' offline drop;

It worked and now database is normal. Even occupied space is also freed now. With out rebooting the system.

SVRMGR> connect internal
Connected.
SVRMGR> shutdown immediate
ORA-01116: error in opening database file 45
ORA-01110: data file 45: '/u8/hitlist/hitlist1.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
SVRMGR> alter database create datafile '/u8/hitlist/hitlist1.dbf' as '/u8/hitlist/hitlist1.dbf' size 2000M;
Statement processed.
SVRMGR> alter database open;
alter database open
*
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45: '/u8/hitlist/hitlist1.dbf'
SVRMGR> recover datafile '/u8/hitlist/hitlist1.dbf';
ORA-00279: change 121814023 generated at 09/14/99 09:24:49 needed for thread 1
ORA-00289: suggestion : /usr/oracle/dbs/arch1_85369.dbf
ORA-00280: change 121814023 for thread 1 is in sequence #85369
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log '/usr/oracle/dbs/arch1_85369.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3



SVRMGR> startup
ORACLE instance started.
Total System Global Area     154897940 bytes
Fixed Size                       44924 bytes
Variable Size                 74571416 bytes
Database Buffers              78643200 bytes
Redo Buffers                   1638400 bytes
Database mounted.
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45: '/u8/hitlist/hitlist1.dbf'
SVRMGR> alter database datafile '/u8/hitlist/hitlist1.dbf' offline drop;
Statement processed.
SVRMGR> alter database open;
Statement processed.
SVRMGR> drop tablespace hitlist;
Statement processed.
SVRMGR> select sysdate from dual;
SYSDATE  
---------
20-SEP-99
1 row selected.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

721 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