Home » RDBMS Server » Networking and Gateways » db links not working (9i oracle on 8 solaris)
db links not working [message #389820] Tue, 03 March 2009 12:27 Go to next message
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
Re: db links not working [message #389829 is a reply to message #389820] Tue, 03 March 2009 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: db links not working [message #389980 is a reply to message #389829] Wed, 04 March 2009 07:27 Go to previous messageGo to next message
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>
Re: db links not working [message #389991 is a reply to message #389980] Wed, 04 March 2009 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: db links not working [message #391543 is a reply to message #389829] Thu, 12 March 2009 08:50 Go to previous messageGo to next message
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
Re: db links not working [message #391547 is a reply to message #389820] Thu, 12 March 2009 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
dash character ("-") are not valid for Oracle Object names
Re: db links not working [message #391548 is a reply to message #391543] Thu, 12 March 2009 08:54 Go to previous messageGo to next message
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
Re: db links not working [message #391563 is a reply to message #391547] Thu, 12 March 2009 10:29 Go to previous messageGo to next message
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.
Re: db links not working [message #391572 is a reply to message #389820] Thu, 12 March 2009 10:50 Go to previous message
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.

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


Current Time: Thu Mar 28 15:59:31 CDT 2024