This discussion is archived
9 Replies Latest reply: Oct 9, 2013 4:14 AM by EdStevens RSS

ORA-03113: end-of-file on communication channel

abigail818 Newbie
Currently Being Moderated

Help! I can't open the database, I get the ora-03113 error message

 

Here's my trace file

 

Tue Oct 08 22:37:15 2013

ARC3 started with pid=34, OS id=3016

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Errors in file e:\app\ABIGAIL\diag\rdbms\orcl\orcl\trace\orcl_lgwr_1952.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Errors in file e:\app\ABIGAIL\diag\rdbms\orcl\orcl\trace\orcl_lgwr_1952.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Errors in file e:\app\ABIGAIL\diag\rdbms\orcl\orcl\trace\orcl_ora_2908.trc:

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

USER (ospid: 2908): terminating the instance due to error 313

Instance terminated by USER, pid = 2908

 

Trace file e:\app\ABIGAIL\diag\rdbms\orcl\orcl\trace\orcl_ora_2908.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows XP Version V5.1 Service Pack 3

CPU                 : 2 - type 586, 2 Physical Cores

Process Affinity    : 0x0x00000000

Memory (Avail/Total): Ph:276M/1791M, Ph+PgF:1116M/3685M, VA:1166M/2047M

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 31

Windows thread id: 2908, image: ORACLE.EXE (SHAD)

 

 

*** 2013-10-08 22:37:15.453

*** SESSION ID:(125.5) 2013-10-08 22:37:15.453

*** CLIENT ID:() 2013-10-08 22:37:15.453

*** SERVICE NAME:() 2013-10-08 22:37:15.453

*** MODULE NAME:(sqlplus.exe) 2013-10-08 22:37:15.453

*** ACTION NAME:() 2013-10-08 22:37:15.453

 

DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

DDE: Problem Key 'ORA 313' was flood controlled (0x1) (no incident)

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

 

*** 2013-10-08 22:37:15.484

USER (ospid: 2908): terminating the instance due to error 313

 

 

I realize I don't have redo1a.log and redo1b .. can I just simply recreate those files at the OS level?

 

I'm using winxp pro, oracle 11gR2

 

