Error on Oracle while using DBLINK

Question

I am Using jboss5.1.x, EJB3.0 ,JPA3.

I am trying to do 'select' query from view which is connected via dblink to another database.

source database is Oracle 9, destination dabatase is Oracle 8.

I am getting this error:

15:27:06,625 WARN [JDBCExceptionReporter] SQL Error: 24777, SQLState: 99999

15:27:06,625 ERROR [JDBCExceptionReporter] ORA-24777: use of non-migratable database link not allowed

I found solution to this error after I understood that I cant use dblink while using XA. So I managed to solved by changing the dblink script to create shared database link as follow:

 CREATE SHARED DATABASE LINK CONNECT TO IDENTIFIED BY AUTHENTICATED BY IDENTIFIED BY USING

everything worked fine in this test environment.

Now I've moved my application to production environment, where the source database is Oracle 11 while the destination is still Oracle 8.

The trick that I used didnt work this time and I couldnt find a solution. This is the new exception I am getting:

    Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
    at ....Caused by: java.sql.SQLException: ORA-01012: not logged on
    ORA-02063: preceding line from TO_VANTIVE

Thanks for your help,

ray,

score 4 · Accepted Answer · answered Mar 31 '11 at 11:06

4

ORA-01012: not logged on

seems to suggest that you haven't configured the new link correctly and since the database is now 11g which may have case sensitive passwords that would be the first thing to check.

Put quotes around the password in the CREATE LINK if the remote schema(s) have case sensitive passwords. Thus

CREATE SHARED DATABASE LINK
CONNECT TO bob IDENTIFIED  BY "MyNewPasswd1"
AUTHENTICATED BY jim IDENTIFIED BY "JimsPass23" USING 'DB01';

answered Mar 31 '11 at 11:06

tell me bob and "MyNewPasswd1" refer to the source database or the destination one? same question about "JimsPass23",'DB01' cardinentals ? thanks. – rayman Mar 31 '11 at 13:17
1

The passwords and user names of both users refer to the remote database .i.e. not the database on which you create the link but the database you are accessing via the link. – Mar 31 '11 at 13:30
Are you able to log on to both users in the remote database directly? If you haven't been able to log on then the other errors are pretty likely to be because of that since you can't really do anything. Either the link isn't working (do you have a TNS entry for the database that matches the link for example) or there is a permissions issue of some kind (wrong password, account locked, not allow to connect or missing user). – Mar 31 '11 at 15:05
I am sorry if I didnt mention, but regular dblink working fine between them. this problem occurs only when I try to "CREATE SHARED DATABASE" – rayman Mar 31 '11 at 15:30
OK, then it is most likely the authenticated by user that is causing the issue (or in fact it may be that you are being able to log into the production environment). You should check you can connect in SQL*Plus and use the link with a simple select. – Mar 31 '11 at 20:30

score 1 · Answer 2 · answered Oct 03 '14 at 13:26

1

We have the same problem with Weblogic, and the solution is to utilize the non XA oracle JDBC driver.

answered Oct 03 '14 at 13:26

Joao Polo

2,153
1
17
26

score 0 · Answer 3 · answered Sep 23 '13 at 14:16

Please make sure the DB link you are using is Public and Shared, if the DB link is not Public and Shared it will throw an Exception ORA-24777: use of non-migratable database link not allowed. But if you try to run the same query directly on DB with out using any Java or XA transaction it will work fine.

score 0 · Answer 4 · edited Oct 26 '12 at 12:26

I was experiencing the same problem here with a Oracle 11g (ORA-24777). I've performed a join between a single table (in my schema) and a view (created by a database link). I've executed the all over a JBoss datasource in XA mode.

In order to get it to work well, I had to change the mode of my dblink view. In this case, is very important to have a precise AUTHENTICATED BY clause to avoid getting "ORA-01012: not logged" on.

Error on Oracle while using DBLINK

4 Answers4