Oracle exception

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle exception

Zdeněk Vráblík-3
Hi all,

I am using Bitronix 1.1 Oracle 10g driver 10.2.0.1 and "sometimes" I
get this errror:

java.sql.SQLException: ORA-29875: failed in the execution of the
ODCIINDEXINSERT routine
ORA-29400: data cartridge error
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 623
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 227

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
        at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:212)
        at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:951)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
        at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4245)

It happens after any exception has happend in database in previous
request. That request was rolled back and new request started new
UserTransaction which ends with this exception.

I have got frequently this exception during using SimpleJTA library
and I have thought it is because of that library.

Have you ever seen such exception?

Thanks for all suggestions.

Regards,
Zdenek

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Ludovic Orban
Administrator
Hi,

I've google'd the oracle error codes you posted and could only find vague information. I'm not a DBA, even less an Oracle one so please forgive my lack of knowledge about oracle-specific features and correct me if I'm wrong.

In my understanding the ODCIIndexInsert is a callback method called by Oracle during usage of Domain Indexes. It seems to be locking a temporary table without waiting if the lock cannot be acquired. Is it a method you wrote yourself (or by someone else in your company) or is it part of some Oracle package ?

Since it seems to only happen after an error has been reported by a previous transaction, there might be something left uncleaned, either by the TM or by your own code.

Can you reproduce that issue with a short piece of code ? In that case I'd advise you to collect BTM's debug logs and post them here. They might tell us more about potential cleaning issues. Tracing the request from the database's side might help as well.

Also query the SYS.DBA_2PC_PENDING system view to make sure there is no pending transaction left. I'd also advise you to upgrade to BTM 1.2 as there is a bug in version 1.1 that make it silently ignore recovery errors.

Lat but not least, since this look very much like an Oracle-specific problem, get support from Oracle DBAs, forums, helpdesk, whatever else you have access to.

Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Zdeněk Vráblík-3
Thanks,
I am going to create small test app and try reproduce this error.

Regards,
Zdenek


On Fri, Apr 4, 2008 at 10:06 AM, Ludovic Orban <[hidden email]> wrote:

>
>  Hi,
>
>  I've google'd the oracle error codes you posted and could only find vague
>  information. I'm not a DBA, even less an Oracle one so please forgive my
>  lack of knowledge about oracle-specific features and correct me if I'm
>  wrong.
>
>  In my understanding the ODCIIndexInsert is a callback method called by
>  Oracle during usage of Domain Indexes. It seems to be locking a temporary
>  table without waiting if the lock cannot be acquired. Is it a method you
>  wrote yourself (or by someone else in your company) or is it part of some
>  Oracle package ?
>
>  Since it seems to only happen after an error has been reported by a previous
>  transaction, there might be something left uncleaned, either by the TM or by
>  your own code.
>
>  Can you reproduce that issue with a short piece of code ? In that case I'd
>  advise you to collect BTM's debug logs and post them here. They might tell
>  us more about potential cleaning issues. Tracing the request from the
>  database's side might help as well.
>
>  Also query the SYS.DBA_2PC_PENDING system view to make sure there is no
>  pending transaction left. I'd also advise you to upgrade to BTM 1.2 as there
>  is a bug in version 1.1 that make it silently ignore recovery errors.
>
>  Lat but not least, since this look very much like an Oracle-specific
>  problem, get support from Oracle DBAs, forums, helpdesk, whatever else you
>  have access to.
>
>  Ludovic
>  --
>  View this message in context: http://www.nabble.com/Oracle-exception-tp16479456p16489439.html
>  Sent from the BTM Users mailing list archive at Nabble.com.
>
>
>  ---------------------------------------------------------------------
>  To unsubscribe from this list, please visit:
>
>     http://xircles.codehaus.org/manage_email
>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Zdeněk Vráblík-3
Hi Ludovic,

I have found the reason. It is in spatial indexes (Oracle GIS extension).

Everything works fine when I switch off these spatial indexes. Than it
is not problem in TransactionManager.

What happens when the UserTransaction.rollback() is called? Where
could I find it in Bitronix?
It happens only after rollback.

Thanks.

Regards,
Zdenek

On Fri, Apr 4, 2008 at 11:12 AM, Zdeněk Vráblík <[hidden email]> wrote:

