ORacle database

[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