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

Failure phenomena:

Oralce startup times the following error:

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

Sql> Startup

ORACLE instance started.

Total System Global area 3657797632 bytes

Fixed Size 2258600 bytes

Variable Size 2013268312 bytes

Database buffers 1627389952 bytes

Redo buffers 14880768 bytes

Database mounted.

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

Process id:2118

Session id:401 Serial Number:5

sql> startup Mount;

Ora-24324:service Handle not initialized

Ora-01041:internal error. Hostdef extension doesn ' t exist

Reason Lookup:

1. Review the Orcle startup log to determine what the specific cause of the error is.

   CD $ORACLE _home/diag/rdbms/ncdbemu/ncdbemu/trace/

   LS-ALCR | grep alert (C-time sort, R-reverse) locates the boot log. Open very long content, not easy to find. So use tracking to find.

2. TAIL-F-N Alert_ncdbemu.log then restarts the startup command above and traces the following.

ARC3 started with pid=23, OS id=2126

Errors in FILE/ORACLE/APP/ORACLE/DIAG/RDBMS/NCDBEMU/NCDBEMU/TRACE/NCDBEMU_ORA_2118.TRC:

Ora-19815:warning:db_recovery_file_dest_size of 20971520000 bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have the following choices to free up space from the 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 the delete files, then use RMAN crosscheck and

DELETE EXPIRED Commands.

************************************************************************

Arch:error 19809 Creating Archive log file to '/backup/fast_recovery_area/ncdbemu/archivelog/2016_02_01/o1_mf_1_603_% U_.arc '

Errors in FILE/ORACLE/APP/ORACLE/DIAG/RDBMS/NCDBEMU/NCDBEMU/TRACE/NCDBEMU_ORA_2118.TRC:

Ora-16038:log 3 sequence# 603 cannot be archived

Ora-19809:limit exceeded for recovery files

Ora-00312:online Log 3 thread 1: '/dbdata/oradata/ncdbemu/redog03m01.rdo '

Ora-00312:online Log 3 thread 1: '/dbdata/oradata/ncdbemu/redog03m02.rdo '

USER (ospid:2118): Terminating the instance due to error 16038

System State Dump requested by (Instance=1, osid=2118), summary=[abnormal instance termination].

System state dumped to trace file/oracle/app/oracle/diag/rdbms/ncdbemu/ncdbemu/trace/ncdbemu_diag_2087_ 20160201162949.trc

Dumping diagnostic data in directory=[cdmp_20160201162949], requested by (Instance=1, osid=2118), Summary=[abnormal instance termination].

Instance terminated by USER, PID = 2118

Mon Feb 01 16:33:49 2016

Starting ORACLE instance (normal)

license_max_session = 0

license_sessions_warning = 0

Initial number of CPUs is 2

CELL communication is configured to use 0 interface (s):

CELL IP Affinity Details:

NUMA Status:non-numa System

Cellaffinity.ora status:n/a

CELL communication would use 1 IP group (s):

GRP 0:

Picked latch-free SCN Scheme 3

Using log_archive_dest_1 parameter default value as Use_db_recovery_file_dest

Autotune of undo retention is turned on.

Imode=br

Ilat =88

License_max_users = 0

SYS Auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real application testing options.

Oracle_home =/oracle/app/oracle/dbhome

System Name:linux

Node Name:ncdbemulator

Release:2.6.32-431.el6.x86_64

Version: #1 SMP Sun 22:19:54 EST 2013

Machine:x86_64

Using parameter settings in Server-side Spfile/oracle/app/oracle/dbhome/dbs/spfilencdbemu.ora

System parameters with Non-default values:

processes = 500

Sessions = 800

Memory_target = 3504M

Control_files = "/dbdata/oradata/ncdbemu/control01.ctl"

Control_files = "/backup/fast_recovery_area/ncdbemu/control02.ctl"

Control_files = "/dbdata/oradata/ncdbemu/control03.ctl"

Db_block_size = 8192

compatible = "11.2.0.4.0"

Log_archive_format = "%T_%S_%R.DBF"

Cluster_database = FALSE

Db_recovery_file_dest = "/backup/fast_recovery_area"

Db_recovery_file_dest_size= 20000M

Undo_tablespace = "UNDOTBS1"

Remote_login_passwordfile= "EXCLUSIVE"

Db_domain = ""

Dispatchers = "(protocol=tcp) (SERVICE=NCDBEMUXDB)"

job_queue_processes = 1000

Audit_file_dest = "/oracle/app/oracle/admin/ncdbemu/adump"

Audit_trail = "DB"

db_name = "Ncdbemu"

Open_cursors = 300

Diagnostic_dest = "/oracle/app/oracle"

4. Solutions

There are three: (in fact, the above log has given 4 solutions.) ）

1) Set the archive to a different directory, modify alter system set Log_archive_dest = Other path

2) Transfer or delete the archive log in the Flashback recovery area.

3) Increase the flash back recovery area. alter system set db_recovery_file_dest_size=30g;

Resolution of the second error (24324 and 01041):

Just exit Sqlplus and log back in.

5. steps to resolve :

1) boot into Mount state,

sql> startup Mount;

Ora-24324:service Handle not initialized

Ora-01041:internal error. Hostdef extension doesn ' t exist

Sql> Startup Nomount

Ora-24324:service Handle not initialized

Ora-01041:internal error. Hostdef extension doesn ' t exist

Sql> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real application testing options

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.4.0 Production on Mon Feb 1 16:33:44 2016

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

Connected to an idle instance.

sql> startup Mount;

ORACLE instance started.

Total System Global area 3657797632 bytes

Fixed Size 2258600 bytes

Variable Size 2013268312 bytes

Database buffers 1627389952 bytes

Redo buffers 14880768 bytes

Database mounted.

2) Check the location and size of recovery area (flashback zone):

Sql> Show parameter db_recovery;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Db_recovery_file_dest String/backup/fast_recovery_area

Db_recovery_file_dest_size Big Integer 20000M

Sql> Show parameter Flashback;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Db_flashback_retention_target integer 1440

Sql> select File_type,percent_space_used,number_of_files from V$flash_recovery_area_usage;

File_type percent_space_used Number_of_files

-------------------- ------------------ ---------------

CONTROL FILE 0 0

REDO LOG 0 0

Archived LOG 99.85 595

BACKUP PIECE 0 0

IMAGE COPY 0 0

FLASHBACK LOG 0 0

FOREIGN archived LOG 0 0

7 rows selected.

3) method One, modify the recovery area size:

Sql> alter system set db_recovery_file_dest_size=30g;

System altered.

Sql> Show parameter db_recovery_file_dest_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Db_recovery_file_dest_size Big Integer 30G

4) Open the database

sql> ALTER DATABASE open;

Database altered.

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