How to fix a missing datafile with status recover

[Former Member]

Dear all,

We have Oracle 10 database. And we create a datafile by mistake, and also by mistake we drop the datafile.

Now the condition is, the datafile is missing but still mentioned in control file, and produce an error every time we do a backup since there's a file missing.

If we select from $vdatafile, the datafile status is recover.

We tried to offline the datafile, but its no help.

How to fix this?

Thanks.

[stefan_koehler]

Hello Fendhy,

> We have Oracle 10 database. And we create a datafile by mistake, and also by mistake we drop the datafile.

In this case it is very important, that you tell us exactly the steps that you have done. Did you take the data file offline, etc.

The easiest way is to restore and recover the data file and drop it after that.

Regards

Stefan

[Former Member]

Dear Stefan,

Not need to restore/recover datafile in this scenario.

Jst run above statement problem will resolve.

Surendra Jain

[stefan_koehler]

Hey Surendra,

of course you have to do it. Just check this test case on an Oracle 10.2.04.

 
SQL> create tablespace droptest datafile '/oracle/TST/oradata/droptest.dbf' SIZE 1M;
SQL> create table dropt (a number) tablespace droptest;
SQL> insert into dropt values (1);
SQL> commit;

SQL> select * from dropt;
         A
----------
         1

SQL> select name, status from v$datafile;
NAME                                                         STATUS
------------------------------------------------------------ -------
....
/oracle/TST/oradata/droptest.dbf                             ONLINE
....

SQL> !rm -f /oracle/TST/oradata/droptest.dbf

SQL> alter system flush  buffer_cache;
SQL> select * from dropt;
select * from dropt
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oracle/TST/oradata/droptest.dbf'

SQL> select name, status from v$datafile;
NAME                                                         STATUS
------------------------------------------------------------ -------
....
/oracle/TST/oradata/droptest.dbf                             RECOVER
....

SQL> ALTER DATABASE DATAFILE '/oracle/TST/oradata/droptest.dbf' OFFLINE DROP;
Database altered.

SQL> select name, status from v$datafile;
NAME                                                         STATUS
------------------------------------------------------------ -------
....
/oracle/TST/oradata/droptest.dbf                             RECOVER
....

SQL> shutdown immediate;
SQL> startup
ORACLE instance started.
...
...
Database mounted.
Database opened.

SQL> select name, status from v$datafile;
NAME                                                         STATUS
------------------------------------------------------------ -------
....
/oracle/TST/oradata/droptest.dbf                             RECOVER
....

His question was "iIf we select from $vdatafile, the datafile status is recover. We tried to offline the datafile, but its no help." ... and this status will still be kept until you recover your data file.

Regards

Stefan

[Former Member]

Thanks for replying (you even make a test wow.. thanks a lot)

We already tried to drop and take offline the datafile as Stefan do the test above, buts its no help.

Assume that we dont have oraarch to recover the datafile, is there anything we can do to fix this?

Edited by: Fendhy Ongko on Aug 11, 2009 12:17 PM

[stefan_koehler]

Hello Fendhy,

> We already tried to drop and take offline the datafile, buts its no help.

I know .. the proof of my statement was for Surendra.

> Assume that we dont have oraarch to recover the datafile, is there anything we can do to fix this?

Uhhh bad. In this case you can just recreate the control files out of a control file script without that missing data file.

For more information about this scenario .. please check sapnote #491160 (Point 10 - if the database is shut down).

Regards

Stefan

[Former Member]

Dear,

Take datafile just offline,

alter database datafile '/oracle/LTD/sapdata3/ltd_5/ltd.data5' offline;

Recreate Datafile again at the same location mentioned in control file

alter database create datafile '/oracle/LTD/sapdata3/ltd_5/ltd.data5' as '/

oracle/LTD/sapdata3/ltd_5/ltd.data5';

Make data file online again and restart the server. Hope it would not ask for recovery.

alter database datafile '/oracle/LTD/sapdata3/ltd_5/ltd.data5' online;

Regards

[Former Member]

Hi Stefan,

Cool....Your example is correct.

offline drop statement fix the problem.

Fendhy ,

try and take backup.

u can run below and what is the error message whn u run this

ALTER TABLESPACE <XYZ> DROP DATAFILE '<FILE_NAME>';

Surendra

[stefan_koehler]

Hello Surendra,

> Cool....Your example is correct. offline drop statement fix the problem.

