database link not active

hi folks,
i am trying to set up a very simple streams flow from one db1 to db2. when i am creating a private dblink from db1 to db2 with the stream-admin user, the dblink is showing as not active.
in the init.ora file for both db1 and db2 i have kept the global_names = TRUE and i have kept the dblink name as db2.
can anyone plzz tell me where am i going wrong.

thanks and regards,
Prasenjit

Hi Prasenjit,

1. Check the network connection between the two databases by "tnsping"
2. Check the alias in the tnsnames.ora file which used in the dblink creation

Regards,
Nir

hi Nir,
thanks for the reply - sorry i didn't mention - i checked the connectivity with the following :

C:\Documents and Settings\121570>tnsping uii.world

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 21-SEP-20
04 13:07:50

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
C:\ORANT\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 172.18.34.133)(PORT = 1521))) (CONNECT_DATA = (SID = UII) (SERVER = DEDI
CATED)))
OK (60 msec)

it seems to be fine.

thanks and regards,
Prasenjit

Hi Prasenjit,

Send us please the error message you've got when you use the db link.

Nir

hi,
actually after doing the Stream Admin set-up on both the db1(pdsource) and db2(uii.world), i wanted to test the dblink thru OEM, where i am getting this.
but Nir, one thing i want to mention here - don't know whether it is relevant or not.
i have set up the db "pdsource" in my pc yes'day thru the OEM - the TNS entry for this comes like

PDSOURCE.TCSKOLKATA.CO.IN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TCS031301)(PORT = 1521))
)
(CONNECT_DATA =
(SID= PDSOURCE)
(SERVER = DEDICATED)
)
)

(cudn't undstand why this TCSKOLKATA part came - is it bcos of some network settings???????)

but for db2 (uii.world) - the TNS entry is like this

UII.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.34.133)(PORT = 1521))
)
(CONNECT_DATA =
(SID = UII)
(SERVER = DEDICATED)
)
)

anything to do with this???

thanks and regards,
Prasenjit

another thing, i am creating this dblink with stream-admin user and it is a private dblink - that's what the setup demands i guess - what if i create it as public with my schema user of "pdsource" ??? - but don't know whether the streams will work or not.
actually was referring to the doc -


[URL=http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96571/man_prep.htm#43906]

thanks and regards,
Prasenjit

check your sqlnet.ora parameter names.default_domain value..
and post your db link creation script, does the domain name appended
in your dblink name? You can also post errorcodes as what nir_s have asked.;)

hi,
checked the sqlnet.ora
NAMES.DEFAULT_DOMAIN = tcskolkata.co.in
SQL> create database link UII.WORLD
2 connect to strmadmin
3 identified by strmadminpd
4 using '(DESCRIPTION =
5 (ADDRESS_LIST =
6 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.34.133)(PORT = 1521))
7 )
8 (CONNECT_DATA =(SERVICE_NAME=UII.WORLD))
9 )';

Database link created.

SQL> select * from uii.ar_classes@uii.world;
select * from uii.ar_classes@uii.world
*
ERROR at line 1:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor


this is what i am getting.

thanks and regards.

can use just use your database alias in your tnsnames.ora instead
of that whole description in that USING statement?
what is the db link created when you query DB_LINK in either user_db_links or dba_db_links?

you seems to be confused between db link name and db alias name.