Oracle ORA-03113 Error Analysis and Solution

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 channelORA-01034: ORACLE not availableORA-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 586ORACLE 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:

First, check the local network settings. check whether there are any nodes with the same name or conflicting IP addresses on the network. If the problem persists, use the following method:

1) Disable NIC: Change the local connection status to disabled;
2) Open the sqlnet. ora file (in Notepad form), and comment out the CNT Verification:

#SQLNET.AUTHENTICATION_SERVICES= (NTS) 

3). Create a database;
4) after the creation is successful, the local connection is restored;

• Database server crash/operating system crash/process exception Kill

During the connection process, if the Oracle database Server crashes or the operating system where the database is located crashes, this error occurs. The cause of the Oracle Server crash may be that the main background process is dead, the Kill operation is performed incorrectly. It is easier to solve this problem. In addition, memory leakage (or virus) in OS-related applications may also cause Oracle background program problems. Recommended troubleshooting steps:

• 1. Check whether the application software processes are running normally;
• 2. Check for Memory leakage;
• 3. virus detection and removal;
• 4. Check whether the system administrator has misoperations;
• 5. Determine No hacker intrusion;
• 6. other uncertainties ......

• Oracle internal error/Bug

If you find errors such as ora-600/ora-07445 found in alert. log in the background_dump_dest directory, you can view the specific information and solutions on the Metalink site. Install software patches.

• Errors Caused by specific SQL, PL/SQL

Try to execute the SQL statement separately, or use SQL _TRACE to trace the SQL statement that causes the problem. Under SQLPlus:

ALTER SESSION SET sql_trace=TRUE;

Invalid characters and unreasonable processing results in SQL statements, and even some uninterpretable causes occasional problems.
SQL example:

SELECT *  FROM (SELECT ROWNUM AS num, k.*          FROM (SELECT   a.cp_code, c.cp_cha_name, a.service_code,                         a.service_name, a.content_name,                         SUBSTR (a.access_time, 1, 8) thedate,                         COUNT (*) AS hit_count                    FROM sm_wap_log_daily_tab a, t_cp_info c                   WHERE (SUBSTR (a.access_time, 1, 8) BETWEEN '20040301'                                                           AND '20040304'                         )                     AND c.cp_code LIKE '%%'                     AND a.cp_code = c.cp_code                     AND a.service_code LIKE '%%'                GROUP BY a.cp_code,                         c.cp_cha_name,                         a.service_code,                         a.service_name,                         a.content_name,                         SUBSTR (a.access_time, 1, 8)                ORDER BY a.cp_code,                         a.service_code,                         a.content_name,                         SUBSTR (a.access_time, 1, 8) DESC) k) n;

The above statement always produces a 9204 error on the 03113/Linux system. The statement is refined and divided into smaller sub-statements for gradual execution. Finally, the problem occurs in

                 ORDER BY a.cp_code,                         a.service_code,                         a.content_name,                         SUBSTR (a.access_time, 1, 8) DESC) k) n; 

SUBSTR (a. access_time, 1, 8) here. Remove SUBSTR (a. access_time, 1, 8. Try to adjust the position of SUBSTR (a. access_time, 1, 8) and the statement is passed. Then, optimize the statement. :)

SELECT *  FROM (SELECT ROWNUM AS num, k.*          FROM (SELECT   a.cp_code, c.cp_cha_name, a.service_code,                         a.service_name, a.content_name,                         SUBSTR (a.access_time, 1, 8) thedate,                         COUNT (*) AS hit_count                    FROM sm_wap_log_daily_tab a, t_cp_info c                   WHERE (SUBSTR (a.access_time, 1, 8) BETWEEN '20040301'                                                           AND '20040304'                         )                     AND c.cp_code LIKE '%%'                     AND c.cp_code = a.cp_code                     AND a.service_code LIKE '%%'                GROUP BY a.cp_code,                         c.cp_cha_name,                         a.service_code,                         a.service_name,                         a.content_name,                         SUBSTR (a.access_time, 1, 8)                ORDER BY (SUBSTR (a.access_time, 1, 8)),                         a.cp_code,                         a.service_code,                         a.content_name DESC) k) n;

• Insufficient system space

Make sure that the database system has sufficient space at any time. this problem occurs if USER_DUMP_DEST and BACKGROUND_DUMP_DEST have no space left. in addition, if AUDIT is enabled, the AUDIT Directory should have enough space. if Trace is activated, the Trace Directory should have enough space. dave Wotton's Local Copy statement indicates that when inserting data into a table, if the file exceeds 2 GB (and the file system has 2 GB limit), this problem will occur.

• Firewall Problems

If the data needs to pass through the firewall, contact the system administrator to check whether the database data is filtered or the communication port is suddenly disabled. If a personal firewall is installed locally, check the local settings.

• Other aspects

There are many reasons for this error. The above columns are only typical cases.

References
Metalink-Technical Support site for http://metalink.oracle.com Oracle, which requires a CSI number to log on.
REFERENCE Note No:

Note:17613.1ORA-3113 on Unix - What Information to CollectNOTE:131207.1How to Set UNIX Environment VariablesNote:131321.1How to Relink Oracle Database Software on UNIXNote:22080.1An Introduction to Error Message Articles

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12