No it isn't fixing the problem .. that was the point that i tried to show you. The data file still keep in status RECOVER. You will only fix this status by recovering, taking it online and dropping it.

But as Fendhy already said .. he has no archive logs anymore .. this is also not an option. The only way to fix this is to recreate the control files with a script.

Regards

Stefan

[Former Member]

HI,

Database ignore the status of that file.

[Former Member]

Sorry to bump this thread again,

I tried to get the same error in a Oracle sandbox server.

I deleted a datafile, so i got a missing datafile, and then i offlined the datafile.

Then, i created a backup control file and deleted line that refer to the error datafile, and then run it as a sql script to create a new control file.

But when a new control file was created, the control file got a missing datafile and offlined it automatically.

This is the control file backuped to a trace file, please notice datafile DATA00001

 STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_58SZY41W_.LOG'  SIZE 50M,
  GROUP 2 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_58SZY59P_.LOG'  SIZE 50M
-- STANDBY LOGFILE

DATAFILE
  'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF',
  'C:\ORACLEXE\ORADATA\XE\UNDO.DBF',
  'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF',
  'C:\ORACLEXE\ORADATA\XE\USERS.DBF',
  'C:\ORACLEXE\ORADATA\XE\DATA00001',
  'C:\ORACLEXE\ORADATA\XE\DATA00002',
  'C:\ORACLEXE\ORADATA\XE\DATA00003'
CHARACTER SET AL32UTF8
;

-- Take files offline to match current control file.
ALTER DATABASE DATAFILE 'C:\ORACLEXE\ORADATA\XE\DATA00001' OFFLINE DROP;

and so on... 


This is the script to recreate

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS  NOARCHIVELOG


DATAFILE
  'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF',
  'C:\ORACLEXE\ORADATA\XE\UNDO.DBF',
  'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF',
  'C:\ORACLEXE\ORADATA\XE\USERS.DBF',
  'C:\ORACLEXE\ORADATA\XE\DATA00002',
  'C:\ORACLEXE\ORADATA\XE\DATA00003'
CHARACTER SET AL32UTF8
; 

This is the result

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS  NOARCHIVELOG
    
DATAFILE
  'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF',

  'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\MISSING00005',
  'C:\ORACLEXE\ORADATA\XE\DATA00002',
  'C:\ORACLEXE\ORADATA\XE\DATA00003'
CHARACTER SET AL32UTF8

-- Take files offline to match current control file.
ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\MISSING00005' OFFLINE;

and so on..

Edited by: Fendhy Ongko on Sep 11, 2009 12:16 PM

[stefan_koehler]

Hello Fendhy,

you are right .. i didn't think about that the datafile is still "registered" in the data dictionary and renamed to "MISSING<XXX>".

After your post i also tried some things in my test environment, but didn't get it without a recovery (archive logs needed).

So to completely clean out this "special" situation you need the archive logs.

Regards

Stefan

[Former Member]

Hello,

just a few remarks to the excellent points mentioned by Stefan in this discussion:

-> if by chance you have the problem with a DMTS tablespace and the datafile in question does not contain any extents, you still have the possibility to drop the datafile with the 'alter tablespace...drop datafile...' command. Using this simple version of managing extent information in dictionary tables has at least one advantage here. Metalink note 781225.1 gives you a complete list of restrictions for dropping datafiles.

-> the 'offline drop' command is usually not helping with problems like this one. I would even go sofar and state that it should have a different syntax which would then not imply that it's completly dropping a datafile

-> only starting with Oracle 10, the 'drop datafile' command is really doing what it is supposed to do

-> if the redo logs are really no longer available, export/import of the tablespace would be the only remaining option. (of course, if it's just a pure index tablespace this would not even be necessary).

Best Regards,

Michael

[Former Member]

The problem is, we dont have the archive log to restore.

Can i exclude a datafile in an online backup?

Thanks.

[Former Member]

It is not possible to only exclude that file duirng an online backup.

Pravin

[Former Member]

Hello,

The problem is that as soon as the datafile is offline the command to put the tablespace into backup mode will fail. If you are using rman it will work (rman does not rely on setting the backup mode for tablespaces).

Best Regards,

Michael

[Former Member]

Hi,

Try below command

ALTER DATABASE DATAFILE u2018<Path\Name>u2019 OFFLINE DROP;

Surendra Jain