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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try to drop the tablespace ....
ASKER
Even when trying to drop the tablespace I am getting ORA 1116, ora 1110 and ORA 27041 errors.
Sreeram
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.
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?
ASKER
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.
ASKER
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.
Thanks,
Sreeram.
ASKER
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_8536 9.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_853 69.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.
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'
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'
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_8536
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_853
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'
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.
ASKER
Sreeram