Analysis and Solution of ORA-03113 errors

Preface
Every DBA in the Process of database management should inevitably encounter a variety of errors (ORA-1547, ORA-904, ORA-1578 ......). Some errors are often called "classic errors" by Oracle DBAs due to frequent and complex causes ". The ORA-3113 "end of file on communication channel" is like this.

We can simply interpret this error as an disconnection between the Oracle client process and the database background process. However, there are actually many reasons for this error: Improper database setup and any behavior that can cause the database background process to crash may cause this error. This error is often accompanied by other errors, such:
ORA-1034 (ORACLE not available)
In addition, the error may occur in the following scenarios:
When Oracle is started
When trying to create a database
When trying to connect to the database
When the client is running SQL/PL/SQL
Database Backup/restoration
In other cases ......
On the forum, we often see the help of the junior DBA for this issue. Here we will simply sort out the problem.

Error causes

According to the situations on the Internet, there are many reasons for the error:
Improper Unix core parameter settings
Incorrect Oracle execution file permissions/Environment Variables
Client Communication cannot be properly handled
Database server crash/operating system crash/process kill
Oracle internal error
Errors Caused by specific SQL, PL/SQL
Insufficient Space
Firewall Problems
Other reasons
Before solving the problem, do the following:
Recall what you did before an error occurs. The more detailed the operation, the better;
Check the alertSID. log file in the background_dump_dest directory;
Search by Google. COM. There is a lot of information on the Internet waiting for you to find out. Don't ask anyone.
Of course, if you find something that is more helpful to you-this document does not need to be read :-)

Error Cause Analysis

Improper Unix core parameter setting/improper init parameter setting
If the operating system core variables are not set correctly during the database installation process, there may be no problems when installing the database file, and 03113 errors may often occur during database creation. Another reason for this is that the processes parameter in the init. ora parameter file specifies an unreasonable value, causing an error when the database is started (of course, this is also the core parameter problem in the final analysis ).

This error message is generally as follows:
ORA-03113: end-of-file on communication channel
A ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

There are two solutions:

1. Modify the core parameters and increase the value of the corresponding core parameters (recommended );
2. Reduce the Processes value of the init. ora parameter.

Note that:
SEMMSL must be set to at least 10 + 'maximum Number of processs ';
SEMMNS also depends on the process parameter values of each database.
Note:

This error type only appears on Unix platforms. In Windows, if the processes value is too large, an error similar to the following occurs:
ORA-00068: invalid value 24200001 for parameter max_rollback_segments,
Must be between 2 and 65535

/* The specified parameter value exceeds 65535 */

Or
ORA-27102: out of memory
/* A big parameter value less than 65535 */software environment:
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Oracle rdbms Version: 8.1.7.0.0

Changing core parameters on a specific platform may vary, see the installation documentation on Oracle Technet (http://otn.oracle.com. The installation documentation for a specific Unix platform also explains the core parameter meanings. If the parameters in Init. ora are improperly set, this error occurs. Experience shows that the shared_pool_size setting is too small, and the timed_statistics = true setting also causes problems.
Incorrect Oracle execution file permissions/Environment Variables
This problem only occurs on Unix platforms. In some cases, the Administrator sometimes installs the compressed packages that have been processed by the Unix tar command for convenience, or the system administrator has specified an additional OS user to manage the database without specifying correct environment variables.
The Oracle execution file is in the $ ORACLE_HOME/bin directory. If any problem occurs, use the following Unix Command to correct it:
# Chmod 7755 $ ORACLE_HOME/bin/oracle
Sometimes you need to relink Oracle.
When you use cp to copy and install the program on Unix, the environment variable and the execution program are often connected. LD_LIBRARY_PATH may cause problems if it is set incorrectly. In this case, you need to relink Oracle. If the executable file oralcle is damaged, relink it. If the parallel server option is installed and the Distributed Lock Manager is not installed or runs properly, errors may occur.
Client Communication cannot be properly handled
1. SQL * Net drive problems:
If you are using a drive with a lower version, replace the drive with the new version. An error occurs when the SQL * Net driver is not connected to the Oracle executable file.
2. Check whether the TCP/IP network is smooth;
3. Common network problems on Windows:
If this problem occurs when creating a database on Windows, consider using the following methods:

1 2 Next Page