Errors in Alert Log File --please help

[anuragmin]

Hi,
I am having the following errors in my Alert Log File.

Fri Jul 20 11:59:10 2001
Errors in file C:\ORANT\rdbms80\trace\sid9LGWR.TRC:
ORA-00345: redo log write error block 8520 count 2
ORA-00312: online log 1 thread 1: 'C:\ORANT\DATABASE\LOGSID91.ORA'
ORA-27072: skgfdisp: 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.

Fri Jul 20 11:59:10 2001
Errors in file C:\ORANT\rdbms80\trace\sid9LGWR.TRC:
ORA-00346: log member marked as STALE
ORA-00312: online log 1 thread 1: 'C:\ORANT\DATABASE\LOGSID91.ORA'


And this is what is there in the lgrw trace file
ORA-00345: redo log write error block 8520 count 2
ORA-00312: online log 1 thread 1: 'C:\ORANT\DATABASE\LOGSID91.ORA'
ORA-27072: skgfdisp: 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.
ORA-00346: log member marked as STALE
ORA-00312: online log 1 thread 1: 'C:\ORANT\DATABASE\LOGSID91.ORA'

Please suggest what should I do about this problem

Thanks
Anurag

[sambavan]

Check the dba_locks to track the session that is holding the lock and the sessions that are waiting for the lock. Then release the lock, by analyzing the code that was causing the lock. It could be a scenario where the session is locking the entire table or some thing in the code. So check the code causing the problem.

Sam

[anuragmin]

Thanks Sam,
Here are the locks which are held currently

Please Suggest

USERNAME SID TY NAME LOC DESCRIPTION
------------ ---- -- -------------------- --- ---------------------------------
(system) 0 RT Redo thread global enqueue lock
(system) 0 TS New block allocation enqueue lock


Thanks
Anurag

[sambavan]

Do you have the database in the archive mode? The logfile seems to be in an incomplete state.

Sam

[anuragmin]

The database is in No archive log mode.


Anurag

[sambavan]

Is it your production db? How many members/group do you have? Can you afford to loose the redo information in it?

Sam

[anuragmin]

Yes , It is a production database .

Following are the groups/members in it.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
--------- --------- --------- --------- --------- --- ---------------- ------------- ---------
1 1 17107 5243904 2 NO CURRENT 24163495 20-JUL-01
2 1 17106 5243904 2 NO INACTIVE 24162941 20-JUL-01

SQL> select * from v$logfile;

GROUP# STATUS
--------- -------
MEMBER
----------------------------------------------------------------------------------------------------
1
C:\ORANT\DATABASE\LOGSID91.ORA

2
C:\ORANT\DATABASE\LOGSID92.ORA

1
C:\ORANT\DATABASE\LOGSID91A.ORA

2
C:\ORANT\DATABASE\LOGSID92A.ORA


Please suggest what should I do and why has this happenned also because I wouldnt want this to happen again.

Thanks a Lot

Anurag

[sambavan]

Add some more groups and members to your instance. I always personally would prefer to have minimum of 5 Groups and 2 members per group.

BTW, are you still getting that error message? What is your current database status?

Sam

[anuragmin]

Hi Sam,
How will adding more groups / member help in this situation.

And the funny thing is now I am not getting the error. Any reasons why.

what are the implications of this error which I got.

Thanks
Anurag

[sambavan]

Yeah, that could be because, when the process was looking to acquire the redo log and the redo log has some incomplete transaction and as a result the log writer had to wait and this would lead to a kind of dead lock. So adding more logs would give the log writer enough time to keep rotating.

Hope this would explain.
Sam