Home » RDBMS Server » Networking and Gateways » error ORA-02019: while selecting table from oracle10g to SQL Server 2005 (oracle 10.1.0.2.0 on IBM AIX 5.2 ,SQL server on window 2003)
error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455124] Mon, 10 May 2010 06:18 Go to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member




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





Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455165 is a reply to message #455124] Mon, 10 May 2010 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You have two systems, AIX & Windows.

It is not clear to me when you post files or commands & results from which system on which they occur.

What is IP# for both systems?

>$lsnrctl status LISTENERMYSQLSERVERDSN
>It is working fine

>c:>tnsping mysqlserver
>it is workin fine

use COPY & PASTE so we can see for ourselves the results

Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455235 is a reply to message #455165] Mon, 10 May 2010 13:24 Go to previous messageGo to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member


Hi Blackswan,

thanks for your quick response.

yes we have two system one IBM AIX 5.2 and second one is windows 2003.


1)ON IBM AIX Machine Ip Address is100.100.50.5 ,ORACLE 10g DATABASE is there.


2)ON WINDOWS 2003 Server IP address is 172.16.11.18, Microsoft SQL Server 2005 installed it, ORACLE 10g CLEINT is installed on it




$lsnrctl status LISTENERMYSQLSERVERDSN

it is working fine

c:>tnsping mysqlserver

it is working fine

NSP_products is SQL server database table.



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


pLease help me its very urgent.


BEST REGARD





[Updated on: Mon, 10 May 2010 13:33]

Report message to a moderator

Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455291 is a reply to message #455165] Tue, 11 May 2010 00:07 Go to previous messageGo to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member



Hi Blackswan,

thanks for your quick response.

yes we have two system one IBM AIX 5.2 and second one is windows 2003.


1)ON IBM AIX Machine Ip Address is100.100.50.5 ,ORACLE 10g DATABASE is there.


2)ON WINDOWS 2003 Server IP address is 172.16.11.18, Microsoft SQL Server 2005 installed it, ORACLE 10g CLEINT is installed on it

$ 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)(Session=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:>tnsping mysqlserver



TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 11-MAY-
010 07:51:32

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.10
.50.5) (PORT = 1521)) (CONNECT_DATA = (SID = mysqlserver) (HS = OK)))
OK (0 msec)



FROM Database10g side (IBM AIX side)

SQL> show user
USER is "NEW"



I created Database link

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

Database link created


Whwn i fire a select statement 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 help me .thanks a lot for pervius reply






Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455356 is a reply to message #455165] Tue, 11 May 2010 05:56 Go to previous messageGo to next message
oradba123
Messages: 86
Registered: June 2009
Location: india
Member


Hi Blackswan,

thanks for your quick response.

yes we have two system one IBM AIX 5.2 and second one is windows 2003.


1)ON IBM AIX Machine Ip Address is100.100.50.5 ,ORACLE 10g DATABASE is there.


2)ON WINDOWS 2003 Server IP address is 172.16.11.18, Microsoft SQL Server 2005 installed it, ORACLE 10g CLEINT is installed on it

Please see my listener.ora file on AIX machine

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

>$lsnrctl status LISTENERMYSQLSERVERDSN
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Production on 11-MAY-2010 13:46:46

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 11-MAY-2010 13:05:07
Uptime 0 days 0 hr. 41 min. 39 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)(Session=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:\>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)


Stil aim getting the sam 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



any suggetion or help for. thanks in advance


BEST REGARDS




Re: error ORA-02019: while selecting table from oracle10g to SQL Server 2005 [message #455425 is a reply to message #455356] Tue, 11 May 2010 09:05 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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)
>

I am not an expert on ODBC & last I actually used it was over a decade ago.
I would have expected the above to show HOST=172.16.11.18 since that is where SQL*Server resides.

So I am not surprised SERVICE_NAME=mysqlserver is not found on HOST = 100.100.50.5
Previous Topic: ORA-12203 error.
Next Topic: Client to server connection
Goto Forum:
  


Current Time: Fri Mar 29 03:06:49 CDT 2024