Heterogeneous Services on 10g

[ericdkauff]

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

[Axr2]

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

[ericdkauff]

Actually, it is installed in that db_1 directory, so that should be correct. But I'll try that, just in case!


- E

[marist89]

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.

[ericdkauff]

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

[ericdkauff]

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

[Axr2]

can you post the output of
select * from dba_db_links;

[marist89]

make sure hstest is defined in the TNSNAMES.ORA of your database's $OH.

[ericdkauff]

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

[Axr2]

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.