Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    Shanghai , China
    Posts
    63

    Unanswered: 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 .
    Attached Files Attached Files
    Oracle is an ocean . I am just a little fish

  2. #2
    Join Date
    Aug 2002
    Location
    Shanghai , China
    Posts
    63
    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 ?)
    Oracle is an ocean . I am just a little fish

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    There could be many reasons for this:

    - bad sector on your disk drive is causing OS to not be able to write Oracle blocks;

    - you have backup software which is trying to backup files by placing lock on them;

    - you have D:\PRODDB\GLOVIA\PROD2A.DBF grown bigger than the OS can handle and the writes attempts are failing;

    - you have virus checking application which lock the file.


    If you redo logs switches are too often, consider adding more groups or members to th eexisting ones.


    Hope that helps,

    clio_usa
    OCP - DBA

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    If your redo logs switch too often, then they are too small. Make them bigger. How much bigger depends on how often you want them to switch. In the training classes I have attended, they recommend a rate of one log switch every 30 minute, but they never give a compelling reason.

    On the other hand, some experts whose opinion's I respect, create extremely large redo-logs. Large enough so that they don't switch during normal activity. Myself, I haven't been able to take the leap of faith from one switch every 30 minutes to never switching without being forced. I pick a size so I get a few switches throughout the day.

    After making the redo logs bigger, I saw a dramatic decrease in wait events due to the excessive log switches.

    HTH,
    Patrick

    Originally posted by clio_usa
    <snip>

    If you redo logs switches are too often, consider adding more groups or members to th eexisting ones.


    Hope that helps,

    clio_usa
    OCP - DBA
    Visit our Web Site

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    It's the same thing - by making them bigger you increse the time before they are getting recycled. The same thing could be done by adding more redo log groups - it will take longer to fill all of the groups and come back to the one swithed last.

    Either ways will help the LGWR process to do its job and not to have to wait for the ARCH to archive the group which it's trying to write to it.

    Hope that helps,

    clio_usa
    OCP - DBA

  6. #6
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Actually, they are not the same. Each log switch causes overhead in the database. SMON and LGWR are busy during a log switch. Increasing the size of the log files decreases the number of log switches, and therfore it decreases this overhead. Increasing the number of log groups does nothing with respect to the number of switches. It just helps insure that LGWR does not wrap around the log groups and cause contention with the ARCH process.

    I believe, in the original post, the poster was concerned with the number of log switches. You reduce log switches by increasing the size of the log files.

    HTH,
    Patrick

    Originally posted by clio_usa
    It's the same thing - by making them bigger you increse the time before they are getting recycled. The same thing could be done by adding more redo log groups - it will take longer to fill all of the groups and come back to the one swithed last.

    Either ways will help the LGWR process to do its job and not to have to wait for the ARCH to archive the group which it's trying to write to it.

    Hope that helps,

    clio_usa
    OCP - DBA
    Visit our Web Site

Posting Permissions

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