CREATION OF DATABASE LINK

Source DB/IP – source / 192.168.0.87

Destination DB/IP – target / 192.168.0.88

Step 1:- Create entry in listener and tnsnames.ora

LISTENER =

 (DESCRIPTION_LIST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.88)(PORT = 1521))

 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

 )

 )

SID_LIST_LISTENER=

 (SID_LIST=

 (SID_DESC=

 (GLOBAL_DBNAME=target)

 (SID_NAME=target)

 )

Tns entry:

rback =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.88)(PORT = 1521))

 (CONNECT_DATA =

 (SERVER = DEDICATED)

 (SERVICE_NAME = target)

 )

 )

Step 2: Start listener in source

[oracle@linux-90 admin]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-OCT-2023 21:34:25

Copyright (c) 1991, 2019, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production

System parameter file is /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Log messages written to /oracle/app/oracle/diag/tnslsnr/linux-90/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.87)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.87)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 25-OCT-2023 21:34:35

Uptime 0 days 0 hr. 0 min. 10 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Listener Log File /oracle/app/oracle/diag/tnslsnr/linux-90/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.87)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "target" has 1 instance(s).

 Instance "target", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@linux-90 admin]$

Step 3: Start listener in Destination.

[oracle@sipl-78 admin]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-OCT-2023 21:34:25

Copyright (c) 1991, 2019, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production

System parameter file is /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Log messages written to /oracle/app/oracle/diag/tnslsnr/linux-90/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.87)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.87)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 25-OCT-2023 21:34:35

Uptime 0 days 0 hr. 0 min. 10 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Listener Log File /oracle/app/oracle/diag/tnslsnr/linux-90/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.87)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "target" has 1 instance(s).

 Instance "target", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@sipl-78 admin]$

Step 4:- Check using tnsping

[oracle@linux-90 admin]$ tnsping rback

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 25-OCT-2023 21:38:19

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:

/oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.87)(PORT =

1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = target)))

OK (180 msec)

[oracle@sipl-78 admin]$

Step 5:- Connect to source and Create Database Link.

[oracle@linux-90 admin]$ export ORACLE_SID=source

[oracle@linux-90 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 25 21:41:03 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL>

SQL> create database link emp_target_db

 2 connect to saiteja identified by saiteja

 3 using 'rback';

Database link created.

SQL> select * from emp@emp_target_db;

 ID

----------

 1

 2

 3

 4

 5

 6

 7

 8

 9

 10

10 rows selected.

SQL>