An headache Question: ORA-01114 at DB startup
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

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

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    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 ?)
    Attached Files Attached Files
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758

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

    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

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    redo log file is around 10 MB and log buffer is around 20MB
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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 .
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Yes, you should stop Oracle Service as well.

    Sanjay

  7. #7
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    So do you mean if I use oradim to stop both 2 services and the backup can be much safer?
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  8. #8
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

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

    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.
    Best wishes!
    Dmitri

  9. #9
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    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
    Thanks
    Kishore Kumar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width