DATABASE LINKS IN ORACLE
A database link is a pointer that
defines a one-way communication path from an Oracle Database server to another
database server. The link pointer is actually defined as an entry in a data
dictionary table. To access the link, you must be connected to the local
database that contains the data dictionary entry.
A database link connection is
one-way in the sense that a client connected to local database A can use a link
stored in database A to access information in remote database B, but users
connected to database B cannot use the same link to access data in database A.
If local users on database B want to access data on database A,then they must
define a link that is stored in the data dictionary of database B
Types
Private database
link
This link is more secure than a
public or global link, because only the owner of the private link, or
subprograms within the same schema, can use the link to access the remote
database.
Public database
link
When many users require an access
path to a remote Oracle Database, you can create a single public database link
for all users in a database.
Rcdb is an
database for target server
Clone as an database for datalink server
Step 1: create a user in the target server and grant the privilages
[oracle@vignesh
~]$ . rcdb.env
[oracle@vignesh
~]$ sqlplus / as sysdba
SQL>
create user vignesh identified by vbt;
User
created.
SQL>
grant connect,resource to vignesh;
Grant succeeded.
SQL> alter user vignesh default tablespace users quota unlimited on users;
Step 2:connect the user create table and insert the sample values
SQL>
conn vignesh/vbt
Connected.
SQL> create table sample(id int,name varchar2(20));
Table created.
SQL> insert into sample values(1,'vignesh');
1 row created.
SQL> insert into sample values(2,'vbt');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from sample;
ID NAME
----------
--------------------
1 vignesh
2 vbt
Step 3: check listener and tns names entry in the network files and ping the two database target and database link database.
[oracle@vignesh ~]$ cd $ORACLE_HOME/network/admin
[oracle@vignesh admin]$ ls
listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
[oracle@vignesh admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:23
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter
error
TNS-00511: No listener
Linux Error: 111: Connection
refused
[oracle@vignesh admin]$ lsnrctl start listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:40
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/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
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to
/u01/app/oracle/diag/tnslsnr/vignesh/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vignesh.localdomain)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias listener
Version TNSLSNR
for Linux: Version 19.0.0.0.0 - Production
Start Date
13-SEP-2021 23:17:40
Uptime 0 days 0
hr. 0 min. 1 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/vignesh/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vignesh.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc",
status UNKNOWN, has 1 handler(s) for this service...
Service "livedb" has 1 instance(s).
Instance "livedb",
status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@vignesh admin]$ tnsping rcdb
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:47
Copyright (c) 1997, 2019, Oracle. All rights reserved.
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 = 192.168.164.128
OK (10 msec)
[oracle@vignesh admin]$ tnsping clone
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:51
Copyright (c) 1997, 2019, Oracle. All rights reserved.
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 = 192.168.164.128)
OK (0 msec)
[oracle@vignesh admin]$ tnsping rcdb
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:51
Copyright (c) 1997, 2019, Oracle. All rights reserved.
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 = 192.168.164.128)
OK (0 msec)
Step 4: create private database link to connect the target database user table:
[oracle@vignesh ~]$ sqlplus / as sysdba
SQL> create database link v_link connect to vignesh identified by vbt using 'rcdb';
Database link created.
After create the link we can view the target database table using the db_link
SQL>
desc sample@v_link;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID
NUMBER(38)
NAME VARCHAR2(20)
SQL> select * from sample@v_link;
ID NAME
----------
--------------------
1 vignesh
2 vbt
Note: private link only accessible for sys users only.
Create other user to connect the db link its not working
SQL> create user vbt identified by vbt;
User created.
SQL> grant connect,resource to vbt;
Grant succeeded.
SQL>
conn vbt/vbt
Connected.
SQL> desc sample@v_link;
ERROR:
ORA-02019: connection description for remote database not found
Step 5:create a public link to access all user in the db link database to target database table.
SQL> create public database link b_link connect to vignesh identified by vbt using 'rcdb';
Database link created.
SQL>
desc sample@b_link;
Name Null? Type
-----------------------------------------
-------- ----------------------------
ID
NUMBER(38)
NAME VARCHAR2(20)
SQL>
conn vbt/vbt
Connected.
SQL>
desc sample@b_link
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID
NUMBER(38)
NAME VARCHAR2(20)
SQL> select * from sample@b_link;
ID NAME
----------
--------------------
1 vignesh
2 vbt
Note: the public link access the target database all the users
Step 6: insert the values in the target database using database link
SQL> insert into sample@b_link values(3,'tharan');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sample@b_link;
ID NAME
----------
--------------------
3 tharan
1 vignesh
2 vbt
Step 7:check the target database can be updated the new record
[oracle@vignesh
~]$ . rcdb.env
[oracle@vignesh
~]$ sqlplus / as sysdba
SQL> conn vignesh/vbt
Connected.
SQL>
SQL> select * from sample;
ID NAME
----------
--------------------
3 tharan
1 vignesh
2 vbt
No comments:
Post a Comment