经典错误之ora 03113

[shezhifei]

最近升级某个存储过程，升级日志无错误，但升级后，此procedure被监听调用一段时间后就报错，ora-03113
手动执行此procedure也是执行一段时间后，就报此错误。

[小灰狼W]

If the ORA-3113 error occurs AFTER you have connected to Oracle then it is most likely that the 'oracle' executable has terminated unexpectedly. C1) Determine which database you were connected to and obtain the following init.ora/spfile parameter values: Parameter Default ~~~~~~~~~ ~~~~~~~ USER_DUMP_DEST $ORACLE_HOME/rdbms/log BACKGROUND_DUMP_DEST $ORACLE_HOME/rdbms/log CORE_DUMP_DEST $ORACLE_HOME/dbs Eg: To find these log into SQL*Plus and issue: SQL> show parameter dump C2) Check in your 'USER_DUMP_DEST' for any Oracle trace files. It is important to find the correct trace file. On UNIX: Use the command 'ls -ltr' to list files in time order with the latest trace files appearing LAST. The trace file will typically be of the form '<SID>_ora_<PID>.trc'. On Windows: Click on the "Modified" column in Windows Explorer to sort the files by their modified date. Files will typically be of the form 'ORA<PID>.TRC'. If you are not sure which trace file may be relevant MOVE all the current trace files to a different directory and reproduce the problem. C3) Check in your 'BACKGROUND_DUMP_DEST' for your alert log and any other trace files produced close to the time of the error. It should be named 'alert_<SID>.log'. C4) UNIX only: If there is no trace file, check for a 'core' dump in the CORE_DUMP_DEST. Check as follows: % cd $ORACLE_HOME/dbs # Or your CORE_DUMP_DEST % ls -l core* If there is a file called 'core' check that it's time matches the time of the problem. If there are directories called 'core_<PID>', check for core files in each of these. It is IMPORTANT to get the correct core file. Now obtain a stack trace from this 'core' file. Check each of the sequences below to see how to do this - one of these should work for your platform. Please, refer to Note 1812.1 - TECH: Getting a Stack Trace from a CORE file on Unix If you have dbx: % script /tmp/core.stack % dbx $ORACLE_HOME/bin/oracle core (dbx) where ... (dbx) quit % exit If you have sdb: % script /tmp/core.stack % sdb $ORACLE_HOME/bin/oracle core * t ... * q % exit If you have xdb: % script /tmp/core.stack % xdb $ORACLE_HOME/bin/oracle core (xdb) t ... (xdb) q % exit If you have adb: % script /tmp/core.stack % adb $ORACLE_HOME/bin/oracle core $c ... $q % exit C5) Try to isolate the SQL command that is executing when the error occurs. Eg: Is it a particular SQL statement or PL/SQL block that causes the error ? In many cases this will be listed in the trace file produced under the heading "Current SQL statement", or near the middle of the trace file under the cursor referred to by the "Current cursor NN" line. If the trace file does not show the failing statement then SQL_TRACE may be used to help determine this, provided the problem reproduces. SQL_TRACE can be enabled in most client tools: Eg: Product Action ~~~~~~~ ~~~~~~ SQL*Plus Issue 'ALTER SESSION SET SQL_TRACE TRUE;' Pro* EXEC SQL ALTER SESSION SET SQL_TRACE TRUE; This should force a server side SQL trace file as detailed in C2 above. The trace file should give a clue as to what SQL was being executed. C6) If no trace file can be found and the problem is reproducible then a SQL*Net trace may help to show what the latest operation sent to the 'oracle' process was. C7) Based on the information collected above try to put together a small test case which will reproduce the problem. This is important for two reasons: a) It gives Oracle Support a small test case if the problem does not look like a known problem. b) It gives you a simple way to check if any patch supplied will fix the problem. C8) If a statement can be isolated which consistently raises an ORA-3113 error then it is worth spending some time collecting additional information, such as: - An execution plan for the statement - Table definitions, column definitions - Information on constraints, triggers etc.. ie: Any additional information about the statement which fails. eg: If a SELECT fails then it may succeed if run under a different optimizer mode. C9) Check if your server Administrator has any scripts which abort long running or CPU intensive processes. An ORA-3113 process can occur if someone kills your Oracle shadow process at the O/S level (Eg: kill -9 on UNIX).

[小灰狼W]

在服务器本地执行该存储过程试试，如果可以成功，检查网络和防火墙 如果和网络无关，按上面官方建议的步骤排查