error ORA-02019: while selecting table from oracle10g to SQL Server 2005
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 11

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

Hybrid View

  1. #1
    Join Date
    Apr 2009
    Posts
    51

    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

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

  3. #3
    Join Date
    Apr 2009
    Posts
    51

    ora-12154

    Quote Originally Posted by vnktummala View Post
    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

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    ))(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.

  5. #5
    Join Date
    Apr 2009
    Posts
    51
    Quote Originally Posted by vnktummala View Post
    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

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

  7. #7
    Join Date
    Dec 2002
    Posts
    60
    Try creating inithsoledb.ora file instead of ODBC version.

  8. #8
    Join Date
    Dec 2002
    Posts
    60
    Are you running two listeners in the same port 1521?. Either change port of one listener or remove one listener all together.

  9. #9
    Join Date
    Apr 2009
    Posts
    51

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

  10. #10
    Join Date
    Dec 2002
    Posts
    60
    You need to copy the entire entry and change port


    LISTENERMYSQLSERVERDSN =
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST= hostname of oracle database)(PORT=1522))
    (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width