An headache Question: ORA-01114 at DB startup

Printable View

11-14-2002, 11:26 PM
ligang

1 Attachment(s)

An headache Question: ORA-01114 at DB startup

The Nonarchived Oracle8.0.5 database on NT is not startup automatically in the morning , in the alert log :
............
alter database "prod" open
Picked broadcast on commit scheme to generate SCNs
Fri Nov 15 08:31:55 2002
Errors in file c:\orant\rdbms80\trace\prodLGWR.TRC:
ORA-01110: data file 9: 'D:\PRODDB\GLOVIA\PROD2A.DBF'
ORA-01114: IO error writing block to file 9 (block # 1)
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 5) Access is denied.
............

Then I startup mount db and issue recover database command, it is Ok and the database can be opened .

This is the second time I face this kind of error . I am thinking there must be some specific reason related with this error .

Gurus : Could you please help to visualize the possbilities to trigger this error . I attach the recent records in alert log . There are some oracle error number and I can see the log switch is quite often .

BTW , I always have question on the log_buffer and redo_log setting .
Based on the official docu. these 2 values should not be set to bigger > 1M , But actually the values in my db is more than 10MB , (But I still see a lot of log switch ?)
11-15-2002, 02:25 AM
SANJAY_G

Re: An headache Question: ORA-01114 at DB startup

Quote:

Originally posted by ligang
BTW , I always have question on the log_buffer and redo_log setting .
Based on the official docu. these 2 values should not be set to bigger > 1M , But actually the values in my db is more than 10MB , (But I still see a lot of log switch ?)

It is tru that the log_buffer > 1 MB will not be of any use but this doesn't apply to redo log file size. Your redo log file can be of any size depending on the transactions. If I am not wrong Orcle recommends a log switch every 20-30 minutes.

What is of 10MB size? log_buffer or your redo log files? BTW Log_buffer size doesn't impact log switch frequency.

HTH
Sanjay
11-15-2002, 03:16 AM
ligang

redo log file is around 10 MB and log buffer is around 20MB
11-15-2002, 03:49 AM
pando

log_buffer setting more than 3MB no extra benefits

redo log should be sized so the log switch are as sanjay suggested

regarding your I/O error, I have seen that many times in NT after making some cold backups
11-15-2002, 05:23 AM
ligang

I start to understand what's the actual cause of this kind of error.
Actually last night coldback takes extra long time than usual .
Maybe it's the cause.

One thing I notice in NT is even after the database shutdown , the locking is still hold on the datafile .
11-15-2002, 05:32 AM
SANJAY_G

Yes, you should stop Oracle Service as well.

Sanjay
11-15-2002, 05:58 AM
ligang

So do you mean if I use oradim to stop both 2 services and the backup can be much safer?
11-15-2002, 11:01 AM
kgb

Re: Re: An headache Question: ORA-01114 at DB startup

Quote:

Originally posted by SANJAY_G
If I am not wrong Orcle recommends a log switch every 20-30 minutes.
What is of 10MB size?

There are different opinions on this: if i have 3 redo log groups which is on different HD, one of them is on shared directory, what for I have to care about log switch interval (20-30 min) ?
I have redo log size 25Mb and it switches several times a day, interval depends on Server load.
11-15-2002, 03:04 PM
Kishore

Why you worry about Log?

You need to worry because it is what determines the amount of data you are going to loose during the system failure. The longer the log switch more likely to loose more data during the failure. It does not mean that you should have a log switch every minute which will be a hit on the performance