-
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.
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Try creating inithsoledb.ora file instead of ODBC version.
-
ora-12154
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
-
))(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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
also make sure IP 100.100.50.5 is in the hosts file with proper host name.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Are you running two listeners in the same port 1521?. Either change port of one listener or remove one listener all together.
-
ora-12154
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)
)
)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|