ORA-03113

I found some DBA interview questions on the Internet recently. One of them asked what might cause an ORA-03113. This message appears when you lose connection to a database as shown in the example below, which was tested on an Oracle 9 database. First a user connects to the database remotely and checks the time:

 

SQL> conn andrew/reid@test9

Connected.

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:40:13

 

SQL>

 

A little later, another user, connected to the server, closes the database:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 23 17:41:59 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

Then, when the remote connection tries to check the time again, it fails with an ORA-03113:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

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

 

SQL>

 

The database is reopened:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 23 18:01:48 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL>

 

If the remote user runs the query again, it works but only after he has reconnected to the database:

 

SQL> l

  1  select to_char(sysdate,'hh24:mi:ss')

  2* time_now from dual

SQL> /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

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

 

SQL> conn andrew/reid@test9

Connected.

SQL> /

 

TIME_NOW

--------

18:03:21

 

SQL>

 

Incidentally, I have noticed that reconnecting to the database does not always work. Sometimes you have to start a fresh SQL*Plus session as well.