Automatic datafile offline due to write error

[Former Member]

Hi,

Our SAP system are down. In the alert.log file, i found that one of the files are being locked by third party backup software.

I'm new to both oracle and basis, pls advise the steps to recover the database. Thank you.

The error in the alert log file:

Errors in file f:\oracle\p02\saptrace\background\p02_lgwr_3896.trc:

ORA-00345: redo log write error block 8404 count 2

ORA-00312: online log 3 thread 1: 'D:\ORACLE\P02\ORIGLOGA\LOG_G13M1.DBF'

ORA-27072: File I/O error

OSD-04008: WriteFile() failure, unable to write to file

O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.

Sat Oct 25 00:23:12 2008

Errors in file f:\oracle\p02\saptrace\background\p02_lgwr_3896.trc:

ORA-00343: too many errors, log member closed

ORA-00346: log member marked as STALE

ORA-00312: online log 3 thread 1: 'D:\ORACLE\P02\ORIGLOGA\LOG_G13M1.DBF'

Sat Oct 25 00:26:04 2008

Incremental checkpoint up to RBA [0x1c1b.2079.0], current log tail at RBA [0x1c1b.20dc.0]

Sat Oct 25 00:35:18 2008

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

file=5 G:\ORACLE\P02\SAPDATA1\SR3_2\SR3.DATA2

error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file

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 5: G:\ORACLE\P02\SAPDATA1\SR3_2\SR3.DATA2

Sat Oct 25 00:35:19 2008

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

file=5 G:\ORACLE\P02\SAPDATA1\SR3_2\SR3.DATA2

error=27070 txt: 'OSD-04016: Error queuing an asynchronous I/O request.

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 5: G:\ORACLE\P02\SAPDATA1\SR3_2\SR3.DATA2

Sat Oct 25 00:36:00 2008

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

file=7 G:\ORACLE\P02\SAPDATA1\SR3_4\SR3.DATA4

error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file

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 7: G:\ORACLE\P02\SAPDATA1\SR3_4\SR3.DATA4

Sat Oct 25 00:45:49 2008

Errors in file f:\oracle\p02\saptrace\usertrace\p02_ora_3876.trc:

ORA-00600: internal error code, arguments: [kdtdelrow-2], [2], [2], [], [], [], [], []

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

ORA-01110: data file 5: 'G:\ORACLE\P02\SAPDATA1\SR3_2\SR3.DATA2'

[Former Member]

Hi,

From the log i can understand that, the redo log file was presently been accessing by some other process where simaltaneously the oracle is trying to revert back the changes done to previous state, since the lock was present and the oracle cannot proceed further until the redo happens, this led oracle to go offline. Oracle will not proceed further until it do a revert back to previous state from redo file to avoid data inconsistency/corruption.

The other process in this scenario could be your third party back-up tool which was doing incremental backup of your redo logs.

You need to restart the SAP server, Oracle DB and re-configure back-up tool. If the problem keep coming contact the backup tool vendor. You can configure backup activities from SAP rather from thrid party tools.

Regards,

Raju.

[Former Member]

Hi,

Thanks for the replies. It has been solved, but

no solution provided.

Is there anyway I could trace what has been done?

And is there any documentation on common failures on oracle and the detailed sequence of steps to check and perform?

thanks.

[Former Member]

Just wondering:

What kind of 'solution' are you waiting for?

Imho there is no other 'solution' but:

Do never ever let a 3rd party software perform a file system backup of Oracle data files while Oracle is up and running.

At best this kind of 'backup' will be completely useless, for lack of consistency.

At worst it will destroy your database.

(And do you really need to know why your database has been destroyed in exactly the way it has, and not in any other way?)

This subject is a bit beyond the scope of a forum thread, imho; let me suggest you thoroughly study Oracle's backup and recovery concepts. For a start:

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

hope this helps

[Former Member]

hi,

you should shutdown your backup application first. Shutdown SAP and Oracle. Than start it again.

And make sure that your 3rd party backup application can be integrate with BRBACKUP (backup utility from SAP).

ardhian

http://sapbasis.wordpress.com

http://ardhian.kioslinux.com

[Former Member]

Hi,

stop the backup application. After restart the sap server, the alert.log

show:

Errors in file f:\oracle\p02\saptrace\background\p02_lgwr_4284.trc:

ORA-00313: open failed for members of log group 3 of thread 1

Pls advise.

Thank you.

[Former Member]

follow Note 546006 - Problems with Oracle due to operating system errors

Note 491160 - Restore scenarios for lost files of oracle databases

cheers,

-Sunil

[Former Member]

Hi

As alway use this information for research purposes only.

I presume that Oracle and SAP application servers are down.

Now would be a good time to make a backup image of your crippled system.

This way you can always get back to this state if needed.

So do an offline backup if possible.

It looks like a log file is damaged or deleted. You may find that you have a

mirror image of it so this might not be the end of the world.

If you have oracle up to the mount point e.g.

startup mount

then you should be able to acces the v$logfile

select GROUP#,STATUS,MEMBER from v$logfile;

This will show you whether the have a mirror setup for the log file you need.

Make sure all the files outlined in this output exist.

e.g. do a DIR in the parant directory.

Post all the output of v$logfile and whether all the files exist.

regards

Stephen

[Former Member]

This is not a problem of SAP nor Oracle, but of an ill-configured backup software.

Stop this software, then shutdown and restart SAP and Oracle.

At best it will come up and run without any problems.

Then re-configure your backup software to not backup Oracle files.

Read about SAP's brbackup instead.

regards