Heterogeneous Services on 10g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Heterogeneous Services on 10g

Hybrid View

  1. #1
    Join Date
    Jul 2004
    Posts
    9

    Angry Heterogeneous Services on 10g

    Hello all -

    I've been trying to configure Heterogeneous Services to create a connection between an Oracle 10g database & a SQL Server database in my sandbox environment.

    I went through the process of:


    1) Creating a System DSN to the SQL Server database. The DSN connects to the database successfully when tested. The DSN name is HSTEST.


    2) Creating the InitHS file (named initHSTEST.ora), here are the results:

    # This is a sample agent init file that contains the HS parameters that are
    # needed for an ODBC Agent.

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO = HSTEST
    HS_FDS_TRACE_LEVEL = OFF


    #
    # Environment variables required for the non-Oracle system
    #
    #set =


    3) Made adjustments to listener.ora. Here are the modifications:

    LISTENERHSTEST =
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=thk10g)(PORT=1521))
    )

    SID_LIST_LISTENERHSTEST=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=HSTEST)
    (ORACLE_HOME = c:\oracle\product\10.1.0\db_1)
    (PROGRAM=hsodbc)
    )
    )




    4) Made adjustments to tnsnames.ora. Here are the modifications:

    HSTEST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = thk10g)(PORT = 1521)
    )
    (CONNECT_DATA=
    (SID=HSTEST)
    )
    (HS=OK)
    )


    I restarted both of my listeners without a problem, but when I try tnsping on the Oracle server, here's what I get:

    C:\>tnsping hstest

    TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 03-MAY-2
    005 12:49:49

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

    Used parameter files:

    TNS-03505: Failed to resolve name




    SQL Server and Oracle are running on the same machine. I don't have an issue with tnsping on any of the other Oracle databases. What I'm trying to figure out is if I have done anything incorrectly on the HS configuration.

    If I haven't done anything incorrectly, is the Transparent Gateway an option? And can I download the Transparent Gateway from OTN for development use?

    All help will be sincerely appreciated!


    - E

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    Everything looks okay in general.

    Look at your ORACLE_HOME in the listener.ora

    It points to ORACLE_HOME = c:\oracle\product\10.1.0\db_1

    remove the db_1. I assume you installed it in the 10.1.0 directory..

  3. #3
    Join Date
    Jul 2004
    Posts
    9
    Actually, it is installed in that db_1 directory, so that should be correct. But I'll try that, just in case!


    - E

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Hmmm. From the error it looks like thk10g is not in your DNS or HSTEST is not in the correct TNSNAMES.ORA file. I would check to make sure all your copies of TNSNAMES.ORA (especially in your $OH) contain the alias you are trying to connect to.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jul 2004
    Posts
    9
    So I thought about that comment about the db_1 directory for a second & it hit me. I also have Oracle Warehouse Builder installed in that sandbox, which is in the db__2 directory. OWB also has its own tnsnames.ora file.

    I put the SID entry into the OWB tnsnames.ora file & presto, I can do a tnsping without an issue!

    C:\Documents and Settings\Administrator>tnsping hstest

    TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 03-MAY-2
    005 15:22:32

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

    Used parameter files:


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = thk10g)(
    PORT = 1521)) (CONNECT_DATA= (SID=HSTEST)) (HS=OK))
    OK (70 msec)



    Thanks for the help!


    - E

  6. #6
    Join Date
    Jul 2004
    Posts
    9
    So now I have one other question:

    I created a database link using the new SID using the following command:

    create database link hstest connect to sa identified by XXXXXXX using 'HSTEST';



    I then tried to query that database link, and I get the following error through SQL+Plus:

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


    Any clues?


    - E

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    can you post the output of
    select * from dba_db_links;

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    make sure hstest is defined in the TNSNAMES.ORA of your database's $OH.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jul 2004
    Posts
    9
    Here are the results of select * from dba_db_links:


    OWNER
    ------------------------------
    DB_LINK
    --------------------------------------------------------------------------------
    USERNAME
    ------------------------------
    HOST
    --------------------------------------------------------------------------------
    CREATED
    ---------
    EKAUFFMAN
    HSTEST
    SA

    OWNER
    ------------------------------
    DB_LINK
    --------------------------------------------------------------------------------
    USERNAME
    ------------------------------
    HOST
    --------------------------------------------------------------------------------
    CREATED
    ---------
    hstest
    03-MAY-05


    OWNER
    ------------------------------
    DB_LINK
    --------------------------------------------------------------------------------
    USERNAME
    ------------------------------
    HOST
    --------------------------------------------------------------------------------
    CREATED
    ---------
    PUBLIC
    PUBS_TEST
    OWB_REPOS

    OWNER
    ------------------------------
    DB_LINK
    --------------------------------------------------------------------------------
    USERNAME
    ------------------------------
    HOST
    --------------------------------------------------------------------------------
    CREATED
    ---------
    (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = thk10g) (PORT
    = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl ))(HS = OK))
    02-MAY-05

    OWNER
    ------------------------------
    DB_LINK
    --------------------------------------------------------------------------------
    USERNAME
    ------------------------------
    HOST
    --------------------------------------------------------------------------------
    CREATED
    ---------


    Ignore the 2nd record - that was my playing around with something in OWB.



    - E

  10. #10
    Join Date
    Oct 2002
    Posts
    807
    See Note 234517.1 on metalink.

    ORA-12154:
    SQL> select * from all_catalog@demo;
    select * from all_catalog@demo
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve service name

    Resolution:
    The database link uses a SQL*Net alias not specified
    in the TNSNAMES.ORA of the database server.
    Query the data dictionary to figure out the 'HOST'
    specified for the database link:
    select db_link, host from user_db_links;
    or
    select db_link, host from dba_db_links;
    The 'HOST' value is the alias of the SQL*Net.
    Please make sure it exists in the TNSNAMES.ORA file
    present at the Oracle database server.

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