ORA-03113 when starting up an Oracle 11g database

Question

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;

Answer 1

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.

Answer 2

I agree with Miracle173's answer. I dug around on metalink (I assume you don't have an account there) and this seems to be the best, supported method to solve your ora-600 [4194] issue.

This error normally happens for a new transaction. The trace file actually shows an active transaction for the undo segment because this is the transaction created by the same process. If the undo segment happens to have an active transaction, then Oracle may recover it later with no problems .

Normally if the header is dumped after the error, the active transaction is gone.

Step 1

SQL> Startup nomount;    --> using spfile

SQL> Create pfile='e:\app\corrupt.ora' from spfile; 

SQL> Shutdown immediate; 

Step 2

Modify the corrupt.ora and set Undo_management=Manual

SQL> Startup mount pfile='e:\app\corrupt.ora' 

SQL> Show parameter undo 

it should show manual

SQL> Alter database open; 

If it comes up

SQL> Create rollback segment r01; 

SQL> Alter rollback segment r01 online; 

Create a new undo tablespace

SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;

You can delay the drop of the Old undo tablespace; this is just to allow the block cleanout to happen for dead transaction. So the below step can be issued after database has been up and running with new undo tablespace for couple of hours.

Drop the Old undo tablespace

SQL> Drop tablespace <undo tablespace name> including contents and datafiles;

Step 3

SQL> Shutdown immediate; 

SQL> Startup nomount ; ---> Using spfile 

SQL> Alter system set undo_tablespace=<new Undo tablespace created> scope=spfile; 

SQL> Shutdown immediate ; 

SQL> Startup 

Hopefully this should allow you to startup without any errors.

Answer 3

It does not look good. Such an internal error points to something serious, maybe corruption. Try this:

startup mount;
recover database;
alter database open;

I think you'll have a better chance of finding an answer on metalink (Oracle support), if you are entitled. Otherwise on the oracle community forums (where I found the suggestion about trying the recovery).

Do you need the data? If yes, do you have a backup of it? You could drop the database and then recreate it, so that you will see if the problem is linked to your installation or to a corruption of the database.