BR0970W Database administration alert - level: ERROR, type:DATA_FILE_MISSIN

[Former Member]

How do i resolve the below error msg, thanks

BR0280I BRCONNECT time stamp: 2009-09-07 07.34.54

BR0969I Checking database administration...

BR0970W Database administration alert - level: ERROR, type: DATA_FILE_MISMATCH, object: /oracle/<SID>/920_64/dbs/MISSING00034

BR0970W Database administration alert - level: ERROR, type: DATA_FILE_MISSING, object: /oracle/<SID>//920_64/dbs/MISSING00034

BR0970W Database administration alert - level: ERROR, type: FILE_OFFLINE, object: /oracle/<SID>/920_64/dbs/MISSING00034

BR0280I BRCONNECT time stamp: 2009-09-07 07.34.57

[Former Member]

Do

select * from v$recover_file;

Please post the results.

This will tell which tablespace the file belongs to and will also give you the file ID.

How come you did not mention the <SID>

When the controlfile was recreated either the file was left out - Oracle finds it missing in the dictionary so puts a "MISSING" entry in its place.

If that is the case, You have three options

1. If you still have the datafile on backup, copy it back and try recreate the controlfile again. You will also need all the archived redo logs from the point the datafile left the database.

2 .If you dont have the datafile on backup but all the archived redologs you can probably recreate the datafile with 'alter database create datafile ' and recover - applying all thre redo.

3. f you dont have the datafile and dont have all the redo then either go back to your last complete backup containing the file.

Pravin

[Former Member]

Hi Pravin

This is the result of

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_

-

---

-

---

-

---

ERROR CHANGE#

-

---

-

---

TIME

-

---

34 OFFLINE OFFLINE

FILE MISSING 0

Thanks

[Former Member]

Hi,

If still the datafiles are missing, check and restore the database from the recent backup....

Thanks and Regards,

[Former Member]

Do a

Select file_id from dba_data_files where file_id =34;

This should give you the file you should be looking for.

If the file is there and for some reason offline, You need to online it.

Pravin

[Former Member]

Hi Pravin

How do i find whether the file exists and see whether its offline. And how do i bring it online....thanks

Select file_id from dba_data_files where file_id =34;

FILE_ID

-

---

34

[Former Member]

What Pravin probably intended was:

Select file_id, file_name from dba_data_files where file_id =34;

This should give you the path where to look.

hope this helps

[Former Member]

Thank You Joe for pointing it out.

At least this will give the location of the file.

But form reading looks like it will be a lost cause as you dont know when the file went offline and you would need all the archive logs since the time it went offline.

But its a good exercise in finding out so that if that if it happens again, then you would know how to fix it.

Pravin

[former_member204746]

what did you do with your system? was this built from a backup% is this a refreshed system?

it looks like you manipulated the control files and forgot to add a few datafiles.

in this state, your database is screwed big time.

time to restore again...

[Former Member]

Thanks Eric, not sure how this system training system was built.

I was running some prechecks before Oracle Upgrade and had come across this issue.

Will try and restore again and do the Upgrade.

Regards

Shaji