Oracle 19c DB Link Configuration & Testing


 1. Introduction

A Database Link (DB Link) in Oracle Database 19c is a schema object that enables one
Oracle database to access objects in another Oracle database over a network.
It provides a logical communication path between two independent databases, allowing SQL statements
to reference remote tables, views, and PL/SQL programs as if they were local objects.
Using a DB Link, organizations can execute remote SELECT, INSERT, UPDATE, and
DELETE operations, call remote stored procedures, and perform distributed transactions
across multiple databases. This makes DB Links a powerful integration mechanism for
enterprise systems that must operate independently yet share data in real time.
In real enterprise environments, organizations rarely rely on a single database. Different
applications, departments, or geographic branches often maintain separate databases for
security isolation, performance optimization, business segregation, and regional
distribution. DB Links allow these independent systems to communicate without physically
merging databases, enabling seamless integration while maintaining architectural
separation.

Database Links can be categorized into:

• Private DB Links – Accessible only to the creator

• Public DB Links – Accessible to all users in the database

• Global DB Links – Enterprise directory-based links

Real-Time Banking Scenario

Consider a real-time banking environment:

• Bank A operates a Core Banking Database (COREDB) storing customer accounts and balances.

• Bank B operates a Card Processing Database (CARDSDB) managing ATM and card transactions.

When a customer withdraws money from an ATM managed by Bank B, the system must verify the account balance stored in Bank A’s database. Instead of duplicating data, Bank B
uses a DB Link to query Bank A in real time. If sufficient balance exists, the transaction is
approved; otherwise, it is declined. This ensures real-time validation without data
replication.
Similarly, in a loan processing system, customer income and credit score data stored in
separate databases can be validated instantly using a DB Link, enabling faster decision-
making and improved operational efficiency.

Advantages of Using DB Links

• Eliminates unnecessary data duplication

• Enables real-time cross-database access

• Simplifies system integration

• Supports distributed transactions

• Reduces infrastructure complexity

• Facilitates enterprise-wide reporting

While powerful, DB Links must be implemented carefully in production environments.
Potential risks include hardcoded credentials, network latency, security exposure,
dependency on remote database availability, and performance overhead for large data
transfers. Best practices include using restricted remote users, enabling encrypted
connections (TCPS), avoiding unnecessary public links, monitoring via DBA_DB_LINKS,
and ensuring proper indexing on remote tables to maintain optimal performance.
This guide is designed to be practical, execution-focused, and easy to follow, making it
suitable for Oracle DBAs, system administrators, and learners who want to gain real-world
experience implementing and managing a fully functional Database Link configuration in
Oracle Database 19c.

By following this document, readers will gain hands-on understanding of how to securely integrate two independent Oracle databases, execute distributed queries, and support enterprise-level use cases such as cross-database reporting, transaction validation, and real- time banking-style integrations.

This guide aims to bridge the gap between theoretical knowledge and practical implementation, ensuring readers develop confidence in deploying Database Links in real production environments.

2. Architecture Overview

Component                    Source (Local)                              Target (Remote)

Server                            srv5.cloudmap.in                          srv7.cloudmap.in

Database Name             prod                                              dell

Oracle Version              Oracle Database 19c                    Oracle Database 19c

Port                               1521                                              1521

Listener Status             Running                                        Running

Test User                      OT                                                 U1


3. Objective

• Configure connectivity between srv5 and srv7

• Create test schema and table on remote DB

• Configure TNS entry

• Create Public and Private Database Links

• Validate distributed query access

• Verify DB link metadata

4. Prerequisites

Before creating a DB Link:

• Oracle Listener running on both servers

• Databases open

• Network connectivity confirmed

• TNS entries configured

• Remote user exists

• Required privileges granted

5. Remote Database Setup (srv7 – DELL)

5.1 Start Database


5.2 Verify Instance & Listener


5.3 Create Test Users


5.4 Create Test Table

                                

                               

