Link to home
Start Free TrialLog in
Avatar of sreeramreddy
sreeramreddy

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of jrguay_
jrguay_

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sreeramreddy
sreeramreddy

ASKER

I am not able to make this table space off line. I am getton ORA 1116 error.

Sreeram
Try to drop the tablespace ....
Even when trying to drop the tablespace I am getting ORA 1116, ora 1110 and ORA 27041 errors.

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