Link to home
Start Free TrialLog in
Avatar of knaren1975
knaren1975

asked on

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
Avatar of niaz
niaz
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of Pratima
Pratima
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of knaren1975
knaren1975

ASKER

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
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)
    )
  )
Hi

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

thx
Naren
Hi knaren,

In the tnsnames, you need to give the Oracle client hostname.
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
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
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;

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

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

thx
Naren
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

Hi knaren,

client version must always same or newer than the database server
Hi

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

THx
Naren