6. Local Database Setup (srv5 – PROD)

6.1 Start Database


6.2 Verify Listener


6.3 Verify TNS Configuration



6.4 Test Connectivity

  

7. Creating Database Links

7.1 Public Database Link (As SYS)



7.2 Test Public DB Link


7.3 Grant DB Link Privilege to User


7.4 Create Private Database Link (As OT User)


7.5 Test Private DB Link


8. Verification


9. Security Considerations (Production Best Practice)

In production:

• Avoid hardcoding passwords

• Use encrypted network (TCPS)

• Use least-privilege remote users

• Restrict PUBLIC DB links if not required

• Monitor via:

SQL > select * from dba_db_links;

10. Common Errors Encountered

Error                        Cause                                                 Resolution

ORA-02010            Missing quotes around TNS alias       Use 'todell'

ORA-00990           Invalid privilege name                         Use CREATE DATABASE LINK

ORA-28002           Password expiring soon                       Change user password

11. Conclusion

The successful implementation of the Database Link in Oracle Database 19c demonstrates how two independent Oracle databases can be securely and efficiently integrated to support real-time enterprise operations. Through proper configuration of Oracle Net (TNS), listener services, remote user authentication, and DB Link creation (private and public), seamless cross-database communication was established without data duplication or architectural compromise.

This implementation validates that distributed queries, remote DML operations, and cross- database reporting can be executed transparently while maintaining security, performance, and structural separation between systems. Real-world scenarios such as banking transaction validation, cross-application reporting, and multi-system data verification highlight the practical value of Database Links in modern enterprise environments.

Production-level considerations, including restricted remote user access, encrypted connectivity (TCPS), performance tuning, and monitoring via data dictionary views, were incorporated to ensure stability, scalability, and compliance with best practices.

Overall, this work provides a complete, end-to-end implementation of a production-oriented Database Link setup, demonstrating both technical execution and architectural understanding. It reinforces how Database Links serve as a powerful and reliable integration mechanism within complex Oracle database ecosystems.

12. Risks and Mitigation

Although Database Links provide powerful cross-database connectivity, improper configuration can introduce operational and security risks. The following table highlights potential risks and their mitigation strategies. 

Risk                                                                                       

  • Hardcoded Credentials     
  • Network Latency
  • Remote Database Dependency
  • Security Exposure
  • Performance Issues
  • Password Expiry

Description 

  • DB Links may store usernames and passwords in clear text.
  • Queries over DB Links depend on network speed between servers.
  • If the remote database is unavailable, queries using DB Links will fail.
  • Public DB Links may allow unauthorized access to remote databases.
  • Large joins across databases can degrade performance.
  • Remote user passwords may expire and break the DB Link.

 Mitigation

  • Use restricted service accounts, Oracle Wallet, or encrypted authentication methods.
  • Optimize queries, reduce large data transfers, and use proper indexing on remote tables.
  • Implement monitoring and fallback mechanisms in applications.
  • Restrict usage, use Private DB Links when possible, and limit privileges of remote users.
  • Use filtering conditions, materialized views, or data replication for large datasets.
  • Implement password management policies or service accounts with controlled expiration.

13. Future Enhancements

To further improve reliability, security, and scalability, the following enhancements can be implemented in future deployments:

1. Secure DB Links Using Oracle Wallet

Store credentials securely using Oracle Wallet instead of plain-text passwords.

2. Encrypted Network Communication

Implement TCPS (SSL/TLS encryption) for secure data transmission between databases.

3. Use of Materialized Views

For frequently accessed remote data, materialized views can reduce network overhead and improve performance.

4. Monitoring and Alerting

Integrate monitoring tools to track DB Link usage, connection failures, and query performance.

5. High Availability Architecture

Combine DB Links with Data Guard or RAC environments to improve database availability and fault tolerance.

6. Role-Based Access Control

Restrict DB Link usage using roles and least-privilege access policies.

No comments:

Post a Comment