error ORA-02019: while selecting table from oracle10g to SQL Server 2005

Hi all,

our oracle 10g database is on AIX 5.2 and i configures heterogeneous service and install oracle 10g cleint on windows 2003 server where SQL Server reside . i configured the following things but i got the error *ORA-02019*. please help me its very urgent. quick response will higly appreciated. thanks alot in advance.

1)create Sytem DSN (mysqlserver) by selectin SQL Native cleint
2)copy the existing entry and paste it in listener.ora file and edit it as below
LISTENERMYSQLSERVERDSN =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST= hostname of oracle database)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_ =
(SID_LIST=
(SID_DESC=
(SID_NAME= mysqlserver)
(ORACLE_HOME = /u01/app/oracle/product/101)
(PROGRAM=hsodbc)
)
)
$lsnrctl status LISTENERMYSQLSERVERDSN

It is working fine

3) configure TNAMES.ora

mysqlserver =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 100.100.50.5)
(PORT = 1521)
)
(CONNECT_DATA =
(SID = mysqlserver)
(HS = OK)
)
)

c:>tnsping mysqlserver

it is workin fine


4)copy inithsodbc.ora and make it initmysqlserver.ora


HS_FDS_CONNECT_INFO = mysqlserver
HS_FDS_TRACE_LEVEL = off


4)create the database link

sql>create database link mysqlserver
connect to "sa" identified by "nsp@123" using 'mysqlserver';


when i selecting SQL Server object from oracle i got below error

SQL> select * from NSP_products@mysqlserver;
select * from NSP_products@mysqlserver
*
ERROR at line 1:
ORA-02019: connection description for remote database not found


pLease help me its very urgent.


BEST REGARD

If it is so urgent and impacting business, raise a P1 SR with Oracle.

Quote:

---

SQL> select * from NSP_products@mysqlserver;
select * from NSP_products@mysqlserver
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

---

make sure you are executing the above query from the schema in where you created the db link.

Thanks,

Try creating inithsoledb.ora file instead of ODBC version.

Quote:

---

Originally Posted by vnktummala

If it is so urgent and impacting business, raise a P1 SR with Oracle.



make sure you are executing the above query from the schema in where you created the db link.

Thanks,

---

when i check status of listener is working fine

$ lsnrctl status LISTENERMYSQLSERVERDSN

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production on 11-MAY-2010 07:48:10

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENERMYSQLSERVERDSN
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production
Start Date 10-MAY-2010 13:12:29
Uptime 0 days 18 hr. 35 min. 41 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/101/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/101/network/log/listenermysqlserverdsn.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=8080))(Presentation=HTTP)(Sessio n=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=2100))(Presentation=FTP)(Session =RAW))
Services Summary...
Service "mysqlserver" has 1 instance(s).
Instance "mysqlserver", status UNKNOWN, has 1 handler(s) for this service...
Service "yscc02.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
Service "yscc02XDB.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
The command completed successfully

c:>tsnping mysqlserver

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 11-MAY-2
010 09:22:18

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

Used parameter files:
E:\Oracle\product\10.1.0\Client_2\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 100.100
.50.5) (PORT = 1521)) (CONNECT_DATA = (SID = mysqlserver) (HS = OK)))
OK (20 msec)





SQL> show user
USER is "NEW"


SQL>create database link mysqlserver
connect to "sa" identified by "nsp@123" using 'MYSQLSERVER';


But when i execute the select statement its giving below error

SQL> select * from NSP_products@mysqlserver;
select * from NSP_products@mysqlserver
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


where NSP_product is SQL Server Database table

any suggestin or help.thanks in advance for quick reply.


Best Regards

Quote:

---

))(Presentation=FTP)(Session =RAW))
Services Summary...
Service "mysqlserver" has 1 instance(s).
Instance "mysqlserver", status UNKNOWN, has 1 handler(s) for this service...
Service "yscc02.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
Service "yscc02XDB.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
The command completed successfully

---

Listener is not supporting the service that you are refering.

In the tnsnames change SID=mysqlserver to SERVICE_NAME=mysqlserver, restart the listener, and post the output of "lsnrctl services"

Thanks,

Quote:

---

Originally Posted by vnktummala

Listener is not supporting the service that you are refering.

In the tnsnames change SID=mysqlserver to SERVICE_NAME=mysqlserver, restart the listener, and post the output of "lsnrctl services"

Thanks,

---

yes i changed to SID to SERVICE_NAME


C:\>tnsping mysqlserver

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 11-MAY-2
010 13:37:42

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

Used parameter files:
E:\Oracle\product\10.1.0\Client_2\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 100.100
.50.5) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=mysqlserver) (HS = OK)))
OK (10 msec)




$ lsnrctl service LISTENERMYSQLSERVERDSN

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production on 11-MAY-2010 13:08:49

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521))
Services Summary...
Service "mysqlserver" has 1 instance(s).
Instance "mysqlserver", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "yscc02.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
Service "yscc02XDB.ysccapps.yamama.com" has 1 instance(s).
Instance "yscc02", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ysccapps.yamama.com)(PORT=32779))
The command completed successfully


Still showing the same error

SQL> select * from NSP_products@mysqlserver;
select * from NSP_products@mysqlserver
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified



PLease check my complete listener.or file# listener.ora Network Configuration File: /u01/app/oracle/product/101/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = yscc02)
(ORACLE_HOME = /u01/app/oracle/product/101)
(PROGRAM = extproc)
)
)


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ysccapps.yamama.com)(PORT = 1521))
)
)
)


LISTENERMYSQLSERVERDSN =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST= 100.100.50.5)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERMYSQLSERVERDSN =
(SID_LIST=
(SID_DESC=
(SID_NAME= mysqlserver)
(ORACLE_HOME = /u01/app/oracle/product/101)
(PROGRAM=hsodbc)
)
)



please any suggetion or help for. thanks in advance.


BEST REGARDS

Quote:

---

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=100.100.50.5)(PORT=1521))
Services Summary...
Service "mysqlserver" has 1 instance(s).
Instance "mysqlserver", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):

---

your service is still not supported by the lister. you need to look into multiple things.

check if you are able to connect to the DB from remote machine.

check SERVICE_NAMES parameter and make sure that your service is listed out there

register your service with database.

and so on ....

It is a common TNS problem and solution depends on situation to situation. You need to put some effort, google for more information, and do some R&D to fix the issue.

Thanks,

also make sure IP 100.100.50.5 is in the hosts file with proper host name.

Thanks,

Are you running two listeners in the same port 1521?. Either change port of one listener or remove one listener all together.

Hi shkahnan,

thanks for you quick response.do i need to chnage port number of new listener that is here in our case LISTENERMYSQLSERVERDSN .

do i need to copy entire existing listener to make new one or just copy the below part of listener.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = yscc02)
(ORACLE_HOME = /u01/app/oracle/product/101)
(PROGRAM = extproc)
)
)