Every DBA in the Process of database management should inevitably encounter a variety of errors (ORA-1547, ORA-904, ORA-1578 ......). Some errors occur frequently.
Every DBA in the Process of database management should inevitably encounter a variety of errors (ORA-1547, ORA-904, ORA-1578 ......). Some errors occur frequently.
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. The emergence of this error is often accompanied by other errors, such as: ORA-1034 ORACLE not available in addition, the error scene is complex, may appear in:
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 reasonsBefore 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 settingIf 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 solution has two:
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. For more information, see the installation documentation on Oracle Technet. 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 VariablesThis 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 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 handled1. 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 = (CNT) 3). Create a database;
4) after the creation is successful, the local connection is restored;
Database server crash/operating system crash/process exception KillDuring 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/BugIf 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/SQLTry 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 undefinable causes may occasionally cause problems.
SQL example: SELECT * FROM (SELECT ROWNUM AS num, k. * FROM (SELECT. cp_code, c. cp_cha_name,. service_code,. service_name,. content_name, SUBSTR (. access_time, 1, 8) thedate, COUNT (*) AS hit_countFROM sm_wap_log_daily_tab a, t_cp_info cWHERE (SUBSTR (. access_time, 1, 8) BETWEEN '000000' AND '000000') AND c. cp_code LIKE '%' AND. cp_code = c. cp_codeAND. service_code LIKE '%' group by. cp_code, c. cp _ Cha_name,. service_code,. service_name,. content_name, SUBSTR (. access_time, 1, 8) order by. cp_code,. service_code,. content_name, SUBSTR (. 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. cp_code,. service_code,. content_name, SUBSTR (. access_time, 1, 8) DESC) k) n; SUBSTR (. 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. cp_code, c. cp_cha_name,. service_code,. service_name,. content_name, SUBSTR (. access_time, 1, 8) thedate, COUNT (*) AS hit_countFROM sm_wap_log_daily_tab a, t_cp_info cWHERE (SUBSTR (. access_time, 1, 8) BETWEEN '000000' AND '000000') AND c. cp_code LIKE '%' AND c. cp_code =. cp_codeAND. service_code LIKE '%' group by. cp_code, c. cp_cha_name,. service_code,. service_name,. content_name, SUBSTR (. access_time, 1, 8) order by (SUBSTR (. access_time, 1, 8),. cp_code,. service_code,. content_name DESC) k) n;
Insufficient system spaceMake 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 ProblemsIf 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 aspectsThere are many reasons for this error. The above columns are only typical cases. References
The Metalink-Oracle technical support site must have 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: failed to Relink Oracle Database Software on UNIXNote: 22080.1An Introduction to Error Message Articles
For more information about Oracle, see the Oracle topic page? Tid = 12