Oracle Error--ORA-03113: End-of-file resolution for communication channels

Origin

today, as usual, login PL/SQL , the login failed, an error occurred " ORA-01034 "and" ORA-27101 "

and then just log in from the command prompt. Oracle , go to see what's going on, and then the problem goes further, wrong " ORA-03113: end of file for communication channel Process id:6320 id:191 serial number:3".

Source of the problem

Oracle error, then go to the error log to find the source of the problem: oracle_ora_6320.trc file found under the e:\app\kang\diag\rdbms\oracle\oracle\trace\ folder , open the display error log:

Trace filee:\app\kang\diag\rdbms\oracle\oracle\trace\oracle_ora_6320.trcoracle Database 11gEnterprise Edition Release 11.2.0.1.0-64bit productionwith thepartitioning, OLAP, Data Mining and Real application testing optionswindows N T VersionV6.1 Service Pack 1cpu:4-type 8664, 2 physicalcoresprocess affinity:0x0x0000000000000000 Memory (avail/total):P h:2805m/6087m, ph+pgf:6761m/12173minstance Name:oracleredo thread Mountedby This instance: 1Oracle processnumber:19windows thread id:6320, image:oracle. EXE (SHAD) * * * 2014-08-1608:18:55.461*** SESSIONID: (191.3) 2014-08-16 08:18:55.461*** CLIENT ID: (2014-08-16) 08:18:55.461*** SERVICE NAME: () 2014-08-16 08:18:55.461*** modulename: (sqlplus.exe) 2014-08-16 08:18:55.461*** ACTION NAME: () 2014-08-16 08:18:55.461 ORA-19815: Warning: db_recovery_file_dest_size bytes (total 4102029312 bytes) used 100%, 0 bytes are available. You are followingchoices to free up space From RecoveRy Area:1. Consider Changingrman RETENTION POLICY. If you is using Data Guard, then consider changing RMAN archivelogdeletion policy.2. Back to files Totertiary device such as tape using RMAN BACKUP RECOVERY area Command.3. ADD disk space Andincrease db_recovery_file_dest_size parameter to reflect the new space.4. Delete unnecessaryfiles using RMAN DELETE command. If an operating system command is used to delete Files,then use RMAN crosscheck and delete EXPIRED commands.********* ORA-19809: Exceeded the limit of the number of recovered files ORA-19804: cannot be reclaimed 33961984 Byte disk space (from 4102029312 limit) * * * 2014-08-1608:18:55.502 4132 krsh.cARCH:Error 19809Creating Archive log file to ' E:\APP\KANG\F Lash_recovery_area\oracle\archivelog\2014_08_16\o1_mf_1_159_%u_. ARC ' * * * * 2014-08-1608:18:55.502 2747 krsi.ckrsi_dst_fail:dest:1err:19809 force:0 blast:1dde:problem Key ' ORA312 ' was Flo OD controlled (0x1) (no incident) ORA-00312: Online log 3 thread 1: ' E:\APP\KANG\ORADATA\ORACLE\REDO03.LOG ' ORA-16038: Log 3sequence# 159 cannot be archived ORA-19809: exceeds the limit of the number of recovered files ORA-00312: Online log 3 thread 1: ' E:\APP\KANG\ORADATA\ORACLE\REDO03. LOG ' * * * 2014-08-1608:18:55.565user (ospid:6320): Terminating the instance due to error 16038

From here we find the root of the problem: "

ORA-19815: Warning: db_recovery_file_dest_size bytes (total 4102029312 bytes) have been used 100% and 0 bytes are available. " is db_recovery_file_dest_size also called the Archive log space is not enough to cause, since the source of the problem found, That would be easy to solve.

Solution Path

Small space, that is in front of us means, one is to set the space larger, the other is to remove the redundant files can be, then we will use both methods.

Through the command window:

-------- set the size of the archive log space

Sqlplus/as Sysdbashutdown Abort     ----shutdown process startup mount       ----Mount Database select * FROM V$recovery_file_dest;--- Query archive log db_recovery_file_dest_size=10737418240; --Set the archive log space to 10GExit---here the space size is set to complete

-------- Delete Archive Log

Rmantarget/   -----into the Rman tool window Rman>crosscheckarchivelog all;  --run this command to mark the archivelog of the invalid expired. Rman>deletenoprompt Archivelog until Time "sysdate-3";  

This is a complete OK up. Then reopen the database: normal use .

one thing to note in deleting an archive is that displaying the archive through the command window is E:\app\kang\flash_recovery_area\oracle\ARCHIVELOG , but we can not manually delete these files in the operating system, because each archivelog is recorded in Controlfile, and when we delete the files in the OS, These archivelog are still recorded in our controlfile, so these logs are also available in Oracle's OEM manager. Because when we manually cleared the files in the archive directory, the records were not erased by us from the controlfile, which means that Oracle did not know that the files were no longer there. Therefore, the command window should be executed to delete these files.

Postscript

Archived logs are designed to make it easier for us to recover a database, but sometimes these archive logs do give us a little bit of a hassle, so these archive logs still need to be noticed.

 

Oracle Error--ORA-03113: End-of-file resolution for communication channels