Home » RDBMS Server » Networking and Gateways » create DATABASE LINK (Oracle 10.2.0.4 on HP-UX)
create DATABASE LINK [message #447067] Thu, 11 March 2010 15:51 Go to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Hello all,

I am trying to create a database link using this syntax:

CREATE PUBLIC DATABASE LINK remote 
   USING 'remote'; 

where 'remote' is the service name.
It creates it successfully giving the message "External database Created"

Note: I am doing using TOAD

But when i am trying to query something like
select * from scott.employee@remote

it gives an error:
ORA-12154: TNS:could not resolve the connect identifier specified

But the 'remote' db entry exists in the tnsnames.

I can't figure out where am i going wrong.

Please help me figuring this out.

Thanks
Munna
Re: create DATABASE LINK [message #447069 is a reply to message #447067] Thu, 11 March 2010 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
>where 'remote' is the service name.

Perhaps it is the "service name", but "using 'remote'"
clause refers to the TNSALIAS (to the left of 1st equal sign).

Log onto the DB server where "remote" exists & issue following command

lsnrctl service


Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version


post content of tnsnames.ora

[Updated on: Thu, 11 March 2010 17:13]

Report message to a moderator

Re: create DATABASE LINK [message #447070 is a reply to message #447067] Thu, 11 March 2010 17:18 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you BlackSwan for your reply, below are what you asked for:
oracle@server1$ lsnrctl service

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521)))
Services Summary...
Service "REMOTE.world" has 2 instance(s).
  Instance "REMOTE", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
  Instance "REMOTE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:36 refused:0 state:ready
         LOCAL SERVER
Service "REMOTE_XPT.world" has 1 instance(s).
  Instance "REMOTE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:36 refused:0 state:ready
         LOCAL SERVER

The command completed successfully


SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


tnsnames.ora
REMOTE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = REMOTE.world)
    )
  )

Thank You,
Munna

[Updated on: Thu, 11 March 2010 17:21]

Report message to a moderator

