Undo data file corrupted, how to do?

[Former Member]

Got following erros in alert log:

Wed Sep 17 00:00:52 2008

Beginning log switch checkpoint up to RBA [0x8b3.2.10], SCN: 34471049

Thread 1 advanced to log sequence 2227

Current log# 3 seq# 2227 mem# 0: M:\ORACLE\NW7\ORIGLOGA\LOG_G13M1.DBF

Current log# 3 seq# 2227 mem# 1: N:\ORACLE\NW7\MIRRLOGA\LOG_G13M2.DBF

Wed Sep 17 00:05:55 2008

Completed checkpoint up to RBA [0x8b3.2.10], SCN: 34471049

Wed Sep 17 00:18:44 2008

Incremental checkpoint up to RBA [0x8b3.250.0], current log tail at RBA

[0x8b3.26a.0]

Wed Sep 17 00:47:50 2008

KCF: write/open error block=0x69 online=1

file=2 P:\ORACLE\NW7\SAPDATA1\UNDO_1\UNDO.DATA1

error=27072 txt: 'OSD-04008: WriteFile() failed.

O/S-Error: (OS 33) The process cannot access the file because

another process has locked a portion of the file.

Automatic datafile offline due to write error on

file 2: P:\ORACLE\NW7\SAPDATA1\UNDO_1\UNDO.DATA1

Wed Sep 17 00:48:17 2008

Errors in file p:\oracle\nw7\saptrace\background\nw7_smon_6056.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'P:\ORACLE\NW7\SAPDATA1\UNDO_1\UNDO.DATA1'

Wed Sep 17 00:48:44 2008

Incremental checkpoint up to RBA [0x8b3.2dd.0], current log tail at RBA

[0x8b3.2dd.0]

Wed Sep 17 00:53:44 2008

Errors in file p:\oracle\nw7\saptrace\background\nw7_smon_6056.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'P:\ORACLE\NW7\SAPDATA1\UNDO_1\UNDO.DATA1'

Wed Sep 17 00:53:46 2008

Errors in file p:\oracle\nw7\saptrace\background\nw7_smon_6056.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'P:\ORACLE\NW7\SAPDATA1\UNDO_1\UNDO.DATA1'

Then the data file UNDO.DATA1 got a status of 'recover'.

Then the cmd RECOVERY DATAFILE 'P:\ORACLE\NW7\SAPDATA1\UNDO_1\UNDO.DATA1'

also got errors:

ORA-00279 : change "string" generated at "string" needed for

thread string

ORA-00289: suggestion :

O:\ORACLE\.....\NW7ARCHARC02227_0654785386.001

ORA-00280: change 34471049 for thread 1 is in sequence #2227

as the offline redo logs were deleted.

The system works fine now as UNDO.DATA2 and UNDO.DATA3 works now.

(Havn't shut down the Instance yet)

How to recover the corrupted data file now?

Could I create another UNDO tablespace PSAPUNDO2 and change to it as per note 600141?

I don't know if it is possible to start DB completely in this situation and suppose it could only open to mount status, is it right?

Looking forward to your help.

Best Regards,

[stefan_koehler]

Hello Mo Ji,

>ORA-00280: change 34471049 for thread 1 is in sequence #2227

>as the offline redo logs were deleted.

>How to recover the corrupted data file now?

You executed the correct steps, but you have to restore the needed archive logfiles. If you don't have them anymore, you have really serious problem and can not recover (easily).

>Could I create another UNDO tablespace PSAPUNDO2 and change to it as per note 600141?

No.. you need to clean this situation first, because you maybe can not rollback some uncommited/failed transactions, if you shutdown your database.

>I don't know if it is possible to start DB completely in this situation and suppose it could only open to mount status, is it right?

If you shutdown your database and try to restart.. the OPEN DATABASE will fail.

Regards

Stefan