Thanks guys!

  • 1. Re: ORA-03113: end-of-file on communication channel
    Alvaro Pro
    Currently Being Moderated

    Was your last shutdown clean or inconsistent? (immediate or abort) ?

     

    What happened that your lost redo log files? Where are they? Were they in use when they vanished?

     

    You could try to do alter database mount and then clear the redo log files (reconstruct them) with:

     

    alter database clear logfile group X; (for each redo log group missing)

     

    and then alter database open;

     

    That will fail if your controlfile sees that data is needed on those redo log files for instance recovery (in case database crashed). In that case your option is either incomplete recovery up to the last SCN before the redo logs disappeared (last SCN on the archived logs, excluding the redos) OR flashback the database to that same SCN (if you have flashback configured).

     

    Regards

  • 2. Re: ORA-03113: end-of-file on communication channel
    sb92075 Guru
    Currently Being Moderated

    >I realize I don't have redo1a.log and redo1b .

    what happened?


    Was the DB cleanly shutdown prior to this situation?


    When  was last time the DB opened without error?

    What changed since then?

  • 3. Re: ORA-03113: end-of-file on communication channel
    abigail818 Newbie
    Currently Being Moderated

    I was just adding redo log files last week and I wanted to save space, so I deleted those redolog files (redo1a.log, redo1b.log) at the OS level and what remained were redo01.log  redo02.log  and  redo03.log

     

    Yes last week everything shutdown normally and the last time the db opened without error was last week. I just start opening the database earlier today (did not touch it since last week) and this error came about.

     

    What changed, besides deleting those redolog files, the drive letters changed where my oracle resided but had that fixed already.

     

    Anyway I went ahead and created those redo log files again, and I get the same error. The trace alert log file shows slightly different now though

     

    SQL> startup

    ORACLE instance started.

     

     

    Total System Global Area  535662592 bytes

    Fixed Size                  1375792 bytes

    Variable Size             364904912 bytes

    Database Buffers          163577856 bytes

    Redo Buffers                5804032 bytes

    Database mounted.

    ORA-03113: end-of-file on communication channel

    Process ID: 2144

    Session ID: 125 Serial number: 5

     

     

    *** 2013-10-08 23:32:40.546

    *** SESSION ID:(125.5) 2013-10-08 23:32:40.546

    *** CLIENT ID:() 2013-10-08 23:32:40.546

    *** SERVICE NAME:() 2013-10-08 23:32:40.546

    *** MODULE NAME:(sqlplus.exe) 2013-10-08 23:32:40.546

    *** ACTION NAME:() 2013-10-08 23:32:40.546

     

    DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

    DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

    ORA-00341: log 1 of thread , wrong log #  in header

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

     

     

    *** 2013-10-08 23:32:40.578

    USER (ospid: 2144): terminating the instance due to error 341

     

     

     

     

    Tue Oct 08 23:32:40 2013

    ARC3 started with pid=34, OS id=2176

    ARC2: Archival started

    ARC1: Becoming the 'no FAL' ARCH

    ARC1: Becoming the 'no SRL' ARCH

    ARC2: Becoming the heartbeat ARCH

    Errors in file e:\app\ABIGAIL\diag\rdbms\orcl\orcl\trace\orcl_lgwr_1156.trc:

    ORA-00341: log 1 of thread 1, wrong log # 2 in header

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

    ORA-00322: log 1 of thread 1 is not current copy

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

    Errors in file e:\app\ABIGAIL\diag\rdbms\orcl\orcl\trace\orcl_lgwr_1156.trc:

    ORA-00341: log 1 of thread 1, wrong log # 2 in header

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

    ORA-00322: log 1 of thread 1 is not current copy

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

    Errors in file e:\app\ABIGAIL\diag\rdbms\orcl\orcl\trace\orcl_ora_2144.trc:

    ORA-00341: log 1 of thread , wrong log #  in header

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1A.LOG'

    ORA-00312: online log 1 thread 1: 'E:\APP\ABIGAIL\ORADATA\ORCL\REDO1B'

    USER (ospid: 2144): terminating the instance due to error 341

    Instance terminated by USER, pid = 2144

  • 4. Re: ORA-03113: end-of-file on communication channel
    Alvaro Pro
    Currently Being Moderated
    Anyway I went ahead and created those redo log files again, and I get the same error

     

    How exactly did you do that ?!

  • 5. Re: ORA-03113: end-of-file on communication channel
    abigail818 Newbie
    Currently Being Moderated

    I can't alter the database or anything cause I can't connect to oracle

     

    SQL> startup

    ORACLE instance started.

     

     

    Total System Global Area  535662592 bytes

    Fixed Size                  1375792 bytes

    Variable Size             364904912 bytes

    Database Buffers          163577856 bytes

    Redo Buffers                5804032 bytes

    Database mounted.

    ORA-03113: end-of-file on communication channel

    Process ID: 2144

    Session ID: 125 Serial number: 5

     

    I manually deleted those redolog files last week at the OS level to save space. I had other redolog files in there, so I thought it was safe to do so. I'll try to do an incomplete recovery as a last resort and see if that helps

  • 6. Re: ORA-03113: end-of-file on communication channel
    abigail818 Newbie
    Currently Being Moderated

    It sounds dumb but I just figured I try it out, I just copied the other redolog files and renamed it to redo1a.log and redo1b

  • 7. Re: ORA-03113: end-of-file on communication channel
    abigail818 Newbie
    Currently Being Moderated

    Thank you, had it recovered and am now able to connect to my database!

  • 8. Re: ORA-03113: end-of-file on communication channel
    Alvaro Pro
    Currently Being Moderated

    Glad to hear it.

     

    As you can see copying redos from other groups and renaming them won't work because oracle maintains metadata about its files, so it detected that in spite the name, it wasn't the redo logs it wanted. Same thing applies to pretty much every other non-logging files such as control files, datafiles, etc. You can't simply recreate them with the OS, it all must go through the database so it will update it's internal structures accordingly, i.e. control file, data dictionary.

     

    Regards

  • 9. Re: ORA-03113: end-of-file on communication channel
    EdStevens Guru
    Currently Being Moderated

    44b7f7b3-a805-46c1-8c59-af2e63e73cff wrote:

     

    It sounds dumb but I just figured I try it out, I just copied the other redolog files and renamed it to redo1a.log and redo1b

    No, that doesn't sound dumb.  Once I had gotten far enough down the thread to see that you had manually deleted one of the two multiplexed redo files from each group, I was going to tell you that that is exactly what you should do.  That is the standard fix for your problem.

     

    What was dumb was deleting them in the first place, thinking oracle would never notice.  Now, go forth and read the Concepts Manual again.

     

    ============================================================================

    BTW, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens?  But at least when people see that on a message they have a recognizable identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is like going to the pub with a bag over your head.

    ============================================================================

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points