> Thanks,
>  I am going to create small test app and try reproduce this error.
>
>  Regards,
>  Zdenek
>
>
>
>
>  On Fri, Apr 4, 2008 at 10:06 AM, Ludovic Orban <[hidden email]> wrote:
>  >
>  >  Hi,
>  >
>  >  I've google'd the oracle error codes you posted and could only find vague
>  >  information. I'm not a DBA, even less an Oracle one so please forgive my
>  >  lack of knowledge about oracle-specific features and correct me if I'm
>  >  wrong.
>  >
>  >  In my understanding the ODCIIndexInsert is a callback method called by
>  >  Oracle during usage of Domain Indexes. It seems to be locking a temporary
>  >  table without waiting if the lock cannot be acquired. Is it a method you
>  >  wrote yourself (or by someone else in your company) or is it part of some
>  >  Oracle package ?
>  >
>  >  Since it seems to only happen after an error has been reported by a previous
>  >  transaction, there might be something left uncleaned, either by the TM or by
>  >  your own code.
>  >
>  >  Can you reproduce that issue with a short piece of code ? In that case I'd
>  >  advise you to collect BTM's debug logs and post them here. They might tell
>  >  us more about potential cleaning issues. Tracing the request from the
>  >  database's side might help as well.
>  >
>  >  Also query the SYS.DBA_2PC_PENDING system view to make sure there is no
>  >  pending transaction left. I'd also advise you to upgrade to BTM 1.2 as there
>  >  is a bug in version 1.1 that make it silently ignore recovery errors.
>  >
>  >  Lat but not least, since this look very much like an Oracle-specific
>  >  problem, get support from Oracle DBAs, forums, helpdesk, whatever else you
>  >  have access to.
>  >
>  >  Ludovic
>  >  --
>  >  View this message in context: http://www.nabble.com/Oracle-exception-tp16479456p16489439.html
>  >  Sent from the BTM Users mailing list archive at Nabble.com.
>  >
>  >
>  >  ---------------------------------------------------------------------
>  >  To unsubscribe from this list, please visit:
>  >
>  >     http://xircles.codehaus.org/manage_email
>  >
>  >
>  >
>
Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Ludovic Orban
Administrator
Zdenek,

