Tuesday, September 27, 2016

ORA-03113: end-of-file on communication channel (Error: ora-03113 at time of database open)

[oracle@techtest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 27 11:11:47 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 3.2002E+10 bytes
Fixed Size                  2269032 bytes
Variable Size            1.4026E+10 bytes
Database Buffers         1.7918E+10 bytes
Redo Buffers               55541760 bytes
Database mounted.
SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3065
Session ID: 66 Serial number: 3


Solution:

Step-1: First look at [alert.log] file the find the actual error
[oracle@techtest alert]$ tail -f log.xml
<msg time='2016-09-27T11:12:09.555+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'
 pid='3065'>
 <txt>ARCH: Error 19809 Creating archive log file to &apos;/fra/TECHDB/archivelog/2016_09_27/o1_mf_1_16404_%u_.arc&apos;
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.555+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''
 pid='3069'>
 <txt>ARC1: Error 19809 Creating archive log file to &apos;/fra/TECHDB/archivelog/2016_09_27/o1_mf_1_16405_%u_.arc&apos;
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.614+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''
 pid='3069'>
 <txt>ARCH: Archival stopped, error occurred. Will continue retrying
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.614+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''
 pid='3069'>
 <txt>ORACLE Instance techdb - Archival Error
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.620+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'
 pid='3065'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/techdb/techdb/trace/techdb_ora_3065.trc:
ORA-16038: log 3 sequence# 16404 cannot be archived
ora-19809:limit exceeded for recovery files
ORA-00312: online log 3 thread 1: &apos;/techtestdb/techdb/redo03.log&apos;
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.620+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.x.12' module=''
 pid='3069'>
 <txt>ORA-16038: log 2 sequence# 16405 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: &apos;/techtestdb/techdb/redo02.log&apos;
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.688+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'
 pid='3065'>
 <txt>USER (ospid: 3065): terminating the instance due to error 16038
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.710+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''
 pid='3031'>
 <txt>System state dump requested by (instance=1, osid=3065), summary=[abnormal instance termination].
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.729+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''
 pid='3031'>
 <txt>System State dumped to trace file /u01/app/oracle/diag/rdbms/techdb/techdb/trace/techdb_diag_3031_20160927111209.trc
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.870+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''
 pid='3031'>
 <txt>Dumping diagnostic data in directory=[cdmp_20160927111209], requested by (instance=1, osid=3065), summary=[abnormal instance termination].
 </txt>
</msg>
<msg time='2016-09-27T11:12:09.951+06:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'
 pid='3065'>
 <txt>Instance terminated by USER, pid = 3065
 </txt>
</msg>

 Step-2:  After look the alert log file, We have found two point on it and that are-

1. Error to create archive log
2. Limit exceeded for recovery files.

>>Step-3:  First try to delete the archive log to overcome error
[oracle@techtest ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Sep 27 11:15:23 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 09/27/2016 11:15:29
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

It is not possible to delete archive log as database is not open and limit exceeded for the recovery file.

>>Step-4:  Increase the size of DB_RECOVERY_FILE_DEST_SIZE

4.1: current value
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 50000M

4.2: Increase size

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =55g;
System altered.

>>Step-5:  Database Open
[oracle@techtest ~]$
[oracle@techtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 27 11:18:30 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>
SQL> alter database open;

Database altered.


SQL> exit
Posted by at
Labels:

2 comments:

Subscribe to: Post Comments (Atom)