1748109 Members
4083 Online
108758 Solutions
New Discussion юеВ

Re: ORacle database

 
Jyoti Mann
Occasional Advisor

ORacle database

I have created datfiles ina tablespace in error. I then took the files offline and removed the files from unix file system. it is now giving errors like below;
ORA-01157: cannot identify/lock data file 21 - see DBWR trace file
ORA-01110: data file 21: '/u04/oradata/opsprd/radactdata42'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory



I think may be if a create a new tablespace in the database and try to move the non existing datafiles and then drop the newly created tablespace. Please advise with the correct syntax
18 REPLIES 18
Coolmar
Esteemed Contributor

Re: ORacle database

Hi,

Check out the following link. I think it might help. You have to create a new empty datafile.

http://forums.oracle.com/forums/thread.jspa?threadID=180609

Here is waht is in there that I think might help you:

"What u have done should not have been done at any point of time. U should never delete a datafile from O/S, if u need the space at the O/S u should resize the datafile.

Now create a new, empty datafile to replace the deleted datafile

ALTER DATABASE CREATE DATAFILE '/usr/oracle/orahome/dbs/paging_O5.dbf' AS '/usr/oracle/orahome/dbs/paging_O5_new.dbf';

This statement creates an empty file that matches the deleted file. Oracle looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.

And NEVER delete a datafile "

Hope this helps,
S
Peter Godron
Honored Contributor

Re: ORacle database

Hi,
and welcome to the forums !

When you took the tablespaces (not files) offline the database was not told to remove them from its files, so when you restarted the DB it is looking for those files holding those tablespaces, but you have deleted them.

You could try looking at, but it depends on your setup:
http://www.csee.umbc.edu/help/oracle8/server.815/a67773/performi.htm#19041

Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.

Jyoti Mann
Occasional Advisor

Re: ORacle database

Thanks for your response, i tried creating the new datafile as
alter database create datafile '/dev_db/oradata/opsdev/radius04.dbf' AS '/dev_db/oradata/opsdev/radius03.dbf'

it gives the following error:
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"/dev_db/oradata/opsdev/radius04.dbf"

i then touch radius04.dbf ( created the empty file manually ) and then tried to alter the database statement which given exactly the same error as above


Please advise
Volker Borowski
Honored Contributor

Re: ORacle database

Hi,
concluding from your posts, you deleted the files in production, so it does not make any sense to re-create a file in your dev DB.

With command CREATE DATAFILE you can only create a file that already belongs to the DB (it has to be visible in V$DATAFILE), which means it has to be created beforehand using CREATE TABLESPACE or ALTER TABLESPACE ADD DATAFILE.

Very strange is, that you get errors, after you took the files offline. That means most likely, that the space you provided for the tablespace in the new files has already been used, before you offlined the file, otherwise nobody would need to access the files. You can query dba_data_files for the file-id of the files deleted and crosscheck dba_extents with this id, if there are extents allocated for any segments (and what type of segments) in these datafiles.

Only way out seems to be to recover this, and that means you would either need a backup of the files and all the redologs from backup to the point, where you took the file offline. V$DATAFILE should give you information on this. Or you need to create the datafile and need all redologs from creation of the file to offline.

If you like to test this in DEV DB before, I'd advise to create a new, non-used tablespace before with a small datafile and play around with this one first.

Complete Recovery would be

1) restore the file or CREATE DATAFILE
2) recover datafile 'filename'
and give all the redologs requested.
3) alter datafile online

Other options, if redologs are not available all the way back:
- if the segments in the deleted files are indexes, they should be re-create-able.
- if the segments in the deleted files are rollback segments it may become very tricky
- if the segments in the deleted files belong to tables and do not contain data yet (means the entire stuff still is segment freespace) it may be possible to reorganize the table to re-gain access. If a
select * from table;
succeeds without error, there is a chance, that no data was in that file.
- if the segments in the deleted files belong to tables and already contain data you will loose exactly this data !

Here are some querys you should run:

select count(*) from v$datafile where status <> 'ONLINE';
select STATUS,NAME from v$datafile where status <> 'ONLINE';

are these the ones we are talking about ?

select distinct segment_name
from dba_extents where file_id in
( select file# from v$datafile where status <> 'ONLINE' );

Track the alert.log for the SCN when the files have been set "offline" or to "recover".

... and hopefully you have the redologs ???

Good hunting
Volker
Yogeeraj_1
Honored Contributor

Re: ORacle database

hi jyoti!

Datafiles are not meant to be dropped once added. Attached is a support note with some methods you can use. (Doing an alter database OFFLINE DROP is not a method either).

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: ORacle database

attachment
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
SANTOSH S. MHASKAR
Trusted Contributor

Re: ORacle database

Hi Jyoti,

If u have an export of tables in the tablespace
of which u have deleted datafile (Which is highly not recommended) OR if u don't have any important data in it then u can drop the tabelspace
itself

SQL> drop tablespace including containts and datafiles cascade constraints;

then recreate the tablespace with same name,

then import tables if u have.


-Santosh
SANTOSH S. MHASKAR
Trusted Contributor

Re: ORacle database

hi,

a correction of small mistake,

read 'including contents' instead of 'including containts'


-Santosh
Jyoti Mann
Occasional Advisor

Re: ORacle database

Hi everyone..
I think we r moving away from the problem here. So i wil give yo more information
The datfile was created in error , the datafile was taken offline and the physical file from the unix file system was then
removed.
The database is still open and working but throwing error as mention earlier. It basically looking for the physical file which it cant find.
I dont wish to take the tablespace or database offline.

This is what i have in mind but not too sure how ,or if it will work.

step1: Create a new tablespace,mounted on a different location.
step2: Alter database rename file 'the file in error from old mounted point' to ' new location'
if this works ..then drop the new tablespace with its content.


Any comments on this please feel free to discuss.

Thank you
Jyoti