Re: create DATABASE LINK [message #447071 is a reply to message #447070] Thu, 11 March 2010 17:29 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
post content of sqlnet.ora

do as below & post your results

SQL> show parameter global

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size	     string
global_names			     boolean	 FALSE

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
V102
Re: create DATABASE LINK [message #447072 is a reply to message #447067] Thu, 11 March 2010 17:37 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
sqlnet.ora on my local machine:
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


sqlnet.ora on the DB server1:
# sqlnet.ora Network Configuration File: /opt/apps/oracle/product/10.2/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (BEQ,NONE)

#SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, KERBEROS5)

SQLNET.KERBEROS5_CONF = /etc/krb5.conf

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = Kerberos


SQL> show parameter global

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
global_names                         boolean     FALSE
SQL> 
SQL> 
SQL> 
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
REMOTE.WORLD


Thank you BlackSwan for your concerns.

-
Munna
Re: create DATABASE LINK [message #447073 is a reply to message #447072] Thu, 11 March 2010 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
comment out line as shown below

#SQLNET.AUTHENTICATION_SERVICES= (NTS)
Re: create DATABASE LINK [message #447230 is a reply to message #447067] Fri, 12 March 2010 09:46 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Good MOrning BlackSwan, i did comment out the line
SQLNET.AUTHENTICATION_SERVICES= (NTS)
in my local sqlnet.ora

Do you want me to try now?
Or do anything else?
Re: create DATABASE LINK [message #447231 is a reply to message #447230] Fri, 12 March 2010 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
>Do you want me to try now?

yes
Re: create DATABASE LINK [message #447232 is a reply to message #447067] Fri, 12 March 2010 10:01 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
I created the link now, using:
CREATE PUBLIC DATABASE LINK REMOTE
USING 'REMOTE'


and then ran a query:
select * from reporter.emp_data@REMOTE;

And it still gives the same error:
ORA-12154: TNS:could not resolve the connect identifier specified
Sad


thanks,
Munna
Re: create DATABASE LINK [message #447233 is a reply to message #447232] Fri, 12 March 2010 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
on client open up command window

CUT line below & PASTE into command window

SET
tnsping REMOTE
sqlplus scott/tiger@REMOTE


CUT commands & results from command window & PASTE back here
Re: create DATABASE LINK [message #447238 is a reply to message #447067] Fri, 12 March 2010 10:30 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
oracle@server1$ SET
ksh: SET:  not found
oracle@server1$ 
oracle@server1$ 
oracle@server1$ tnsping REMOTE

TNS Ping Utility for HPUX: Version 10.2.0.4.0 - Production on 12-MAR-2010 09:27:52

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

Used parameter files:
/opt/apps/oracle/product/10.2/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
oracle@server1$ sqlplus scott/tiger@REMOTE

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 09:28:26 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

Re: create DATABASE LINK [message #447243 is a reply to message #447238] Fri, 12 March 2010 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
open up command window

CUT line below & PASTE into command window

env | sort
cat /etc/hosts

CUT commands & results from command window & PASTE back here
Re: create DATABASE LINK [message #447245 is a reply to message #447067] Fri, 12 March 2010 12:07 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
oracle@server1$ env | sort
BANNER_ADMIN=/opt/apps/bannerTEST/admin
BANNER_HOME=/opt/apps/bannerTEST
BANNER_LINKS=/opt/apps/bannerTEST/links
BANNER_ROOT=/opt/apps
COBDIR=/opt/microfocus/cobol
COBPATH=/home/oracle:/opt/apps/bannerTEST/links:/opt/apps/bannerTEST/general/exe
COBPREF=
COBSUFX=
COLUMNS=101
DISPLAY=192.168.20.16:10.0
EDITOR=vi
ERASE=^H
EXE_HOME=/opt/apps/bannerTEST/general/exe
HOME=/home/oracle
LD_LIBRARY_PATH=/opt/apps/oracle/product/10.2/lib:/opt/microfocus/cobol/lib
LINES=64
LOGNAME=oracle
MAIL=/var/mail/oracle
MANPATH=/usr/share/man/%L:/usr/share/man:/usr/contrib/man/%L:/usr/contrib/man:/usr/local/man/%L:/usr/local/man:/opt/ipf/man:/opt/ldapux/share/man/%L:/opt/ldapux/share/man:/opt/ldapux/ypldapd/man:/opt/sec_mgmt/share/man:/usr/dt/share/man:/opt/samba/man:/opt/samba/WTEC_Support_Tools/man:/opt/dsau/man:/opt/resmon/share/man/%L:/opt/gnome/man:/opt/openssl/man:/opt/openssl/prngd/man:/opt/wbem/share/man:/opt/VRTS/man:/opt/graphics/common/man:/opt/perl/man:/opt/sec_mgmt/share/man/%L:/opt/ssh/share/man:/opt/gwlm/man/%L:/opt/gwlm/man:/opt/ignite/share/man/%L:/opt/ignite/share/man:/usr/local/samba/man:/opt/samba/cfsm_man:/opt/cifsclient/share/man:/opt/amgr/man:/opt/amgr/man/%L:/opt/drd/share/man/%L:/opt/drd/share/man:/opt/resmon/share/man:/usr/contrib/kwdb/share/man:/opt/perl_32/man:/opt/perl_64/man:/opt/sfmdb/pgsql/man:/opt/sfm/share/man:/opt/swm/share/man/%L:/opt/swm/share/man:/opt/swa/share/man/%L:/opt/swa/share/man
ORACLE_BASE=/opt/apps/oracle
ORACLE_HOME=/opt/apps/oracle/product/10.2
ORACLE_LPARGS=-Plp
ORACLE_LPPROG=lpr
ORACLE_PATH=.:/home/oracle:/opt/apps/bannerTEST/links:/opt/apps/bannerTEST/admin
ORACLE_SID=TESTDB
ORAENV_ASK=
PATH=.:/opt/apps/bannerTEST/general/exe:/opt/apps/bannerTEST/admin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/perl/bin:/opt/ipf/bin:/opt/hparray/bin:/opt/nettladm/bin:/opt/fcms/bin:/usr/bin/X11:/opt/sec_mgmt/bastille/bin:/opt/dsau/bin:/opt/dsau/sbin:/opt/resmon/bin:/opt/gnome/bin:/usr/contrib/kwdb/bin:/opt/wbem/bin:/opt/wbem/sbin:/opt/graphics/common/bin:/opt/sec_mgmt/spc/bin:/opt/ssh/bin:/opt/hpsmh/bin:/opt/upgrade/bin:/opt/gwlm/bin:/opt/ignite/bin:/usr/contrib/bin/X11:/opt/sas/bin:/opt/atok/bin:/opt/drd/bin:/opt/firefox:/opt/mozilla:/opt/perl_32/bin:/opt/perl_64/bin:/opt/sfm/bin:/opt/swm/bin:/opt/swa/bin:/opt/thunderbird:/opt/microfocus/cobol/bin:/opt/apps/oracle/product/10.2/bin:/opt/apps/bannerTEST/links
PWD=/opt/apps/oracle/admin/TESTDB/bdump
SFTP_PERMIT_CHMOD=1
SFTP_PERMIT_CHOWN=1
SFTP_UMASK=
SHELL=/usr/bin/ksh
SSH_CLIENT=10.1.7.206 9238 22
SSH_CONNECTION=10.1.7.219 9238 192.168.30.15 12
TERM=xterm
TZ=MST7MDT
USER=oracle
WINDOWID=4184318
_=/usr/bin/env

oracle@server1$
oracle@server1$
oracle@server1$vi /etc/hosts
# Configured manually by root on JAN.31, 2003.  The form for each entry
# is:  <IP Address>     <Official Host Name>    <Aliases>
# For Example:
# See the hosts(4) manual page for more information.
# Note: The entries cannot be preceded by a space.
#       The format described in this file is the correct format.
#       The original Berkeley manual page contains an error in
#       the format description.
#
# THIS SERVER
#HOST SERVERS
192.168.20.16   server1                 # server1

#End Of Hosts File

Re: create DATABASE LINK [message #447249 is a reply to message #447245] Fri, 12 March 2010 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
please do as follows

cat /opt/apps/oracle/product/10.2/network/admin/tnsnames.ora
ls -ltr /opt/apps/oracle/product/10.2/network/admin/
mv /opt/apps/oracle/product/10.2/network/admin/sqlnet.ora /opt/apps/oracle/product/10.2/network/admin/sqlnet.ora.bak
tnsping REMOTE
cp /opt/apps/oracle/product/10.2/network/admin/sqlnet.ora.bak /opt/apps/oracle/product/10.2/network/admin/sqlnet.ora
Re: create DATABASE LINK [message #447250 is a reply to message #447067] Fri, 12 March 2010 12:50 Go to previous message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
BlackSwan,

Thanks a ton for your concern.

It was all because the database entry was not present in the server1 tnsnames.ora file.

I included it now and i am able to create and use the DB links.

Thank you very much.


-
Munna
Previous Topic: setting Default Listener
Next Topic: setup connect-time failover
Goto Forum:
  


Current Time: Wed Dec 17 20:38:02 CST 2014

Total time taken to generate the page: 0.11002 seconds