[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 803
  • Last Modified:

COnnectin to sqlserver from oracle

Hi

I am trying to connect sqlserver from Oracle using HS gateways. I configured listener,tnsnames.ora successfullyl Listerner also started succcessfuly

After that I created database link in Oracle , but when i am trying to connect to sqlserver from oracle, i am getting following error:

ORA_12154: TNS: COULD NOT RESOLVE THE cONNECT IDENTIFIER SPECIFIED

Please help me to fix this issue. Its very urgent


fyi:

I installed ORACLE HS gateway software on the machine where oracle client is installed and also on the same machine itself is Sqlserver database server.


Thx
Naren
0
knaren1975
Asked:
knaren1975
1 Solution
 
niazCommented:
Do you have more then one Oracle installed on your system? if so, please make sure that you have configure your tnsnames.ora file for the right Oracle Install.

Please make sure that your parenthesis are matched when you edited your tnsnames.ora and listener.ora files.

You can use tnsping command from command prompt to verify the connectivity and your TNS Service Name Descriptor. This will also give the path for the TNS_ADMIN folder in use.

Please post your listener.ora and tnsnames.ora files.
0
 
knaren1975Author Commented:
Hi Niaz,

Please find the listener and tnsnames.ora files below:

# listener.ora Network Configuration File: F:\app\product\11.1.0\client_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENERMYSQLSERVERDSN =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENERMYSQLSERVERDSN=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=testfeed)
         (ORACLE_HOME = F:\app\product\11.1.0\client_1)
         (PROGRAM=dg4odbc)
       )
      )

ADR_BASE_LISTENERMYSQLSERVERDSN = F:\product\11.2.0\tg_1

TNSNAME.ORA
=============

MYSQLSERVERDSN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNUITY = tcp.world)(PROTOCOL = TCP)(HOST = <oraclehostname>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ismfeed)
      (HS=OK)
    )
  )


I checked tnsping and listener they are fine.

thx
Naren
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
OP_ZaharinCommented:
Can u share with us your tnsnames settings? You can find the file on your ORACLE HS gateway machine on <drive>\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora

As per below tnsnames sample, you should use "MYDB" as the databse host string.

MYDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.23.150.33)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MYPRODDB)
    )
  )
0
 
knaren1975Author Commented:
Hi

In the hostname of TNS entry do we need to give the Oracl client Hostname or sqlserver database hiostname. pls clarify

thx
Naren
0
 
OP_ZaharinCommented:
Hi knaren,

In the tnsnames, you need to give the Oracle client hostname.
0
 
knaren1975Author Commented:
Hi

I used servername where oracleclient is installed  as hostname in tnsnames.ora after changing i am getting the following error :

ORA-12518: TNS:listener could not hand off client connection

thx
Naren
0
 
knaren1975Author Commented:
Hi

pls let mwe know In TNS Listener under oracl_home, do we need to give the home  directory name of Oracle Gate way or client home directory?

thx
Naren
0
 
OP_ZaharinCommented:
1-Check whether your listener version is compatible with the database. Use same or higher version of listener.

2-Set following parameter in listener.ora and restarted listener:
DIRECT_HANDOFF_TTC_LISTENER=OFF

3-Check the DB Processes:
select * from v$resource_limit where resource_name='processes';

RESOURCE_NAME | CURRENT_UTILIZATION | MAX_UTILIZATION | INITIAL_ALLO | LIMIT_VALUE
processes             |                22                      |               34             |       150               |      150

alter system set processes=300 scope=spfile;

0
 
OP_ZaharinCommented:
For Oracle Home, use the location of your Oracle home directory.
eg: c:\oracle\product\11.1.0\client_1
0
 
knaren1975Author Commented:
Hi Zaharin

My oracle client version is 11.1.0 where as gateway version is 11.2.0 is that causing issue?

thx
Naren
0
 
knaren1975Author Commented:
tje follwoing is my listener file

# listener.ora Network Configuration File: F:\app\product\11.1.0\client_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENERMYSQLSERVERDSN =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = loncsdbdion1.uk.db.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENERMYSQLSERVERDSN=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=ismfeed)
         (ORACLE_HOME = F:\app\product\11.1.0\client_1)
         (PROGRAM=dg4odbc)
       )
      )

ADR_BASE_LISTENERMYSQLSERVERDSN = F:\product\11.2.0\tg_1

0
 
OP_ZaharinCommented:
Hi knaren,

client version must always same or newer than the database server
0
 
knaren1975Author Commented:
Hi

Atlast i am able to fix the issue. thanks to all for your help

THx
Naren
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now