DB Link configuration problem!

[amitstora]

Hi all!

I am having a problem setting up/configuring database link from PGVL DB to remote DB ITMIT as I have never before set up Db Link.

On PGVL DB(source),I have issued the following command from system
user:

Grant CREATE PUBLIC DATABASE LINK TO PVL;

Next,from PVL schema:

create public database link pgvl.orcl connect to pvl identified by pvl using 'remotesite';

Next,in tnsnames.ora file on PGVL DB,I have setup the following:

orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = integrator-15)
(Port = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME= orcl)(SRVR=DEDICATED))
)


remotesite=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = integrator-15)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = orcl)
(GLOBAL_NAME=PGVL.ORCL)
)
)

I have setup GLOBAL_NAMES =FALSE on PGVL DB but had set GLOBAL_NAMES=TRUE on the Target DB ORCL.

DB Link status at DBA Studio shows 'NOT ACTIVE'.

When I pass the following command at PVL schema on PGVL DB,I get following errors:

SQL> conn pvl/pvl@pgvl
Connected.

SQL> select count(*) from pvl.department_master@pgvl.orcl;
select count(*) from pvl.department_master@pgvl.orcl
*
ERROR at line 1:
ORA-02019: connection description for remote database not found


SQL> select count(*) from pvl.department_master @pgvl.orcl. itminfotech.local;
ERROR at line 1:
ORA-02019: connection description for remote database not found

Now to connect to remote DB ORCL from PGVL DB,I have to give the domain name as well otherwise Oracle refuses connection as follows:

SQL> conn pvl/pvl@orcl
ERROR:
ORA-12154: TNS:could not resolve service name


Warning: You are no longer connected to ORACLE.
SQL> conn pvl/pvl@orcl.itminfotech.local
Connected.
SQL>


Could anyone suggest as to what am I missing/doing wrong in the configuration.

Suggestion from anyone would be appreciated.

Thanks & regards,

Amit.

[adewri]

confusion and more confusion....

just fill in the blanks...

1. LOCAL DATABASE NAME ___________ (connect string)
2. LOCAL DATABASE GLOBAL NAME ______________
3. LOCAL USERNAME _____________
4. REMOTE DATABASE NAME _______________ (connect string)
5. REMOTE DATABASE GLOBAL NAME _______________
6. REMOTE DATABASE USERNAME ___________ PASSWORD __________

[nabeel]

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.

Global database link
When an Oracle network uses Oracle Names, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.


Users of Database Links
When creating the link, you determine which user should connect to the remote database to access the data. The following table explains the differences among the categories of users involved in database links:

User Type Meaning And Sample Link Creation Syntax

Connected user
A local user accessing a database link in which no fixed username and password have been specified. If SYSTEM accesses a public link in a query, then the connected user is SYSTEM, and Oracle connects to the SYSTEM schema in the remote database.

Note: A connected user does not have to be the user who created the link, but is any user who is accessing the link.

CREATE PUBLIC DATABASE LINK hq USING 'hq';

Current user
A global user in a CURRENT_USER database link. The global user must be authenticated by an X.509 certificate (an SSL-authenticated enterprise user) or a password (a password-authenticated enterprise user), and be a user on both databases involved in the link. Current user links are an aspect of the Oracle Advanced Security option.

CREATE PUBLIC DATABASE LINK hq CONNECT TO CURRENT_USER using 'hq';

Fixed user
A user whose username/password is part of the link definition. If a link includes a fixed user, then the fixed user's username and password are used to connect to the remote database.

CREATE PUBLIC DATABASE LINK hq CONNECT TO jane IDENTIFIED BY doe USING 'hq';