Solved

ORA-03113 End of file on communication Channel error when accessing Spatial Indexes

Posted on 2004-08-02
5
10,268 Views
Last Modified: 2013-12-11
Hello. I have recently installed a 9.2.0.5 Oracle Spatial database, and am getting the <ORA-03113: End of file on communication Channel> error when accessing Spatial Indexes (query/drop/create/etc). It was going fine to start with, but four invalid SYS.ODCIINDEXxxxx types were concerning me - even though they were apparently not directly causing any problems. On Metalink (TAR 3903528.996), Oracle adviced me to run the following statements:

ALTER TYPE SYS.ODCICOLINFO RESET;
ALTER TYPE SYS.ODCIARGDESC RESET;
ALTER TYPE SYS.ODCICOST RESET;
ALTER TYPE SYS.ODCIINDEXINFO RESET;

Did that, and it validated the objects. But ever since then, I get the ORA-03113 for anything that requires a spatial index. Does anyone know what the above 4 statements actually did, or how I can retract my steps? Also, has anyone ever encountered this problem before. Also, I haven't really been able to work out the link between Data Cartridge and the Spatial Indexes. Lots of questions I know, but am getting desperate!!! Any light on the subject will be appreciated! Thanks.

Shanaka
0
Comment
Question by:Shanaka111
5 Comments
 
LVL 7

Accepted Solution

by:
BobMc earned 400 total points
ID: 11701430

I assume you have no other invalid objects or dependant types? If so run utlrp

There are some bugs in the 9.2.0.5 upgrade - have  a look at Metalink note Note:222844.1, (if you havent already)

If you have reset the TYPE version, you might also need to upgrade the tables using these types with ALTER TABLE <tablename> UPGRADE INCLUDING DATA
0
 
LVL 47

Expert Comment

by:schwertner
ID: 11703209
9.2.0.5 is relatively new version.
Post a TAR on Metalink  to make Oracle aware.
0
 
LVL 4

Assisted Solution

by:ramumorla
ramumorla earned 100 total points
ID: 11705683
 
1) Is it only one tool that encounters the error or   do you get an ORA-3113 from any tool doing a similar operation?

 If the problem reproduces in SQL*Plus, use this in all tests
 below.

 2) Check if the problem is just restricted to:                            [ ] One particular UNIX user,              [ ] Any UNIX user                       or  [ ] Any UNIX user EXCEPT as the Oracle user.

 3)     Check if the problem is just restricted to:                            [ ] One particular ORACLE logon                      or  [ ] Any ORACLE logon that has access to the                                   relevant tables.

 4)     If you have a client-server configuration 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 .

 5)     Do you have a second server or database version where the                 same operation works correctly?

 Connecting to Oracle  


 If the ORA-3113 error occurs when actually connecting to Oracle then     continue with this section. If you connect to Oracle successfully and get     the error on an established connection, please go to the next section     'An Established Connection'.


 Local Connections     ~~~~~~~~~~~~~~~~~

 For local connections check the following:

 1)      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: For 7.3.X  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  For 8.1.X   % script /tmp/relink.out   % cd



$ORACLE_HOME/rdbms/lib  % make -f ins_rdbms.mk no_parropt  or  % make -f ins_rdbms.mk ops_off  For 9.2.X   Login as the Oracle software owner and shutdown all                 database instances on all nodes in the cluster.

 % script /tmp/relink.out
 % cd $ORACLE_HOME/rdbms/lib
 % make -f ins_rdbms.mk rac_off


 If this step did not fail with fatal errors,

 proceed to next step.  

 % make -f ins_rdbms.mk ioracle  Note:  If these steps fail with a Fatal Error,

 then you will need to open a Service Request and submit these errors to

 Oracle Support for analysis If the above reports any errors Oracle support will need to                    see the contents of the file /tmp/relink.out.

 2)     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.            

3) Your 'oracle' executable may be corrupt. Relink it as follows:   For 7.3.X thru 8.1.7                       Log in as the 'oracle' user.  % script /tmp/relink.out   % cd $ORACLE_HOME/rdbms/lib                % rm -f oracle   % make -f oracle.mk ioracle   % exit

  For 9.2.X  use the relink executible  

 relink                            usage:                             relink       <parameter>                              parameters: all, oracle, network,                                           client, client_sharedlib interMedia,        ctx, precomp, utilities, oemagent, ldap   If this reports any errors Oracle support will need to see    the contents of the file /tmp/relink.out .

 4)  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.

5) 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.

 Remote Connections    
 For remote connections check the following:

1)     Check if you can make LOCAL connections. If not then follow the steps above for LOCAL connection problems.            
2)     Which SQL*Net protocol are you using ?

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 as follows:         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 do not  receive any output then:

 % 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.

 3) 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.

 4)     If you have truss available try to truss the Oracle connection. You will normally need the root privilege to do this  and should use truss on the relevant listener process

 (see Table C1)

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

  % truss -o /tmp/truss.out -f -eall -p <PID_of_orasrv>    


  Attempt the connection to reproduce the ORA-3113 then  interrupt this 'truss' session.                      

 An Established Connection:  ==========================

 If the ORA-3113 error occurs AFTER you have connected to Oracle, then  it is most likely that the oracle executable has terminated unexpectedly.

1)      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 SQL*DBA or Server Manager and:

 SQLDBA> show parameter dump  

 2)     Check in your 'USER_DUMP_DEST' for any Oracle trace file.

 It is important to find the correct trace file. Use the  command 'ls -ltr' to list files in time order with the latest trace files appearing LAST.

 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.

The trace file will typically be of the form 'ora_<PID>.trc'.

3)  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 its 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 for the procedure.

One of the following 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                        If you have gdb:                         % script /tmp/core.stack                          % gdb $ORACLE_HOME/bin/oracle core                           (gdb) bt > stack.out                          (gdb) quit                                      

  4)     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 ?   To help establish this turn on SQL_TRACE for the client  tool.  

Eg: Product     Action              

SQL*Forms   Use the '-s' statistics option at run time.

 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 #2 above.

 The trace file should give a clue as to what SQL was being executed.  

5)     Try to obtain any SQL*Net trace to show what the latest   operation sent to the oracle process was.

For SQL*Net V2 ask for the article [NOTE:16564.1]

 For SQL*Net V1 check which SQL*Net protocol you are using and note the Debug environment variable from table C1 above.

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              

 6)     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.
7)     It may be useful to follow the instructions in step (4) above.

his will produce a lot of output but MAY be useful if no other information is available.
.  
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 11718958
Did you get a chance to look at the walk through I posted?

Thanks
0
 

Author Comment

by:Shanaka111
ID: 11720688
Hi guys. Due to time constraints, I decided to go ahead with a clean re-install. Much as I hate to admit it, I did miss the note that BobMC mentioned (happens to the best of us, I hope!). It was possibly (probably) due to this that I was ending up with the invalid objects that led to this in the first place. Thanks a lot for your comments/suggestions. Schwertner, yes, I had been using Metalink (had 3 TARs in relation to my Spatial install!) Ranumorla, I should have mentioned that my platform is Win2003 Server.. but thanks for the effort. You had a lot of generic debugging/testing information, but I had pretty much established most of that information. The key definitely was missing the note : ( Thanks heaps though.

Shanaka

Anyhow, I will accept BobMC's answer, as that is what tipped the scale and prompted me to re-install.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now