Sunday, January 20, 2013

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

This article is a follow-up to the previous article:
As pointed out in that article, there are limitations and issues with cloning a database. This article describes one of the issues (see also [234]):
  • ORA-03113: end-of-file on communication channel

ORA-03113


When we tried to startup a cloned database, we saw the following message:

SQL> startup;
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size                  2240344 bytes
Variable Size            1811939496 bytes
Database Buffers         1.0972E+10 bytes
Redo Buffers               40890368 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9809
Session ID: 5339 Serial number: 3

It turns out that database has been mounted OK.  But, database failed when it was opened.

ALTER DATABASE OPEN Failed


Oerr is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files.  Here is the suggested actions from oerr:

$ oerr ORA 03113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup. 
//          Also, look in the alert.log file for any errors. Finally, test to 
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.


One of the suggested action is to look in the alert.log file.  To find the location of alert.log, you do:
  1. Find the location of diagnostic destination from the initialization parameter file (i.e., dbs/init<sid>.ora ).  For example, we have this entry:
    • diagnostic_dest=/slot/fiz7865/log
  2. From there, you can find alert.log file in the following sub directory:
    • <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

In the alert_<sid>.log, we have found the following messages:

ALTER DATABASE OPEN
Errors in file /slot/fiz7865/log/diag/rdbms/fiz7865/fiz7865/trace/fiz7865_lgwr_9793.trc:
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1: '/data1/rup3.redolog/log2.dbf'
Errors in file /slot/fiz7865/log/diag/rdbms/fiz7865/fiz7865/trace/fiz7865_lgwr_9793.trc:
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1: '/data1/rup3.redolog/log2.dbf'
Errors in file /slot/fiz7865/log/diag/rdbms/fiz7865/fiz7865/trace/fiz7865_ora_9809.trc:
ORA-00338: log 1 of thread  is more recent than control file
ORA-00312: online log 2 thread 1: '/data1/rup3.redolog/log2.dbf'
USER (ospid: 9809): terminating the instance due to error 338

What Happened?


As stated in ORA-00338, our redo log file is more recent than control file.  This happened because we have cloned control files first and then cloned redo files only after we have found that they were missing.  Note that in between two cloning, we have also restarted our source database.  So, after we have synchronized the control files and the redo files, we were able to start up database successfully.  In the alert.log file, you should find the following messages:

ALTER DATABASE OPEN
Thread 1 opened at log sequence 371
  Current log# 2 seq# 371 mem# 0: /data1/rup3.redolog/log2.dbf
Successful open of redo thread 1

References

  1. Simplify Cloning by Using Hosts File
  2. ORA-00313: open failed for members of log group 1 of thread 1
  3. Using rsync to Clone Local and Remote Systems
  4. ORA-01031: insufficient privileges
  5. Migrating Oracle B2B from Test to Production (T2P) (Chap 10 of the Book "Getting Started with Oracle SOA B2B Integration: A Hands-On Tutorial")
    • This section provides a real-world scenario to replicate (clone) the test environment to production for Oracle SOA.
    • Oracle Fusion Middleware provides a series of scripts for this task.
  6. Oracle Products: What Patching, Migration, and Upgrade Mean? (Xml and More)
    • For your Oracle production systems, follow official recommendations as shown in this article.

No comments: