Oracle 19c Database Links: A Complete, Step-by-Step Implementation Guide

Configure, secure, and validate Public and Private Database Links between two independent Oracle 19c databases — with full SQL transcripts, real-world banking scenarios, and production hardening tips.

1. Introduction — What Is a Database Link?

A Database Link (DB Link) in Oracle Database 19c is a schema object that establishes a one-way logical communication path from one Oracle database to another over the network. Once a DB Link is created, SQL statements running on the local database can transparently reference tables, views, and PL/SQL programs that physically reside on a remote database — as if they were local objects.

In practical terms, a DB Link lets you:

Execute remote SELECT, INSERT, UPDATE, and DELETE statements
Call remote stored procedures and functions
Participate in distributed transactions (two-phase commit)
Build cross-database reports without ETL or replication

Real enterprise environments rarely run on a single database. Different applications, departments, or geographic regions typically maintain independent databases for security isolation, performance, business segregation, and regional distribution. DB Links bridge those silos without physically merging them — preserving architectural separation while enabling real-time data exchange.

Categories of Database Links

Type	Visibility	Typical Use
Private DB Link	Accessible only to the user who created it	Most production scenarios — least privilege
Public DB Link	Accessible to all users in the database	Shared utility links; use sparingly
Global DB Link	Defined in an enterprise directory (LDAP/OID)	Large multi-database enterprises

2. A Real-Time Banking Scenario

To make the concept concrete, consider a real-time banking environment:

Bank A operates a Core Banking Database (COREDB) holding customer accounts and balances.
Bank B operates a Card Processing Database (CARDSDB) handling ATM and card transactions.

When a customer withdraws money from an ATM managed by Bank B, the system must verify the account balance held in Bank A's database. Instead of duplicating sensitive customer data across institutions, Bank B issues a real-time query through a DB Link to Bank A. If the balance is sufficient, the transaction is approved; otherwise, it is declined. There is no replication, no nightly batch, no stale data — only a transactional, real-time validation.

The same pattern applies to loan processing: customer income, employer records, and credit-bureau scores often live in separate databases. A DB Link lets the loan application validate eligibility instantly, accelerating decisions and improving operational efficiency.

3. Why Use DB Links? (Advantages & Risks)

✅ Advantages

Eliminates unnecessary data duplication — query the source of truth directly
Enables real-time cross-database access — no replication lag
Simplifies system integration — fewer moving parts than middleware
Supports distributed transactions — atomic commits across databases
Reduces infrastructure complexity — no need for additional ETL platforms
Facilitates enterprise-wide reporting — join across domains seamlessly

⚠️ Risks to Be Aware Of

While powerful, DB Links must be implemented carefully in production:

Hardcoded credentials stored inside the DB Link definition
Network latency affecting query response times
Security exposure through over-permissive remote accounts
Hard dependency on remote database availability
Performance overhead for very large data transfers

These risks are addressed in Section 11 — Security Considerations and Section 13 — Risks and Mitigation.

4. Architecture Overview

