ITPub博客

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

棘手的ora-03113

原创 Linux操作系统 作者:jlandzpa 时间:2019-03-19 13:00:06 0 删除 编辑

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

                          SQL and BIND activity during startup
                          REDO applied
                          Information about transactional rollback required
 

         A4.3)    Startup the instance as described at the top of this section.
                 
                 As soon as the error occurs REMOVE the above events from the init.ora
                 file and shutdown. Collect together the trace files and alert logs
                 as described in Section C
 

                 
 


(B) ORA-3113 attempting to connect to the database

   
   Which SQL*Net layer are you using ?
 

         a. SQL*Net2 or Net8  -> Goto B1
            This is the default for Oracle 7.0.15 onwards
 

         b. SQL*Net 1          -> Goto B2
            Note: This can only be used with Oracle releases before 7.3
 

 

B1) Connecting to Oracle using SQL*Net V2 / Net8
 

   Net8 or SQL*Net2 should report network related errors if a problem
   occurs whilst establishing a connection to a remote Oracle shadow 
   process. An ORA-3113 implies that the connection itself has been established
   but then is lost. As such follow the steps in Section C
 

B2) Connecting to Oracle using SQL*Net V1
 

   This section is included for historical reasons. 
 

 

   Is the tool you are trying to connect from

 

            a. A local connection ? -> Goto BL

               ie: The client is on the same machine as the database and you are

                        not trying to connect over a network connection.

 

            b. A remote connection ? -> Goto BR

 

 

   BL) Local SQL*Net V1 Connections

 

      For local connections check the following:

 

        BL1)    Have you installed the Parallel Server Option ?

                ORA-3113 will occur if you have installed the Parallel

                Server Option but do NOT have a Distributed Lock Manager

                installed or running.

 

                To deinstall the Parallel Server Option:

 

                        Shut down any Oracle instances

                        % script /tmp/relink.out

                        % cd $ORACLE_HOME/rdbms/lib

                        # 'oracle' should not exist so delete it if it present

                        % rm -f oracle

                        % make -f oracle.mk no_parropt ioracle

                        % exit

 

                If this reports any errors Oracle support will need to see

                the contents of the file /tmp/relink.out .

 

 

        BL2)    Try using the SQL*Net V1 driver for local connections:

 

                        setenv TWO_TASK P:

 

                Then try the client tool. If this now works you may have a 

                problem with the default SQL*Net driver.

 

 

        BL3)    Your 'oracle' executable may be corrupt. Relink it thus:

 

                        Log in as the 'oracle' user.

                        % script /tmp/relink.out

                        % cd $ORACLE_HOME/rdbms/lib

                        % rm -f oracle        

                        % make -f oracle.mk ioracle

                        % exit

 

                If this reports any errors Oracle support will need to see

                the contents of the file /tmp/relink.out .

 

 

        BL4)    Some Unix platforms need LD_LIBRARY_PATH to be set 

                correctly to resolve any dynamically linked libraries.

                As the user with the problem:

 

                        % script /tmp/ldd.out

                        % id

                        % cd $ORACLE_HOME/bin

                        % ldd oracle

                        % exit

 

                If the 'ldd' command does not exist go to the next step below.

                Check that all lines listed show a full library file. If there

                are any 'not found' lines reported contact Oracle support

                with the output of /tmp/ldd.out .

 

 

        BL5)    If you cannot connect as the Oracle user AND your system has 

                the 'truss' command try the following when logged in as 

                'oracle' (using the relevant client tool):

                

                        % truss -o /tmp/truss.out -f sqlplus user/password

                        Exit from sqlplus (or the problem tool)

 

                Keep the file /tmp/truss.out safe - Oracle MAY need to see it.

 

 

   BR) Remote SQL*Net v1 Connections

 

      For remote connections check the following:

 

        BR1)    Check if you can make LOCAL connections. If not then follow

                the steps above for LOCAL connection problems.

 

 

        BR2)    Which SQL*Net protocol are you using ?

 

                Table B.1

                ~~~~~~~~~

                SYMBOL     SQL*Net V1 Layer  Prefix  Debug       Listener

                ------     ----------------  ------  -----       --------

                osnptt      PIPE Two Task     P:     OSNTTD      none

                osnasy      ASYNC             A:     OSNADBUG    none

                osnttt      TCP/IP Two Task   T:     OSNTDBUG    orasrv

                osntlitcp   TCP/TLI Two Task  TT:    OSNTLIDBUG  tcptlisrv

                osntlispx   SPX Two Task      X:     OSNTLIDBUG  spxsrv

                osndnt      DECNET Two Task   D:     OSNDDBUG    none

 

 

                For the protocol you are using check that the 'oracle'

                executable has this linked in thus:

 

                        Log in as oracle on the server

                        % script /tmp/drivers.out

                        % cd $ORACLE_HOME/bin

                        % drivers oracle

                        % exit

 

                        Eg: If you are using TCP/IP it should list TCP/IP.

 

                If the 'drivers' command does not exist on your machine check

                the 'oracle' executable as below substituting the relevant 

                symbol from Table C.1 for the word 'SYMBOL'. If you get *NO*

                output the driver is probably *NOT* installed.

 

                        % script /tmp/symbols.out

                        % cd $ORACLE_HOME/bin

                        % nm oracle | grep -i SYMBOL    # Use relevant SYMBOL

                        % exit

 

                        Eg: For SQL*Net TCP/IP you would use the command:

 

                                % nm oracle | grep -i osnttt

 

                If the required driver is not installed you should:

 

                        a) Relink Oracle (See step (B3) above).

                        b) Re-check the 'oracle' executable for the

                           relevant driver. If it is still missing then

                           the relevant SQL*Net driver has probably not 

                           been installed. Reinstall the required SQL*Net

                           driver.

 

 

        BR3)    Check your /etc/oratab or /var/opt/oracle/oratab file

                is of the form:

 

                        # Comments begin with a HASH

                        SID:/path/to/oracle/home:N

 

                And confirm:

                

                        [ ] There are no blank lines.

                        [ ] The PATH to ORACLE_HOME is correct and contains

                             no environment variables.

                        [ ] There are no ':'s in the ORACLE_HOME path.

                        [ ] There is NOTHING at the end of the line.

                            The last character on a line should be Y or N.

                            There should NOT be a fourth field.

 

 

        BR4)    Unix only:

                        If you have 'truss' available try to truss the Oracle 

                connection. You will normally need 'root' privilege to do this

                and should use truss on the relevant listener process (see

                Table B1)

                Eg: For TCP/IP the listener is 'orasrv' so enter these commands

                    as 'root':

 

                        % truss -o /tmp/truss.out -f -eall -p 

                

                        Attempt the connection to reproduce the ORA-3113 then

                        interrupt this 'truss' session.

 

                This will produce a LOT of output so keep it but do not send it

                to Oracle initially - just make a note that it is available.

 

 


(C) Client sees ORA-3113 running SQL / PLSQL

   
   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 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 Server Manager and:
 

                        SVRMGRL> show parameter dump
 

 

        C2)     Check in your 'USER_DUMP_DEST' for any Oracle trace file.
                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 '_ora_.trc'.
                 On NT:
                   Click on the "Modified" column in Windows Explorer to sort the 
                   files by their modified date. Files will typically be of the form
                   'ORA.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.

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

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

注册时间:2001-10-12

  • 博文量
    110
  • 访问量
    82311