ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 棘手的ora-03113

棘手的ora-03113

原创 Linux操作系统 作者:jlandzpa 时间:2019-06-23 07:15:05 0 删除 编辑
再续
 

        C3)     Check in your 'BACKGROUND_DUMP_DEST' for your alert log and
                 any other trace files produced close to the time of the error.
                 On Unix this should be named 'alert_.log'.
                 On NT this should be named 'ALRT.LOG'
 

        C4)     Unix Only:
                 If there is no trace file check for a 'core' dump in the
                CORE_DUMP_DEST. Check thus:
 

                        % cd $ORACLE_HOME/dbs   # Or your CORE_DUMP_DEST
                        % ls -l core*
 

                If there is a file called 'core' check its time matches the 
                time of the problem. If there are directories called 
                'core_' 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
                for how to do this - one of these should work for your 
                platform:
 

 

                    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 does not show the failing statement then
                 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 SQL*Net trace may help to show what the latest operation sent 
                 to the 'oracle' process was. 
                For SQL*Net V2 / V8 tracing see the article <Note:16564.1>
         
                 Historical note:

                                     For SQL*Net V1 check which SQL*Net protocol you are using 

                            and note the 'Debug' environment variable from table B1 below.

                            Then catch SQL*Net trace from the client. Eg: For SQL*Net

                            TCP/IP and sqlplus:

            

                                    % setenv OSNTDBUG -1    # Use correct OSN*DBUG variable

                                    % sh

                                    % sqlplus scott/tiger@t:hostname:sid 2>/tmp/net1.out

                        
 

        C7)     Based on information collected above try to determine 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 raised 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 O/S
                 level (Eg: kill -9 on Unix). 
 


(D) Server trace reports ORA-3113

         D1)      It is unusual for a server trace to report ORA-3113.
                 However, this can occur if the server loses contact with 
                 the client OR a database link connection.
                 Treat this the same as an ORA-3113 in a client process
                 and follow the steps in Section C.
 

         D2)      The following line may be added to the init.ora file
                 to help collect maximum information when the error occurs:
 

                          event="3113 trace name errorstack level 3"
 

                 If you already have "EVENT=" lines in the init.ora file
                 this MUST directly below the other "Event=" lines.

(E) Additional Checks / Information

        E1)     Is it just this one tool that encounters the error or 
                do you get ORA-3113 from any tool doing a similar operation ?
                If the problem reproduces in SQL*Plus use this in all tests 
                below.
 

        E2)     Unix only:
                 Check if the problem is just restricted to:
 

                        [ ] One particular UNIX user,
                        [ ] Any UNIX user 
                    or  [ ] Any UNIX user EXCEPT as the Oracle user.
 

 

        E3)     Check if the problem is just restricted to:
 

                        [ ] One particular ORACLE logon
                    or  [ ] Any ORACLE logon that has access to the 
                                relevant tables.
 

        E4)     If this is a client-server set up does this occur from:
 

                        [ ] Any client 
                        [ ] Just one particular client 
                    or  [ ] Just one group of clients ?
                            If so what do these clients have in common ?
                            Eg: Software release .
                
        E5)     Do you have a second server or database version where the
                same operation works correctly ?
 

 

         E6)      Ensure there is free disk space in:
 

                   a. Your USER_DUMP_DEST and BACKGROUND_DUMP_DEST locations
 

                   b. Your AUDIT destination (Unix)
                          The default is $ORACLE_HOME/rdbms/audit
 

                   c. Your Oracle Trace directory if Oracle Trace is enabled
                          See <Note:45482.1>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60525/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 棘手的ora-03113
下一篇: 测试
请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    268
  • 访问量
    172258