COMMIT Causes ORA-03113

A colleague had a problem with an Oracle 11.2.0.1 database today. It affected the following table:

SQL> desc bepe.sd_national_holiday

Name                       Null?    Type

-------------------------- -------- ------------------

SD_NATIONAL_HOLIDAY_ID     NOT NULL NUMBER(15)

DAY_DATE                            DATE

DESCRIPTION                         VARCHAR2(500)

CREATED_BY                 NOT NULL VARCHAR2(100)

CREATION_DATE              NOT NULL TIMESTAMP(6)

ACTIVITY_BY                NOT NULL VARCHAR2(100)

ACTIVITY_DATE              NOT NULL TIMESTAMP(6)

TCN                        NOT NULL NUMBER

 

SQL>

The table was empty:

SQL> select count(*) from bepe.sd_national_holiday

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

… but, when I tried to add data to it, the COMMIT, which followed, failed with an ORA-03113. Typing EXIT, without doing a COMMIT, caused the same problem:

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('15-APR-2015')

  3  /

 

1 row created.

 

SQL> commit

  2  /

commit

     *

ERROR at line 1:

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

Process ID: 7037

Session ID: 133 Serial number: 6339

 

SQL> conn /

Connected.

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('15-APR-2015')

  3  /

 

1 row created.

 

SQL> exit

ERROR:

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

Process ID: 0

Session ID: 133 Serial number: 6343

 

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options (with complications)

APXDEV1 /export/home/oracle/andrew >

The table remained empty:

SQL> l

  1  select count(*)

  2* from bepe.sd_national_holiday

SQL> /

 

  COUNT(*)

----------

         0

 

SQL>

… and trying to move it did not work either:

SQL> l

  1  alter table bepe.sd_national_holiday

  2* move tablespace apex_13482906934262524

SQL> /

alter table bepe.sd_national_holiday

                 *

ERROR at line 1:

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

Process ID: 11685

Session ID: 156 Serial number: 21804

 

SQL>

I was able to reproduce this error again and again. I thought there might be some corruption in the underlying data file but dbverify did not show anything. I looked at the messages in the alert log:

ORA-07445: exception encountered: core dump [qesrcRseObj_Invalidate()+4] [SIGSEGV] [ADDR:0x18] [PC:0x1029AFAC4] [Address not mapped to object] []

The closest match I could find for these on My Oracle Support was unpublished bug 9025861. In Oracle 11.2, results from functions run against tables can be cached. Then Oracle can access these cached values rather than rerun the functions. The suggested fix for this bug is to upgrade to Oracle 11.2.0.2 or set result_cache_max_size to zero, which stops cached results being used. I set the parameter to zero:

SQL> l

  1  select value from v$parameter

  2* where name = 'result_cache_max_size'

SQL> /

 

VALUE

----------

2621440

 

SQL> alter system set result_cache_max_size = 0

  2  /

 

System altered.

 

SQL>

… and the problem went away:

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('15-APR-2015')

  3  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

Strangely enough, resetting the parameter to its previous value did not make the problem return:

SQL> alter system set result_cache_max_size = 2621440

  2  /

 

System altered.

 

SQL> select value from v$parameter

  2  where name = 'result_cache_max_size'

  3  /

 

VALUE

----------

2621440

 

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('16-APR-2015')

  3  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> 

However, this is not a permanent cure. I did the same thing just over a month ago and the problem went away then too but, as you can see, it returned today for no apparent reason.

We noticed that an Oracle 11.2.0.2 database, which is being used for similar work, has not been affected by this problem so upgrading to this version might be the safest option.