dblink no listener - normal connection works
Krzysztof Cierpisz
select 'a' from dual@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@chris-ub:/$ sqlplus GUEST/GUEST@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
Krzysztof Cierpisz
> but now even when I change it back (the service name) to TEST and try
> to create dblink using 'TEST' it's not working.
>
correction to the above,
replacing OXO with TEST in tnsnames.ora and in dblink creation works.
I don't understand now why OXO as tnsname is not working returning no
listener.
thanks,
chris
Carlos
global dbname / GLOBAL_NAMES issue?
Cheers.
Carlos.
Krzysztof Cierpisz
>
> global dbname / GLOBAL_NAMES issue?
>
> Cheers.
>
> Carlos.
not sure:
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='global_names';
VALUE
--------------------------------------------------------------------------------
FALSE
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
EMEADB11
chris
Mark D Powell
> On 14 Jul., 11:35, Carlos <miotromailcar...@netscape.net> wrote:
>
>
>
> > global dbname / GLOBAL_NAMES issue?
>
> > Cheers.
>
> > Carlos.
>
> not sure:
> SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='global_names';
>
> VALUE
> FALSE
>
> SQL> select * from global_name;
>
> GLOBAL_NAME
> EMEADB11
>
> chris
One of the default rules for a database link is that the link name
must match the remote database name: what is the name of the remote
database?
HTH -- Mark D Powell --
Mladen Gogala
> One of the default rules for a database link is that the link name must
> match the remote database name: what is the name of the remote database?
That is true only in the situations when the global_names=true, which is
a recommended setting, though not the default one.
phil_h...@yahoo.com.au
By default, a DB link will use the TNS names file in the database
home. However, it's possible that when you run tnsping, you're using
another Oracle home with a different TNS names file.
-- Phil
Krzysztof Cierpisz
name of the remote database is
TEST.xxxx.xxxx.xxx (with domain name)
chris
Krzysztof Cierpisz
definitely I have only one Oracle home.
there is only one tnsnames.ora file on my system.
it's fairly new entry into tnsnames.ora file, and as mentioned above
when I change tnsname entry from OXO into TEST both connections
(sqlplus and dblink) work fine.
thanks,
chris
Mark D Powell
As an interesting side fact, I remember the default being true so I
checked the manuals: Sure enough using 10.2 documentation it does
default to true but on 8.1.7 (which has 8.1.6 Reference) the default
was true. So I grabbed the 9.2 documentation and it was true there.
Krzysztof, what is the setting of global_names on the target
database? I believe this parameter controls incoming requests and not
outgoing link names.
Mladen Gogala
> As an interesting side fact, I remember the default being true so I
> checked the manuals: Sure enough using 10.2 documentation it does
> default to true but on 8.1.7 (which has 8.1.6 Reference) the default was
> true. So I grabbed the 9.2 documentation and it was true there.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
global_names boolean FALSE
This is my very own DB on my home machine. I haven't changed the
parameter. The documentation may say whatever it wants, when I install
the database from scratch, the parameter is set to false.
Carlos
> Krzysztof, what is the setting of global_names on the target
> database? I believe this parameter controls incoming requests and not
> outgoing link names.
>
> HTH -- Mark D Powell --
Let the Oracle speak:
"The name that you give to a link on the local database depends on
whether the remote database that you want to access enforces global
naming. If the remote database enforces global naming, then you must
use the remote database global database name as the name of the link.
"
That was my first question to OP.
Cheers.
Carlos.
Sybrand Bakker
<ciap...@gmail.com> wrote:
>definitely I have only one Oracle home.
>there is only one tnsnames.ora file on my system.
>it's fairly new entry into tnsnames.ora file, and as mentioned above
>when I change tnsname entry from OXO into TEST both connections
>(sqlplus and dblink) work fine.
>
>thanks,
>chris
Which tnsnames.ora are you referring to? The one on the server?
Because it is that one which will be used by the database link!
You need to establish whether the source database server can connect
to the target database, your client is irrelevant.
------
Sybrand Bakker
Senior Oracle DBA
Krzysztof Cierpisz
> On Wed, 14 Jul 2010 23:24:17 -0700 (PDT), Krzysztof Cierpisz
>
I was referring to tnsnames.ora file on my local server (EMEADB11)
it is actually the same entry used from my client when executing
sqlplus (in this case client and server are same machines)
tnsname entry points to remote server.
--
chris
Krzysztof Cierpisz
I cannot see that value (no access to v$parameter) -> it's a very
limited account
Carlos
Ask the DBA, but this will probably be the answer.
It seems it was a global dbname / GLOBAL_NAMES issue after all... ;-)
Cheers.
Carlos.
gazzag
>
> Ask the DBA, but this will probably be the answer.
>
> It seems it was a global dbname / GLOBAL_NAMES issue after all... ;-)
>
> Cheers.
>
> Carlos
He shoot, he scores! First the World Cup, now this... You're on a
roll :D
-g
kkn...@gmail.com
joel garry
> modern compressible flow(anderson) solution manual s available sir?