database link not active

[prasen999]

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

[nir_s]

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

[prasen999]

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

[nir_s]

Hi Prasenjit,

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

Nir

[prasen999]

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

[prasen999]

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

[reydp]

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.

[prasen999]

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.

[reydp]

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?

[reydp]

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