ORA-24777: Use of Non-Migratable Database Link Not Allowed

ORA-24777: Use of Non-Migratable Database Link Not Allowed

Error Overview

Error Text: ORA-24777: use of non-migratable database link not allowed

ORA-24777 occurs when a session using shared server (MTS) or distributed XA transactions attempts to use a database link that does not support transaction migration between processes. Distributed transactions in shared server mode require migratable links so different shared servers can pick up the same transaction.

Understanding Migratable vs Non-Migratable Links

Type	Created With	Behavior
Non-migratable	CONNECT TO user IDENTIFIED BY pwd	Tied to the connecting session’s process
Migratable	CONNECT TO CURRENT_USER or shared via XA	Can be used by any shared server

In dedicated server mode, all links work fine. In shared server mode, only migratable links work for distributed transactions because each call may be served by a different shared server process.

Common Causes

Shared Server Mode + Non-Migratable Link

• Database configured with shared servers
• Application uses traditional CONNECT TO user IDENTIFIED BY pwd link
• Distributed transaction spans multiple SQL calls

XA Transactions

• Application server uses XA distributed transactions
• Database link not configured for XA participation
• Mixed local and global transactions

Application Server Pool

• Connection pool returns different physical connections
• Transaction context lost between calls
• Implicit XA behavior in J2EE/Tuxedo

Diagnostic Steps

Check Connection Mode

-- See if session is dedicated or shared
SELECT sid, server, type, status
FROM v$session
WHERE audsid = USERENV('SESSIONID');

-- SERVER = 'DEDICATED' or 'SHARED'

-- Check shared server config
SHOW PARAMETER shared_servers;
SHOW PARAMETER dispatchers;

Inspect Database Link Definition

-- View link configuration
SELECT db_link, username, host, created
FROM dba_db_links
WHERE db_link LIKE 'TARGET%';

-- A link with username defined is non-migratable
-- A link with empty username (CURRENT_USER) is migratable

Check Active Distributed Transactions

-- See in-flight distributed transactions
SELECT * FROM v$global_transaction;

SELECT local_tran_id, global_tran_id, state
FROM dba_2pc_pending;

Resolution Steps

1. Use Migratable (CURRENT_USER) Database Link

-- Drop non-migratable link
DROP DATABASE LINK target_db;

-- Create migratable link using CURRENT_USER
CREATE DATABASE LINK target_db
CONNECT TO CURRENT_USER
USING 'TARGET_TNS';

-- Each user must exist on remote DB with same authentication

Note: CURRENT_USER links require remote DB to recognize the local user (often via global users with enterprise security).

2. Switch Session to Dedicated Server

# Use TNS entry that forces dedicated server
# tnsnames.ora
TARGET_DEDICATED =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))
        (CONNECT_DATA =
            (SERVICE_NAME = service)
            (SERVER = DEDICATED)
        )
    )

-- Application connects to dedicated TNS alias
sqlplus user/pwd@target_dedicated

3. Configure XA-Compatible Setup

-- Grant XA privileges
GRANT SELECT ON sys.dba_pending_transactions TO xa_user;
GRANT EXECUTE ON sys.dbms_xa TO xa_user;
GRANT FORCE ANY TRANSACTION TO xa_user;

-- Confirm XA support
SELECT * FROM v$option WHERE parameter = 'XA';

For application servers using XA:

• Configure datasource as XA datasource (e.g., OracleXADataSource)
• Ensure database link uses authentication compatible with XA

4. Refactor to Avoid Distributed Transaction

If feasible, eliminate the distributed transaction:

-- Instead of cross-DB transaction:
INSERT INTO local_table VALUES (...);
INSERT INTO remote_table@target_db VALUES (...);
COMMIT;  -- distributed commit

-- Use local staging + Oracle Streams/GoldenGate replication:
INSERT INTO local_table VALUES (...);
INSERT INTO local_replication_queue VALUES (...);
COMMIT;
-- Async replication handles cross-DB delivery

5. Connect User Configuration

For migratable CONNECT TO CURRENT_USER:

-- On remote DB, ensure local users exist
CREATE USER app_user IDENTIFIED EXTERNALLY;
GRANT CONNECT, RESOURCE TO app_user;

-- Or use enterprise users via OID

Common Scenarios

Scenario 1: Application Server with Shared Servers

WebLogic uses XA datasource. DB has shared_servers > 0.
Cross-DB transaction → ORA-24777

Fix: Either disable shared servers, or recreate links as CONNECT TO CURRENT_USER.

Scenario 2: Tuxedo Distributed Transactions

Tuxedo /Q application updates two databases via XA.
ORA-24777: use of non-migratable database link not allowed

Fix: Configure XA-compliant link or use single XA datasource.

Scenario 3: Reporting Tool Hits Shared Server

Crystal Reports → Oracle (shared server) → DB link → remote DB

Fix: Use dedicated server connection for reporting; shared server poorly suited for distributed reads.

Sample Output

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-24777: use of non-migratable database link not allowed

SQL> SELECT server FROM v$session WHERE audsid = USERENV('SESSIONID');
SERVER
---------
SHARED

SQL> SELECT db_link, username FROM dba_db_links WHERE db_link = 'TARGET_DB';
DB_LINK     USERNAME
----------- ----------
TARGET_DB   APP_USER

-- Non-migratable: USERNAME populated. Recreate as CURRENT_USER.
SQL> DROP DATABASE LINK target_db;
SQL> CREATE DATABASE LINK target_db CONNECT TO CURRENT_USER USING 'TARGET';
Database link created.

Prevention Strategies

Architecture Decisions

• Choose dedicated server for applications using DB links extensively
• Avoid mixing shared server and distributed transactions
• Use enterprise users + global authentication for CURRENT_USER links

Connection Pool Configuration

<!-- WebLogic XA datasource example -->
<jdbc-data-source>
    <name>OracleXA</name>
    <jdbc-driver-params>
        <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>
        <url>jdbc:oracle:thin:@host:1521:service</url>
    </jdbc-driver-params>
</jdbc-data-source>

Link Audit

-- Identify non-migratable links that may break with shared server
SELECT db_link, username,
       CASE WHEN username IS NULL THEN 'MIGRATABLE'
            ELSE 'NON-MIGRATABLE' END AS link_type
FROM dba_db_links;

Use TNS Aliases for Mode Selection

# dedicated for app server
TARGET_DEDICATED = (... (SERVER=DEDICATED) ...)

# shared for OLTP
TARGET_SHARED = (... (SERVER=SHARED) ...)

Related Errors

• ORA-02049: Distributed lock timeout
• ORA-02050: Transaction rolled back, remote DBs may be in-doubt
• ORA-02063: Preceding line from database link
• ORA-02068: Following severe error from database link
• ORA-24756: Transaction does not exist

Troubleshooting Checklist

• Verify session uses shared or dedicated server (v$session.server)
• Check link type (non-migratable if username populated)
• Review shared server parameters
• Recreate link as CONNECT TO CURRENT_USER for migratability
• Configure XA datasource for application server
• Consider dedicated server for distributed-transaction workloads