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 |
Description |
Mitigation |
|
Hardcoded Credentials |
DB Links may store usernames and passwords
in clear text. |
Use restricted service accounts, Oracle
Wallet, or encrypted authentication methods. |
|
Network Latency |
Queries over DB Links depend on network
speed between servers. |
Optimize queries, reduce large data
transfers, and use proper indexing on remote tables. |
|
Remote Database Dependency |
If the remote database is unavailable,
queries using DB Links will fail. |
Implement monitoring and fallback
mechanisms in applications. |
|
Security Exposure |
Public DB Links may allow unauthorized
access to remote databases. |
Restrict usage, use Private DB Links when
possible, and limit privileges of remote users. |
|
Performance Issues |
Large joins across databases can degrade
performance. |
Use filtering conditions, materialized
views, or data replication for large datasets. |
|
Password Expiry |
Remote user passwords may expire and break
the DB Link. |
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.