database link error

[jennifer2007]

Hi Friends,

Can you help me with my database link problem please.
Something just went wrong here :(

You see, I have 2 10g DBs on both WIN2003 (ORCL, AND ORCL2).
On ORCL server I created a database link connecting to ORCL2.
I also make a connection test first, say:
At db server 1,
C:\> sqlplus scott/tiger@orcl (connected)
C:\> sqlplus scott/tiger@orcl2 (connected)

Connecting back to orcl (conn scott/tiger@orcl) I then create a db link:

SQL> create database link orcl2_lnk connect to scott identified by
tiger using 'orcl2';

SQL> select * from tab@orcl2_lnk;
select * from tab@orcl2_lnk
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


Why is this error pleaseeeeeeeee

Thanks a lot

[ales]

The connection desription for the orcl2 database has to be in tnsnames.ora on the orcl server, not on the client.

As an option, you can put the whole connection description into the create database link statement:

SQL> create database link orcl2_lnk connect to scott identified by
tiger using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(PORT = orcl2_port)(HOST = orcl2_host))) (CONNECT_DATA = (SERVICE_NAME = orcl2_service_name)))';

[PAVB]

Be sure you have your orcl2 TNSNAMES entry in orcl://${ORACLE_HOME}/network/admin/tnsnames.ora then test if from box orcl by doing tnsping orcl2

[jennifer2007]

Of course hunnies I know that I have made several db link connections before, like 100s of times and this is my first time to fail
Tha fact that I can connect to orcl2 at sever1
C:\> sqlplus scott/tiger@orcl2 (connected)
means I have entry of orcl2 in the tnsnames.ora at server1

My tnsnames.ora at server01 is
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER01)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)


I am working only on these 2 servers talking with each other and i dont
use any client PCs yet. I can tsnping orcl2.

Any more ideas pls.

[marist89]

congratulations, you've just given everybody SCOTT's access rights, good luck on that security audit

Anyway, you have an issue with your networking, period. Either your tnsnames.ora file is messed up (most likely) or your listener.ora file is not listening correctly (less likely).

[jennifer2007]

its funny i just drop and recreated the db links with the same command and
its ok now