Corrupt Logfile
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Corrupt Logfile

  1. #1
    Join Date
    Aug 2000
    Location
    Straham NH
    Posts
    73

    Corrupt Logfile

    I have a database that will not start. I can not perform an alter database open. I have a corrupted log file. The log file is the current logfile. I'm using 10gR1 on Windows 2000 server. The database is not in archive log mode. I also can not get to the managment screens on the machine. The service will not start. Is there any what to get the logfile removed while in the mount state?

  2. #2
    Join Date
    Jun 2006
    Posts
    259
    Can you post the alert log messages and relevant error codes?
    Is your redo log group multi plexed? ie multiple members per group?

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    If the database is not in archivelog mode, which logfile is corrupt?
    I remember when this place was cool.

  4. #4
    Join Date
    Aug 2000
    Location
    Straham NH
    Posts
    73
    Current Logfile is corrut. We have had a recent history of this machine stopping all processes at different points dount the runs. The alert logs never show a database problem (at this point in time we believe this to be a hardware powersuupply issue). The alter database open command responds that the current relod log group file is curputed at Number therad 1 then stops trying to open the database.


    Alert log just before system chrash:
    Sun Jul 02 23:57:09 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2032
    Current log# 1 seq# 2032 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP1LOG.DBF
    Sun Jul 02 23:58:16 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2033
    Current log# 2 seq# 2033 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP2LOG.DBF
    Sun Jul 02 23:59:22 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2034
    Current log# 3 seq# 2034 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF
    Mon Jul 03 00:02:12 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2035
    Current log# 6 seq# 2035 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\G6LOG.DBF
    Mon Jul 03 00:03:34 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2036
    Current log# 5 seq# 2036 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\G5LOG.DBF
    Mon Jul 03 00:04:46 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2037
    Current log# 1 seq# 2037 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP1LOG.DBF
    Mon Jul 03 00:05:46 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2038
    Current log# 2 seq# 2038 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP2LOG.DBF
    Mon Jul 03 00:06:54 2006
    Private_strands 18 at log switch
    Thread 1 advanced to log sequence 2039
    Current log# 3 seq# 2039 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF


    Alert log duing atempt to alter database open:

    License high water mark = 1
    Instance terminated by USER, pid = 1820
    Wed Jul 05 15:52:35 2006
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    Picked latch-free SCN scheme 2
    KCCDEBUG_LEVEL = 0
    Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
    Autotune of undo retention is turned on.
    Dynamic strands is set to TRUE
    Running with 1 shared and 18 private strand(s). Zero-copy redo is FALSE
    IMODE=BR
    ILAT =18
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    Starting up ORACLE RDBMS Version: 10.1.0.2.0.
    System parameters with non-default values:
    processes = 150
    __shared_pool_size = 79691776
    __large_pool_size = 4194304
    __java_pool_size = 8388608
    sga_target = 167772160
    control_files = D:\ORACLE\PRODUCT\10.1.0\ORADATA\ROWELLS\CONTROLFILE\O1_MF_292QM28R_.CTL, D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ROWELLS\CONTROLFILE\O1_MF_292QM3J9_.CTL
    db_block_size = 8192
    __db_cache_size = 71303168
    compatible = 10.1.0.2.0
    db_file_multiblock_read_count= 16
    db_create_file_dest = D:\oracle\product\10.1.0\oradata
    db_recovery_file_dest = D:\oracle\product\10.1.0\flash_recovery_area
    db_recovery_file_dest_size= 2147483648
    undo_management = AUTO
    undo_tablespace = UNDOTBS1
    remote_login_passwordfile= EXCLUSIVE
    db_domain =
    dispatchers = (PROTOCOL=TCP) (SERVICE=ROWELLSXDB)
    job_queue_processes = 10
    background_dump_dest = D:\ORACLE\PRODUCT\10.1.0\ADMIN\ROWELLS\BDUMP
    user_dump_dest = D:\ORACLE\PRODUCT\10.1.0\ADMIN\ROWELLS\UDUMP
    core_dump_dest = D:\ORACLE\PRODUCT\10.1.0\ADMIN\ROWELLS\CDUMP
    sort_area_size = 65536
    db_name = ROWELLS
    open_cursors = 300
    pga_aggregate_target = 25165824
    PMON started with pid=2, OS id=1916
    MMAN started with pid=3, OS id=1708
    DBW0 started with pid=4, OS id=1292
    LGWR started with pid=5, OS id=1756
    CKPT started with pid=6, OS id=1140
    SMON started with pid=7, OS id=1524
    RECO started with pid=8, OS id=1772
    Wed Jul 05 15:52:37 2006
    starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
    CJQ0 started with pid=9, OS id=2100
    Wed Jul 05 15:52:37 2006
    starting up 1 shared server(s) ...
    Wed Jul 05 15:52:39 2006
    ALTER DATABASE MOUNT
    Wed Jul 05 15:52:39 2006
    Controlfile identified with block size 16384
    Wed Jul 05 15:52:47 2006
    Setting recovery target incarnation to 2
    Wed Jul 05 15:52:50 2006
    Successful mount of redo thread 1, with mount id 1277176455
    Wed Jul 05 15:52:50 2006
    Database mounted in Exclusive Mode.
    Completed: ALTER DATABASE MOUNT
    Wed Jul 05 15:53:05 2006
    alter database open
    Wed Jul 05 15:53:12 2006
    Beginning crash recovery of 1 threads
    Wed Jul 05 15:53:15 2006
    Started first pass scan
    Wed Jul 05 15:53:32 2006
    Errors in file d:\oracle\product\10.1.0\admin\rowells\udump\rowells_ora_1996.trc:
    ORA-00313: open failed for members of log group 3 of thread 1

    Wed Jul 05 15:54:29 2006
    Aborting crash recovery due to error 368
    Wed Jul 05 15:54:29 2006
    Errors in file d:\oracle\product\10.1.0\admin\rowells\udump\rowells_ora_1996.trc:
    ORA-00368: checksum error in redo log block
    ORA-00353: log corruption near block 25243 change 17595633 time 07/03/2006 00:09:39
    ORA-00312: online log 3 thread 1: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF'

    ORA-368 signalled during: alter database open...


    Command Prompt Actions:
    Microsoft Windows 2000 [Version 5.00.2195]
    (C) Copyright 1985-2000 Microsoft Corp.

    C:\Documents and Settings\gpmazzone>set oracle_sid=rowells

    C:\Documents and Settings\gpmazzone>sqlplus /nolog

    SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 5 15:51:03 2006

    Copyright (c) 1982, 2004, Oracle. All rights reserved.

    SQL> connect / as sysdba
    Connected.
    SQL> alter database mount
    2 /
    alter database mount
    *
    ERROR at line 1:
    ORA-00750: database has been previously mounted and dismounted


    SQL> startup mount;
    ORA-01081: cannot start already-running ORACLE - shut it down first
    SQL> shutdown abort;
    ORACLE instance shut down.
    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 167772160 bytes
    Fixed Size 787968 bytes
    Variable Size 95418880 bytes
    Database Buffers 71303168 bytes
    Redo Buffers 262144 bytes
    Database mounted.
    SQL> alter database open
    2 /
    alter database open
    *
    ERROR at line 1:
    ORA-00368: checksum error in redo log block
    ORA-00353: log corruption near block 25243 change 17595633 time 07/03/2006
    00:09:39
    ORA-00312: online log 3 thread 1:
    'D:\ORACLE\PRODUCT\10.1.0\ORADATA\GRP3LOG.DBF'
    Last edited by GARYM; 07-05-2006 at 03:55 PM.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    what the database shutdown cleanly? if so just open reset logs

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    Are the Logs multiplexed? is there more than 1 logfile per group or do you have just one logfile per group?

  7. #7
    Join Date
    Jun 2006
    Posts
    259
    Since it was a crashed instance. It was not shut down cleanly....

    And from the alert log messages, I'm guessing the logs were not multiplexed either.
    The control files appear to be on the same devices as well.

    This DB is now finished. There is no way to recover your corrupted log file. And since it is used to protect the integrity of the data file, they too are unrecoverable. Even after a resetlogs....

    Reset logs would do the trick, allowing you to open. But the data is inconsistent/incomplete.

    In the future:
    Protect the instance... Create multiple redo log members per group on seperate disk devices (at least 3).
    You don't need 7-8 groups... 3-4 should suffice.
    Use at least 2 control files on seperate disk devices.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hmm. First things first, when the database is down, backup all the files (datafiles, controlfiles, logfiles, init.ora, spfile, etc.).

    Next, I'd try opening resetlogs. In theory, your datafiles should be in a consistent state, but you don't know what got screwed up when you came down hard.

    As a last resort, I'd do an incomplete recovery until scn 17595633 or 17595632 and open resetlogs. Of course, you have a backup and are in archivelog mode, right?

    Protect the instance... Create multiple redo log members per group on seperate disk devices (at least 3).
    You don't need 7-8 groups... 3-4 should suffice.
    Use at least 2 control files on seperate disk devices.
    Hmm, you want three copies of the log files and only two of the control files? You definitely want multiple copies, but with each copy comes performance implications.

    3-4 log groups may or may not be appropriate. This is a generalization (to which I don't agree because it doesn't fit every case) and needs to be understood before implementing.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by marist89

    As a last resort, I'd do an incomplete recovery until scn 17595633 or 17595632 and open resetlogs. Of course, you have a backup and are in archivelog mode, right?

    you a funny guy, what is this archive log mode thing you speak of

  10. #10
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Hi Friends,

    Try to create new logfile groups...and drop the corrupted ones.

    This is really the disadvantage of oracle against mssql server,
    it is very strict when in comes to integrity.
    Supposing I got redolog corruption todate (same scenario with
    GARYM) but i want to get my data as of yesterday...since all of the
    datafiles are still intact. And i know how to restart the process
    or redo it manually all the transaction made during the day coz
    supposing i know my application very well, or maybe its just a test
    database.
    Can I not forced it to startup? Disregarding all the transactions today. My point is since the datafiles are still intact why I am not
    allowed to open it? and just put it tob waste

    Thanks a lot

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