ORacle database

[Jyoti Mann]

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

[Coolmar]

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]

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]

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]

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]

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

[Yogeeraj_1]

attachment

[SANTOSH S. MHASKAR]

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]

hi,

a correction of small mistake,

read 'including contents' instead of 'including containts'


-Santosh

[Jyoti Mann]

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

[Volker Borowski]

>>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'

This will not work !
If you create a new TS, you will create a new datafile which will get a new file_id.
The controlfile will know this (in v$datafile) and the datafile itself will know it (in it's header block).
Those two have to match !
Even if you get some wild movement done in any way, you will never ever get the file with ID 1234 in it's headerblock set online then as a file with ID 4321 in the controlfile/v$datafile !

You are loosing time and are of risk, that you will loose or overwrite redologs that you might need for recovery. This risk will become bigger the longer you are waiting.

Did you do the check of dba_extents to verify, if extents are allocated in the missing files?

Did you check, that you have access to/or are able to restore the redologs you require for recovery ?

Again, there is no way to get rid of the files again beside reorganizing the tablespace.

What can be done is :
1) create the file
2) recover the file
3) online the file
4) resize the file to the smallest possible size
5) live with this or do a reorg of the tablespace

You will not be able to spoof a diffrent file in the required location as you are intending. Someone with deep oracle headerblock structure knowlegde and a very good hex-editor might be able to, but this will be beyond any support ever and will surely lead to other types of corruption.

Sorry if this is bad news, but it is the truth.

To check out, what redo information you'll need for recovery, you can check v$datafile, fields

OFFLINE_CHANGE#
and
CREATION_CHANGE#

if the offline was close after the creation, there is a good chance, that you are going to need only a single or a few redologs for recoverey after the create datafile. To map the CHANGE# numbers to the redolog seq. numbers check the alert log.

Good luck
Volker

[Yogeeraj_1]

hi Jyoti,

It is CRUCIAL to understand that once added, a file is part of the database until you DROP THE TABLESPACE it is associated with !!!


To revert back to the initial stage, try to do the following:

alter database recover datafile
'';

alter database datafile '' online;


Assuming that you are running under archive log mode!

let us know.

kind regards
yogeeraj

[Jyoti Mann]

thanks yogeeraj and Volker
thanks for all your help.

I did raise a TAR with ORACLE earlier on this issue.

As i was saying before the datafile which had been deleted from OS , was never used. No data was written to it at all. The file was taken offline as soon as it was created and the physical file was deleted from OS( learned from my silly mistake and wil lnever be so dum again:)
However this is what ORACLE COnsultant saying

SOLUTION / ACTION PLAN
=======================
To implement the solution, please execute the following steps:

1.
Backup controlfile to trace.
------> This will generate trace file in the udump directory

2.
Shutdown the database

3.
Startup the database

If the errors are not generated to the alert log file

But if the errors are generated to the alert log file. Then continue with the following

4.
shutdown the database

5.
Comment the lines of those datailes as in the example below
----->You can comment any line by typing "--" in the beginning of the line

DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_SYSTEM_2QZSOM6J_.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_UNDOTBS1_2QZSOMFD_.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_SYSAUX_2QZSOM8D_.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_USERS_2QZSOMG0_.DBF',
-- 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\DROP_TEST.DBF', ---------->This line is commented
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\NOT_DROP_TEST.DBF'

6.
Startup nomount

7.
Execute the SQL stamtments in the trace file generated as in the example below

---->Start executing from the "CREATE CONTROLFILE" statment
------>This will generate new control file

CREATE CONTROLFILE REUSE DATABASE "ORCL_ARC" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\ONLINELOG\O1_MF_1_2QZSQD09_.LOG',
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL_ARC\ONLINELOG\O1_MF_1_2QZSQG9S_.LOG'
) SIZE 50M,
GROUP 2 (
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\ONLINELOG\O1_MF_2_2QZSQJN6_.LOG',
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL_ARC\ONLINELOG\O1_MF_2_2QZSQLR1_.LOG'
) SIZE 50M,
GROUP 3 (
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\ONLINELOG\O1_MF_3_2QZSQNTY_.LOG',
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL_ARC\ONLINELOG\O1_MF_3_2QZSQQG8_.LOG'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_SYSTEM_2QZSOM6J_.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_UNDOTBS1_2QZSOMFD_.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_SYSAUX_2QZSOM8D_.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\O1_MF_USERS_2QZSOMG0_.DBF',
-- 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\DROP_TEST.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL_ARC\DATAFILE\NOT_DROP_TEST.DBF'
CHARACTER SET WE8MSWIN1252
;

8.
startup

Let me know what you guys think of it.

Thnx
Jyoti

[Yogeeraj_1]

hi,
as long as the data file is not being used by any tablespace, the above would do.

Depending the amount of data/objects stored on the tablespace, I would prefer to do a recovery on the datafile, create a new tablespace, move all the objects to the new tablespace and finally drop the "problem" tablespace.

These are my views...

kind regards
yogeeraj

[Volker Borowski]

Hi,

I doubt that it will work.

If the file was empty when you offlined it, there is absolutly no reason, why you should get the errors from your very first posting (at least not that I know).

Again: Check if there are extents in the file -> dba_extents.

Second: Re-Creating the controlfile will only work as long, as the eliminated fileid will never been accessed again. Obviously your DB does this, otherwise you would not get the errors. When this happens, a new controlfile entry will automaticly been generated (with the file_id you are trying to get rid off) and a generated filename 'MISSING9999' (ask google for that).
I had this with a customer, who copied PRD to QAS after the PRD-DB had been extended, but he created the controlfile with an older script, that did not know about the new files. When data had to be accessed that was in those new files, the entries imediately showed up and errors flooded all over the alert-log :-)

