Ora 03113 как исправить

Имеем тестовую СУБД Oracle XE (11.2.0.1.x86_64), на Oracle Linux 5.2 x86_64.
При попытке открыть БД из sqlplus получаем ошибку:

ORA-03113: end-of-file on communication channel
Process ID: 4862
Session ID: 91 Serial number: 3

Гугл намекает, что возможны какие-либо проблемы со свободным местом. Смотрим, что с ним:
$ df -h

Видим, что на уровне операционной системы все в порядке.
Смотрим, что в alert.log
$ view /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log

Видим такую запись после попытки открыть БД:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 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 ‘/u01/app/oracle/fast_recovery_area/XE/archivelog/2015_04_21/o1_mf_1_243_%u_.arc’
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_5607.trc:
…

Т.е. вся область восстановления чем-то забита.
Попутно понятно, куда пишутся archivelog:
/u01/app/oracle/fast_recovery_area/XE/archivelog/

Смотрим, чем забита fast recovery area:
du -sh /u01/app/oracle/fast_recovery_area/XE/*
10G    /u01/app/oracle/fast_recovery_area/XE/archivelog
Видим, что 10Гб «съели» archivelog. Надо почистить.
Запускаем Recovery manager:
$ rman TARGET /
RMAN> list backup;
…
RMAN-03002: failure of list command at 04/21/2015 14:47:28
ORA-01507: database not mounted

БД должна быть примонтирована, чтобы RMAN показал бэкапы.
Монтируем:
$ sqlplus / as sysdba
SQL> alter database mount;

Database altered.

Смотрим бэкапы:
$ rman TARGET /
RMAN> list backup;
…
specification does not match any backup in the repository

Нет ни одного бэкапа. Что само по себе печально, но для тестовой БД устраивает.

Зато видим порядка 300 архивных логов:
$ rman TARGET /
RMAN> list archivelog all;
…

Чтобы исправить ситуацию, временно увеличиваем db_recovery_file_dest_size, создаем бэкап и удаляем ненужные архивные логи.
$ sqlplus / as sysdba
SQL> alter system set db_recovery_file_dest_size=12G;

System altered.

Делаем бэкап БД, controlfile и архивных логов.
$ rman TARGET /
RMAN> backup as compressed backupset database plus archivelog delete input;

Возвращаем обратно значение параметра инициализации db_recovery_file_dest_size:
$ sqlplus / as sysdba
SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

It works!

After hours of misdirection from official Oracle support, I dove into this on my own and fixed it. I am documenting it here in case someone else has this problem.

To do any of this, you must be the oracle user:

$ su - oracle

Step 1: You need to look at the alert log. It isn’t in /var/log as expected. You have to run an Oracle log reading program:

$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>

Notice the ADR base. That is not the install. You need to see the homes so you can connect to the one that you use.

adrci> show homes
ADR Homes:
diag/rdbms/cci/CCI
diag/tnslsnr/cci/listener
diag/tnslsnr/cci/start
diag/tnslsnr/cci/reload

CCI is the home. Set that.

adrci> set home diag/rdbms/cci/CCI
adrci>

Now, you can look at the alert logs. It would be very nice if they were in /var/log so you could easily parse the logs. Just stop wanting and deal with this interface. At least you can tail (and I hope you have a scrollback buffer):

adrci> show alert -tail 100

Scroll back until you see errors. You want the FIRST error. Any errors after the first error are likely being caused by the first error. In my case, the first error was:

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.

This is caused by transactions. Oracle is not designed to be used. If you do push a lot of data into it, it saves transaction logs. Those go into the recovery file area. Once that is full (50GB full in this case). Then, Oracle just dies. By design, if anything is messed up, Oracle will respond by shutting down.

There are two solutions, the proper one and the quick and dirty one. The quick and dirty one is to increase db_recovery_file_dest_size. First, exit adrci.

adrci> exit

Now, go into sqlplus without opening the database, just mounting it (you may be able to do this without mounting the database, but I mount it anyway).

$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> startup mount

Now, you can increase your current db_recovery_file_dest_size, increased to 75G in my case:

SQL> alter system set db_recovery_file_dest_size = 75G scope=both

Now, you can shutdown and startup again and that previous error should be gone.

The proper fix is to get rid of the recovery files. You do that using RMAN, not SQLPLUS or ADRCI.

$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> backup archivelog all delete input;

If you’ve got RMAN-06171: not connected to target database, than try to use rman target / instead of just rman

Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your database and be back in business.

In my previous article i have given the resolution as well as introduction about different errors like ORA-12154: TNS listener error  and ORA-00600 and ORA-01722: invalid number.The fourth most searched Error for oracle is ORA-03113: end-of-file on communication channel.This error is searched approximately 23 k times per month in google search engine.In this article i will try to explain you why this error will come and how this error will solve.There are 2 types of errors in Oracle one is syntax errors and others are the errors caused by connection.There are lot of errors in oracle where the mechanical steps does not work and we need to check N number of points to resolve the errors just like ORA-00600.There are different steps to solve ORA-03113 error.

“ORA-03113 error will be searched approximately 23 k times per month on google.”

Why ORA-03113 error will come?

This is another type of error which is most frequently coming in oracle.There should be multiple reasons for this error.This is not a simple error like syntax errors.There are following reasons for this error :

Situation 1 : 

  Connection between client and server is broken.

This error will come when connection between client and server is broken.The connection will broken because of multiple reasons like connection timeout.

Situation 2 : 

  Server machine Crash issue.

Due to some reasons if the server machine is crashed then this issue will come.The user needs to confirm that server machine is working well.

Situation 3 :

Processes kill on server side

When processes killed from server side this error will occur.In this situation user needs to restart the server so that all processes will run properly.

Situation 4 :

Incorrect connection handling

There are lot of times unknowingly user is handling multiple incorrect connections.This error will cause because of multiple wrong connection handling at a same time.

Situations 5 :

Network issue

Due to network issue this error will come.Need to check the network cause for the same.

Situation 6 :

Connection abort

Due to connection abort issue this error will come.

So This error will not come because of syntax.It is coming frequently due to network cause.There are no mechanical steps to resolve this error.But with my experience i will give the set of  checkpoints using which user will solve this issue.

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of the Error :

To resolve ORA-03113 there are no mechanical steps.But system admin as well as database administrator will solve this error by doing some task.

Solution 1 :

System administrator needs to check whether there is server crash or not.If server is crashed then using disaster recovery server need to make the connection.

Solution 2:

If server side processes aborted or stopped then just restart the server and take bounce of server.Check that all Servers as well as supporting processes are up and running.

Solution 3:

Check for the network issues and try to establish connection with oracle.

Solution 4:

Check oracle alert.log files for the errors and try to  resolve the exceptions coming in alert.log file.

Solution 5:

User SQL_TRACE on sessions to find out the problem.

Solution 6:

Check USER_DUMP_DEST and BACKGROUND_DUMP_DEST directories and try to reproduce the issue again.

Solution 7 :

One of the cause of this error is firewall.So check for all firewall configurations and it is working properly and connection is made properly between oracle.

These are some solutions or checkpoints i have given to resolve this error.Hope you find this article interesting.Don’t forget to share this article with everyone.