-
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
-
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..
-
Actually, it is installed in that db_1 directory, so that should be correct. But I'll try that, just in case!
- E
-
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
-
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
-
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
-
can you post the output of
select * from dba_db_links;
-
make sure hstest is defined in the TNSNAMES.ORA of your database's $OH.
Jeff Hunter
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|