Visit Counter

Wednesday, May 18, 2016

ORA-03113: end of file on communication channel



SQL> startup
ORACLE instance started.

Total System Global Area 16535183300 bytes
Fixed Size 2176288 bytes
Variable Size 1006635744 bytes
Database Buffers 637534208 bytes
Redo Buffers 7172096 bytes
ORA-03113: end-of-file on communication channel
Process ID: 5736
Session ID: 191 Serial number: 1

I get the same error if I try start with RMAN:

RMAN> startup mount

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 03/07/2011 09:02:11
ORA-03113: end-of-file on communication channel
Process ID: 7256
Session ID: 192 Serial number: 5



ALERT_SSAERP.LOG


-bash-4.1$ tail - 100 alert_ssaerp.log


Errors in file /ora/app/oracle/diag/rdbms/ssaerp/ssaerp/trace/ssaerp_ora_2269.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4781506560 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/ora/app/oracle/fast_recovery_area/SSAERP/archivelog/2016_05_18/o1_mf_1_135_%u_.arc'
Errors in file /ora/app/oracle/diag/rdbms/ssaerp/ssaerp/trace/ssaerp_arc1_2271.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4781506560 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARC1: Error 19809 Creating archive log file to '/ora/app/oracle/fast_recovery_area/SSAERP/archivelog/2016_05_18/o1_mf_1_136_%u_.arc'
Errors in file /ora/app/oracle/diag/rdbms/ssaerp/ssaerp/trace/ssaerp_ora_2269.trc:
ORA-16038: log 3 sequence# 135 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/ora_dat/ssaerp/redo03.log'
USER (ospid: 2269): terminating the instance due to error 16038
System state dump requested by (instance=1, osid=2269), summary=[abnormal instance termination].
System State dumped to trace file /ora/app/oracle/diag/rdbms/ssaerp/ssaerp/trace/ssaerp_diag_2251_20160518145705.trc
Dumping diagnostic data in directory=[cdmp_20160518145705], requested by (instance=1, osid=2269), summary=[abnormal instance termination].
Instance terminated by USER, pid = 2269
-bash-4.1$ pwd



$ /ora/app/oracle/diag/rdbms/ssaerp/ssaerp/trace




Solution:




SPFILE File

SQL> Shutdown immediate;
SQL> startup mount

SQL>alter system set db_recovery_file_dest_size = 10G scope=both;


$ rman
Recovery Manager: Release 11.2.0.4.0 - production on Jul 03 18:11:11 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> backup archivelog all delete input;





PFILE


I changed the below parameter in initssaerp.ora

db_recovery_file_dest_size of 4781506560000


Remove files on below location

/ora/app/oracle/fast_recovery_area

-bash-4.1$ ./sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Jul 03 16:56:18 2011
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1.0292E+10 bytes
Fixed Size                  2190488 bytes
Variable Size            1946162024 bytes
Database Buffers         8321499136 bytes
Redo Buffers               21839872 bytes
Database mounted.
Database opened.


SQL>












No comments:

Post a Comment