The rollback code is in the bitronix.tm.twopc.Rollbacker class (http://fisheye.codehaus.org/browse/btm/branches/1.2/src/bitronix/tm/twopc/Rollbacker.java?r=104).

Basically all it does is calling javax.transaction.xa.XAResource.rollback() on all enlisted resources (ie: all databases & JMS servers used during the transaction). This happens at line 110 in the Rollbacker class.

This is exactly as per XA spec, there is nothing special at all about that. to know more about the XA internals I'd recommend you to read this: http://jroller.com/pyrasun/category/XA

Since you narrowed the problem to spatial indexes, I advise you to look deeper in this area. I noticed you posted a question in Oracle's support forum (http://forums.oracle.com/forums/thread.jspa?threadID=638791&tstart=0) and I hope you'll get an answer on that pretty quickly. It might also help using new versions of the database and/or the JDBC driver.

At last resort (and only if everything else failed) you can register Oracle as a non-XA resource as described here: http://docs.codehaus.org/display/BTM/LastResourceCommit

Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Zdeněk Vráblík-3
Hi Ludovic,

I have found the reason of the Oracle exception.
The Oracle Spatial pl sql functions create temp tables and Oracle
doesn't support Temporary tables in distributed transaction.

I have solved it with taking the  underlying connection from
JdbcConnectionHandler. I use the underlying connection to do the
readonly request. It seems it works.

I am just wondering if there is any limitation of using the underlying
connection or do you think it may cause any problems I can't see now?

 I will put full solution description into Oracle forum and Bitronix
wiki later this week.

Thanks.

Regards,
Zdenek

On Sat, Apr 5, 2008 at 9:38 AM, Ludovic Orban <[hidden email]> wrote:

>
>  Zdenek,
>
>  The rollback code is in the bitronix.tm.twopc.Rollbacker class
>  (http://fisheye.codehaus.org/browse/btm/branches/1.2/src/bitronix/tm/twopc/Rollbacker.java?r=104).
>
>  Basically all it does is calling javax.transaction.xa.XAResource.rollback()
>  on all enlisted resources (ie: all databases & JMS servers used during the
>  transaction). This happens at line 110 in the Rollbacker class.
>
>  This is exactly as per XA spec, there is nothing special at all about that.
>  to know more about the XA internals I'd recommend you to read this:
>  http://jroller.com/pyrasun/category/XA
>
>  Since you narrowed the problem to spatial indexes, I advise you to look
>  deeper in this area. I noticed you posted a question in Oracle's support
>  forum (http://forums.oracle.com/forums/thread.jspa?threadID=638791&tstart=0)
>  and I hope you'll get an answer on that pretty quickly. It might also help
>  using new versions of the database and/or the JDBC driver.
>
>  At last resort (and only if everything else failed) you can register Oracle
>  as a non-XA resource as described here:
>  http://docs.codehaus.org/display/BTM/LastResourceCommit
>
>  Ludovic
>  --
>  View this message in context: http://www.nabble.com/Oracle-exception-tp16479456p16510860.html
>
>
> Sent from the BTM Users mailing list archive at Nabble.com.
>
>
>  ---------------------------------------------------------------------
>  To unsubscribe from this list, please visit:
>
>     http://xircles.codehaus.org/manage_email
>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Ludovic Orban
Administrator
Thanks for posting your solution, that might help other poeple as well.

About the JdbcConnectionHandler's underlying connection, I would only use it at last resort. There might be some other more standard way to achieve the same result.

Since this seems to be caused by the running XA transaction, have you tried suspending the transaction before executing your readonly query ? This works the same as the EJB 'NOT_SUPPORTED' parameter: when the transaction is suspended, any work done on the connection is made outside the scope of the XA transaction.

Eg:

Transaction t = tm.suspend();
// any statement executed here will be executed outside the current XA transaction
tm.resume(t);

This would make your code more maintainable and portable to another TM or would not break if I ever had to change the BTM internals. I'd suggest you to give a try to this solution. If it does not work you can always revert back to the solution you found.

Thanks for your feedback.
Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Zdeněk Vráblík-3
Hi,
I got response from Oracle:
In a distributed transaction, different branches of the transaction
can execute in different sessions. The branches c
an detach from their current session and migrate to another within the transacti
on scope. To maintain the consistency of Spatial indexes in distributed transact
ions, you must follow the usage guidelines in this section.

When the first insert, update, or delete operation on a spatial table
(one with a spatial index) is
performed in a distributed transaction, all subsequent insert, update, or delet
e operations on the table, as well as any prepare to commit operation (the first
branch to prepare a commit), in the transaction should happen in the same sessi
on as the first operation. The branches performing these subsequent operations w
ill first have to connect to the session in which the first operation was perfor
med.
< end Oracle response

I am not familiar so much with distributed transactions. Is ti
possible to limit all transaction to one branch or session?

I am going to test another recomendation to set spatial index version
to 1. (default is 4) This setting should dissable better(quicker)
spatial indexing. It will be slower, but it may be sufficient.

What do you think about the limitation for one branch or session? Is
it possible to set it in Bitronix or it depends on the database
transaction business logic?

Thanks.

Regards,
Zdenek

On Wed, Apr 30, 2008 at 4:10 PM, Ludovic Orban <[hidden email]> wrote:

>
> Thanks for posting your solution, that might help other poeple as well.
>
> About the JdbcConnectionHandler's underlying connection, I would only use it
> at last resort. There might be some other more standard way to achieve the
> same result.
>
> Since this seems to be caused by the running XA transaction, have you tried
> suspending the transaction before executing your readonly query ? This works
> the same as the EJB 'NOT_SUPPORTED' parameter: when the transaction is
> suspended, any work done on the connection is made outside the scope of the
> XA transaction.
>
> Eg:
>
> Transaction t = tm.suspend();
> // any statement executed here will be executed outside the current XA
> transaction
> tm.resume(t);
>
> This would make your code more maintainable and portable to another TM or
> would not break if I ever had to change the BTM internals. I'd suggest you
> to give a try to this solution. If it does not work you can always revert
> back to the solution you found.
>
> Thanks for your feedback.
> Ludovic
> --
> View this message in context: http://www.nabble.com/Oracle-exception-tp16479456p16984928.html
> Sent from the BTM Users mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Ludovic Orban
Administrator
Zdenek,

What Oracle calls as session is I guess a JDBC connection. A branch is simply the part of a XA transaction on one database. One branch always sticks to a single connection unless you migrate it yourself (using tm.suspend and tm.resume).

According to their description of the DB's limitations, it should just work out of the box unless your logic involves transaction suspension on a connection and resume on another one. There is no reason for this so I doubt you'd have done that.

You should look at a debug trace to see what is happening in practice. Post it here if you want me to look at it as well but I doubt we'll find anything special.

Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Oracle exception

Zdeněk Vráblík-3
Hi,
thanks for reply. I have read some articles to better understand the
XA. I hope I have found solution. I use just begin/end transaction and
get/close connection operations.

The possible solution (without Oracle verification):

I have two connections. One(non transactional) connection for getting
data and do oracle specific data modification and other(transactional)
for data modification.

The non transactional connection is used to read only operations and
importing Oracle Images, which needs OrcalceConnection,
OraclePreparedStatement ... I am not able to make this transactional
anyway.
The transactional connection is used to write data to database without
doing any plsql procedures. This causes only one issue. I am not able
to read data during transaction. Usually it is not necessary, because
data are in cache(something like ehCache). The only problem are data
which don't use the cache and cache reload ...

I am not able to reproduce the error in my use cases. It doesn't mean
the problem disappeared, but I could live with low frequency of
occurrences. I have observed that the database has recovered from this
error after a few minutes.

Thanks for help.

Regards,
Zdenek

On Sun, May 4, 2008 at 1:31 PM, Ludovic Orban <[hidden email]> wrote:

>
> Zdenek,
>
> What Oracle calls as session is I guess a JDBC connection. A branch is
> simply the part of a XA transaction on one database. One branch always
> sticks to a single connection unless you migrate it yourself (using
> tm.suspend and tm.resume).
>
> According to their description of the DB's limitations, it should just work
> out of the box unless your logic involves transaction suspension on a
> connection and resume on another one. There is no reason for this so I doubt
> you'd have done that.
>
> You should look at a debug trace to see what is happening in practice. Post
> it here if you want me to look at it as well but I doubt we'll find anything
> special.
>
> Ludovic
> --
> View this message in context: http://www.nabble.com/Oracle-exception-tp16479456p17045615.html
> Sent from the BTM Users mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email