This guide implements a working DB Link between two physically separate Oracle 19c servers:

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`

Logical Flow

   ┌──────────────────────────────┐                ┌──────────────────────────────┐
   │   srv5.cloudmap.in (PROD)    │                │   srv7.cloudmap.in (DELL)    │
   │ ──────────────────────────── │                │ ──────────────────────────── │
   │  Oracle 19c                  │  DB LINK ───►  │  Oracle 19c                  │
   │  LISTENER (Port 1521)        │  (TNS 1521)    │  LISTENER (Port 1521)        │
   │  Local user : OT             │ ◄─────────     │  Remote user : U1            │
   │  Public  link: REMOTEDB      │                │  Schema U1.ACCOUNT           │
   │  Private link: PRODTODELL    │                │   ┌─────┬────────┬──────┐    │
   │                              │                │   │ 224 │ Nitesh │ 5000 │    │
   │  User OT:                    │                │   │ 227 │ sam    │ 4000 │    │
   │   SELECT * FROM              │                │   │ 225 │ RAJ    │ 6000 │    │
   │   account@PRODTODELL;        │                │   │ 321 │ pinto  │      │    │
   │                              │                │   └─────┴────────┴──────┘    │
   └──────────────────────────────┘                └──────────────────────────────┘

The local PROD database opens a TCP connection on port 1521 to the remote DELL listener. From the user's perspective, account@PRODTODELL looks like a local table — Oracle Net handles the rest under the covers.

5. Objectives of This Guide

By the end of this implementation, you will have:

✅ Configured network connectivity between srv5 (PROD) and srv7 (DELL)
✅ Created a test schema and table on the remote database
✅ Configured a working tnsnames.ora entry
✅ Created both Public and Private Database Links
✅ Validated distributed query access end-to-end
✅ Verified DB Link metadata via the Oracle data dictionary

6. Prerequisites

Before creating a DB Link, confirm the following:

Oracle Listener running on both servers
Both databases are OPEN
Network connectivity confirmed (firewall/port 1521 reachable)
tnsnames.ora entries configured on the source server
Remote database user exists
Required privileges granted (e.g., CREATE [PUBLIC] DATABASE LINK, CREATE SESSION)

7. Remote Database Setup (srv7 — DELL)

The remote (target) side hosts the data we want to query. We will start the database, verify the listener, create a remote user, and load a test table.

7.1 Start the Remote Database

[oracle@srv7 admin]$ . oraenv
ORACLE_SID = [dell] ? dell
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@srv7 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 9 11:04:12 2025
Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  830469280 bytes
Fixed Size                  8901792 bytes
Variable Size             528482304 bytes
Database Buffers          285212672 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.
SQL> exit

7.2 Verify Instance & Listener

[oracle@srv7 admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-SEP-2025 11:05:07

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                09-SEP-2025 10:57:08
Uptime                    0 days 0 hr. 7 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/srv7/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv7.cloudmap.in)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "dell" has 1 instance(s).
  Instance "dell", status READY, has 1 handler(s) for this service...
The command completed successfully

The service dell is READY — exactly what we need.

7.3 Create the Remote Test User

SQL> create user u1 identified by u1;
User created.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
USERDATA

SQL> grant connect, resource to u1;
Grant succeeded.

SQL> alter user u1 quota unlimited on userdata;
User altered.

💡 Tip: Granting RESOURCE is convenient for a lab but is broader than what most production DB-Link accounts need. In a real deployment, grant only the object-level SELECT/INSERT/etc. privileges actually required.

7.4 Create the Test Table & Insert Sample Data

Connect as the remote user and build the ACCOUNT table — this is what the local PROD database will query through the DB Link.

SQL> conn u1/u1
Connected.

SQL> show user
USER is "U1"

SQL> create table account
(
  Acno  number(4),
  Name  varchar2(30),
  Bal   number(6,2),
  Phone number(10)
);
Table created.

SQL> insert into account (acno, name, BAL, phone)
     values (224, 'Nitesh', 5000.00, 8899221144);
1 row created.

SQL> insert into account values (227, 'sam', 4000.00, 8899221146);
1 row created.

SQL> insert into account (acno, phone, BAL, name)
     values (225, 8899221145, 6000.00, 'RAJ');
1 row created.

SQL> insert into account (acno, name, phone)
     values (321, 'pinto', 8889990001);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from account;

      ACNO NAME                                  BAL      PHONE
---------- ------------------------------ ---------- ----------
       224 Nitesh                               5000 8899221144
       227 sam                                  4000 8899221146
       225 RAJ                                  6000 8899221145
       321 pinto                                     8889990001

The remote side is now ready. Note that Phone for pinto is populated, but Bal is NULL — this is intentional, to demonstrate later that the DB Link returns nullable columns faithfully.

8. Local Database Setup (srv5 — PROD)

The local side initiates the connection. We need the database open, the listener up, and a working tnsnames.ora entry pointing at srv7.

8.1 Start the Local Database

[oracle@srv5 admin]$ . oraenv
ORACLE_SID = [prod] ? prod
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@srv5 admin]$ sqlplus / as sysdba

SQL> startup
ORACLE instance started.

Total System Global Area 1065353184 bytes
Fixed Size                  8904672 bytes
Variable Size             616562688 bytes
Database Buffers          436207616 bytes
Redo Buffers                3678208 bytes
Database mounted.
Database opened.

8.2 Verify the Local Listener

[oracle@srv5 admin]$ lsnrctl status LISTENER

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Start Date                09-SEP-2025 10:26:19
Uptime                    0 days 0 hr. 39 min. 41 sec
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv5.cloudmap.in)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "prod.cloudmap.in" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB.cloudmap.in" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully

8.3 Configure `tnsnames.ora`

The DB Link will reference a TNS alias, so it must already exist in tnsnames.ora at $ORACLE_HOME/network/admin/.

[oracle@srv5 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
# /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TOPROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv5.cloudmap.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

TODELL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv7.cloudmap.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dell)
    )
  )

The two aliases:

TOPROD — points to the local PROD database (used for self-tests / sanity checks)
TODELL — points to the remote DELL database (this is what the DB Link will USE)

8.4 Test Connectivity with `tnsping`

Always confirm the network path before creating a DB Link. If tnsping fails, the DB Link will fail — no exceptions.

[oracle@srv5 admin]$ tnsping todell

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 09-SEP-2025 11:05:23

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = srv7.cloudmap.in)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dell)))
OK (40 msec)

[oracle@srv5 admin]$ tnsping toprod

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = srv5.cloudmap.in)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod)))
OK (10 msec)

Both pings return OK — the path is healthy.

9. Creating Database Links

With the network proven, we can now create the actual links. We will create one PUBLIC link (visible to every user) and one PRIVATE link (visible only to user OT).

9.1 Create a Public Database Link (as SYS)

A public link is a quick way to validate the plumbing — every user sees it immediately.

[oracle@srv5 admin]$ sqlplus / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create public database link remotedb
     connect to u1 identified by u1
     using 'todell';
Database link created.

Anatomy of the statement:

Clause	Meaning
`create public database link remotedb`	Public link, visible to all users; name is `REMOTEDB`
`connect to u1 identified by u1`	Authenticate to the remote DB as user `u1` with password `u1`
`using 'todell'`	Use the TNS alias `TODELL` from `tnsnames.ora`

🔒 Security note: The password is stored in the data dictionary and ships with every export of the link. Never use a privileged account here. See Section 11 for hardening.

9.2 Test the Public DB Link

SQL> select * from account@remotedb;

      ACNO NAME                                  BAL      PHONE
---------- ------------------------------ ---------- ----------
       224 Nitesh                               5000 8899221144
       227 sam                                  4000 8899221146
       225 RAJ                                  6000 8899221145
       321 pinto                                     8889990001

🎉 The remote ACCOUNT table is now queryable from PROD as if it were local. The @remotedb suffix is what tells Oracle to route the query across the link.

9.3 Grant DB-Link Privilege to a Regular User

Now we move from "PUBLIC" to a more secure pattern — letting an application user OT create its own private link. First, the privilege:

SQL> conn / as sysdba
Connected.

SQL> grant create database link to ot;
Grant succeeded.

⚠️ Caution: The exact privilege name is CREATE DATABASE LINK for private links and CREATE PUBLIC DATABASE LINK for public links. A common mistake is to grant CREATE DB LINK or to misspell the keywords — any deviation produces ORA-00990: missing or invalid privilege.

9.4 Create a Private Database Link (as OT)

SQL> conn ot/ot
ERROR:
ORA-28002: the password will expire within 7 days
Connected.

SQL> show user
USER is "OT"

SQL> create database link prodtodell
     connect to u1 identified by u1
     using 'todell';
Database link created.

The ORA-28002 is informational, not fatal — the connection still succeeds. It's a reminder to refresh OT's password before it actually expires (which would break this link silently in production).

9.5 Test the Private DB Link

SQL> select * from account@prodtodell;

      ACNO NAME                                  BAL      PHONE
---------- ------------------------------ ---------- ----------
       224 Nitesh                               5000 8899221144
       227 sam                                  4000 8899221146
       225 RAJ                                  6000 8899221145
       321 pinto                                     8889990001

Same data as the public link — but only user OT can use this one.

10. Verification — Inspect the Data Dictionary

Oracle exposes DB-Link metadata through the DBA_DB_LINKS view. Always run this check after creating a link.

SQL> conn / as sysdba
Connected.

SQL> select db_link, username, host, created from dba_db_links;

DB_LINK                  USERNAME   HOST       CREATED
------------------------ ---------- ---------- ----------
SYS_HUB                             SEEDDATA   17-APR-19
REMOTEDB.CLOUDMAP.IN     U1         todell     09-SEP-25
PRODTODELL.CLOUDMAP.IN   U1         todell     09-SEP-25

What this tells us:

SYS_HUB — built-in Oracle 19c administrative link; leave it alone.
REMOTEDB.CLOUDMAP.IN — our PUBLIC link (the .CLOUDMAP.IN suffix is the database's DB_DOMAIN).
PRODTODELL.CLOUDMAP.IN — our PRIVATE link, owned by OT.

Useful related views:

View	Purpose
`DBA_DB_LINKS`	All links across all schemas (DBA only)
`ALL_DB_LINKS`	Links the current user can use
`USER_DB_LINKS`	Links owned by the current user
`V$DBLINK`	Open links in the current session

11. Security Considerations (Production Best Practice)

A working DB Link is not yet a safe DB Link. Before promoting to production, layer in these controls:

Practice	Why It Matters
Avoid hardcoded passwords	Use Oracle Wallet with `connect to current_user using 'wallet_alias'` or CMU/Kerberos for true credential-less links
Use encrypted network (TCPS)	Plain TCP carries the password in clear text on the wire during initial authentication
Use least-privilege remote users	The remote account should only have `SELECT` (or specific DML) on the exact tables required — not `RESOURCE` or `DBA`
Restrict PUBLIC DB Links	Public links are visible to every schema, including least-trusted ones; prefer private links by default
Monitor regularly	`SELECT * FROM dba_db_links;` and audit `SYS.LINK$` for unauthorized link creation
Set password rotation policies	Coordinate `OT` and `U1` password expiry with the DB-Link credential refresh process

A minimal monitoring query for ongoing health:

SELECT db_link, username, host, created
FROM   dba_db_links
ORDER  BY created DESC;

12. Common Errors & Resolutions

These three errors come up repeatedly during DB-Link work — keep this table handy.

Error	Cause	Resolution
ORA-02010	Missing single quotes around the TNS alias in `USING`	Use `using 'todell'` — the alias must be a string literal
ORA-00990	Invalid privilege name	Use `GRANT CREATE DATABASE LINK TO <user>;` (or `CREATE PUBLIC DATABASE LINK` for public)
ORA-28002	Password expiring soon for the connecting user	Change the user's password (`ALTER USER <name> IDENTIFIED BY <new_pwd>;`) and update the link if the password is embedded

Other common ones you may encounter:

ORA-12154 — TNS:could not resolve the connect identifier specified → The alias in USING doesn't exist in tnsnames.ora on the source server.
ORA-12541 — TNS:no listener → The remote listener is down or the port is blocked by a firewall.
ORA-01017 — invalid username/password → The CONNECT TO credentials are wrong, or the remote password has changed.

13. Risks and Mitigation

A consolidated risk register for production DB-Link deployments:

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.

14. Future Enhancements

To improve reliability, security, and scalability beyond this baseline implementation, consider:

Secure DB Links Using Oracle Wallet — Store credentials securely using Oracle Wallet instead of plain-text passwords.
Encrypted Network Communication — Implement TCPS (SSL/TLS encryption) for secure data transmission between databases.
Use of Materialized Views — For frequently accessed remote data, materialized views reduce network overhead and improve performance.
Monitoring and Alerting — Integrate monitoring tools to track DB-Link usage, connection failures, and query performance.
High Availability Architecture — Combine DB Links with Data Guard or RAC environments to improve availability and fault tolerance.
Role-Based Access Control — Restrict DB-Link usage using roles and least-privilege access policies.

15. Conclusion

The successful implementation of a 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 has been 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 — banking transaction validation, cross-application reporting, and multi-system data verification — highlight the practical value of Database Links in modern enterprise Oracle 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.

In short: this guide provides a complete, end-to-end, production-oriented Database Link setup that bridges the gap between theoretical understanding and real-world implementation. DB Links remain one of the most powerful and reliable integration mechanisms in the Oracle ecosystem — when configured deliberately, they are also one of the safest.

Thanks for reading.

BR,

Syed Zaheer

Search This Blog

Cloud, Database, Apps and AI Workshop