ORA-03113 when starting up an Oracle 11g database

oracleoracle-11gstartup

I have read "ORA-03113: end-of-file on communication channel" on startup and others from this site to solve my problem but I still have not solved it. All I get is the following:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jun 28 17:28:20 2014

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
SQL> alter system set db_recovery_file_dest_size=35G scope=both;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
Database opened.
SQL> select instance_name from v$instace;
select instance_name from v$instace
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 43880
Session ID: 170 Serial number: 5


SQL>

Environment: Windows 7 64 bit, Oracle 11g.

Here are the details from alerts.log:

Sat Jun 28 17:32:26 2014
Flush retried for xcb 0x8a6711a8, pmd 0x8afba968
Doing block recovery for file 3 block 2686
Block recovery from logseq 13, block 68 to scn 132386303
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
  Mem# 0: E:\APP\TRUCNGUYEN\ORADATA\XDLL\REDO01.LOG
Block recovery completed at rba 13.69.16, scn 0.132386305
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc  (incident=36103):
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc:
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
PMON (ospid: 55932): terminating the instance due to error 472
Sat Jun 28 17:32:27 2014
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_q001_79168.trc:
ORA-00472: PMON  process terminated with error
ORA-1092 : opidrv aborting process Q001 ospid (2836_79168)
Sat Jun 28 17:32:27 2014
ORA-1092 : opidrv aborting process W000 ospid (2836_56844)
Sat Jun 28 17:32:29 2014
ORA-472 : opidrv aborting process S000 ospid (2836_85052)
Instance terminated by PMON, pid = 55932

I'm a newbie in Oracle. I would really appreciate any direction and especially step by step instructions to fix this.

I have run the commands:

recover database;  
alter database open;

but it still has the same error when I run:

select instance_name from v$instance;

Best Answer

From ParnassusData:

This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

ARGUMENTS:

Arg [a] - Maximum Undo record number in Undo block

Arg [b] - Undo record number from Redo block

Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

Oracle is unable to roll back an uncommitted transaction in its transaction table. You probably have corruption in either the rollback/undo segment or corruption in the object in which the rollback/undo segment is trying to apply the undo record.

Refer to Note: 39283.1 for more details on the description of this error.

Oracle Note 39283.1 says more or less the same.

Here is a link where somebody solved the problem by creating a new undo tablespace: SAP, Databases and other IT stuff

Here are the statements:

-- Creation of a new Undo tablespace:
CREATE UNDO TABLESPACE undotbs
DATAFILE 'c:\oracle\10201\dev\undotbs1.dbf'
SIZE 300M AUTOEXTEND ON NEXT 20M
MAXSIZE 800M;

-- Change the default undo tablespace to the new one
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs SCOPE=BOTH;

-- Drop the problematic undo tablespace
DROP TABLESPACE oldundotbs;

To create a tablespace the database must be open.

I don't know how to reproduce this error so I cannot test how to repair the system. I also think I won't get any feedback from the OP. But nevertheless let's think about, how we can proceed.

  • The best is to open a service request at Oracle Support.

  • One possibility is to restore the undo tablespace. But maybe the error will occur again.

  • Then a point in time restore of the whole database will be an option. But this means loss of data.

  • We will try to repair the system by creating a new undo tablespace an dropping the old one as described above.

To repair the system we unset the UNDO_TABLESPACE parameter. Then the SYSTEM tablespace becomes the default tablespace and I assume the database can be opened now.

startup nomount
alter system reset undo_tablespace scope=spfile;
shutdown
startup

Now we can proceed as above and create and set a new undo tablespace an drop the problem tablespace as described above.

Related Question