Oracle error--ora-03113: End of file for communication channel ____oracle

My Oracle database was installed last November, and then it was installed, then it was normal, there was no problem, but then I didn't use my own local Oracle, Pl/sql has been connected to the machine, Then today suddenly want to do some testing on their own machine, pl/sql incredibly has not even, prompted the following error.

Prompt ORA-03113: End of file of communication channel
process id:0
session id:0 serial number: 0

After that is a series of Niang Google Forum and so on, toss for a long, after all, is to solve.

Workaround:
First step:

c:\users\administrator> sqlplus/as sysdba
sql> shutdown abort;
sql> startup Mount;
Sql> show parameter background_dump_dest;

NAME                                 TYPE        VALUE
-----------------------------------------------------------------------------
Background_dump_dest                 string      E:\app\Administrator\diag\rdbms\crm\crm\trace                                       

We can see this path above, E:\app\Administrator\diag\rdbms\crm\crm\trace.

The role of this directory:
It specifies the path name (directory or disk) to which the trace file is written for the background process (LGWR,DBW n, and so on) during the Oracle operation. It also defines the location of the database alert files that record important events and messages.

We go into the path (E:\app\Administrator\diag\rdbms\crm\crm\trace), find the Alert_oracle.log, and use Notepad to open it (note: If the log file is larger, the system may be stuck, No response, need to wait a little while. The visible file record error is as follows:

From here we find the root of the problem: "

ORA-19815: Warning: db_recovery_file_dest_size byte (total 4102029312 bytes) has been used 100%, and 0 bytes are still available. "Is db_recovery_file_dest_size also called the Archive log space is not enough, since the root cause of the problem is found, it is easy to solve."

Ways to Solve

The space is small, that put in front of us the method is, one is to set the space big point, the other is to remove the superfluous files, then we will use both methods.

Step Two:
--– through a command window: Setting the size of the archive log space

Sql> select * from V$recovery_file_dest;
Sql> alter system set db_recovery_file_dest_size=10737418240; ---here is 10G.
sql> ALTER DATABASE open;
Sql> exit;

Step Three:
--– Delete Archive Log

C:\users\administrator> rman target/;  --Enter the RMAN tool window
rman> crosscheck archivelog all;  --run this command to mark the archivelog of the invalid expired.
rman> Delete Expired archivelog all;--delete the expired archive log directly.
rman> Delete noprompt archivelog until time "sysdate-3";  --can also be deleted directly with a specified date.

It's completely OK to be here. Next, reopen the database: normal use.

Attention:

One thing to note in deleting an archive is that the display archive is displayed through a command window under E:\app\Administrator\flash_recovery_area\CRM\ARCHIVELOG, But we can not manually delete these files in the operating system, this is because in the Controlfile records each archivelog information, when we delete these files in the OS, These archivelog information is still recorded in our controlfile, so these logs are still present in Oracle's OEM manager. Because when we manually clear the files in the archive directory, the records were not removed from the controlfile, which means Oracle did not know that the files were no longer there. So it's still a command window to execute the command to delete these files.

Note:

Archive logs are actually designed to help us recover from the database, but sometimes these archived logs can actually give us a little bit of trouble, so these archive logs need our attention.

Original address: http://www.2cto.com/database/201308/235338.html