Friday, September 23, 2011

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.

No comments: