Archive for the ‘oracle’ tag
Oracle Spatial: java.sql.SQLRecoverableException: No more data to read from socket
We’re using Oracle Spatial on my current project so that we can locate points within geographical regions and decided earlier in the week to rename the table where we store the SDO_GEOMETRY objects for each region.
We did that by using a normal table alter statement but then started seeing the following error when we tried to insert test data in that column which takes an SDO_GEOMETRY object:
org.hibernate.exception.JDBCConnectionException: could not execute native bulk manipulation query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:219)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:396)
at $Proxy53.insertTariffZone(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:71)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:202)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1157)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
... 39 moreWe couldn’t see anything particularly wrong in what we’d done and none of the error messages we got were being particularly helpful.
Eventually we asked the DBA on our team to help out and he showed us how to look up the Oracle system logs which in our case were located at:
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
We ran the query again and noticed new files were being written to that location, one of which had the following error message:
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x40] [PC:0x2FDAE7D, mdidxid()+2563] [flags: 0x0, count: 1] DDE: Problem Key 'ORA 7445 [mdidxid()+2563]' was flood controlled (0x2) (incident: 3851) ORA-07445: exception encountered: core dump [mdidxid()+2563] [SIGSEGV] [ADDR:0x40] [PC:0x2FDAE7D] [Address not mapped to object] [] ORA-13203: failed to read USER_SDO_GEOM_METADATA view ssexhd: crashing the process... Shadow_Core_Dump = PARTIAL
We’d forgotten to rename the table in the USER_SDO_GEOM_METADATA view, exactly as the message says!
Running the following statement sorted us out:
UPDATE user_sdo_geom_metadata SET table_name = 'NEW_NAME' where table_name = 'OLD_NAME';
Oracle: dbstart – ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
We ran into an interesting problem when trying to start up an Oracle instance using dbstart whereby we were getting the following error:
-bash-3.2$ dbstart ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
Ignoring the usage message we thought that setting the environment variable was what we needed to do, but…
-bash-3.2$ export ORACLE_HOME_LISTNER=$ORACLE_HOME -bash-3.2$ dbstart ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
We ended up looking at the source of dbstart to see what was going on:
# First argument is used to bring up Oracle Net Listener ORACLE_HOME_LISTNER=$1 if [ ! $ORACLE_HOME_LISTNER ] ; then echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener" echo "Usage: $0 ORACLE_HOME"
The usage message does explain that you’re supposed to call it like this:
-bash-3.2$ dbstart $ORACLE_HOME
But it still seems a bit weird/misleading to me that you’d override the value of a global variable from inside a script which doesn’t suggest that it’s going to do that!
Such is life in Oracle land..
Oracle: exp – EXP-00008: ORACLE error 904 encountered/ORA-00904: “POLTYP”: invalid identifier
I spent a bit of time this afternoon trying to export an Oracle test database so that we could use it locally using the exp tool.
I had to connect to exp like this:
exp user/password@remote_address
And then filled in the other parameters interactively.
Unfortunately when I tried to actually export the specified tables I got the following error message:
EXP-00008: ORACLE error 904 encountered ORA-00904: "POLTYP": invalid identifier EXP-00000: Export terminated unsuccessfully
I eventually came across Oyvind Isene’s blog post which pointed out that you’d get this problem if you tried to export a 10g database using an 11g client which is exactly what I was trying to do!
He explains it like so:
The export command runs a query against a table called EXU9RLS in the SYS schema. On 11g this table was expanded with the column POLTYP and the export command (exp) expects to find this column.
I needed to download the 10g client so that I could use that version of exp instead. I haven’t quite got it working yet but at least it’s a different error to deal with!
