OraFAQ Forum: Networking and Gateways

Home » RDBMS Server » Networking and Gateways » db links not working (9i oracle on 8 solaris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

db links not working [message #389820]

Tue, 03 March 2009 12:27

mfergi
Messages: 7
Registered: January 2009
Location: Cincinnati, OH

Junior Member

can create db links, but due to the network administrator changing the domain to include a dash, get a variety of errors that lead back to the illegal character. pasting below some examples. would appreciate any help or thoughts on this.
SQL> select owner, db_link, substr(host,1,20)
2 from all_db_links;

OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------

SUBSTR(HOST,1,20)
--------------------
PRODSYS
REMOTE2DEV1.CIN.L-3COM.COM
dev1

SQL> select * from bnftdate@'REMOTE2DEV1.CIN.L-3COM.COM';
select * from bnftdate@'REMOTE2DEV1.CIN.L-3COM.COM'
*
ERROR at line 1:
ORA-01729: database link name expected

SQL> select * from bnftdate@remote2dev1.cin.l-3com.com;
select * from bnftdate@remote2dev1.cin.l-3com.com
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> select * from bnftdate@"REMOTE2DEV1";
select * from bnftdate@"REMOTE2DEV1"
*
ERROR at line 1:
ORA-02085: database link REMOTE2DEV1.CIN.L-3COM.COM connects to
DEV1.CIN.L-3COM.COM

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

prod.cin.l-3com.com

SQL> select * from bnftdate@REMOTE2DEV1;
select * from bnftdate@REMOTE2DEV1
*
ERROR at line 1:
ORA-02085: database link REMOTE2DEV1.CIN.L-3COM.COM connects to
DEV1.CIN.L-3COM.COM

SQL> create table mfergus as (select * from bnftdate@remote2dev1);
create table mfergus as (select * from bnftdate@remote2dev1)
*
ERROR at line 1:
ORA-02085: database link REMOTE2DEV1.CIN.L-3COM.COM connects to
DEV1.CIN.L-3COM.COM

SQL> create table mfergus as (select * from bnftdate@'REMOTE2DEV1');
create table mfergus as (select * from bnftdate@'REMOTE2DEV1')
*
ERROR at line 1:
ORA-01729: database link name expected

SQL> create table mfergus as (select * from bnftdate@"REMOTE2DEV1");
create table mfergus as (select * from bnftdate@"REMOTE2DEV1")
*
ERROR at line 1:
ORA-02085: database link REMOTE2DEV1.CIN.L-3COM.COM connects to
DEV1.CIN.L-3COM.COM

Report message to a moderator

Re: db links not working [message #389829 is a reply to message #389820]

Tue, 03 March 2009 12:46

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-02085: database link %s connects to %s
 *Cause: a database link connected to a database with a different name.
  The connection is rejected.
 *Action: create a database link with the same name as the database it
  connects to, or set global_names=false.

Simple as reading the documentation.

Regards
Michel

Report message to a moderator

Re: db links not working [message #389980 is a reply to message #389829]

Wed, 04 March 2009 07:27

mfergi
Messages: 7
Registered: January 2009
Location: Cincinnati, OH

Junior Member

global_names = false already. created the link using the same name as the connect string, still no luck using the links. any one else have any ideas? the dash is not by choice, company mandated so will need work around for the invalid character too.
thanks in advance for any help,
m

SQL> create database link "dev1.cin.l-3com.com" using 'dev1.cin.l-3com.com';

Database link created.

SQL> create table mferg as (select * from bnftdate@'dev1.cin.l-3com.com';
create table mferg as (select * from bnftdate@'dev1.cin.l-3com.com'
*
ERROR at line 1:
ORA-01729: database link name expected

SQL> create table mferg as (select * from bnftdate@"dev1.cin.l-3com.com");
create table mferg as (select * from bnftdate@"dev1.cin.l-3com.com")
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name

SQL>

Report message to a moderator

Re: db links not working [message #389991 is a reply to message #389980]

Wed, 04 March 2009 08:04

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-12154: TNS:could not resolve the connect identifier specified
 *Cause:  A connection to a database or other service was requested using
 a connect identifier, and the connect identifier specified could not
 be resolved into a connect descriptor using one of the naming methods
 configured. For example, if the type of connect identifier used was a
 net service name then the net service name could not be found in a
 naming method repository, or the repository could not be
 located or reached.
 *Action:
   - If you are using local naming (TNSNAMES.ORA file):
      - Make sure that "TNSNAMES" is listed as one of the values of the
        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA)
      - Verify that a TNSNAMES.ORA file exists and is in the proper
        directory and is accessible.
      - Check that the net service name used as the connect identifier
        exists in the TNSNAMES.ORA file.
      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
        file.  Look for unmatched parentheses or stray characters. Errors
        in a TNSNAMES.ORA file may make it unusable.
   - If you are using directory naming:
      - Verify that "LDAP" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Verify that the LDAP directory server is up and that it is
        accessible.
      - Verify that the net service name or database name used as the
        connect identifier is configured in the directory.
      - Verify that the default context being used is correct by
        specifying a fully qualified net service name or a full LDAP DN
        as the connect identifier
   - If you are using easy connect naming:
      - Verify that "EZCONNECT" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Make sure the host, port and service name specified
        are correct.
      - Try enclosing the connect identifier in quote marks.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.

Regards
Michel

Report message to a moderator

Re: db links not working [message #391543 is a reply to message #389829]

Thu, 12 March 2009 08:50

mfergi
Messages: 7
Registered: January 2009
Location: Cincinnati, OH

Junior Member

thanks for the email, but i had already read the documentaion and recreated the links using the naming convention as suggested. still does not work. tnsping does work with same name for both instances. if link used without the " " around the domain name (company mandated number-(dash)-letter part), ERROR at line 1: ORA-01729: database link name expected.
if don't use the " says ERROR at line 1: ORA-12154: TNS:could not resolve service name. anyone with any other ideas? and please keep the sarcasm at a minimum. most people don't appreciate it. i know i need help, and not as experienced as most, but i am trying and would welcome any real assistance. that is why i joined this forum and am posting a question. thanks in advance,
m

Report message to a moderator

Re: db links not working [message #391547 is a reply to message #389820]

Thu, 12 March 2009 08:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

dash character ("-") are not valid for Oracle Object names

Report message to a moderator

Re: db links not working [message #391548 is a reply to message #391543]

Thu, 12 March 2009 08:54

mfergi
Messages: 7
Registered: January 2009
Location: Cincinnati, OH

Junior Member

sorry for the typo, meant if DO use the " says ERROR at line 1: ORA-12154: TNS:could not resolve service name. anyone with any other ideas? and please keep the sarcasm at a minimum. most people don't appreciate it. i know i need help, and not as experienced as most, but i am trying and would welcome any real assistance. that is why i joined this forum and am posting a question. thanks in advance,
m

Report message to a moderator

Re: db links not working [message #391563 is a reply to message #391547]

Thu, 12 March 2009 10:29

mfergi
Messages: 7
Registered: January 2009
Location: Cincinnati, OH

Junior Member

yes, i have read a lot of documentation that states it over and over again -- NO DASHES. however, since it is company mandated, the domain name was decided for me. i have to find a work around to this. using quotes, single and double, i can create the link but cannot get it to work. any ideas?????? would really, really appreciate any help getting this to work.

Report message to a moderator

Re: db links not working [message #391572 is a reply to message #389820]

Thu, 12 March 2009 10:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You have been handed a pig & instructed to teach it to fly.
Good Luck.

Oracle does not know or care what the actual Domain Name (DNS) is.

The string which follows USING is just a pointer used to access the tnsnames.ora file.
It could just as easily be foo_bar.com & work fat, dumb, & happy for you.

Now take a deep breath, relax & explain why the string inside the database must EXACTLY match the results from DNS.

Report message to a moderator

Previous Topic:	TNS-12518: TNS:listener could not hand off client connection
Next Topic:	Listener down on one node

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 11:31:09 CST 2025