Hi guys,
I'm getting confused with the steps involved in this because the Oracle documentation is sub-par ("HS_FDS_CONNECT_INFO=dns" is not an adequate example) and different blogs/forum posts saying different things.
I'm trying to connect from Oracle 11.2.0.3 on Linux(64) to a SQL Server 2008 R2 on x64 with a database named DBA_Housekeeping. Does anybody have a clear guide of the steps involved (and where)? Can anyone see what I'm doing wrong here?
Oracle hostname: gblabl76
SQL Server hostname: UKAHES066
All steps I've done are on the Oracle server. I haven't done anything on the SQL Server (am I supposed to install a client/drivers on there?):
1. created initDBA_Housekeeping.ora in $ORACLE_HOME/hs/admin/
HS_FDS_CONNECT_INFO = "UKAHES066\\UKAHES066.DBA_Housekeeping"
HS_FDS_TRACE_LEVEL = OFF
#HS_FDS_SHAREABLE_NAME =
2. Added something to listener.ora on the oracle server & then started the listener (said state UNKNOWN):
MSSQL = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = gblabl76)(PORT = 1523)))
SID_LIST_MSSQL =
(SID_LIST =
(SID_DESC =
(SID_NAME = DBA_Housekeeping)
(ORACLE_HOME = /oracle/product/11.2.0.3/db_1)
(PROGRAM = dg4odbc)
)
)
3. Added a tnsnames entry:
DBA_Housekeeping =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(CONNECT_DATA = (SID = DBA_Housekeeping))
(HS=OK)
)
)
4. Created a login on the SQL Server instance defaulting to this database. Then I created a database link in Oracle:
CREATE PUBLIC DATABASE LINK DBA_Housekeeping CONNECT TO "hkadm" IDENTIFIED BY "hkadm" USING 'DBA_Housekeeping';
But when I attempt to query the database over a db link I get the error listed. Can anybody see where I'm going wrong?
Do I need to install drivers on the Windows server (and does this require a reboot?)?
Do I need to create this DNS thing or is the string in the hs .ora enough?
Thanks for any guidance :s I'm hoping to connect to our SQL Server instances so they can be monitored in APEX.
Mike