Third: Creating a new controlfile eliminates the fileid from the controlfile and allows you to open the db, but is does not clean up your dictionary-data in your system tablespace (why that? Because CREATE CONTROLFILE brings your database from NOMOUNT to MOUNT, which means your database is not open, so no system tables will be updated accordingly)
- dba_segments
- dba_extents
- dba_free_space
- may_be_more ...
They may all know about the file-id that has been eliminated. So there may remain an inconsistency. I only know this method (creating a spoofy controlfile with some files missing) as a way to recover data if you only want to restore a dedicated part of the database on an other box to extract i.e. a specific table. I would not use it to recover a production database.

On the other hand, since you did not yet state if you can get the redologs, I assume you may run noarchive mode and in this case the given suggestion might very well be the only way out ?!?!?

Volker

[Jyoti Mann]

i am sad to hear none of you r feeling optimistic about the solution Oracle consultant provided.

The files werent written at all. The database do run in archiving mode..so I should have redo logs available

[Volker Borowski]

Ok,
if you have logs, let's check out, what needs to be done. Can you give us the result from this select ?

select
STATUS,
OFFLINE_CHANGE#,
CREATION_CHANGE#,
NAME
from V$DATAFILE
WHERE STATUS<> 'ONLINE';

Volker

[Jyoti Mann]

select
STATUS,
OFFLINE_CHANGE#,
CREATION_CHANGE#,
NAME
from V$DATAFILE
WHERE STATUS<> 'ONLINE';

2 3 4 5 6 7
STATUS OFFLINE_CHANGE# CREATION_CHANGE#
------- --------------- ----------------
NAME
--------------------------------------------------------------------------------
SYSTEM 0 6
/u04/oradata/opsprd/system01.dbf

RECOVER 0 1343316412
/u04/oradata/opsprd/radactdata42

RECOVER 0 1343432247
/u04/oradata/opsprd/radactdata42.dbf


STATUS OFFLINE_CHANGE# CREATION_CHANGE#
------- --------------- ----------------
NAME
--------------------------------------------------------------------------------
RECOVER 0 1343434072
/u04/oradata/opsprd/radactdata43.dbf

[Volker Borowski]

Ok,

looks like the file have not been taken offline inside oracle, as the offline_change still is 0.

Hmmm, it can also be, that this is only a missinformation because the file itself is not accesible any more. I'm not so sure about this. I thought the offline_change# is recorded in v$datafile and the headerblock.

/u04/oradata/opsprd/radactdata42
/u04/oradata/opsprd/radactdata42.dbf
/u04/oradata/opsprd/radactdata43.dbf

Your creation SCNs for these files are splitted a bit

1343316412
1343432247
1343434072

if you offlined them all at the same time, it looks like you would need at least a couple of logs for the first file.

Now check the alertlog, to see if you get all redologs from SCN 1343316412 onwards.
If you have them, you are able to recover !

I would check the behavior of the offline_change# in v$datafile in your test-db like this:
Create a dummy tablespace with a small datafile
Query v$datafile, esp. offline_change#
alter database datafile '...' offline;
Query v$datafile, esp. offline_change#
Rename the datafile on OS level.
Query v$datafile, esp. offline_change#
rename back the file to it's real name.
Query v$datafile, esp. offline_change#
alter database datafile '...' online;

This could prove at least, that the offline_change# in v$datafile goes to 0 wenn you rename the file. If it stays the same after you renamed the file, it shows, that the files have no been taken offline.
If it flips to 0 and comes back after you put the file back in place, you might have more luck, that not so many redologs are needed.

Volker