ORA-03113 Error Analysis

Error
Fenng (Fenng@itpub.net)

Copyright NOTICE: Reprint Please indicate the author and the source

Objective

Every DBA will inevitably encounter a variety of errors (ORA-XXXX) in the process of database management. Some of the errors have been dubbed "Classic Bugs" by DBAs for their frequent occurrences and complex causes. of which ORA-3113 "end of Fileon Communication channel "is one of those.

We can simply interpret this error as a disconnect between the Oracle client process and the database daemon connection. However, there are actually many reasons for this error, improper setting of the database, Any behavior that can cause a database daemon to crash can produce this error. This error is often accompanied by other errors, such as: ORA-1034 ORACLE not available.

In addition, the scene of the error is complex and may occur in:
The time of Oracle to start; The time when an attempt was made to create a database; The time when an attempt is made to connect to the database; When the client is running Sql/pl/sql; When the database is backed up/restored; In some other cases ...

At the forum, you can often see the junior DBA asking for help on this issue. Here is a simple way to sort out the problem. Improper place, please advise more!


All the wrong reasons

According to the information on the network, there are some reasons for the error:
UNIX core parameter set improper Oracle Execute file permissions incorrect/environment variable problem client communication does not properly handle database server crashes/operating system crashes/processes being killed Oracle internal error specific SQL, PL/SQL caused error space not enough firewall issues other reasons
Before you start to solve the problem, do the following things:

1, recall what you have done before the error, the more detailed the better;
2, look at the Background_dump_dest directory of the AlertSID.log file is also what you want to do;
3, Google, there is a lot of information on the Internet waiting for you to find, do not ask anyone else. Of course, if you find something that is very helpful to you-this document will not be read, do not delay your time, hehe.


Improper setup of Unix core parameters/init parameter settings

If the database does not have the correct operating system core variables set during installation, it may be when the database files are installed
No problem, 03113 errors often occur when you create a database. Another reason for this is Init.ora
The processes parameter in the parameter file specifies an unreasonable value, and starting the database causes the error to occur (of course, this boils down to
The bottom is also the core parameter of the problem).

This error message is generally as follows:

Ora-03113:end-of-file on communication channel
Ora-01034:oracle Not available
Ora-27101:shared Memory realm does not exist

There are two solutions:
1 Modify the core parameters, increase the value of the corresponding core parameters (recommended);
2 reduces the value of the processes of the Init.ora parameter.

It is to be noted that:
SEMMSL must be set to a maximum of at least 10 + process numbers.
Semmns also relies on the process parameter values on each database.

-------------------------------------------------------------------------------
Note:
This error type only appears on UNIX platforms. If the value of processes is too large on Windows, it appears:
Ora-00068:invalid value 24200001 for parameter max_rollback_segments, must is
Between 2 and 65535/* The parameter value specified at this time is more than 65535 */
Or
Ora-27102:out of Memory/* A large parameter value of less than 65535 * *
My Software Environment:
Windows Version 5.0 Service Pack 3, CPU type 586
ORACLE RDBMS version:8.1.7.0.0.
-------------------------------------------------------------------------------


Changing the core parameters on a specific platform may vary, please refer to the installation documentation on Oracle Technet (http://otn.oracle.com). The installation documentation for a particular UNIX platform also has an explanation of the meaning of the core parameters.

This error can occur if the parameters in Init.ora are not set properly. Experience shows that shared_pool_size settings are too small to have errors, in addition to Timed_statistics=true settings will also cause problems.


Incorrect Oracle execution file permissions/environment variable issues

This problem only occurs on UNIX platforms. It is common for some of you to use UNIX for convenience
The TAR command has been handled by the compression pack installation, or the system administrator has specified additional OS users can also manage the number
According to the library, the correct environment variables are not specified.

ORACLE execution files are under the $oracle_home/bin directory, and if problems occur, they should be corrected with the following Unix-like commands:

chmod 7755 $ORACLE _home/bin/oracle

In some cases, Oracle will be relink operations.
When installed over a CP copy on UNIX, there are often problems with environment variables and individual execution of program connectivity issues. Ld_
Library_path If set incorrectly can cause problems, in this case, Oracle should be relink. If
The executable file oralcle is corrupted and relink to it.

If the parallel server option is installed and distributed Lock Manager is not installed or running correctly, it can also cause errors.


Client communication is not handled properly

Problem with Sql*net drive:
If you are using a lower version of the drive, replace it with a newer version of the driver. Sql*net
Driver without connecting to the Oracle executable file can cause errors.

Check to see if the network is unobstructed

Frequently asked questions for Windows platforms:
When you create a database on the Windows platform, consider the following method if this problem occurs:
First check the local network settings. See if there are any nodes with the same name or conflicting IP on the network. If the problem persists, you can protect
Keep it in the following way:
1. Disable the network card: Change the local connection status to Disabled;
2. Open the Sqlnet.ora file (in Notepad) and comment out the NTS validation:
#SQLNET. Authentication_services= (NTS).
3. Create a database;
4. After the successful creation, restore the local area connection.

Database server crash/operating system crash/process kill

During the connection process, if the Oracle database server crashes or the operating system on which the database is located crashes, this
An error. The reason for the collapse of Oracle server may be that the main background process is dead. The kill was done incorrectly. If this is the case, it is easier to solve. In addition, an OS-related application that has a memory leak (or a virus) can also cause an Oracle daemon problem.

Recommend the wrong way:
1, to see whether the relevant process of application software is running normally;
2, to see if there is no memory leakage;
3, killing virus;
4, to determine the system administrator did not carry out misoperation;
5, determine no hacker intrusion behavior.
6. Other uncertainties ...


Oracle Internal Error/Bug

If you view the Alert.log in the Background_dump_dest directory and find errors such as ora-600, you can go to Metalin
K site to view specific information and its solution. In general, you need to make a software patch.


Errors caused by specific SQL, Pl/sql

Try separating the SQL, or you can trace it with sql_trace to find the SQL statement that caused the problem:
Under the Sqlplus:
ALTER session SET Sql_trace TRUE;

Illegal characters in SQL statements and unreasonable processing results occasionally cause problems.


Insufficient system space

Be sure to have enough space in your database system at all times. If User_dump_dest
and background_dump_dest have no space left, this can cause this problem. In addition, if the audit is turned on, the audit directory should have enough space. If trace is activated, the trace directory should have enough space.

Dave Wotton's documentation shows that when inserting data into a table, this problem can occur if the file exceeds 2G (and the file system has a 2G limit).


The problem with the firewall

If the data is going through the firewall, contact your system administrator and ask if the database data has been filtered or suddenly banned
Stop the access port. If you have a personal firewall installed locally, check your local settings.


Other aspects of the description

There are a number of reasons for this error, and there are only a few typical cases listed above. Often go to some database technology forum can be
It can help. For example, Itpub (http://www.itpub.net), Cnoug (http://www.cnoug.org) and so on.


Reference information/More reading

Http://Metalink.oracle.com
Oracle's technical Support site, the CSI number is available to log in.
Reference Note number:
note:17613.1
ORA-3113 on unix-what information to Collect
note:131207.1
How to Set UNIX environment Variables
note:131321.1
Relink Oracle Database Software on UNIX
note:22080.1

Http://www.google.com/grphp?hl=zh-CN
Google News Group

Http://www.jlcomp.demon.co.uk/faq/ORA-3113.html
A FAQ on the site of Jonathan Lewis, a technical expert

Http://home.clara.net/dwotton/dba/ora3113.htm
A very classic document that Dave Wotton summed up.