[错误]分析ora-03113错误！

我们在使用Oracle数据库时经常碰到的一个错误，看看下面的分析和解决办法，也许对你很有帮助！
         Causes of ORA-3113's


ORA-3113 "end-of-file on communication channel" is a catch-all error message which simply means that the foreground oracle client process (svrmgrl, sqlplus, RMAN, Oracle Forms etc.) has lost contact with its background shadow process.

Unfortunately there are many causes of this. Network glitches were clearly the cause that the Oracle developers had in mind when they phrased the error message, but anything which causes the shadow process to crash will cause this error to be reported.

This message may also be accompanied by other messages, either displayed on the screen or in log files. If the background process which has crashed is one of the essential Oracle servers processes (PMON, SMON, DBWR etc.) you may also get an ORA-1034 ORACLE not available error message. Looking in the alert log or looking through any trace files in your background_dump_dest may help diagnose the problem.



Here are some of the causes of ORA-3113 I've accumulated so far:
In the situations below I have indicated the platform and Oracle version number where I eXPerienced the problem. But it is likely that some of the causes may occur on other platforms and other versions of Oracle as well.



Invalid value for processes parameter in init.ora when starting database
We had a value for processes of 150, which was
higher than that permitted by the values of kernel parameters SEMMSL and SEMMNS

This gave the errors:

       ORA-24323: value not allowed
       ORA-03113: end-of-file on communication channel
       ORA-01034: ORACLE not available
       ORA-27101: shared memory realm does not exist

The solution is either to redUCe the value of the init.ora parameter processes or (preferably) increase the values of these kernel parameters (and reboot your server).

(See the Oracle8i Installation Guide Release 8.1.7 for Sun SPARC Solaris manual for information on setting the Solaris shared memory and semaphore kernel parameters correctly.

In particular, SEMMSL must be set to at least 10 + the largest value of processes of any database on the server. SEMMNS is also dependent on the values of the processes parameter for each of your databases.

For other platforms, the procedure for changing kernel parameters may vary so consult the platform-specific documentaion for your plaform on Oracle Technet or on the installation CD.)

Interestingly, this problem occurred during a default installation of Oracle 8.1.7, so it was the installer software that had generated this value for processes, resulting in the installer failing to create an initial database. Note that Oracle (rather unhelpfully) doesn't tell you which parameter has an invalid value, or why it is invalid. Presumably there are other init.ora parameters which will cause similar symptoms if incorrectly set.
(Oracle 8.1.7.0.0 on Solaris)

setuid
bit needs setting on the oracle executable
(Unix only). The symptom for this (at least, in my case) was that SQL*Plus on the server gave an ORA-3113 error when started by any userid other than oracle. The database remained up.

The oracle executable in the $ORACLE_HOME/bin Directory should have the permissions:

   -rwsr-sr-x   1 oracle   dba      34049236 Oct 11 17:17 oracle

The important features are the ownership of the executable (by oracle) and the setting of the setuid bit (the first s in the permissions string.) This allows the shadow processes to execute as if they were being executed by the oracle userid and therefore allows them to read and write to the database files, which are normally protected with write Access granted to oracle only, and sometimes with read access restricted as well. The setting of the setgid bit (the second s ) is not important.

If the permissions are wrong, they can be corrected with the command:

   chmod 7755 $ORACLE_HOME/bin/oracle

Relinking the oracle executable (see below) will also set the permissions correctly. In fact, it is probable that in many cases changing the permissions was all that was required, rather than a full relink.

A possible cause of this problem is using tar or cp or some other unix file-copying utility to move or clone the Oracle installation. These tools will not preserve the setuid permissions on executables unless run as root. (Thanks to Paul
Ingram for his insights into this issue.)
(Oracle 8.1.7.0.0 on Solaris)

Oracle executables need relinking
There are three ways of doing this:

There's an option on the Oracle Installer to relink the executables.
I didn't use this method as I didn't have the installation CDROM to hand.

Enter the following Unix commands:

       cd $ORACLE_HOME/rdbms/lib

       make -f ins_rdbms.mk install

This relinked about a dozen executables, including $ORACLE_HOME/bin/oracle, and solved the problem. (Interestingly, the sqlplus executable was not relinked.)

Enter the following Unix commands:

       cd $ORACLE_HOME/bin

       ./relink all

Alternatively, given that it would seem necessary to only relink the oracle executable, ./relink oracle should be sufficient.

./relink without any parameters gives a list of possible targets for relinking.

Before embarking on a relinking exercise, it may first be worth checking whether the real cause of the problem is simply incorrect file permissions (see above).
(Oracle 8.1.7.0.0 on Solaris)

shared_pool_size too small.
We had it set to 100Mb. Increasing it to 200Mb solved the problem. (Yes, I know 200Mb is probably still too small for good performance.)
(Oracle 8.1.6.0.0 on Solaris)

timed_statistics=true.
Oracle Support could not provide a solution to this one: they just advised me to run my database with timed_statistics=false. Doing this did indeed
cure a lot of ORA-3113's I was getting when querying data-dictionary tables.
(Oracle 8.1.6.0.0 on Solaris)

An insert into a table which grew into an additional extent allocated in a datafile which was over 2Gb in size.
This caused the database to crash. I can't remember whether it was the action of growing the datafile which generated the ORA-3113, or whether it was the action of extending the table into the area above the 2Gb limit which caused it.

We then got ORA-3113's when trying to restart the database. I think what happened was that each time we brought it up it tried to re-apply the redo logs to bring the database into a consistent state before opening it. But this caused an insert above the 2Gb limit again and another ORA-3113 crash. I can't remember how I got round the problem. Probably "recover until cancel".
(Oracle 8.1.6.0.0 on Solaris)

Create table as select .... where there was an invalid procedure or function called from a trigger defined on the table we were selecting from.
I can't specify this in any more detail: we used the $ORACLE_HOME/rdbms/admin/utlrp.sql script to recompile all invalid packages, procedures and functions. This solved the problem, but I don't know which of the procedures or functions was the original cause of the problem.
(Oracle 8.1.6.0.0 on Solaris)



Make use of Oracle Support / Metalink
If you have a look at Metalink you'll find quite a lot of problems related to ORA-3113, with lots of diverse causes. You will probably need Oracle Support's help to solve most ORA-3113's