Active Database Link Monitoring (vdblink.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script queries Oracle views to provide database information via the vdblink.sql script.

The Script

rem  vdblink.sql
rem
ttitle 'Open Database Links'
rem
col db_link format a23 heading 'DATABASE LINK'
col owner_id format 9990 heading 'OWNER|ID'
col logged_on format a6 heading 'LOGGED|ON'
col heterogeneous format a6 heading 'HETERO'
col protocol format a8 heading 'PROTOCOL'
col open_cursors format 999 heading 'OPEN|CURSORS'
col in_transaction format a3 heading 'IN|TRX'
col update_sent format a6 heading 'UPDATE|SENT'
col commit_point_strength format 990 heading 'COMMIT|POINT|STRENGTH'
rem
select db_link, owner_id, logged_on, heterogeneous,
       protocol, open_cursors, in_transaction,
       update_sent, commit_point_strength
  from v$dblink
 order by db_link;

What This Script Does

This script monitors currently active database link connections, providing real-time information about distributed database sessions. It shows connection status, transaction involvement, open cursors, and commit coordination details, making it essential for managing distributed database environments and troubleshooting distributed transaction issues.

Key Features

• Active Link Monitoring: Shows only currently open database link connections
• Transaction Status: Displays distributed transaction participation
• Resource Tracking: Monitors open cursors and connection resources
• Protocol Information: Shows connection protocols and link types
• Commit Coordination: Displays commit point strength for distributed transactions
• Heterogeneous Support: Identifies connections to non-Oracle databases

Usage

@vdblink.sql

No parameters required - shows all currently active database links.

Required Privileges

SELECT on V$DBLINK

Sample Output

                                        Open Database Links

DATABASE LINK           OWNER LOGGED HETERO PROTOCOL OPEN IN  UPDATE COMMIT
                           ID ON     ON              CURSORS TRX SENT  POINT
                                                                     STRENGTH
----------------------- ----- ------ ------ -------- ---- --- ------ ------
PROD_DW.EXAMPLE.COM        45 YES    NO     TCP        3 YES NO          1
SALES_DB.COMPANY.NET      123 YES    NO     TCP        1 NO  NO          1
LEGACY_SYSTEM.OLD.ORG      67 YES    YES    ODBC       0 NO  NO          0
HR_DATABASE              NULL YES    NO     TCP        2 YES YES         1

Key Output Columns

• DATABASE LINK: Name of the database link
• OWNER ID: User ID that opened the database link connection
• LOGGED ON: Whether the link is currently logged on (YES/NO)
• HETERO: Whether this is a heterogeneous connection to non-Oracle database (YES/NO)
• PROTOCOL: Network protocol used for the connection (TCP, IPC, etc.)
• OPEN CURSORS: Number of cursors currently open on this link
• IN TRX: Whether the link is participating in a distributed transaction (YES/NO)
• UPDATE SENT: Whether updates have been sent over this link (YES/NO)
• COMMIT POINT STRENGTH: Relative strength for distributed commit coordination (0-255)

Understanding Database Link Status

Connection Status

Active Connections

-- LOGGED ON = YES indicates:
-- Connection is established and active
-- Network session is open
-- Ready to process SQL statements
-- Resources allocated for the connection

Connection Types

-- HETERO = NO: Oracle-to-Oracle connection
-- HETERO = YES: Oracle to non-Oracle database
-- Different capabilities and limitations apply

Transaction Participation

Distributed Transactions

-- IN TRX = YES indicates:
-- Link is part of a distributed transaction
-- Two-phase commit protocol may be involved
-- Transaction must be coordinated across databases
-- All participating databases must commit or rollback together

Update Activity

-- UPDATE SENT = YES indicates:
-- Data modification statements have been executed
-- Changes are pending on remote database
-- Commit or rollback will affect remote data
-- Transaction coordination is required

Resource Usage

Open Cursors

-- OPEN CURSORS shows:
-- Number of active cursors on the remote database
-- Resource consumption on target system
-- Potential for cursor leaks if not properly closed
-- Performance impact on both local and remote systems

Commit Point Strength

-- Values typically 0, 1, or higher:
-- 0: Read-only or no commit coordination
-- 1: Standard commit point strength
-- Higher values: Greater commit coordination priority
-- Used in distributed transaction resolution

Common Use Cases

1. Distributed Transaction Monitoring

   • Monitor active distributed transactions
   • Identify long-running distributed operations
   • Troubleshoot transaction coordination issues
   • Verify transaction completion

2. Resource Management

   • Track database link resource usage
   • Identify cursor leaks on remote connections
   • Monitor connection pool utilization
   • Optimize distributed query performance

3. Connection Troubleshooting

   • Diagnose database link connectivity issues
   • Verify protocol and connection types
   • Identify failed or hanging connections
   • Monitor heterogeneous database connections

4. Performance Analysis

   • Assess distributed query impact
   • Monitor network protocol efficiency
   • Track remote resource consumption
   • Identify distributed transaction bottlenecks

Troubleshooting Database Link Issues

Connection Problems

Link Not Appearing

-- Possible causes:
-- Database link not being used
-- Connection failed to establish
-- Network connectivity issues
-- Authentication problems

LOGGED ON = NO

-- Indicates connection problems:
-- Network interruption
-- Remote database unavailable
-- Authentication failure
-- Connection timeout

Transaction Issues

Stuck Distributed Transactions

-- Symptoms:
-- IN TRX = YES for extended periods
-- UPDATE SENT = YES without commit/rollback
-- Sessions waiting for distributed transaction resolution

-- Investigation steps:
-- Check V$GLOBAL_TRANSACTION for details
-- Verify network connectivity to remote databases
-- Check for blocking locks on remote systems
-- Review distributed transaction logs

Commit Coordination Problems

-- Issues with commit point strength:
-- Multiple databases with same strength
-- Network failures during commit processing
-- Remote database unavailable during commit
-- Two-phase commit timeout issues

Resource Leaks

High Open Cursor Counts

-- Indicates potential problems:
-- Application not closing cursors properly
-- Cursor leaks in distributed queries
-- Inefficient cursor management
-- Resource exhaustion on remote database

Advanced Analysis

Session Correlation

-- Identify sessions using database links:
SELECT s.sid, s.username, s.machine, s.program,
       d.db_link, d.open_cursors, d.in_transaction
FROM v$session s, v$dblink d
WHERE s.saddr = d.owner_id
ORDER BY d.db_link, s.sid;

Transaction Details

-- Get distributed transaction information:
SELECT gt.globalid, gt.formatid, gt.branchid,
       gt.branches, gt.refcount, gt.status,
       d.db_link, d.in_transaction
FROM v$global_transaction gt, v$dblink d
WHERE gt.status != 'COLLECTED'
ORDER BY gt.globalid;

Historical Analysis

-- Monitor database link usage patterns:
-- Run script periodically to track:
-- Peak usage times
-- Common connection patterns
-- Resource utilization trends
-- Transaction completion rates

Performance Optimization

Connection Management

Optimize Link Usage

-- Best practices:
-- Close connections when not needed
-- Use connection pooling where appropriate
-- Minimize distributed transaction scope
-- Batch operations to reduce round trips

Cursor Management

-- Prevent cursor leaks:
-- Explicitly close cursors in application code
-- Use cursor_sharing parameter appropriately
-- Monitor cursor usage regularly
-- Implement cursor management guidelines

Transaction Optimization

Minimize Distributed Transactions

-- Strategies:
-- Design to avoid distributed transactions when possible
-- Use materialized views for read-only data
-- Implement eventual consistency where appropriate
-- Batch operations to reduce transaction overhead

Commit Point Strength Planning

-- Consider database roles:
-- Assign higher strength to more reliable databases
-- Consider network reliability in strength assignment
-- Plan for failure scenarios
-- Document commit coordination strategy

Network and Protocol Analysis

Protocol Performance

-- TCP protocol considerations:
-- Network latency impact
-- Bandwidth utilization
-- Connection pooling benefits
-- SSL/encryption overhead

Heterogeneous Connections

-- Special considerations for HETERO = YES:
-- Limited SQL functionality
-- Data type conversion overhead
-- Different transaction semantics
-- Reduced optimization opportunities

Monitoring and Alerting

Key Metrics

-- Monitor regularly:
-- Number of active connections
-- Long-running distributed transactions
-- High cursor counts per link
-- Failed connection attempts

Alert Thresholds

-- Set alerts for:
-- Open cursors > 50 per link
-- Distributed transactions > 30 minutes
-- Connection failures or timeouts
-- Unusual commit point strength conflicts

Trending Analysis

-- Track over time:
-- Peak connection usage
-- Average transaction duration
-- Cursor usage patterns
-- Network protocol efficiency

Integration with Other Monitoring

Database Link Configuration

-- Correlate with DBA_DB_LINKS:
SELECT d.owner, d.db_link, d.host, d.created,
       v.logged_on, v.in_transaction, v.open_cursors
FROM dba_db_links d
LEFT JOIN v$dblink v ON d.db_link = v.db_link
ORDER BY d.owner, d.db_link;

Session Analysis

-- Combine with session information:
-- Active SQL statements using database links
-- Wait events related to distributed operations
-- Resource usage by distributed sessions
-- Performance metrics for remote operations

Related Scripts

• ddblink.sql - Database link definitions and configuration
• vsession.sql - Session information including distributed transactions
• gvtrans.sql - Transaction analysis across instances
• vqueue.sql - Queue and messaging analysis

Best Practices

1. Regular Monitoring

   • Include in daily health checks
   • Monitor during peak usage periods
   • Track distributed transaction patterns
   • Document normal usage baselines

2. Resource Management

   • Implement cursor management standards
   • Monitor and control connection usage
   • Plan for distributed transaction coordination
   • Optimize network protocols and configuration

3. Troubleshooting Approach

   • Check connectivity first
   • Verify authentication and permissions
   • Monitor transaction coordination
   • Analyze resource usage patterns