Tales From A Lazy Fat DBA

Hi gUYS,

Recently I was contacted by someone to take a look at an application problem where they caught few ORA errors in their logs, received “ORA-24777: use of non-migratable database link not allowed” which was for one of their important DBLink. They were using WebLogic as an application server and 19c as Oracle DB Version!
Okay, talking about the issue, this usually happens due to a session using shared connection (XA) using DBLink with dedicated connection. About fixing this problem, there are few ways how you can fix this issue i.e. setting dispatchers, Change or server on tnsnames.ora to SHARED etc., but would like to discuss two of the other methods that I tried to fix the problem.

First Solution:

1. Open your Weblogic Server Admin Console and click on Services -> Data Sources -> {Choose the datasource you are using in your DB adapter} -> Connection Pool
2. Change Driver ClassName from oracle.jdbc.xa.client.OracleXADataSource to oracle.jdbc.OracleDriver

Second Solution:
Sometimes, you don’t want to set the system wide change in your WebLogic by changing the driver class name, you can alter your DBLink and make it a SHARED DBLink. Shared database links purpose is to curb down the number of connections to the remote database by sharing same connection link between the source and the remote by multiple users sessions.

-- This was the previous DDL of the DBLink
CREATE DATABASE LINK test_dblink 
   CONNECT TO testuser IDENTIFIED BY mypassword
   USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=monkeyserver.ontadomain.com)(PORT=1524))
            (CONNECT_DATA=(SERVICE_NAME=TESTDB1))
          )';


-- This is what I have altered it to to make it SHARED DBLink and worked for me.
CREATE SHARED DATABASE LINK test_dblink 
   CONNECT TO testuser IDENTIFIED BY mypassword
   USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=monkeyserver.ontadomain.com)(PORT=1524))
            (CONNECT_DATA=(SERVICE_NAME=TESTDB1))
          )';

Hope It Helped!
Prashant Dixit