dblink no listener - normal connection works

From: Krzysztof Cierpisz <ciapecki_at_gmail.com>
Date: Wed, 14 Jul 2010 01:00:47 -0700 (PDT)
Message-ID: <0b844198-f21b-4d2a-82c2-6d963be279df_at_y11g2000yqm.googlegroups.com>

I get ORA-12541: TNS:no listener when executing: select 'a' from dual_at_oxo_dbl2;

oxo_dbl2 is database link created with:
CREATE DATABASE LINK oxo_dbl2
CONNECT TO "GUEST" IDENTIFIED BY "GUEST" USING 'OXO'; tnsname entry for OXO is:
OXO =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx.xx.xxxxx.xxx)(PORT = 1521))
(CONNECT_DATA =

    (SERVER = DEDICATED)
    (SID = TEST)
   )
  )

I hid the host with x's

tnsping OXO works:
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx.xx.xxxxx.xxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = TEST)))
OK (530 msec)

connection with sqlplus works:
chris_at_chris-ub:/$ sqlplus GUEST/GUEST_at_OXO;

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jul 14 09:53:24 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select 'a' from dual;

'A'

---
a

what can be the reason that over db link the query is not working
returning "No Listener"?

it was working before when my tnsname entry looked like this:
TEST =
  (DESCRIPTION =


   (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx.xx.xxxxx.xxx)(PORT = 1521))

   (CONNECT_DATA =

    (SERVER = DEDICATED)
    (SID = TEST)
   )
  )

but now even when I change it back (the service name) to TEST and try
to create dblink using 'TEST' it's not working.

thanks for your help
chris

Received on Wed Jul 14 2010 - 03:00:47 CDT