Data File Missing

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
sreeramreddyAsked:
Who is Participating?
 
jrguay_Connect With a Mentor Commented:
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
 
sreeramreddyAuthor Commented:
I am not able to make this table space off line. I am getton ORA 1116 error.

Sreeram
0
 
jrguay_Commented:
Try to drop the tablespace ....
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sreeramreddyAuthor Commented:
Even when trying to drop the tablespace I am getting ORA 1116, ora 1110 and ORA 27041 errors.

Sreeram
0
 
jrguay_Commented:
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
 
mshaikhCommented:
Make sure that the filesystem the datafile is on is mounted and accessible from the OS prompt. What OS are you using?
0
 
sreeramreddyAuthor Commented:
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
 
jrguay_Commented:
Try rebooting the machine.
0
 
sreeramreddyAuthor Commented:
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
 
sreeramreddyAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.